How to connect to PostgreSQL from Java over SSL

Configuring JDBC connectivity to PostgreSQL over SSL involves a few steps, especially when setting up SSL validation or opting for a non-validating SSL connection. Below is a tutorial demonstrating both options.

Step 1: Verifiy SSL Connectivity with the Database

Firstly, make sure that you have properly configured PostgreSQL to use SSL. The simplest way to do that, is opening a connection with the psql client tool:

./bin/psql -h localhost -U postgres
psql (14.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

From the output we can see that the connection is using SSL. We also can check the list of ciphers used for encrypting the connection

Step 2: Configure the JDBC Connection String

There are mainly two ways to ontroduce SSL Connectivity from the JDBC side:

For standard SSL mode, set the connection parameter sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory to enable validation.

For example:

String url = "jdbc:postgresql://your-postgres-server:5432/your-database?ssl=true&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory";
Properties props = new Properties();
props.setProperty("user", "your-username");
props.setProperty("password", "your-password");

Connection conn = DriverManager.getConnection(url, props);
// Use 'conn' for database operations

For non-validating ssl connections, you can use sslfactory=org.postgresql.ssl.NonValidatingFactory . For example:

String url = "jdbc:postgresql://your-postgres-server:5432/your-database?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory";
Properties props = new Properties();
props.setProperty("user", "your-username");
props.setProperty("password", "your-password");

Connection conn = DriverManager.getConnection(url, props);
// Use 'conn' for database operations

By setting the connection URL parameter sslfactory=org.postgresql.ssl.NonValidatingFactory will turn off all SSL validation.

Step 3: Provide the Certificates to the Java Client application

Finally, before starting our Java Client application, we need to provide the KeyStores and TrustStores so that you can validate the SSL Connection.

For example, if you are using keytool and openssl to generate the Keystores and certificates:

keytool -import -alias server -file server-ca.pem -keystore truststore.jks -storepass password
openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -out client.p12 -name client
keytool -importkeystore -deststorepass password -destkeystore keystore.jks -srckeystore client.p12 -srcstoretype PKCS12 -srcstorepass password -alias client

Then, include in the JVM options the javax.net.ssl properties to load the KeyStores and TrustStore:

export JAVA_OPTS="$JAVA_OPTS -Djavax.net.ssl.keyStore=keystore.jks -Djavax.net.ssl.keyStorePassword=password -Djavax.net.ssl.trustStore=truststore.jks -Djavax.net.ssl.trustStorePassword=password"

Conclusion

Follow these steps according to your SSL requirement: SSL validation or non-validating SSL connection, and ensure proper handling of SSL certificates for secure JDBC connectivity to PostgreSQL.