Database Authentication with Spring Security

Imagine if you will, a user has an existing user account with a database server. And you want to log that user in to your website, using that database user info. Here's how to do it with Spring Security.

public class DbAuthenticationProvider implements AuthenticationProvider {

    private String url;

    @Override
    public Authentication authenticate(Authentication authentication) throws AuthenticationException {

        Connection connection = null;
        Statement getRoles = null;
        ResultSet rs = null;

        try {

            Properties properties = new Properties();
            properties.put("user", authentication.getName());
            properties.put("password", authentication.getCredentials().toString());

            connection = DriverManager.getConnection(this.url, properties);

        } catch (SQLException exp){
            try { connection.close(); } catch(SQLException exp2){};
            throw new BadCredentialsException("Bad Credentials");
        }

        /* Authentication worked, now get the user's roles */

        try {

            List grantedAuthorities = new ArrayList<>();

            getRoles = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);

            /* we're connected to the db as userX, so applicable_roles will only show userX's roles */

            rs = getRoles.executeQuery("select role_name from information_schema.applicable_roles");

            while(rs.next()){
                grantedAuthorities.add(new SimpleGrantedAuthority(rs.getString(1)));
            }

            UserDetails user = new User(authentication.getName(), authentication.getCredentials().toString(), grantedAuthorities);

            return new UsernamePasswordAuthenticationToken(user, authentication.getCredentials(), grantedAuthorities);

        } catch (SQLException exp) {
            throw new AuthenticationServiceException(exp.getLocalizedMessage());
        } finally {
            try { rs.close(); } catch (SQLException exp) {}
            try { getRoles.close(); } catch (SQLException exp) {}
            try { connection.close(); } catch (SQLException exp) {}
        }

    }

    @Override
    public boolean supports(Class aClass) {
        return true;
    }

    public void setUrl(String url) {
        this.url = url;
    }
}

In applicationContext-security.xml, add:

<beans:bean class="com.databasepatterns.jdbc.DbAuthenticationProvider" id="dbAuthenticationProvider">
    <beans:property name="url" value="jdbc:postgresql://localhost:5432/dbname" />
</beans:bean>

<authentication-manager>
    <authentication-provider ref="dbAuthenticationProvider"/>
</authentication-manager>