Securing PostgreSQL with SSL Encryption

When implementing Patroni for PostgreSQL high availability, ensuring secure connections is paramount. One essential aspect is configuring SSL encryption to safeguard data transmission. Below is a step-by-step guide to configure SSL encryption for Patroni-managed PostgreSQL instances, along with detailed explanations at each stage.

Step 1: Patroni Configuration Update

patronictl -c /etc/patroni/patroni.yml edit-config

Adjust the patroni.yml configuration file to incorporate SSL encryption:

# force clients to use TLS v1.3 or newer

postgresql:
  parameters:
    ssl: true
    ssl_ca_file: /var/data/root.crt
    # (change requires restart)
    ssl_cert_file: /var/data/server.crt
    ssl_ciphers: TLS_AES_256_GCM_SHA384:TLS_AES_128_GCM_SHA256:TLS_AES_128_CCM_SHA256:TLS_CHACHA20_POLY1305_SHA256:ECDHE-ECDSA-AES256-CCM:ECDHE-ECDSA-AES128-CCM:DHE-RSA-AES256-CCM:DHE-RSA-AES128-CCM:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-CHACHA20-POLY1305
    ssl_crl_file: ''
    # (change requires restart)
    ssl_key_file: server.key
    # force clients to use TLS v1.3 or newer
    ssl_min_protocol_version: TLSv1.3
    ssl_passphrase_command: This is only test
    ssl_prefer_server_ciphers: true

Ensure to specify SSL-related parameters including the CA file, server certificate, key file, and preferred ciphers. Set other parameters such as loop_wait and maximum_lag_on_failover to tailor Patroni's behavior.

Step 2: SSL Key and Certificate Generation

To create a simple self-signed certificate for the server, valid for 3650 days, use the following OpenSSL command, replacing dbhost.yourdomain.com with the server's host name:

cd /var/data/

openssl req -new -x509 -days 3650 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"

chmod og-rwx server.key

To create a server certificate whose identity can be validated by clients, first create a certificate signing request (CSR) and a public/private key file:

openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key

Then, sign the request with the key to create a root certificate authority (using the default OpenSSL configuration file location on Linux):

openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

Step 3: Generate trusted root certificate

Finally, create a server certificate signed by the new root certificate authority:

openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key

openssl x509 -req -in server.csr -text -days 3650 \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out server.crt

Step 4: Bonus: create chain of trust that includes intermediate certificates:

# root
openssl req -new -nodes -text -out root.csr \
  -keyout root.key -subj "/CN=root.yourdomain.com"
chmod og-rwx root.key
openssl x509 -req -in root.csr -text -days 3650 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -signkey root.key -out root.crt

# intermediate
openssl req -new -nodes -text -out intermediate.csr \
  -keyout intermediate.key -subj "/CN=intermediate.yourdomain.com"
chmod og-rwx intermediate.key
openssl x509 -req -in intermediate.csr -text -days 1825 \
  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
  -CA root.crt -CAkey root.key -CAcreateserial \
  -out intermediate.crt

# leaf
openssl req -new -nodes -text -out server.csr \
  -keyout server.key -subj "/CN=dbhost.yourdomain.com"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 \
  -CA intermediate.crt -CAkey intermediate.key -CAcreateserial \
  -out server.crt

Step 5: Update pg_hba.conf

Edit the pg_hba.conf file to allow SSL-encrypted connections:

hostssl all all 10.10.80.68/32 md5

Specify the appropriate authentication method (md5) and SSL settings.

Step 6: Test SSL Connection and Check

psql 'host=10.10.80.68 user=postgres sslmode=require'

select name, setting from pg_settings where name like 'ssl%file';
        name        |       setting
--------------------+----------------------
 ssl_ca_file        | /var/data/root.crt
 ssl_cert_file      | /var/data/server.crt
 ssl_crl_file       |
 ssl_dh_params_file |
 ssl_key_file       | server.key
(5 rows)

Step 7: Additional Information

1. Use TYPE hostssl when administrating the database cluster as a superuser.
2. Use TYPE hostnossl for performance purposes and when DML operations are deemed safe without SSL connections.
3. A self-signed certificate can be used for testing, but a certificate signed by a certificate authority (CA) (either one of the global CAs or a local one) should be used in production so that clients can verify the server’s identity. If all the database clients are local to the organization, using a local CA is recommended.

Connect to the PostgreSQL server using psql with SSL mode set to require. Input the necessary credentials and observe successful SSL connection establishment. By meticulously following these steps alongside the provided configuration commands, SSL encryption can be seamlessly integrated into Patroni-managed PostgreSQL instances, fortifying data security during transmission. For more detailed and technical articles like this, keep following our blog on Medium. If you have any questions or need further assistance, feel free to reach out in the comments below and directly.