How To Configure PostgreSQL To Use Mutual TLS?

Owned by Justas Samuolis, created
Last updated: Sept 13, 2023 by Raido Kaju
2 min read

The article describes how to configure Central Server and Security Server to use mutual TLS (mTLS) for database connection so it would be more secure.

Prerequisites

To configure Central Server or Security Server to use mTLS for the database connections, signed certificates should be prepared to all sides.

For development purposes the self signed root CA can be used. The sample instructions how to generate certificates is provided at the bottom.

PostgreSQL

  1. Modify postgresql.conf configuration file to enable ssl and provide paths to certificates:

    ssl = on ssl_cert_file = '<path to server certificate>' ssl_key_file = '<path to server key>' ssl_ca_file = '<path to root CA>'

  2. Modify pg_hba.conf configuration file. Add the hostssl (or change the existing host to hostssl) entries with clientcert authentication option. This option can be set to verify-ca or verify-full. Both options require the client to present a valid (trusted) SSL certificate. More details can be found here https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

    hostssl all all 127.0.0.1/32 scram-sha-256 clientcert=verify-ca

  3. Restart PostgreSQL service

    sudo service postgresql restart

Central Server (< 7.3.0)

Steps to configure Central Server to use mTLS for database connections:

  1. Modify /etc/xroad/db.properties. Provide paths to root CA and client certificates.

    Note. JDBC client supports key file format PKCS-12 or PKCS-8. To convert PEM key the following command can be used:

  2. Restart the Central Server

  3. Verify Central Server started successfully.

Security Server / Central Server (>= 7.3.0)

Steps to configure Security Server to use mTLS for database connections:

  1. Edit /etc/xroad/db.properties. Modify the connection.url and provide paths to client certificates.

    Note. JDBC client supports key file format PKCS-12 or PKCS-8.

  2. Restart Security Server

  3. Verify Security Server started successfully.

Creating certificates

Instructions how to create certificates with self signed root CA.

  1. Create root CA private/public keys and certificate signing request (CSR):

    Sign the root certificate:

  2. Create a server certificate and sign by root CA:

    server.key and server.crt will be used on PostgreSQL server.

  3. Create and sign client certificate for client. Create a separate certificate for each client.

    client.key and client.crt will be used on the client.

More details on configuring PostgreSQL can be found in official PostgreSQL documentation: