Using Trusted CA signed SSL Certificates with PostgreSQL Clients

How to install and configure Trusted CA signed SSL certificates for PostgreSQL clients.

PostgreSQL allows for clients to provide their own SSL certificates at login time for the purposes of authentication via SSL certificate based identity verification. Client SSL certificate based identity verification can also be performed when using any non-SSL certificate authentication methods.

Please note that the use of client certificates is only allowed for SSL encrypted server connections, which requires the use of server-side SSL certificates (and, optionally, a client-side root CA certificate store). For information on creating and installing server SSL certificates please see our Using Trusted CA signed SSL Certificates with PostgreSQL Servers Knowledge Base article.

The following instructions require the use of the openssl CLI utility which is included with the OpenSSL packages provided by all major Linux distributions via their primary package management tools. For other operating systems the OpenSSL wiki maintains a list of ways to obtain pre-built binaries.

Generation of client key and certificates is much the same as with server keys/certificates in that you create a private key and CSR, deliver the CSR to your trusted CA, and install the private key and signed certificate/certificate chain received from the trusted CA with the following differences:

• The client's private key, signed certificate, and intermediate certificates in the signing chain (if present) are installed on the client host while the root CA's certificate are installed on the server. This is the opposite of the install locations for server keys/certificates.
• If you wish to use the cert authentication method or the clientcert=verify-full authentication option then you must use the name of the database role the given certificate will be used for as the CSR's Common Name value.

In the examples below we will generate and install certificates to allow authentication for a database user named robert and install them for a system user named bob. When generating and installing your own keys, CSRs, and certificates you should alter the command appropriately for your own system users and database roles.

You can generate your server private key and CSR with a single command like so:

openssl req -new -nodes -text \
-sha256 -newkey rsa:4096 \
-keyout robert.key \
-out robert.csr

This command will prompt you to enter a number of metadata items to be included in the CSR and the signed certificate your CA will later provide based on it. For example:

[root@localhost]# openssl req -new -nodes -text -sha256 -newkey rsa:4096 -keyout robert.key -out robert.csr
Generating a RSA private key
..................................................++++
.........................................................................................++++
writing new private key to 'robert.key'

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) [XX]:US
State or Province Name (full name) []:Massachusetts 
Locality Name (eg, city) [Default City]:Bedford
Organization Name (eg, company) [Default Company Ltd]:Example, LLC.
Organizational Unit Name (eg, section) []:TestDept
Common Name (eg, your name or your server's hostname) []:dba_user
Email Address []:dba_user@example.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@localhost tmp]# ls
robert.csr robert.key

While all are technically optional for successful completion of the above command two of them -- Common Name and Password -- do require special consideration where Postgres client certificates are concerned, and you should also consult with your trusted CA as they may have requirements for specific metadata fields as well.

If you wish to use the cert authentication method or clientcert=verify-full authentication option with another authentication method then this field must contain name of the database role the resulting certificate will be used to authenticate and/or verify, e.g. robert in this example. This is further detailed in the pg_hba.conf section below.

If you provide a password when generating your CSR then the client will be required to provide it on every login, either via the sslpassword client connection parameter or manually, in addition to any database user password requirements imposed by the server via its pg_hba.conf. Most users will prefer to leave their client certificate's password field blank to disable this behavior.

After submitting your newly generated CSR to your trusted CA they will return a signed certificate file to you. In addition to your signed certificate your trusted CA will return the certificates in the certificate chain used to sign your certificate. That will be comprised of:

• A single root certificate that was used to sign your server certificate.

or more likely:

• An intermediate certificate used to sign your server certificate.
• The root certificate at the beginning of the signed certificate chain that leads to the intermediate certificate used to sign your server certificate.
• Any other intermediate certificates in the signing chain between the root certificate and intermediate certificate used to sign your server certificate.

For more details on certificate signing chains see "How can I determine the order of certificates in a certificate chain?" in this article's companion article, Using Trusted CA signed SSL Certificates with PostgreSQL Servers.

The default installation location of the client's SSL private key file is $HOME/.postgresql/postgresql.key with 0600 permissions for the system user on each client host that will use your certificate, e.g. for our example system user bob and the above generated robert.key:

cp robert.key /home/bob/.postgresql/postgresql.key
chmod 0600 /home/bob/.postgresql/postgresql.key

If you wish to install to a different location and/or file name then you must provide the custom path at connection time using the sslkey client connection parameter or the PGSSLKEY environment variable at connection time. For example, if you were to keep the key file's original name:

cp robert.key /home/bob/.postgresql/robert.key
chmod 0600 /home/bob/.postgresql/robert.key

Then when using, for example, psql to connect to your server at a host named mydb.somehost.com you could specify the location via this:

psql "host=mydb.somehost.com user=robert sslmode=require sslkey=/home/bob/.postgresql/robert.key"

or:

PGSSLKEY=/home/bob/.postgresql/robert.key psql "host=mydb.somehost.com user=robert sslmode=require"

