PostgreSQL TLS — Practical Zero Trust

How to get and renew PostgreSQL TLS certificates

Written Janaury 10, 2022

Zero Trust or BeyondProd approaches require authenticated and encrypted communications everywhere. TLS is the cryptographic protocol that powers encryption for all your technologies. For TLS, you need certificates. This practitioner's tutorial provides instructions for automating PostgreSQL TLS certificate renewal and enabling server-side encryption.

Try it

Create a private key and request a certificate

Before you can configure PostgreSQL TLS, you will need a certificate issued by a trusted certificate authority (CA). If you already have a certificate, private key, and CA root certificate from your organization's existing CA, you can skip to the PostgreSQL TLS configuration section below. If you need to generate a certificate, you can:

To request a certificate from your CA using the step CLI, bootstrap your CA with step ca bootstrap and run the following command (sub the server name for the actual name / DNS name of your PostgreSQL server).

step ca certificate "db.example.net" server.crt server.key

Your certificate and private key will be saved in server.crt and server.key respectively.

Configure PostgreSQL to use the certificate

You can test your certificate by starting up PostgreSQL with TLS enabled.

The simplest way to try PostgreSQL with TLS is to use a Docker container for testing.

mkdir tls mv server.crt server.key tls docker run -it --rm -e POSTGRES_HOST_AUTH_METHOD=trust \ --mount type=bind,source="$PWD"/tls,target=/var/run/postgresql/tls \ -p 5432:5432 \ postgres \ -c ssl=on \ -c ssl_cert_file=/var/run/postgresql/tls/server.crt \ -c ssl_key_file=/var/run/postgresql/tls/server.key \ -c ssl_min_protocol_version=TLSv1.2
  • Note that ssl=on enables TLS support, but does not require TLS.
  • To require TLS on the server side, you must configure the pg_hba.conf file to use the hostssl rule type. See the pg_hba.conf file documentation for details.
  • TLS can be enforced on the client side with sslmode=require, sslmode=verify-ca, or sslmode=verify-full options. See PostgreSQL libpq SSL support for a full description of these client options.
  • Do not use POSTGRES_HOST_AUTH_METHOD=trust in production. It's provided as an easy way to pre-configure the pg_hba.conf file for testing environments.

Test PostgreSQL TLS configuration

Now run the psql client with strict certificate verification, and check that SSL is in use:

mkdir ~/.postgresql
step ca root > ~/.postgresql/root.crt
psql "postgresql://postgres@db.example.net:5432/postgres?sslmode=verify-full"

You should see that TLS is used to establish the connection:

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
  
postgres=#

Operationalize It

Select a provisioner

Smallstep CAs use provisioners to authenticate certificate requests using passwords, one-time tokens, single sign-on, and a variety of other mechanisms.

  • ACME (RFC8555) is an open standard, used by Let's Encrypt, for authenticating certificate requests. To use ACME on a private network you need to run an ACME server. ACME is harder to setup, but has a large client ecosystem (some software even has built-in support).
  • Other provisioners use the open source step CLI and do not require a local network agent. The instructions below focus on the JWK provisioner, but can be repurposed with small tweaks to operationalize all non-ACME provisioners.
Show me instructions for...

The right provisioner depends on your operational environment.

The JWK provisioner is the most general-purpose provisioner. It supports password and one-time token-based authentication. To add a JWK provisioner called postgresql to a hosted Certificate Manager authority (if you haven't already), run:

step ca provisioner add postgresql --type JWK --create --x509-default-dur 720h

For instructions on adding provisioners to open source step-ca, or to learn more about other provisioner types, see Configuring step-ca Provisioners.

The ACME protocol requires access to your internal network or DNS in order to satisfy ACME challenges. For hosted Certificate Manager CAs, you'll need to configure an ACME Registration Authority on your network that will act as an ACME agent to Certificate Manager.

Configure PostgreSQL TLS Certificate Automation

We've created a systemd-based certificate renewal timer that works with step. Check out our documentation on Renewal using systemd timers for background on how these timers work.

To install the certificate renewal unit files, run:

cd /etc/systemd/system sudo curl -sL https://files.smallstep.com/cert-renewer@.service \ -o cert-renewer@.service sudo curl -sL https://files.smallstep.com/cert-renewer@.timer \ -o cert-renewer@.timer

The renewal timer will check your certificate files every five minutes and renew them after two-thirds of their lifetime has elapsed.

We've created a systemd renewal timer for renewing certificates with a Smallstep CA (see non-ACME Linux instructions). However, we haven't yet investigated how to modify that timer for ACME use cases. We're working on it, but feel free to contribute this content directly on GitHub. At this point, you will need to manually create the cert-renewer@.service and cert-renewer@.timer template files.

To renew and hot-reload the PostgreSQL server certificate, we will need a PostgreSQL-specific systemd override file that supplies the certificate file paths to the renewer. To install the override, run:

sudo mkdir /etc/systemd/system/cert-renewer@postgresql.service.d cat <<EOF | sudo tee /etc/systemd/system/cert-renewer@postgresql.service.d/override.conf [Service] ; "Environment=" overrides are applied per environment variable. This line does not ; affect any other variables set in the service template. Environment=CERT_LOCATION=/var/run/postgresql/tls/server.crt \\ KEY_LOCATION=/var/run/postgresql/tls/server.key EOF

After the renewer renews the certificate, the cert-renewer@postgresql.service unit will run systemctl try-reload-or-restart on postgresql.service. For online certificate rotation to work properly, confirm that the postgresql.service on your system (typically in /usr/lib/systemd/system) has an ExecReload defined that will run pg_ctl reload to hot-reload the PostgreSQL server certificate. You should see something like:

ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s

To start the renewal timer, run:

sudo systemctl daemon-reload sudo systemctl enable --now cert-renewer@postgresql.timer

You'll see that the timer is active, by checking the output of systemctl list-timers.

Distribute your root certificate to end users and systems

Once PostgreSQL server TLS is enforced, you'll need to make sure that remote clients will verify and trust certificates signed by your CA. Sadly, PostgreSQL's psql client does not verify server certificates by default. To guarantee authenticated communication with the server, it needs to be explicitly configured to trust your CA, and to verify the server's certificate (using sslmode=verify-full).

The psql CLI client will not read CA certificates from the system trust store such as the macOS Keychain. The psql client looks for CA certificates in the ~/.postgresql/root.crt file in the user's home directory.

Research notes

In researching PostgreSQL TLS, we did some thorough investigation. Here are our rough notes if you are interested in diving deeper.

  • Client verification of server certificates Use the sslrootcert config parameter to change the location of the root CA bundle. By default, psql will look in ~/.postgresql/root.crt. Use sslmode=verify-full to check both the server certificate CA chain, and that the certificate common name matches the server hostname.

  • Here's an example of a pg_hba.conf that will configure PostgreSQL to require TLS for all non-local connections:

    # TYPE     DATABASE        USER            ADDRESS              METHOD
    local      all             all                                  trust
    hostssl    all             all             localhost            scram-sha-256
    hostssl    all             all             0.0.0.0/0            scram-sha-256
    
    # Or, use client certificates with a CN set to the username
    #
    # TYPE     DATABASE        USER            ADDRESS              METHOD
    hostssl    all             all             0.0.0.0/0            cert
    

Contribute to this document

The Practical Zero Trust project is a collection of living documents detailing TLS configuration across a broad spread of technologies. We'd love to make this document better. Feel free to contribute any improvements directly on GitHub.