Using Mutual TLS on the Client Side with Psql
How to use TLS, client authentication, and CA certificates in Psql
Create a private key and request a certificate for your Psql client
Before you can teach your client 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 Psql client, 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 client name for the actual name / DNS name of 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.
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.
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=443 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=443 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).
Connect to a Server from Psql
Subscribe to updates
Unsubscribe anytime. See our privacy policy.
© 2024 Smallstep Labs, Inc. All rights reserved.