Spring Security – JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap

In the tutorial, JavaSampleApproach will show you how to create a Spring Security JDBC Authentication with SpringBoot + PostgreSQL + Bootstrap.

I. Technologies

– Apache Maven 3.5.2
– Spring Tool Suite – Version 3.9.0.RELEASE
– Spring Boot – 1.5.10.RELEASE
– Bootstrap
– PostgreSQL

II. Goal

We create a Spring MVC Web Application as below:

JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap - project structure

With 5 urls:

– ‘/’: access with everyone.

JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap - home page

– ‘/user’: must authenticate and be accessed with user ROLE {USER, ADMIN}

JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap - user page

– ‘/admin’: accessed by user with role Admin

JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap - admin page

– ‘/login’: login page

JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap - sign in

– ‘/403’: HTTP Error 403 Forbidden

JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap - access denied

We create 2 MySQL tables for 2 users (username/password):
– jack/jack has 2 roles {USER, ADMIN}
– peter/peter has 1 role USER

JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap - tables

-> jack/jack can access both pages {user.html, admin.html}. While peter/peter just accesses 1 page user.html.

III. Implementation

Step to do
– Create Spring Boot project
– Create Controller
– Create View Pages
– Configure Database
– Configure WebSecurity

1. Create Spring Boot project

Use SpringToolSuite to create a SpringBoot project with below dependencies:


	org.springframework.boot
	spring-boot-starter-jdbc


	org.springframework.boot
	spring-boot-starter-security


	org.springframework.boot
	spring-boot-starter-thymeleaf


	org.springframework.boot
	spring-boot-starter-web



	org.postgresql
	postgresql
	runtime

2. Create Controller
package com.javasampleapproach.springsecurity.jdbcauthentication.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
 
@Controller
public class WebController {
   
    @RequestMapping(value="/")
    public String home(){
        return "home";
    }
   
    @RequestMapping(value="/user")
    public String user(){
        return "user";
    }
  
    @RequestMapping(value="/admin")
    public String admin(){
        return "admin";
    }
   
    @RequestMapping(value="/login")
    public String login(){
        return "login";
    }
   
    @RequestMapping(value="/403")
    public String Error403(){
        return "403";
    }
}

3. Create View Pages
home.html



Security with Spring Boot


	

Hello, This is Home page!

User Page
Admin Page%MINIFYHTMLd6c1cfef5b5e3c7c5ed66cc8b35827df16%
user.html



Welcome Security with Spring Boot!


	

Hello, the page is for Users!

Home
admin.html



Welcome Security with Spring Boot!


	

Hello, the page is for Admin!

Home
login.html



	Welcome Security with Spring Boot!
	
	
	%MINIFYHTMLd6c1cfef5b5e3c7c5ed66cc8b35827df17%%MINIFYHTMLd6c1cfef5b5e3c7c5ed66cc8b35827df18%

	
Sign In

UserName or PassWord is wrong. Please check again!

Logged out.

403.html



Security with Spring Boot


	

Access is Denied!

Home
4. Configure Database

Open ‘application.properties’, configure database properties:

spring.datasource.url=jdbc:postgresql://localhost/testdb
spring.datasource.username=postgres
spring.datasource.password=123

Create ‘user’ table with 3 columns:

CREATE TABLE users(
   username varchar(20) NOT NULL,
   password varchar(20) NOT NULL,
   enabled boolean NOT NULL DEFAULT FALSE,
   primary key(username)
);

Create ‘user_roles’ table:

create table user_roles (
  user_role_id SERIAL PRIMARY KEY,
  username varchar(20) NOT NULL,
  role varchar(20) NOT NULL,
  UNIQUE (username,role),
  FOREIGN KEY (username) REFERENCES users (username)
);

Insert data to 2 tables:

INSERT INTO users(username,password,enabled) VALUES ('jack','jack', true);
INSERT INTO users(username,password,enabled) VALUES ('peter','peter', true);
 
INSERT INTO user_roles (username, role) VALUES ('jack', 'ROLE_USER');
INSERT INTO user_roles (username, role) VALUES ('jack', 'ROLE_ADMIN');
INSERT INTO user_roles (username, role) VALUES ('peter', 'ROLE_USER');
5. Configure WebSecurity
package com.javasampleapproach.springsecurity.jdbcauthentication.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
 
@Configuration
@EnableAutoConfiguration
public class SecurityConfig extends WebSecurityConfigurerAdapter {
 
	@Autowired
	DataSource dataSource;
 
