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.

If your organization does not yet run its own internal CA, you can read more about creating and running a CA using the open source smallstep software here.

$ step ca certificate "" 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 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. exactly matches the PostgreSQL database user specified in connections from clients.

# ... hostssl all myuser 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.

All documentation content from the Hello mTLS project is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0).

Creative Commons License

Connect to Your PostgreSQL Server from a Client

© 2020 Smallstep Labs, Inc. All rights reserved.