PSQLException "Could not open SSL root certificate file" when attempting SSL connection with Postgres via JDBC

Question

In Java 15, using the PostgreSQL JDBC Driver (ver. 42.2.18, JDBC4.2) from jdbc.postgresql.org with this DataSource:

PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerNames( new String[] { "my-server-address" } );
ds.setPortNumbers( new int[] { 25060 } );
ds.setSsl( true );
ds.setDatabaseName( "mydb" );
ds.setUser( "scott" );
ds.setPassword( "tiger" );
this.dataSource = ds;

…the code Connection conn = this.dataSource.getConnection(); throws this exception:

org.postgresql.util.PSQLException: Could not open SSL root certificate file /Users/basilbourque/.postgresql/root.crt.

I know my Postgres 12 server is set up for SSL connections, as my IntelliJ Ultimate has a database access feature (DataGrip) that is successfully connecting with SSL (TLS) protection.

So what is wrong my DataSource configuration in JDBC?

Answer 1

Thanks to this Github issue page for the pgjdbc driver, I found this post by davecramer:

As of 42.2.5 ssl=true implies verify-full as per the release notes. If you wish to get the old behaviour use sslmode=require

Sure enough, replacing ds.setSsl( true ); with ds.setSslMode( "require" ); allowed my JDBC driver make a connection via DataSource.

        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setServerNames( new String[] { "my-server-address" } );
        ds.setPortNumbers( new int[] { 25060 } );
        ds.setSslMode( "require" );  // Replaces: ds.setSsl( true );
        ds.setDatabaseName( "mydb" );
        ds.setUser( "scott" );
        ds.setPassword( "tiger" );
        this.dataSource = ds;

I have no idea what any of these SSL/TLS related options are actually doing, but this worked for me to connect to my DigitalOcean managed Postgres database server.

The following code snippet now runs successfully:

        try
                (
                        Connection conn = this.dataSource.getConnection() ;
                        Statement stmt = conn.createStatement() ;
                )
        {
            String sql =
                    """
                    SELECT uuid_generate_v1()
                    ;
                    """;
            try (
                    ResultSet rs = stmt.executeQuery( sql ) ;
            )
            {
                while ( rs.next() )
                {
                    UUID uuid = rs.getObject( 1 , UUID.class );
                    System.out.println( "uuid = " + uuid );
                }
            }
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }