Root.Crt Not Found Postgresql

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

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

Requirement of Client certificates in PostgreSQL SSL

Don't trust random articles, consult the official documentation.

As you have found, you need to use the connection string option sslmode=verify-full (or set the environment variable PGSSLMODE to verify-full) to verify the server certificate.

For that, you do not need client certificates. All you need is the certification authority (CA) certificate on the client, so that the client can verify that the server certificate was signed with that certificate.

Client certificates are only needed if you want the server to authenticate the client using certificates.

If you don't specify the CA certificate's path with either the sslrootcert connection string option or the PGSSLROOTCERT environment variable, the client will search the CA certificate in ~/.postgresql/root.crt, as detailed in the documentation.

Postgresql and SSL certificat

According to the PostgreSQL Reference you should check your $PGDATA directory for the server.crt file. In cases is missing you should either request a new certificate or make self signed one and place the file there.

Excel2016: Cannot query PostgresSQL database: Server certificate not accepted

I have finally found a workaround for my problem.
What you can do is to:

1. Install the current postgresql driver from here

2. Follow the instruction from this video

With this, you can connect to your postgreSQL database by ODBC.

---

Related Topics