	@Autowired
	public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
		auth.jdbcAuthentication().dataSource(dataSource)
				.usersByUsernameQuery("select username,password, enabled from users where username=?")
				.authoritiesByUsernameQuery("select username, role from user_roles where username=?");
	}
 
	@Override
	protected void configure(HttpSecurity http) throws Exception {
		http.authorizeRequests().antMatchers("/", "/home").permitAll().antMatchers("/admin").hasRole("ADMIN")
				.anyRequest().authenticated().and().formLogin().loginPage("/login").permitAll().and().logout()
				.permitAll();
		http.exceptionHandling().accessDeniedPage("/403");
	}
}

IV. SourceCode

SpringSecurityJDBCAuthenticationPostgreSQL



By grokonez | February 9, 2017.

Last updated on March 8, 2018.



Related Posts


13 thoughts on “Spring Security – JDBC Authentication – SpringBoot + PostgreSQL + Bootstrap”

  1. HI!

    I was trying to do your tutorial, but there was a problem. When i want to autowire the dataSource in the SecurityConfig, IDEA sais, that “Could not autowire. There is more than one bean of ‘DataSource’ type.” I tried to google the solution and found that if i exclude the datasource autoconfiguration from autoconfig, its ok for IDEA, but it looks, that the page isn’t working, it asks for authentication again, and again. Does’t allow me to sign in.

    Do you have any idea, whats wrong? I have a local postgreSQL database, which is created and tested the connection.

    Thanks!
    Mate

    1. Hi Mate,

      I had tested the attached sourcecode again and it works well!
      I also review the code, it is okay with:

      @Configuration
      @EnableAutoConfiguration
      public class SecurityConfig extends WebSecurityConfigurerAdapter {
       
      	@Autowired
      	DataSource dataSource;
      

      We use SpringToolSuite editor to create tutorial, please double check again your IDE.

      And you can download the attached sourcecode then try to build and run it with below commands:
      mvn clean install and mvn spring-boot:run

      Don’t forget to create 2 tables: users & user_roles then insert data to it with SQL scripts in the tutorial.

      Regards,

  2. Very nice tutorial.
    I have a nice question regarding mapping of /login to login.html.

    How application comes to know that he needs to invoke ‘login.html’ when he accesses ‘/login’ and gets ‘login’ String as view name?

    1. Hi,

      We have a segment code for configure HttpSecurity

      @Override
      	protected void configure(HttpSecurity http) throws Exception {
      		http.authorizeRequests().antMatchers("/", "/home").permitAll().antMatchers("/admin").hasRole("ADMIN")
      				.anyRequest().authenticated().and().formLogin().loginPage("/login").permitAll().and().logout()
      				.permitAll();
      		http.exceptionHandling().accessDeniedPage("/403");
      	}
      

      So when a user accesses security paths like: {“/admin”, “/welcome”}.
      The “/login” will always be redirected for asking to authenticate with every first time accessing.
      And also whenever the session is expired, “/login” url will be invoked.

      Regards,

  3. Exactly what I was looking for, thank you! But can you explain like I’m 5, why this:

    	@Autowired
    	public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
    		auth.jdbcAuthentication().dataSource(dataSource)
    			.usersByUsernameQuery("select username,password, enabled from users where username=?")
    			.authoritiesByUsernameQuery("select username, role from user_roles where username=?");
    	}
    

    will recognize new additions to the users table, even while spring-boot is running (no reboot needed), but this will not?

        @Autowired
        public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
            jdbcTemplate.query(
                "SELECT users,password FROM users",
                (rs, rowNum) -> new Guest(rs.getString("username"), rs.getString("password")))
                .forEach(guest -> {
                    try {
              auth.inMemoryAuthentication().withUser(guest.getUsername()).password(guest.getPassword()).roles("USER");
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                });
        }
    

    despite that both of these methods are ran only once when spring-boot starts up? There seems to be lots of behind-the-scenes operations that I’m not grasping. I get that in memory authentication is hardcoding and jdbc authentication is not, can someone explain what’s going on behind jdbcauthentication? When (or in which file) exactly is it querying the database again during runtime?

    It also doesn’t matter if you name the method “configAuthentication” or “configureGlobal”, it still gets called the same way/sequence? I’m quite confused on what exactly is it overriding (I don’t see the methods listed in WebSecurityConfigurerAdapter class) .

    1. Hello Emily,

      We have some usecase to work with SpringSecurity Provider:
      – If just for testing with some users, We can use Memory-Provider
      – If your users store in database, you can user JDBC-Provider
      – Spring Security also support LDAP-provider.

      We can use extends WebSecurityConfigurerAdapter for configure.

      Function:

      @Autowired
      public void configAuthentication(AuthenticationManagerBuilder auth) throws Exception {
      	auth.jdbcAuthentication().dataSource(dataSource)
      			.usersByUsernameQuery("select username,password, enabled from users where username=?")
      			.authoritiesByUsernameQuery("select username, role from user_roles where username=?");
      }
      

      is used to invoked AuthenticationManagerBuilder auth with jdbcAuthentication.

      Regards,
      JSA

    1. Hi Igor Lima,

      You can implement AuthenticationSuccessHandler as below sample:

      @Component
      public class CustomAuthenticationSuccessHandler  implements AuthenticationSuccessHandler {
       
      	private Logger logger = LoggerFactory.getLogger(this.getClass());
      	
      	@Override
      	public void onAuthenticationSuccess(HttpServletRequest request,
      			HttpServletResponse response, Authentication authentication)
      			throws IOException, ServletException {
              //set our response to OK status
              response.setStatus(HttpServletResponse.SC_OK);
              
              boolean admin = false;
              
              logger.info("AT onAuthenticationSuccess(...) function!");
              
              for (GrantedAuthority auth : authentication.getAuthorities()) {
                  if ("ROLE_ADMIN".equals(auth.getAuthority())){
                  	admin = true;
                  }
              }
              
              if(admin){
              	response.sendRedirect("/admin");
              }else{
              	response.sendRedirect("/welcome");
              }
      
      	}
      }
      

      More details at: Spring Security – Customize Login Handler

      Regards,
      JSA

  4. Hi sir I don’t know but when I add this part of your code

    @Autowired
    	DataSource dataSource;
    	
    	public void configAuthentication(AuthenticationManagerBuilder auth)throws Exception{
    		auth
    		.jdbcAuthentication().dataSource(dataSource)
    		.usersByUsernameQuery("select email,password from recruiter_user where email=?")
    		.authoritiesByUsernameQuery("select email, role from recruiter_user where email=?");
    	}
    
    It stopped working! Application runs but my first page stopped showing up
    
  5. 18:52:45.025 [localhost-startStop-1] ERROR org.springframework.web.context.Conte
    xtLoader – Context initialization failed
    org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating
    bean with name ‘securityConfiguration’: Unsatisfied dependency expressed throug
    h field ‘dataSource’; nested exception is org.springframework.beans.factory.NoSu
    chBeanDefinitionException: No qualifying bean of type ‘javax.sql.DataSource’ ava
    ilable: expected at least 1 bean which qualifies as autowire candidate. Dependen
    cy annotations: {@org.springframework.beans.factory.annotation.Autowired(require
    d=true)}

    I am running through servlet

    public class SpringMvcInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
     
        @Override
        protected Class[] getRootConfigClasses() {
            return new Class[] { CGRecruiterWebConfiguration.class };
        }
      
        @Override
        protected Class[] getServletConfigClasses() {
            return null;
        }
      
        @Override
        protected String[] getServletMappings() {
            return new String[] { "/" };
        }
     
    }
    
    @Configuration
    @EnableWebMvc
    @ComponentScan(basePackages = "com.bus.acquire.web")
    public class CGRecruiterWebConfiguration extends WebMvcConfigurerAdapter
    {
    	  @Override
    	  public void configureViewResolvers(ViewResolverRegistry registry)
    		{
    			InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
    			viewResolver.setViewClass(JstlView.class);
    			viewResolver.setPrefix("/WEB-INF/Views/");
    			viewResolver.setSuffix(".jsp");
    
    			registry.viewResolver(viewResolver);
    		}
    	  
    	  
    
    	  @Override
    	    public void addResourceHandlers(ResourceHandlerRegistry registry)
    	    {
    	        registry.addResourceHandler("/resources/**").addResourceLocations("/resources/");
    	    }
    }
    
    1. Hello,
      Very nice tutorials, please publish the tutorial on OAuth with Spring Boot having the database, preferably MySQL implementation.

      Thanks,
      Samir

  6. java.lang.IllegalStateException: Failed to load ApplicationContext
    Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ‘demoSecurityConfig’: Unsatisfied dependency expressed through field ‘securityDataSource’; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type ‘javax.sql.DataSource’ available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
    Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type ‘javax.sql.DataSource’ available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}

Got Something To Say:

Your email address will not be published. Required fields are marked *

*