Steps to Configure JDBCAuthentication in a Spring Application

Hey fellow developers, As it's clear from the title of this blog I will be guiding you through the steps involved in configuring the JDBCAuthentication in a Spring Application.

The purpose of JDBCAuthentication is to authenticate users against a database table and perform user login and authorization operations.

Without wasting any more words, let's jump in!

Step 1. Configure the Datasource Bean:

The preferred way is to configure the database connection properties in the ‘application.properties’ file and the Spring Boot will automatically create the Datasource bean based on the provided configuration. This way we can externalize the configuration and easily switch between different database environments without modifying the source code.

Here is an example of configuring the database properties in the application.properties file:

In case the database is PostgreSQL:

spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver

In the above configuration, you need to replace mydatabase, username, and password with the actual values corresponding to your PostgreSQL database.

Using this configuration, Spring Boot will automatically create and configure the ‘Datasource’ bean, and you can use it for JDBCAuthentication without explicitly defining the bean.

However, you need to ensure that you have the necessary JDBC driver dependency in your project’s build configuration(e.g. Maven or Gradle) so that Spring Boot can access the appropriate driver class. For example, In the case of PostgreSQL, if you’re using Maven, you can include the following dependency in your ‘pom.xml’ file.

<dependencies>
    <!-- Other dependencies -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.24</version>
    </dependency>
</dependencies>

Step 2. Configure the authentication manager:

@Configuration
@EnableWebSecurity
public class SecurityConfiguration {

@Autowire
public DataSource dataSource;

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth)

  throws Exception {

    auth.jdbcAuthentication()
      .usersByUsernameQuery("select username,password,enabled "
        + "from user_accounts "
        + "where username = ?")
      .authoritiesByUsernameQuery("select username,role "
        + "from user_accounts "
        + "where username = ?")
      .dataSource(dataSource);
    }

// Other security configurations...

}

Here we create a configuration class(‘SecurityConfiguration’) and define ‘configureGlobal(AuthenticationManagerBuilder auth)’ method to configure the authentication manager.

We use the jdbcAuthentication() method to enable JDBC-based authentication and passed the Datasource bean to the ‘datasource()’ method in order to specify the database connection for authentication.

We also provide the SQL queries for user authentication and authorization using the usersByUsernameQuery() and authoritiesByUsernameQuery() methods, respectively. These queries retrieve user credentials (username, password, enabled, and user roles) from the "user_accounts" table.

Note: The actual database schema and table names may vary depending on your application’s specific requirements.

The schema which I used is:

CREATE SEQUENCE IF NOT EXISTS user_accounts_seq;

CREATE TABLE IF NOT EXISTS user_accounts (
    user_id BIGINT NOT NULL DEFAULT nextval('user_accounts_seq') PRIMARY KEY,
    username varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    role varchar(255),
    enabled boolean NOT NULL
);

Step 3. Configure HttpSecurity:

Although we have already completed our JDBCAuthentication in Step 2, this step is just to provide completeness to the SecurityConfiguration by configuring HttpSecurity where you can define your authorization rules:

@Bean
 SecurityFilterChain web(HttpSecurity http) throws Exception {

  http.authorizeHttpRequests(authorize -> {

   try {
    authorize
    .requestMatchers("/admin/**").hasRole("ADMIN") // Require ADMIN role for /admin/**
    .requestMatchers("/user/**").hasRole("USER") // Require USER role for /user/**
    .anyRequest().authenticated() // Require authentication for all other requests
    .and()
    .formLogin();                 //Enable form-based authentication
        } catch (Exception e) {
            e.printStackTrace();
            }
         }
       );

    return http.build();
}

Here we define the ‘SecurityFilterChain’ bean and within it ‘web(HttpSecurity http)’ method to configure the HttpSecurity for different URLs and authentication requirements.

Inside the web(HttpSecurity http) method:

  • We use the ‘authorizeHttpRequests()’ method to define access rules for different URLs.

  • The ‘requestMatchers’ method is used to specify URL patterns and the corresponding role requirements.

  • The ‘.anyRequest().authenticated()’ method ensures that any other request not specified in the ‘requestMatchers’ will require authentication.

  • Finally, we enabled form-based authentication using ‘.formLogin()’. This allows users to log in using a form-based login page.

Note: The actual URL patterns, role names, and security requirements may vary depending on your application's specific needs.

Woohoo!! You reached here which means you have successfully configured the JDBCAuthentication and you have successfully defined security rules for your project.

Best of luck! with your project. Please do leave a like and your valuable feedback.

Thank You