PostgreSQL
Psql

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.key

Your 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.crt

Your 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.key

You'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.key

If 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 md5

Finally, restart your PostgreSQL server for your changes to take effect.

Improve this content

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.crt

Make sure PostgreSQL has access to the file.

$ sudo chown postgres:postgres /var/lib/pgsql/data/root.crt

Configure 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=1

Alternatively, 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=1

Finally, 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.key

Your 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.key

The 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).

Creative Commons License
Subscribe
Subscribe to updates

Unsubscribe anytime. See our privacy policy.