PreparedStatmentCallBack error in JDBC authentication

Question

I am working on JDBC authentication and when i try to access an API it gives "FORBIDDEN" without asking user name and password.

Here is my security configuration.

@Configuration
@EnableGlobalMethodSecurity(prePostEnabled = true)
@EnableWebSecurity(debug = true)
static class SecurityConfiguration extends WebSecurityConfigurerAdapter {

    @Autowired
    private DataSource dataSource;

    @Value("${spring.queries.users-query}")
    private String usersQuery;

    @Value("${spring.queries.roles-query}")
    private String rolesQuery;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {

        auth.
        jdbcAuthentication()
            .usersByUsernameQuery(usersQuery)
            .authoritiesByUsernameQuery(rolesQuery)
            .dataSource(dataSource);
    }


    @Override
    protected void configure(HttpSecurity http) throws Exception {

        http.httpBasic().and().authorizeRequests().//
                antMatchers(HttpMethod.GET, "/vendor/**", "/usermanagement").hasRole("USER").//
                antMatchers(HttpMethod.POST, "/vendor","/usermanagement").hasRole("LEAD").//
                antMatchers(HttpMethod.PUT, "/vendor/**", "/usermanagement").hasRole("LEAD").//
                antMatchers(HttpMethod.DELETE, "/vendor/**", "/usermanagement").hasRole("ADMIN").and().//
                csrf().disable();
    }
}

spring.queries.users-query=select name, password, active from users where name=?
spring.queries.roles-query=select u.name, r.role from users u inner join user_role ur on(u.user_id=ur.user_id) inner join role r on(ur.role_id=r.role_id) where u.name=?

When i give CORRECT authentication, i can not log in and the error is,

"message": "PreparedStatementCallback; SQL [select name, password from users where name=?]; 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.",

Model Entity is:

@Entity
@Table(name = "role")
public class RoleEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="role_id")
    private int id;

    @Column(name="role")
    private String role;
}

@Entity
@Table(name = "users")
public class UserEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="user_id")
    private int id;

    private String password;

    private String name;

    private boolean active;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<RoleEntity> roles;
}

the entries in database i have is
Users table
user_id name password
1 jack jack

Role table
role_id role
1 ADMIN
2 USER
3 LEAD

user_role table
user_id role_id
1 1
1 2
1 3

Answer 1

Acording with JdbcUserDetailsManagerConfigurer.usersByUsernameQuery javadoc, you must provide a query:

for selecting the username, password, and if the user is enabled by username. Must contain a single parameter for the username.

It seems that you are selecting only username and password, add a third column to your select (or true if the user are always enabled)

spring.queries.users-query=select name, password, true from users where name=?

Hope it helps.