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)