What should I do to avoid excessive SELECTs for roles when working with Spring Security?

Question

Look at what Hibernate does

Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?
Hibernate: select a1_0.user_id,a1_1.id,a1_1.role from user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id where a1_0.user_id=?

It makes a separate query for each user to get their roles. I would like to avoid it as it looks like a performance killer. I believe there's a way to make Hibernate execute only one query for all users to get their roles in one go

As I understand it, the problem has to do with the way my Spring Security is configured

@Configuration
@Import(WebMvcConfig.class)
@EnableWebSecurity
public class WebSecurityConfig {
    private final DataSource dataSource;

    private static final String USERS_BY_USERNAME_QUERY = """
            SELECT username, password, enabled
            FROM users where username = ?
            """;

    private static final String AUTHORITIES_BY_USERNAME_QUERY = """
            SELECT users.username, roles.role
            FROM user_role
            JOIN users ON user_role.user_id = users.id
            JOIN roles ON user_role.role_id = roles.id
            WHERE users.username = ?
            """;

    public WebSecurityConfig(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http.authorizeHttpRequests()
                .requestMatchers("/user/**").hasAuthority("USER")
                .requestMatchers("/admin/**").hasAuthority("ADMIN")
                .anyRequest().authenticated()
                .and().formLogin()
                .successHandler(successHandler())
                .and().logout().logoutUrl("/logout").logoutSuccessUrl("/login");
        return http.build();
    }

    @Bean
    public AuthenticationProvider authenticationProvider() {
        JdbcUserDetailsManager userDetailsManager = new JdbcUserDetailsManager(dataSource);
        userDetailsManager.setUsersByUsernameQuery(USERS_BY_USERNAME_QUERY);
        userDetailsManager.setAuthoritiesByUsernameQuery(AUTHORITIES_BY_USERNAME_QUERY);

        DaoAuthenticationProvider authProvider = new DaoAuthenticationProvider();
        authProvider.setUserDetailsService(userDetailsManager);
        authProvider.setPasswordEncoder(passwordEncoder());
        return authProvider;
    }

    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder(10);
    }

    @Bean
    public AuthenticationSuccessHandler successHandler() {
        return (httpServletRequest, httpServletResponse, authentication) ->
                httpServletResponse.sendRedirect("/");
    }
}

What should I do to avoid excessive SELECTs for roles when working with Spring Security?

My entities:

@Entity
@Table(name = "users")
@Getter
@Setter
@EqualsAndHashCode
public class User implements UserDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(nullable = false, unique = true)
    private String username;
    @Column(nullable = false)
    private String password;
    @Column(nullable = false)
    private String name;
    @Column(name = "last_name", nullable = false)
    private String lastName;
    @Column(nullable = false)
    private String department;
    @Column
    private int salary;
    @Column(nullable = false)
    private byte age;
    @Column
    private String email;
    @Column(name = "enabled", nullable = false)
    private byte enabledByte;
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "user_role",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    @EqualsAndHashCode.Exclude
    private Set<Role> authorities;

@Entity
@Table(name = "roles")
@Getter
@Setter
@EqualsAndHashCode
public class Role implements GrantedAuthority {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(name = "role", nullable = false, unique = true)
    private String authority;
    @ManyToMany(mappedBy = "authorities")
    @EqualsAndHashCode.Exclude
    private Set<User> userList;

If you want, you may use my schema.sql and data.sql files for a quick setup. It will create the tables and fill them with sample rows. I think it would be best not to post the scripts directly here to avoid unnecessary clutter

What I tried:

1. Setting the fetch type to EAGER

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "user_role",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    @EqualsAndHashCode.Exclude
    private Set<Role> authorities;

SELECTs were still present

2. Using a @Query annotation in my repository and pass a native query that returns roles too

public interface UserDao extends JpaRepository<User, Long> {
    @Query(value = """
SELECT users.id, users.username, users.password, users.name,
users.last_name, users.department, users.salary, users.age,
users.email, users.enabled, roles.role
FROM user_role
JOIN users ON user_role.user_id = users.id
JOIN roles ON user_role.role_id = roles.id
WHERE username != :username
""", nativeQuery = true)
    List<User> findAllExceptLoggedUser(String username);

Result:

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: pp.spring_bootstrap.models.User.authorities: could not initialize proxy - no Session

I want to stress that @ManyToMany(fetch = FetchType.EAGER) was still in my User class. If I change it to LAZY, same effect

Answer 1

JSQL's JOIN FETCHes help in this kind of situation

@Configuration
@EnableWebSecurityConfig
public class WebSecurityConfig {
// ...
    @Bean
    public UserDetailsService userDetailsService() {
        return username -> {
            User user = userDao.findByUsername(username);
            if (user == null) {
                String msg = String.format("No user with username %s is found in the database", username);
                throw new UsernameNotFoundException(msg);
            }
            return user;
        };
    }

    @Bean
    public AuthenticationProvider authenticationProvider() {
        DaoAuthenticationProvider authProvider = new DaoAuthenticationProvider();
        authProvider.setUserDetailsService(userDetailsService());
        authProvider.setPasswordEncoder(passwordEncoder());
        return authProvider;
    }

public interface UserDao extends JpaRepository<User, Long> {
    @Query(value = """
            SELECT u
            FROM User u LEFT JOIN FETCH u.authorities
            WHERE u.username != :username
            """)
    List<User> findAllExceptLoggedUser(String username);

    @Query(value = """
            SELECT u
            FROM User u LEFT JOIN FETCH u.authorities
            WHERE u.username = :username
            """)
    User findByUsername(String username);

In the console, you may see something like

Hibernate: select u1_0.id,u1_0.age,a1_0.user_id,a1_1.id,a1_1.role,u1_0.department,u1_0.email,u1_0.enabled,u1_0.last_name,u1_0.name,u1_0.password,u1_0.salary,u1_0.username from users u1_0 left join (user_role a1_0 join roles a1_1 on a1_1.id=a1_0.role_id) on u1_0.id=a1_0.user_id where u1_0.username=?