[FIXED] Use SCRAM-SHA-256 on old Java 8 app + Spring to connect to postgresql/postgis

Issue

I have to modify an old java 8 application that connects to a Postgres (PostGIS) database via Spring.

I need to consider authentication via SCRAM-SHA-256. I do not manage the server part, only the application part.

Currently, I am instantiating a PGPoolingDataSource object to create the connection (URL + User + Password). Then, it's used within a JdbcTemplate object, like this:

public JdbcTemplate createJdbcTemplate(ConnectionInfo cnxInfos)
{
    return new JdbcTemplate(createDataSource(cnxInfos));
}

public DataSource createDataSource(ConnectionInfo cnxInfos)
{
    PGPoolingDataSource ds = new PGPoolingDataSource();
    ds.setUrl(cnxInfos.url);
    ds.setUser(cnxInfos.user);
    ds.setPassword(cnxInfos.pwd);
    return ds;
}

There is another post on this authentication topic but via the basic DriverManager and not Spring. Is there a solution that would save me from having to change all the APIs ? Maybe another DataSource subclass ? Or just change de value given to ds.setPassword(...) with an encrypted key ?

Moreover, in POM.xml, there is (groupId/artifactId/version) org.postgresql/postgresql/9.4.1212 and net.postgis/postgis-jdbc/2.1.7.2. Maybe I should upgrade to newer versions.

Thanks!

Solution

There is nothing you have to do on the client side, except use a less ancient version of the JDBC driver. Your code wouldn't have to change.

Then make sure that the DBA sets password_encryption = scram-sha-256 and change the user's password. That should be all.

Answered By - Laurenz Albe
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)