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
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!
	
	
	
	
	


	
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

2 1 vote
Article Rating
Subscribe
Notify of
guest
1.2K Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments