Add database authentication to Spring Data Rest application

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.

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());
                }
    }

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;
    }
}