A Complete Guide to Securely Connecting PostgreSQL and Psql Using Mutual TLS
How to use TLS, client authentication, and CA certificates in PostgreSQL and Psql
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.
Create a private key and request a certificate for your Psql client
Request a new certificate from your CA to represent your Psql client.
$ step ca certificate "myuser" client.crt client.keyYour certificate and private key will be saved in client.crt and client.key respectively.
Open a connection from Psql using mutual TLS
Now, we need only to configure our Psql client to make authenticated requests using our certificate and private key. The CA root certificate will be used to verify that the client can trust the certificate presented by the server.
Connect to your PostgreSQL database using psql connection parameters to specify the location of your client certificate, private key, and root CA certificate.
Setting the sslmode parameter to verify-full also ensures that the PostgreSQL server name matches the name in the certificate it presents to clients.
$ psql "host=myserver.internal.net port=5432 user=myuser dbname=mydatabase sslmode=verify-full sslcert=client.crt sslkey=client.key sslrootcert=ca.crt"As an alternative, if you'd like to avoid specifying file paths on each connection, you can copy them to your psql client's configuration directory.
$ mkdir -p ~/.postgresql
$ cp ca.crt ~/.postgresql/root.crt
$ cp client.crt ~/.postgresql/postgresql.crt
$ cp client.key ~/.postgresql/postgresql.keyThe connection command then becomes only the following:
$ psql "host=myserver.internal.net port=5432 user=myuser dbname=mydatabase sslmode=verify-full"Read more
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).
Subscribe to updates
Unsubscribe anytime. See our privacy policy.
© 2024 Smallstep Labs, Inc. All rights reserved.