Using Trusted CA signed SSL Certificates with PostgreSQL Servers

How to install and configure Trusted CA signed SSL certificates on a Postgres server.

SSL encryption of PostgreSQL client-server communications with SSL certificates signed by a trusted Certificate Authority (CA) is fast becoming a default security requirement for many organizations. This article will describe the process of how to obtain signed SSL server certificates from your trusted CA, where to install them and their supporting files, and how to configure your server and clients to use them. Once you have your server certificates installed and working if you wish to also install SSL client certificates from your trusted CA please see our Using Trusted CA signed SSL Certificates with PostgreSQL Clients 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.

You are responsible for generating and/or obtaining an SSL private key and certificate, which contains the public key corresponding to the given private key. Below we show how to do that "from scratch" by first generating a private key and certificate signing request (CSR), with the CSR then being signed by your trusted CA and returned as the certificate for use. Many organizations will have their own means of obtaining SSL keys and certificates (for example: via request of the organization's security team). If that applies to you then you may begin at 3. Install the private key file. below once you have your private key and signed certificate in hand.

You can generate your server private key and CSR using the openssl req command:

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

Here's a breakdown those options:

• -newkey rsa:4096 - Generate a new CSR and an RSA private key 4096 bits in size.
• -keyout - The name of the generated private key file.
• -out - The name of the generated CSR file.
• -sha256 - Sign the request with the sha256 digest.
• -nodes - Do not encrypt the private key itself.
• -text - Generate the CSR in text form.

When run the above 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:

• Country Name (2 letter code)
• State or Province Name (full name)
• Locality Name (eg, city)
• Organization Name (eg, company)
• Organizational Unit Name (eg, section)
• Common Name (eg, your name or your server's hostname)
• Email Address
• A challenge password
• An optional Company name

Note that the exact list prompted for by your system may vary depending on it local OpenSSL configuration. While all are technically optional for successful completion of the above command, two of them -- Common Name and Challenge Password -- do require special consideration where Postgres server certificates are concerned, and you should also consult with your trusted CA as they may have requirements for specific metadata fields as well.

While, in general, any value can be given here (including blank/nothing), if you wish to use the verify-full client sslmode when connecting to the server (see "sslmode (client side SSL connection specification)" below) this field must contain the full FQDN that is used by clients as the server hostname value when connecting. For example, this client connection attempt will only work if the server certificate's Common Name value is 'my-db.my-company.net':

psql "host=my-db.my-company.net user=someuser dbname=somedb sslmode=verify-full"

Here is how the PostgreSQL manual describes the use of a password on your certificate:

"If the private key is protected with a passphrase, the server will prompt for the passphrase and will not start until it has been entered. Using a passphrase by default disables the ability to change the server's SSL configuration without a server restart, but see ssl_passphrase_command_supports_reload. Furthermore, passphrase-protected private keys cannot be used at all on Windows."

Given that, unless you are working on a non-Windows system with security requirements that specify the use of a passphrase on your database server certificates, this should be left blank.

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.
• Any other intermediate certificates in the signing chain between the root certificate and 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.

For more details on certificate signing chains see "How can I determine the order of certificates in a certificate chain?" in the FAQs section below.

The target location for the server's SSL private key file is determined by the ssl_key_file configuration parameter, which defaults to 'server.key' (hence the name we used when generating it) in the data directory:

mv server.key $PGDATA/
chmod 0600 $PGDATA/server.key

The target location for the server's SSL certificate file is determined by the ssl_cert_file configuration parameter, which defaults to 'server.crt' in the data directory.

Any intermediate trusted CA certificate(s)present should be concatenated to the server certificate, first the intermediate certificate that signed your server certificate then the one that signed that one and so on.

The root trusted CA certificate should be copied to a file named root.crt in $HOME/.postgresql/root.crt for each client host and system user that will make connections to the server. This allows said client(s) to validate that the certificate or certificate chain presented by the server at connection time was signed by a locally held trusted root CA in $HOME/.postgresql/root.crt.

Let's say your trusted CA returns the following files to you, where SERVER-FQDN is the CN value you used in your CSR above, and you wish to have system user bob connect to the server and validate the server's certificate:

RootCA.crt
IntermediateCA_FOO.crt
IntermediateCA_BAR.crt
SERVER-FQDN.crt 

We can install those certificates for use by the Postgres server and validation by the bob system user like so:

# Copy the signed cert to $PGDATA/server.crt
cp SERVER-FQDN.crt $PGDATA/server.crt

# Append the intermediate certs to the server certificate file
cat IntermediateCA_BAR.crt IntermediateCA_FOO.crt >> $PGDATA/server.crt

# Copy the root CA certificate in the `bob` system user's $HOME/.postgresql/root.crt
cp RootCA.crt ~bob/.postgresql/root.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?"

In order to use the certificates you've just installed to enable SSL encrypted connections the server must have its ssl configuration parameter set to 'on'. You can check that with this SHOW query:

SHOW ssl;

If it is off you can enable it by running the following with a superuser login:

ALTER SYSTEM SET ssl = 'on';
SELECT pg_reload_conf();

Postgres allows for SSL-enabled and non-SSL-enabled TCP connections as specified by the use of hostssl, hostnossl, and host (which will accept both SSL and non-SSL connections) type entries in pg_hba.conf. In order to only allow SSL connections for a given pg_hba.conf entry you should only use hostssl, e.g. this will enable SSL encrypted connections to the mydb database for user enterprisedb from the localhost TCP address with scram-sha-256 password authentication:

hostssl mydb enterprisedb 127.0.0.1/32 scram-sha-256

And you can explicitly reject all non-SSL TCP requests from anywhere with this:

hostnossl all all all eject

The PostgreSQL client interface library, libpq, provides the sslmode client connection parameter to allow the client to specify its SSL preferences. A full list of allowed values along with detailed descriptions of the protection offered by each in section "34.19.3. Protection Provided in Different Modes" in the SSL Support chapter of the PostgreSQL online manual. For the purposes of this article you should use verify-ca or verify-full, with the difference being:

• verify-ca - The server certificate or certificate chain will be validated against root CA certificate installed on the client host. If that validation fails or if there is no root CA certificate present on the client host then the connection will not be allowed.
• verify-full - In addition to the root CA certificate validation performed as described above in the verify-ca section the server certificate's Common Name metadata attribute must match the hostname used by the client when connecting to the server. If it does not then the connection will not be allowed.

Note that there is a third option, require, that the client can use to request an SSL connection. That will perform the root CA certificate validation if it is present on the client host, but if a root CA certificate is not present the connection will still be allowed. As that can allow for Man-in-the-middle attacks, either verify-ca or verify-full should be used to require that the client has a root CA certificate to validate against the certificate or certificate chain provided by the server.

At this point you should have:

1. Your server's private key and certificate files installed on the server, as well as any intermediate certificates appended to the server's certificate file.
2. The signing certificate chain's root certificate installed on your client host(s).
3. hostssl entries for your client host(s) in your server's pg_hba.conf.

Let's say you wish to connect to the mydb database on host my-db.my-company.net with user enterprisedb.

Using the psql client, this will verify the server's presented certificate chain against the root CA certificate stored on the client host you are connecting from:

psql "host=my-db.my-company.net user=enterprisedb dbname=mydb sslmode=verify-ca"

In addition, the certificate verification performed by sslmode=verify-ca, specifying sslmode=verify-full will also confirm that the hostname used in the client connection string matches the Common Name metadata value of the server's certificate:

psql "host=my-db.my-company.net user=enterprisedb dbname=mydb sslmode=verify-full"

The openssl x509 command can be used to examine various certificate content items. Of particular note are the Subject and Issuer items:

• Subject - The Subject line will contain all of the metadata items entered when the given certificate's CSR was created.
• Issuer - The Issuer line will contain all of the same metadata items from the certificate that was used to sign the given certificate.
• A root CA certificate will have the same values for both the Subject and Issuer field since it is self-signed.

Given the above and a set of certificates with multiple intermediate certificates, you can determine the proper ordering by running the following command for each certificate:

openssl x509 -noout -subject -issuer -in <path_to_cert> 

and noting:

• The Subject and Issuer field values from the root certificate will match as described above.
• The Issuer field of any non-root certificate will be equal to the above Subject of the certificate that signed it.

Let's say that you've got the following four certificates:

RootCA.crt
IntermediateCA_FOO.crt
IntermediateCA_BAR.crt
YourCert.crt 

Run the following four commands:

openssl x509 -noout -issuer -subject -in RootCA.crt 
openssl x509 -noout -issuer -subject -in IntermediateCA_FOO.crt
openssl x509 -noout -issuer -subject -in IntermediateCA_BAR.crt 
openssl x509 -noout -issuer -subject -in YourCert.crt 

Then note:

• The Issuer field on the intermediate certificate signed by the root certificate will match said root certificate's Issuer and Subject values. Let's say that is IntermediateCA_BAR.crt.
• The Issuer field on the IntermediateCA_FOO.crt certificate should then be equal to the Subject field on the IntermediateCA_BAR.crt certificate.
• The Issuer field on the YourCert.crt certificate should then be equal to the Subject field on the IntermediateCA_FOO.crt certificate.
• The Subject field on the YourCert.crt certificate should contain the metadata items you provided when creating the CSR you submitted to your trusted CA.

which establishes the certificate chain order as: RootCA.crt -> IntermediateCA_BAR.crt -> IntermediateCA_FOO.crt -> YourCert.crt

Given a signed certificate named YourCert.crt and a private key named server.key you can verify that they match by executing the following openssl rsa and openssl x509 commands and verifying that the outputs match:

openssl rsa -noout -modulus -in server.key | openssl md5
openssl x509 -noout -modulus -in YourCert.crt | openssl md5