Let's say your trusted CA returns the following files to you:

RootCA.crt
IntermediateCA_FOO.crt
IntermediateCA_BAR.crt
robert.crt 

Similarly to the installation of the private key file detailed above, the default installation location for the signed certificate, robert.crt in this example, is at $HOME/.postgresql/postgresql.crt for the system user on each client host that will use your certificate, e.g. for the system user 'bob' in our ongoing example:

cp robert.crt /home/bob/.postgresql/postgresql.crt

Custom locations and/or file names may be specified at connection time via the sslcert connection parameter or PGSSLCERT environment variable.

The intermediate trusted CA certificate(s) should be concatenated to the client certificate file, first the intemediate certificate that signed your server certificate then the one that signed that one and so on. So, with our example above if IntermediateCA_BAR.crt was used to sign your certificate and IntermediateCA_FOO.crt signed IntermedidateCA_BAR.crt then:

cat IntermediateCA_BAR.crt IntermediateCA_FOO.crt >> /home/bob/.postgresql/postgresql.crt

If, as in the example above, you have multiple certificates from your CA comprising a certificate chain but are unsure what order they should be in (i.e. which signed which), see the below FAQ titled "How can I determine the signing order of certificates in a certificate chain?"

The trusted CA's root certificate, RootCA.crt, should be appended to the server's root CA store (if it is not already there) to allow the server to perform validation of your client certificate chain. The location of the server's root CA store is specified via the its ssl_ca_file configuration parameter, For example, if that is set on the server as ssl_ca_file=root.crt then on the Postgres server:

cat RootCA.crt >> $PGDATA/root.crt

There are two options for specifying the use of client SSL certificates via pg_hba.conf entries on the server:

1. Use the clientcert auth option with any authentication method (see "auth-options" in the Postgres manual, section 21.1. The pg_hba.conf File)
2. Use the cert authentication method.

The clientcert auth option can be used with any auth method and can be set to either verify-ca or verify-full:

• verify-ca functions the same way as it does when used for the sslmode connection parameter when establishing SSL encrypted server connections but in reverse: Here the server has a list of known root CA certificates that it checks against the certificate or certificate chain presented by the client.
• verify-full also functions similarly, it performs the certificate chain validation done with verify-ca and then also checks that the certificate's Common Name value matches the name of the database role being authenticated (vs. the Common Name containing the server's FQDN when for validating server certificates).

If you wish to require the use of a password for the client in addition to identity verification via client certificates you could instead use:

hostssl mydb robert 127.0.0.1/32 scram-sha-256 clientcert=verify-full

This can also be used to, for example, require the client to provide verifiable certificates when using LDAP for authentication:

hostssl mydb robert 127.0.0.1/32 ldap clientcert=verify-full ldapserver=ldap.example.net ldapprefix="cn=" ldapsuffix=", dc=example, dc=net" ldaptls=1

The cert authentication method allows the given client(s) to log in without supplying a password to the server by supplying a verifiable certificate at connection time (although if the certificate itself requires a password then that must be supplied at client connection time). Please note that, as described by the PostgreSQL manual on the cert authentication method vs. clientcert authentication option:

It is redundant to use the clientcert option with cert authentication because cert authentication is effectively trust authentication with clientcert=verify-full.

So this pg_hba.conf entry:

hostssl mydb robert 127.0.0.1/32 cert

is equivalent to this:

hostssl mydb robert 127.0.0.1/32 trust clientcert=verify-full

Let's say that sytem user bob on client host 10.17.0.10 will connect to the database mydb on serverhost 10.17.0.10 with database user robert, with the server requiring client certificate authentication via this pg_hba.conf entry:

hostssl mydb robert 10.17.0.10 cert

If you have installed the client SSL certificate (including any intermediate certificates) and key files at their default locations (/home/bob/.postgresql/postgresql.crt and /home/bob/.postgresql/postgresql.key for the bob system user on the 10.17.0.10 client host) then you can test the connection via psql with the following:

psql "host=10.17.0.1 user=robert dbname=mydb sslmode=require"

If instead you have installed the client certificate and key files at custom locations you must specify them with the sslcert and sslkey connection parameters or PGSSLKEY and PGSSLCERT environment variabeles. For example, if you have them installed at /home/bob/.postgresql/robert.crt and /home/bob/.postgresql/robert.key:

psql "host=10.17.0.1 user=robert dbname=mydb sslmode=require sslcert=/home/bob/.postgresql/robert.crt sslkey=/home/bob/.postgresql/robert.key"

or:

PGSSLKEY=/home/bob/.postgresql/robert.key PGSSLCERT=/home/bob/.postgresql/robert.crt psql "host=10.17.0.1 user=robert dbname=mydb sslmode=require"

In all cases the server must have a copy of the the client certificate chain's root certificate in its trusted CA store (ssl_ca_file) for the above connections to be successful.