Configuring Your PostgreSQL Server for Mutual TLS
How to use TLS, client authentication, and CA certificates in PostgreSQL
Create a private key and request a certificate for your PostgreSQL server
Before you can teach your server to speak TLS, you will need a certificate issued by a trusted certificate authority (CA). If your organization already runs its own CA and you have a private key and certificate for your PostgreSQL server, along with your CA's root certificate, you can skip to the next step.
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 "myserver.internal.net" server.crt server.keyYour certificate and private key will be saved in server.crt and server.key respectively.
Request a copy of your CA root certificate, which will be used to make sure each application can trust certificates presented by other applications.
$ step ca root ca.crtYour certificate will be saved in ca.crt.
Configure PostgreSQL to authenticate itself with its TLS certificate
We now want to instruct our PostgreSQL server to identify itself using the certificate issued in the last step and to force clients to connect over TLS.
To start PostgreSQL in SSL mode, first enable SSL in postgresql.conf.
# ...
ssl = on
# ...Put your server.crt and server.key files in your installation's data directory, often at /var/lib/pgsql/data or /usr/local/pgsql/data. Make sure their filenames are server.crt and server.key respectively, which are the expected defaults.
$ sudo cp server.crt /var/lib/pgsql/data/server.crt
$ sudo cp server.key /var/lib/pgsql/data/server.keyYou'll need to ensure that PostgreSQL has access to the files and set the private key file permissions to disallow access to world or group.
$ sudo chown postgres:postgres /var/lib/pgsql/data/server.{crt,key}
$ sudo chmod 0600 /var/lib/pgsql/data/server.keyIf you'd like to specify a different path for these files, manually configure them in postgresql.conf.
# ...
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
# ...In your pg_hba.conf file, change all records for non-local connections from host to hostssl to require clients to connect over TLS. It might look something like this.
# TYPE DATABASE USER ADDRESS METHOD
# ...
hostssl all all all md5Finally, restart your PostgreSQL server for your changes to take effect.
Read more
Configure PostgreSQL to require clients to authenticate with a certificate issued by your CA
To tell PostgreSQL to use mutual TLS and not just one-way TLS, we must instruct it to require client authentication to ensure clients present a certificate from our CA when they connect.
Move your ca.crt certificate to your PostgreSQL data directory—often at /var/lib/pgsql/data or /usr/local/pgsql/data—and name it root.crt (the usual convention, though other paths are possible).
$ sudo cp ca.crt /var/lib/pgsql/data/root.crtMake sure PostgreSQL has access to the file.
$ sudo chown postgres:postgres /var/lib/pgsql/data/root.crtConfigure postgresql.conf to point to your root CA certificate. PostgreSQL will use this certificate to verify certificates presented by clients.
# ...
ssl_ca_file = 'root.crt'
# ...Configure pg_hba.conf, creating hostssl records with the clientcert=1 option for all relevant connections. It might look something like this:
# TYPE DATABASE USER ADDRESS METHOD
# ...
# IPv4 remote connections for authenticated users
hostssl all myuser 0.0.0.0/0 md5 clientcert=1Alternatively, if you'd like to disable password authentication and lean exclusively on client certificates for authentication, change from the md5 authentication method and use the cert method instead. Note, however that this requires that the identity used as the Common Name in the certificate (when issued by your CA, eg. myserver.internal.net) exactly matches the PostgreSQL database user specified in connections from clients.
# ...
hostssl all myuser 0.0.0.0/0 cert clientcert=1Finally, restart your PostgreSQL server for your changes to take effect.
That's it! PostgreSQL should now be able to receive TLS connections from clients who authenticate themselves using a certificate issued by your trusted CA.
Automate certificate renewal
By default, step-ca issues certificates with a 24 hour expiration. Short-lived certificates have many benefits but also require that you renew your certificates each day before they expire. How you renew certificates is often dependent on how you deploy your application. See the step-ca certificate lifecycle management docs for more information.
All documentation content from the Hello mTLS project is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0).
Connect to Your PostgreSQL Server from a Client
Subscribe to updates
Unsubscribe anytime. See our privacy policy.
© 2024 Smallstep Labs, Inc. All rights reserved.