CODEWITHSUNDEEP
spring-securityspring-jdbc
Gerald Petron
Gerald Petron

Reputation: 31

Using spring-security: WebSecurityConfigurerAdapter: auth.jdbcAuthentication().usersByUsernameQuery: what should the query string look like?

Using postgresql db with two tables to authenticate login ...(users and authorities)

CREATE TABLE users
(
   username character(50) NOT NULL,
   password character(50) NOT NULL,
   enabled boolean NOT NULL,
   CONSTRAINT users_pkey PRIMARY KEY (username)
)

CREATE TABLE authorities
(
    username character(50) NOT NULL,
    authority character(50) NOT NULL,
    CONSTRAINT fk_authorities_users FOREIGN KEY (username)
       REFERENCES users (username) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)

What am I doing wrong when I try to do the following?

registerAuthentication(AuthenticationManagerBuilder auth){

auth.
      jdbcAuthentication()
            .dataSource(dataSource)
            .usersByUsernameQuery("select username,password,enabled 
                 from users where username = ?")
            .authoritiesByUsernameQuery("select username,authority from 
                 authorities where username = ?");

}

Thanks

Upvotes: 2

Views: 11136

Answers (1)

radovana
radovana

Reputation: 81

Try this:

auth.jdbcAuthentication()                
    .dataSource(dataSource)                
    .usersByUsernameQuery("select username as principal, password as credentials, true from users where username = ?")               
    .authoritiesByUsernameQuery("select username as principal, authority as role from authorities where username = ?")                
    .rolePrefix("ROLE_");

May be this source code can help you: https://github.com/spring-projects/spring-security-javaconfig/blob/master/spring-security-javaconfig/src/test/groovy/org/springframework/security/config/annotation/authentication/NamespaceJdbcUserServiceTests.groovy

Upvotes: 8

Related Questions