Add database authentication to Spring Data Rest application

Question

I'm creating an application using Spring Data REST with Thymeleaf.

Initially I created my models, controllers, dao and services. All worked fine. I'm now trying to add security to my application. Right now I'm just focused on the login/logout.

I've been able to create an in memory authentication as below:

@Configuration
@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {
    

    
    @Autowired
    @Qualifier("securityDataSource")
    private DataSource securityDataSource;

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        
        // add users for in memory authentication
        UserBuilder users = User.withDefaultPasswordEncoder();
        
        auth.inMemoryAuthentication()
        .withUser(users.username("paul").password("test123").roles("MEMBER", "ADMIN"))
        .withUser(users.username("sandra").password("test123").roles("MEMBER", "ADMIN"))
        .withUser(users.username("matthew").password("test123").roles("MEMBER"));
    }

}

I want to change this to database authentication though. I'm pretty sure I can create a jdbc connection and change my config method to something like this:

@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {

        auth.jdbcAuthentication().dataSource(securityDataSource);
        
}

My problem is that I'm already accessing the database through my DAO interfaces. E.g:

public interface UserRepository extends JpaRepository<User, Integer> {
    
    // method to sort by last name
    public List<User> findAllByOrderByLastNameAsc();

}

My users table has an email and password column which will be used as the username/password.

Is it possible to also authenticate by using this in some way? I can provide additional information but am reluctant to just post everything and hope somebody will write it for me.

Answer 1

Since you've already created the DAO interfaces, it may be easier to create a UserDetailsService implementation:

@Service
@NoArgsConstructor @ToString @Log4j2
public class UserDetailsServiceImpl implements UserDetailsService {
    @Autowired private UserRepository userRepository = null;

    @Override
    @Transactional(readOnly = true)
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        org.springframework.security.core.userdetails.User user = null;

        try {
            Optional<User> optional = userRepository.findBy...(username);
            HashSet<GrantedAuthority> set = new HashSet<>();
            /*
             * Add SimpleGrantedAuthority to set as appropriate
             */
            user = new org.springframework.security.core.userdetails.User(username, optional.get().getPassword(), set);
        } catch (UsernameNotFoundException exception) {
            throw exception;
        } catch (Exception exception) {
            throw new UsernameNotFoundException(username);
        }

        return user;
    }
}

and wire it in with:

    @Autowired private UserDetailsService userDetailsService = null;
    ... private PasswordEncoder passwordEncoder = ...;

    @Override
    public void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService)
            .passwordEncoder(passwordEncoder);
    }

For some additional clarity, here is the complete context of my implementation:

@Service
@NoArgsConstructor @ToString @Log4j2
public class UserDetailsServiceImpl implements UserDetailsService {
    @Autowired private CredentialRepository credentialRepository = null;
    @Autowired private AuthorityRepository authorityRepository = null;

    @Override
    @Transactional(readOnly = true)
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
        User user = null;

        try {
            Optional<Credential> credential = credentialRepository.findById(username);
            Optional<Authority> authority = authorityRepository.findById(username);
            HashSet<GrantedAuthority> set = new HashSet<>();

            if (authority.isPresent()) {
                authority.get().getGrants().stream()
                    .map(Authorities::name)
                    .map(SimpleGrantedAuthority::new)
                    .forEach(set::add);
            }

            user = new User(username, credential.get().getPassword(), set);
        } catch (UsernameNotFoundException exception) {
            throw exception;
        } catch (Exception exception) {
            throw new UsernameNotFoundException(username);
        }

        return user;
    }
}

Answer 2

suppose db table name is users and authorities. dataSource is configured in application.yml.

    @Autowired
    private DataSource dataSource;
    
@Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.jdbcAuthentication()
                .dataSource(dataSource)
                .usersByUsernameQuery("select username,password,enabled from users WHERE username=?")
                .authoritiesByUsernameQuery("select username,authority from authorities where username=?")
                .passwordEncoder(new BCryptPasswordEncoder());
                }
    }