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.

PostgreSQL SSL Configuration - Part I

This post describes how to setup PostgreSQL so that we can connect without providing a user password.  Instead, we will use certificates to connect.  This is required in some secure environments.

Part II of this post will describe how to connect to PostgreSQL using Java and certificates.

Before we, start we'll build a root CA and an intermediate CA.  This way, we can generate certificates when we please.

This site was very clear and concise in detailing how to setup the above structure.  We'll use RHEL 7.4 and PostgreSQL 9.6.5.

Note that you may customize your openssl.cnf for your own needs.  Again, this is a test scenario.  You would most likely be getting your certificates from a certified CA.

Once your root and intermediate CAs' are set up, generate a certificate signing request (CSR) for your PostgreSQL server:

# cd /root/ca
# openssl genrsa -out intermediate/private/server.key 2048
# chmod 400 intermediate/private/server.key

Create the certificate:
# openssl req -config intermediate/openssl.cnf \
 -key intermediate/private/server.key \
 -new -sha256 -out intermediate/csr/server.csr

You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:pgserver.domain.us
Email Address []:

Sign the certificate using the intermediate CA:

# openssl ca -config intermediate/openssl.cnf \
   -extensions server_cert -days 375 -notext -md sha256 \
   -in intermediate/csr/server.csr \
   -out intermediate/certs/server.crt

Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 4098 (0x1002)
        Validity
...
...
...
            X509v3 Key Usage: critical
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication
Certificate is to be certified until Jan 13 20:55:45 2019 GMT (375 days)
Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

We can verify the certificate as follows:

# openssl x509 -noout -text -in intermediate/certs/server.crt
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 4098 (0x1002)
    Signature Algorithm: sha256WithRSAEncryption
...
...
...

The CA chain file should have been created if you followed the guide.  If not, this is how you can do it:

# cd /root/ca/
# cat intermediate/certs/intermediate.cert.pem /root/ca/certs/ca.cert.pem > ca-chain.cert.pem
# chmod 444 intermediate/certs/ca-chain.cert.pem

As root, copy the ca-chain.cert.pem, server.crt and server.key files to the $PGDATA directory:

# export PGDATA=/var/lib/pgsql/9.6/data
# cd /root/ca
# cp intermediate/certs/ca-chain.cert.pem $PGDATA/root.crt
# cp intermediate/certs/server.crt $PGDATA
# cp intermediate/private/server.key $PGDATA
# cd $PGDATA
# chown postgres:postgres root.crt server.crt server.key

As the postgres user, make the following changes to the $PGDATA/postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

Restart the PostgreSQL server:

$ pg_ctl restart


Now create a certificate for the postges user:

# cd /root/ca
# openssl genrsa -out intermediate/private/postgresql.key 2048
# chmod 400 intermediate/private/postgresql.key

Create the certificate:

# cd /root/ca
# openssl req -config intermediate/openssl.cnf \
 -key intermediate/private/postgresql.key \
 -new -sha256 -out intermediate/csr/postgresql.csr

Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:postgres.domain.us
Email Address []:

As before, sign the certificate request using the intermediate CA:

# cd /root/ca
# openssl ca -config intermediate/openssl.cnf \
 -extensions server_cert -days 375 -notext -md sha256 \
 -in intermediate/csr/postgresql.csr \
 -out intermediate/certs/postgresql.crt

Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 4099 (0x1003)
        Validity
            Not Before: Jan  5 16:52:54 2018 GMT
            Not After : Jan 15 16:52:54 2019 GMT
...
...
...
                TLS Web Server Authentication
Certificate is to be certified until Jan 15 16:52:54 2019 GMT (375 days)
Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

Make usre /var/lib/pgsql/.postgresql exists and is owned by postgres.

Next, copy the following to /var/lib/pgsql/.postgresql:
# cd /root/ca
# cp intermediate/certs/postgresql.crt /var/lib/pgsql/.postgresql
# cp intermediate/private/postgresql.key /var/lib/pgsql/.postgresql
# cp intermediate/certs/ca-chain.cert.pem /var/lib/pgsql/.postgresql/root.crt
# chown postgres:postgres /var/lib/pgsql/.postgresql/*

Our host IP is 10.10.10.123.  This is reflected in the pg_hba.conf file.

As the postgres user, make the following changes to $PGDATA/pg_hba.conf and $PGDATA/pg_ident.conf:

pg_ident.conf
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
ssl-test                   postgres.domain.us                 postgres

pg_hba.conf
# TYPE       DATABASE   USER            ADDRESS                 METHOD
hostssl         all                    all                  10.10.10.123/32          cert clientcert=1 map=ssl-test

Now, we can test our SSL connection to the database:

$ cd /var/lib/pgsql/.postgresql

$ psql 'host=10.10.10.123 port=5432 dbname=postgres user=postgres sslmode=require sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt'

psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#

Logs should show something like the following:

Jan  5 12:14:20 pgs01 postgres[3817]: [6-1] 2018-01-05 12:14:20 EST [3817]: [1] user=postgres,db=postgres 2018-01-05 12:14:20.975 EST 00000 5a4fb26c.ee9 pgs01(37242) [unknown] 2018-01-05 12:14:20 EST LOG:  connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)

PostgreSQL Backup Script

This script backups a PostgreSQL cluster. 

It is assumed that this cluster is STIG'd using DISA STIG guidelines.  SSL is turned on and configured correctly.

The following parameters are set in postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /data/pgsql/9.6/wals/%f'

For this case:
PGDATA=/data/pgsql/9.6/data
BKUPDIR=/var/lib/pgsql/9.6/backup

For security reason, SSL is turned on.  The following is set in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

It is assumed that valid certs have been installed for PostgreSQL.

The pg_hba.conf file looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl         all     all     10.10.10.123/32        cert clientcert=1 map=ssl-test
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv4 network connections
hostnossl    all          all             0.0.0.0/0            reject

The pg_ident.hba file looks like this:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
ssl-test        postgres.dmn.org       postgres

The backup script is shown below:

#!/bin/sh

# pg_basebackup won't work with hostssl and cert clientcert=1
# so, we do the backup using the API
LABEL="Base Backup on $(/bin/hostname) on $(/bin/date +%Y%m%d%S)"
DT=`/bin/date +%Y%m%d%S`
BKUPDIR=/var/lib/pgsql/9.6/backup
HOST=pgs01.dmn.mil
STR="hostssl replication postgres 10.10.10.123/32 trust"

echo $STR
/bin/echo $STR >> /data/pgsql/9.6/data/pg_hba.conf

/usr/pgsql-9.6/bin/pg_ctl reload

/usr/pgsql-9.6/bin/pg_basebackup -D ${BKUPDIR}/${DT} --xlog-method=fetch --format=tar --write-recovery-conf --progress --verbose --username=postgres --no-password --gzip --host=${HOST} --label="${LABEL}"

/bin/sed -i '/hostssl replication postgres 10.10.10.123\/32 trust/d' /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload

Before the backup begins, the script adds the hostssl replication line defined by STR above and does a pg_ctl reload so that the changes in pg_hba.conf are picked by the postgres server.  This allows a trusted connection to be made from the local server using SSL (or you could simply change this to 127.0.0.1).

The backup is executed using pg_basebackup.

After the backup is completed, the hostssl replication line is removed and the pg_hba.conf file is reloaded.  This is done for STIG compliance.

The backup is run from root's cron once a week:

# PostgreSQL Weekly Full Backups on Sundays
0 0 * * 0 /bin/su -l postgres -c "/bin/sh /data/pgsql/bkup.sh"