How to use Spring Security JDBC Authentication with PostgreSQL & Spring Boot

In this tutorial, JavaSampleApproach uses PostgreSQL & Spring Boot to develop a Spring Security JDBC for Authentication.

Related Articles:
How to use Spring Security JDBC Authentication with MySQL & Spring Boot
Spring Security – Config Security for Web MVC by Spring Boot
Spring Security – Customize Login Handler
Spring Security Customize Logout Handler
Spring Security – Customize Authentication Provider


I. Technologies

– Java 1.8
– Maven 3.3.9
– Spring Boot: 1.5.6.RELEASE
– Spring Tool Suite – Version 3.8.1.RELEASE
– Postgresql

II. Overview
1. Project Structure

spring security jdbc authentication - project structure

2. URLs of Web Application

We create a MVC Web Application with 6 url:
– “/”, “/home”: access with everyone.
– “/welcome”: must authenticate and be accessed with user ROLE: USER or ADMIN.
– “/admin”: accessed by user with Role: Admin.
– “/login”: login page.
– “/403”: HTTP Error 403 Forbidden.

3. Step to do

– Create a Spring Boot project
– Create Controller
– Create View Pages
– Configure Database
– Configure WebSecurity
– Run & Enjoy Results

III. Practices
1. Create a Spring Boot project

Open Spring Tool Suite, File->New->Spring Starter Project: New Spring Starter Project is open, input info about project.

Press Next button, add needed dependencies:
– For Security, choose Core -> select: Security
– For Template Engines, choose Template Engines->select: Thymeleaf.
– For Database PostgreSQL, choose SQL->select: PostgreSQL & JDBC
– For Web MVC, choose Web -> select: Web
spring security jdbc authentication - dependecies
– Press Finish -> Spring Boot Project will create successfully.
Open pom.xml and check dependencies:

2. Create Controller

Create a simple MVC Controller with 6 url:
– “/”, “home” -> return: home.html page
– “/welcome” -> return: welcome.html page
– “/admin” -> return: admin.html page
– “/login” -> return: login.html page
– “/403” -> return 403.html page

3. Create View Pages

Create home.html page
Home page has a Button that navigates to Welcome Page.

Create WelcomePage: welcome.html
Welcome page is a protect by security, So we need login before go to Welcome.

– Create Admin Page: admin.html
Admin Page is accessed with Admin users.

Spring provides a default login pages, but we can customize a login.html page as below:

Create an access denied pages: 403.html
If an user try to access url but Not has permission, our web app will redirect to 403.html for notified message.

4. Configure Database

Open application.properties, configure database properties:

Create User table with 3 columns: username, password and enabled(used to active a user account).

Create user_roles:

Insert data to 2 tables:

We had created 2 active accounts: jack/jack with role: USER/ADMIN & peter/peter with role: USER

Users table
spring security jdbc authentication - usertable
User_Role table:
spring security jdbc authentication - user role table

5. Configure WebSecurity

– Create a SecurityConfig class that extends – WebSecurityConfigurerAdapter
– Override method: configAuthentication(AuthenticationManagerBuilder auth) to setup SQL queries for users & roles.
Override configure(HttpSecurity http) to customize http requests.

6. Run & Enjoy Results

Build & Run the project with Spring Boot App mode.
– Login with both accounts: Jack/Jack & Peter/Peter can access Welcome Page:
spring security jdbc authentication - welcome page
– Login with Jack/Jack account, can access Admin Page beacause Jack has ADMIN role.
spring security jdbc authentication - admin page
– But with Peter/Peter account, can NOT access Admin Page beacause Peter just has USER role only.
spring security jdbc authentication - denied page

IV. Sourcecode

SpringSecurityAuthentication

By JavaSampleApproach | February 9, 2017.

Last updated on August 27, 2017.


Related Posts


7 thoughts on “How to use Spring Security JDBC Authentication with PostgreSQL & Spring Boot”

  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:

      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

      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:

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

    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:

      is used to invoked AuthenticationManagerBuilder auth with jdbcAuthentication.

      Regards,
      JSA

Got Something To Say:

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

*