java - org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2

Question

I decided to modify my application by adding there authentication using spring security and database. Before I was using plain authentication with user and password in XML. It worked fine.

My authentication-manager looks like this

<authentication-manager>
    <authentication-provider>
        <jdbc-user-service data-source-ref="dataSource"
        users-by-username-query="select username, password from pmc.username_password where username=?;"
        authorities-by-username-query="select a.username, b.role from pmc.username_password a, pmc.username_role b where a.username = b.username and a.username=?;" />
    </authentication-provider>
</authentication-manager>

But when I make an attempt to authenticate I've got exception

org.springframework.security.authentication.InternalAuthenticationServiceException: PreparedStatementCallback; SQL [select u
sername, password from pmc.username_password where username=?;]; The column index is out of range: 3, number of columns: 2.;
 nested exception is org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.

What is wrong in my sql syntax in XML file?

Answer 1

Spring Security expects three columns from the user query, in order:

1. username
2. password
3. enabled (boolean)

You don't have the last. If you don't have have an equivalent to "enabled" in your database, you can use a TRUE constant.