Connecting with SSL to CrateDB using the PostgreSQL protocol from Java-based applications

If you are using CrateDB Cloud, or if you have configured a server certificate for an on-premises deployment, and you try to enforce SSL on a PostgreSQL connection to CrateDB you may come across an error message like this:

Could not open SSL root certificate file C:\Users\Hernan\AppData\Roaming\postgresql\root.crt.
  C:\Users\Hernan\AppData\Roaming\postgresql\root.crt (The system cannot find the path specified)

or

org.postgresql.util.PSQLException: Could not open SSL root certificate file C:\Users\Hernan\AppData\Roaming\postgresql\root.crt

This is not specific to CrateDB, and you would get the same message trying to connect to an actual PostgreSQL instance, but I found no simple explanation of this error message and the options available, so here are my two cents.

What happens here is that the client is trying to confirm the server we are establishing an encrypted connection with is indeed the machine we intended to reach, doing this involves validating that the certificate used by the server has been issued by a trusted certification authority.
In this case, the client driver is trying to find the details of valid certification authorities on a PKCS12 file on the location indicated in the error message.

I find that in most cases it makes sense to pick one of the two options below to address this.

If we want the communication channel with the server to be encrypted, but we are on a trusted network environment and do not require verification of the server certificate, we can use this in our connection string:

ssl=true&sslmode=require

But if we want to have both encryption and the confirmation that we are talking to the intended server, we can tell the driver to use the list of certification authorities our JVM accepts:

ssl=true&sslmode=verify-full&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory

Some software (DBeaver for instance) may have separate configuration settings where you can set the SSL Factory and SSL mode:

I hope this helps. As usual please do not hesitate to let us know your thoughts in the CrateDB Community.