JDBC身份验证中的PreparedStationCallback错误

Question

我正在进行JDBC身份验证，当我尝试访问API时，它会在不询问用户名和密码的情况下给出“禁止”。

这是我的安全配置。

@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=?

当我给出正确的身份验证时，我无法登录，错误是，

"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.",

模型实体为：

@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;
}

数据库中的条目是
用户表
user_id名密码
1 jack

角色表
role_id角色
1 ADMIN
2 USER
3 LEAD

user_role表
user_idrole_id
1 1
1 2
1 3

Answer 1

根据JDBCUserDetailsManager配置器。usersByUsernameQuery javadoc，必须提供一个查询：

用于选择用户名、密码以及用户是否通过用户名启用。用户名必须包含单个参数。

似乎您只选择用户名和密码，添加第三列到您的选择（如果用户总是启用，则为true）

spring.queries.users-查询=选择名称，密码，真从用户名=？

希望能有帮助。