PostgreSQL SSL Configuration - Part II

This post describes how to connect to PostgreSQL using Java and client/auth.  This assumes that client and server certificates have been installed as described in the previous post.

The PgClientCertDemo.zip can be downloaded here.

Our environment is RHEL 7.4, PostgreSQL 9.6.5 and openjdk version "1.8.0_151".

Java Truststore and Keystore
First, create a Java truststore.  Assume that our postgres home is set to /data/pgsql.  We need the root and intermediate certificates which can be downloaded from your CA or from the ca-chain.cert.pem file.  From this file, copy the intermediate and root certs into separate files e.g. CA-root.crt and CA-intermediate.crt.

Once these are placed in /data/pgsql/.postgresql, import these into the keystore as follows:

$ keytool -import -file CA-root.crt -alias CA-root -keystore cacerts
Enter keystore password:
Re-enter new password:
Owner: CN=...
...
...
...
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 6C 8A 94 A2 77 B1 80 72   1D 81 7A 16 AA F2 DC CE  l...w..r..z.....
0010: 66 EE 45 C0                                        f.E.
]
]
Trust this certificate? [no]:  yes
Certificate was added to keystore

$ keytool -import -file CA-intermediate.crt -alias CA-intermediate -keystore cacerts
Enter keystore password:
Certificate was added to keystore

Next, concatenate the postgresql.crt and postgresql.key file as follows:

$ cat postgresql.key postgresql.crt > postgresql.pem

The postgresql.pem will include the private key, the postgres certificate and the intermediate and root certificates.

Take this PEM file and import into the keystore:

$ openssl pkcs12 -export -in postgresql.pem -out keystore.jks -name postgres -noiter -nomaciter
Enter Export Password:
Verifying - Enter Export Password:

So, we now have a trusted certificate store (cacerts) and key store (keystore.jks).

You can check the contents of the trusted store and key store as follows:

$ keytool -list -v -keystore cacerts
$ keytool -list -v -keystore keystore.jks

You will be prompted for a password.

Or, check the contents specifying an alias:

$ keytool -list -v -keystore keystore.jks -alias postgres
$ keytool -list -v -keystore cacerts -alias CA-root
$ keytool -list -v -keystore cacerts -alias CA-intermediate

Source Code
Unzip the contents of PgClientCertDemo.zip to a temporary location.  Once can either use Maven to compile or manually as described in the steps below.

Copy the following files to the root of your temporary location:
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/App.java
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/TestConnection.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLError.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLSocketFactory.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomX509KeyManager.java

So now you should have the following files in your temporary location:
App.java
TestConnection.java
CustomSSLError.java
CustomSSLSocketFactory.java
CustomX509KeyManager.java

Open each of these and comment out the following line:
package au.com.postnewspapers.pgclientcertdemo;

Next, download the following JARs into the same location as where the source code is:
commons-lang3-3.7.jar or commons-lang3-3.7.jar
postgresql-42.1.4.jar

Open the following files:
App.java
CustomSSLSocketFactory.java

and change:
import org.apache.commons.lang.exception.ExceptionUtils;
to
import org.apache.commons.lang3.exception.ExceptionUtils;

If you are using the old version of org.apache.commons.lang package, then the above change is not required.

Compilation Step
Compile each class as follows:

$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomX509KeyManager.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLSocketFactory.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLError.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar TestConnection.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar App.java

Run Step

Run the test as follows:

$ java -cp .:postgresql-42.1.4.jar:commons-lang3-3.7.jar \

-Djavax.net.ssl.trustStore=/data/pgsql/.postgresql/cacerts App default jks \

/data/pgsql/.postgresql/keystore.jks Password1234 \

'jdbc:postgresql://10.10.10.123/postgres?user=postgres&ssl=true'

Using trusted root store: /data/pgsql/.postgresql/cacerts
trustStore is: /data/pgsql/.postgresql/cacerts
trustStore type is : jks
trustStore provider is :
init truststore
adding as trusted cert:
  Subject: CN=...

...

...

...

main, WRITE: TLSv1.2 Application Data, length = 147
main, READ: TLSv1.2 Application Data, length = 355
main, WRITE: TLSv1.2 Application Data, length = 87
main, READ: TLSv1.2 Application Data, length = 49
main, WRITE: TLSv1.2 Application Data, length = 108
main, READ: TLSv1.2 Application Data, length = 94
main, WRITE: TLSv1.2 Application Data, length = 84
main, READ: TLSv1.2 Application Data, length = 202
main, WRITE: TLSv1.2 Application Data, length = 29
main, called close()
main, called closeInternal(true)
main, SEND TLSv1.2 ALERT:  warning, description = close_notify
main, WRITE: TLSv1.2 Alert, length = 26
main, called closeSocket(true)
main, called close()
main, called closeInternal(true)
main, called close()
main, called closeInternal(true)
Connection and query successful, server version is PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Conclusion

This completes the exercise to show how to connect to PostgreSQL using client/auth using Java.  Note that CRL checking is not done in this exercise.  The code would need to be modified to accomplish this.  We only supply the keystore.jks password and not the postgres password.