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:
- Roll your own with open source
step-ca
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.keyYour 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=onenables TLS support, but does not require TLS. - To require TLS on the server side, you must configure the
pg_hba.conffile to use thehostsslrule type. See thepg_hba.conffile documentation for details. - TLS can be enforced on the client side with
sslmode=require,sslmode=verify-ca, orsslmode=verify-fulloptions. See PostgreSQL libpq SSL support for a full description of these client options. - Do not use
POSTGRES_HOST_AUTH_METHOD=trustin production. It's provided as an easy way to pre-configure thepg_hba.conffile 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
stepCLI 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.
To learn more, see Configuring step-ca Provisioners.
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 720hFor instructions on adding provisioners to open source step-ca, or to learn more about other provisioner types, see Configuring step-ca Provisioners.
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.
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.timerYou'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.
Full research notes
Client verification of server certificates Use the
sslrootcertconfig parameter to change the location of the root CA bundle. By default, psql will look in~/.postgresql/root.crt. Usesslmode=verify-fullto 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.confthat 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
Further reading
- Configure pg_hba.conf to require TLS for user connections
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.
Subscribe to updates
Unsubscribe anytime. See our privacy policy.
© 2023 Smallstep Labs, Inc. All rights reserved.