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

user_642706 0
Viewed 6878

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?

datasource jdbc ssl postgresql
1 Answers

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