Spring Security JDBC Authentication query twice to the database

Expected Behavior

Added option to use a single query to get all credentials. That could be a combination method of the current usersByUsernameQuery() and authoritiesByUsernameQuery().

Current Behavior

The jdbcAuthentication() queries twice to the database when authenticating. The first query is to get the credentials, and the second is to get the authorities.

@Bean
public AuthenticationManager authenticationManager(HttpSecurity http) throws Exception {
    // Sử dụng 2 AuthenticationProvider để xác thực JWT và username/password (khi login)
    AuthenticationManagerBuilder builder = http.getSharedObject(AuthenticationManagerBuilder.class);
    builder.authenticationProvider(jwtAuthenticationProvider);
    builder.jdbcAuthentication()
        .dataSource(dataSource)
        .usersByUsernameQuery("""
            SELECT TenTaiKhoan AS username, MatKhau AS password, 1 AS enabled
            FROM TaiKhoan
            WHERE TenTaiKhoan = ?""".replaceAll("\\R", " "))
        .authoritiesByUsernameQuery("""
            SELECT TenTaiKhoan AS username, LoaiTaiKhoan AS role
            FROM TaiKhoan
            WHERE TenTaiKhoan = ?""".replaceAll("\\R", " "));
    return builder.build();
}

Context

My current workaround is to use DaoAuthenticationProvider with UserDetailsService, it still works although I prefer JDBC Authentication.

Comment From: jzheaux

Thanks for the suggestion, @tonghoangvu.

The tricky part about adding this is knowing which columns mean what. The user's query can either request the username, password, and enabled values or it can also request the values for account locked, account expired, and credentials expired. Adding another option adds complexity to the code to determine whether or not to extract authorities from the result set as well.

This also has some awkward API implications since there is a setter for the user details query and a setter for the authorities query. If we are to add the capacity to combine them, then that means that we'd have a conflicting set of features, which has the potential to confuse users.

Instead, you can get your desired behavior by overriding the loadUsersByUsername method and performing your custom query, like so:

public class MyUserDetailsService extends JdbcUserDetailsManager {
    public MyUserDetailsService(DataSource dataSource) {
        super(dataSource);
        super.setEnableAuthorities(false);
    }

    @Override
    protected List<UserDetails> loadUsersByUsername(String username) {
        return getJdbcTemplate().query(myCustomQuery, this::myCustomMapper, username);
    }

    private UserDetails myCustomMapper(ResultSet rs, int rowNum) throws SQLException {
        // ...
    }
}

Alternatively, you may find that Spring Data is a great deal more powerful than JdbcUserDetailsManager. Then, you can implement a UserDetailsService that queries a Spring Data repository.