Configure PostgreSQL 17 SSL encryption and certificate-based authentication

Install PostgreSQL 17 and OpenSSL

Install PostgreSQL 17 and the OpenSSL tools needed to build a certificate authority.

sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17 openssl

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql17-server postgresql17 openssl
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable --now postgresql-17

Create a private certificate authority

The CA signs both the server certificate and every client certificate. Keep the CA private key offline or heavily restricted after issuing certificates.

sudo mkdir -p /etc/postgresql/ssl/ca
cd /etc/postgresql/ssl/ca
sudo openssl genrsa -aes256 -out ca.key 4096
sudo openssl req -new -x509 -days 3650 -key ca.key -sha256 \
  -out ca.crt \
  -subj "/C=NL/O=Example Corp/CN=Example Corp PostgreSQL CA"

You will be prompted for a passphrase to encrypt the CA private key. Use a strong passphrase and store it in a secrets manager, not on the filesystem.

Generate the server certificate

Create a key and certificate signing request (CSR) for the PostgreSQL server. The common name must match the hostname clients use to connect.

sudo mkdir -p /etc/postgresql/ssl/server
cd /etc/postgresql/ssl/server
sudo openssl genrsa -out server.key 4096
sudo openssl req -new -key server.key -out server.csr \
  -subj "/C=NL/O=Example Corp/CN=db01.example.com"

Sign the CSR with the CA to produce the server certificate, valid for one year.

sudo openssl x509 -req -in server.csr -CA /etc/postgresql/ssl/ca/ca.crt \
  -CAkey /etc/postgresql/ssl/ca/ca.key -CAcreateserial \
  -out server.crt -days 365 -sha256

Set correct ownership and permissions on certificates

PostgreSQL refuses to start if the server private key is readable by other users. The postgres process owns and reads these files directly, so ownership must belong to the postgres user with restrictive permissions.

sudo chown postgres:postgres /etc/postgresql/ssl/server/server.key /etc/postgresql/ssl/server/server.crt
sudo chmod 600 /etc/postgresql/ssl/server/server.key
sudo chmod 644 /etc/postgresql/ssl/server/server.crt
sudo chown postgres:postgres /etc/postgresql/ssl/ca/ca.crt
sudo chmod 644 /etc/postgresql/ssl/ca/ca.crt

Enable SSL in postgresql.conf

Point PostgreSQL at the certificate files and turn on SSL support.

ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server/server.key'
ssl_ca_file = '/etc/postgresql/ssl/ca/ca.crt'
ssl_prefer_server_ciphers = on

Enforce strong TLS protocol versions and cipher suites

Restrict connections to TLS 1.2 and above, and set an explicit cipher list to block weak algorithms.

ssl_min_protocol_version = 'TLSv1.2'
ssl_max_protocol_version = 'TLSv1.3'
ssl_ciphers = 'HIGH:!aNULL:!MD5:!3DES:!RC4'
ssl_ecdh_curve = 'prime256v1'

Restart PostgreSQL to apply the SSL settings.

sudo systemctl restart postgresql

Issue a client certificate for mutual TLS

Each client certificate's common name must exactly match the PostgreSQL role it authenticates as. Generate one per application or admin user.

sudo mkdir -p /etc/postgresql/ssl/clients
cd /etc/postgresql/ssl/clients
sudo openssl genrsa -out app_user.key 4096
sudo openssl req -new -key app_user.key -out app_user.csr \
  -subj "/C=NL/O=Example Corp/CN=app_user"
sudo openssl x509 -req -in app_user.csr -CA /etc/postgresql/ssl/ca/ca.crt \
  -CAkey /etc/postgresql/ssl/ca/ca.key -CAcreateserial \
  -out app_user.crt -days 365 -sha256

Set restrictive ownership before distributing the client key to the application host.

sudo chmod 600 app_user.key
sudo chmod 644 app_user.crt

Create the matching PostgreSQL role

The role name must exactly match the certificate common name for cert authentication to succeed.

sudo -u postgres psql -c "CREATE ROLE app_user LOGIN;"
sudo -u postgres psql -c "GRANT CONNECT ON DATABASE appdb TO app_user;"

Configure pg_hba.conf for certificate authentication

Set the authentication method to cert and require clientcert verification so PostgreSQL rejects any connection lacking a CA-signed client certificate.

# TYPE  DATABASE  USER      ADDRESS         METHOD
hostssl appdb     app_user  203.0.113.0/24  cert clientcert=verify-full
hostssl all       all       0.0.0.0/0       reject

The clientcert=verify-full option checks both that the certificate is signed by the trusted CA and that its common name matches the connecting role. Reload PostgreSQL to apply the rules.

sudo systemctl reload postgresql