Question: How do you set up TLS for PostgreSQL replication?

Answer

Setting up TLS (Transport Layer Security) for PostgreSQL replication involves configuring your primary and replica servers to communicate over a secure channel. This is crucial for protecting sensitive data in transit between the primary database server and its replicas. Here's a step-by-step guide:

Step 1: Generate the TLS Certificates

First, you need to create SSL certificates. You can use self-signed certificates or obtain them from a Certificate Authority (CA). For simplicity, this example shows how to generate a self-signed certificate and key.

mkdir -p ~/certs
cd ~/certs
openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj \"/CN=postgres-server\"
chmod og-rwx server.key

Copy server.crt and server.key to the PostgreSQL data directory on both the primary and replica servers. The location of the data directory can vary but is often found at /var/lib/postgresql/12/main.

Step 2: Configure the Primary Server

Edit the postgresql.conf file on the primary server to enable SSL:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Also, configure the pg_hba.conf file to require SSL for replication connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Replication connections
hostssl replication     all             all                     md5

Step 3: Configure the Replica Server

On the replica server, modify the postgresql.conf similarly to enable SSL (as done for the primary in Step 2).

For setting up the replication itself, ensure the connection string in the primary_conninfo setting of the recovery configuration (recovery.conf for versions before PostgreSQL 12, or in the postgresql.conf for PostgreSQL 12 and later) includes SSL options:

primary_conninfo = 'host=primary_host port=5432 user=replication_user password=replication_password sslmode=require'

This tells the replica to connect to the primary using SSL.

Step 4: Restart PostgreSQL Servers

After configuring both the primary and replica servers, restart them to apply the changes:

sudo systemctl restart postgresql

Final Thoughts

By following these steps, you have configured your PostgreSQL servers for replication over a secure TLS connection. This setup enhances the security of your data by ensuring that replication traffic is encrypted.

Remember to keep your certificates and keys secure, and consider using certificates from a trusted CA for production environments.