How to Configure PostgreSQL SSL Connection

This tutorial shows you how to configure PostgreSQL SSL connection using self-signed certificates. You'll learn to:

Generate SSL certificates (CA, server, client)
Configure PostgreSQL server for SSL
Test SSL connections from clients

Prerequisites#

# Verify PostgreSQL installation
postgres --version

# Verify OpenSSL installation
openssl version

Ensure you have PostgreSQL and OpenSSL installed.

OpenSSL Config#

Set up the configuration file:

cat >req.conf <<EOF
[ req ]
distinguished_name = req_distinguished_name
x509_extensions = v3_ca
prompt = no
[ req_distinguished_name ]
C = CN
ST = GD
O = Bytebase
CN = root
[ v3_ca ]
basicConstraints = critical,CA:TRUE
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid:always,issuer:always
[ v3_req ]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[ alt_names ]
IP.1 = YOUR_SERVER_IP
IP.2 = 127.0.0.1
DNS.1 = YOUR_SERVER_HOSTNAME
DNS.2 = localhost
EOF

Replace YOUR_SERVER_IP with your real server IP and YOUR_SERVER_HOSTNAME with your server hostname.

Generate Certificates#

Generate Root CA key and certificate:

openssl genrsa -out ca-key.pem 2048
openssl req -x509 -new -key ca-key.pem -sha256 -days 36500 -out ca-cert.pem -extensions 'v3_ca' -config req.conf

Generate Server key and certificate:

openssl genrsa -out server-key.pem 2048
openssl req -new -sha256 -key server-key.pem -out server-req.pem -subj "/C=CN/ST=GD/O=Bytebase/CN=localhost" -config req.conf
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -in server-req.pem -out server-cert.pem -extfile req.conf

Generate Client key and certificate:

openssl genrsa -out client-key.pem 2048
openssl req -new -sha256 -key client-key.pem -out client-req.pem -subj "/C=CN/ST=GD/O=Bytebase/CN=postgres-client" -config req.conf
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -in client-req.pem -out client-cert.pem -extfile req.conf

Configure PostgreSQL Server#

Copy SSL files and set permissions:

For macOS (Homebrew):

# For Apple Silicon Macs
sudo mkdir -p /opt/homebrew/var/postgresql@14/ssl
sudo cp ca-cert.pem server-cert.pem server-key.pem client-cert.pem client-key.pem /opt/homebrew/var/postgresql@14/ssl/
sudo chown $(whoami):staff /opt/homebrew/var/postgresql@14/ssl/*
sudo chmod 600 /opt/homebrew/var/postgresql@14/ssl/*-key.pem
sudo chmod 644 /opt/homebrew/var/postgresql@14/ssl/ca-cert.pem /opt/homebrew/var/postgresql@14/ssl/server-cert.pem /opt/homebrew/var/postgresql@14/ssl/client-cert.pem

# For Intel Macs
sudo mkdir -p /usr/local/var/postgresql@14/ssl
sudo cp ca-cert.pem server-cert.pem server-key.pem client-cert.pem client-key.pem /usr/local/var/postgresql@14/ssl/
sudo chown $(whoami):staff /usr/local/var/postgresql@14/ssl/*
sudo chmod 600 /usr/local/var/postgresql@14/ssl/*-key.pem
sudo chmod 644 /usr/local/var/postgresql@14/ssl/ca-cert.pem /usr/local/var/postgresql@14/ssl/server-cert.pem /usr/local/var/postgresql@14/ssl/client-cert.pem

Or change the paths to match your PostgreSQL version if different. Check the version with:

brew list --versions postgresql
ls -la /opt/homebrew/var/ | grep postgresql

For Linux (Ubuntu/Debian):

sudo mkdir -p /etc/postgresql/ssl
sudo cp ca-cert.pem server-cert.pem server-key.pem client-cert.pem client-key.pem /etc/postgresql/ssl/
sudo chown postgres:postgres /etc/postgresql/ssl/*
sudo chmod 600 /etc/postgresql/ssl/*-key.pem
sudo chmod 644 /etc/postgresql/ssl/*.pem

Edit PostgreSQL configuration file:

For Apple Silicon Macs (Homebrew):

sudo nano /opt/homebrew/var/postgresql@14/postgresql.conf

Configuration for macOS (Apple Silicon):

# SSL Configuration
ssl = on
ssl_cert_file = '/opt/homebrew/var/postgresql@14/ssl/server-cert.pem'
ssl_key_file = '/opt/homebrew/var/postgresql@14/ssl/server-key.pem'
ssl_ca_file = '/opt/homebrew/var/postgresql@14/ssl/ca-cert.pem'
listen_addresses = '*'

For Intel Macs (Homebrew):

sudo nano /usr/local/var/postgresql/postgresql.conf

Configuration for macOS (Intel):

# SSL Configuration
ssl = on
ssl_cert_file = '/usr/local/var/postgresql@14/ssl/server-cert.pem'
ssl_key_file = '/usr/local/var/postgresql@14/ssl/server-key.pem'
ssl_ca_file = '/usr/local/var/postgresql@14/ssl/ca-cert.pem'
listen_addresses = '*'

For Linux (Ubuntu/Debian):

sudo nano /etc/postgresql/*/main/postgresql.conf

Configuration for Linux:

# SSL Configuration
ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server-cert.pem'
ssl_key_file = '/etc/postgresql/ssl/server-key.pem'
ssl_ca_file = '/etc/postgresql/ssl/ca-cert.pem'
listen_addresses = '*'

Edit authentication configuration:

For macOS (Homebrew):

# For Apple Silicon Macs
sudo nano /opt/homebrew/var/postgresql@14/pg_hba.conf

# For Intel Macs
sudo nano /usr/local/var/postgresql/pg_hba.conf

For Linux (Ubuntu/Debian):

sudo nano /etc/postgresql/*/main/pg_hba.conf

Add SSL requirement to pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Only allow SSL connections
hostssl all             all             127.0.0.1/32            md5
hostssl all             all             ::1/128                 md5
hostssl all             all             0.0.0.0/0               md5

# Explicitly reject non-SSL connections
host    all             all             127.0.0.1/32            reject
host    all             all             ::1/128                 reject
host    all             all             0.0.0.0/0               reject

Important: Remove or comment out any existing host entries that don't explicitly reject connections to ensure SSL is required.

This configuration requires SSL for all connections, using md5 authentication.

Restart PostgreSQL:

For macOS (Homebrew):

brew services restart postgresql

For Linux (Ubuntu/Debian):

sudo systemctl restart postgresql

Test SSL Connection#

Prerequisites Check#

Ensure PostgreSQL is running with SSL enabled:

# Check PostgreSQL service status
brew services list | grep postgresql

# Check if PostgreSQL is listening on port 5432
lsof -i :5432

# Test basic connectivity
nc -zv localhost 5432

Test SSL Connection#

First, ensure your PostgreSQL user exists:

# Connect as default user (no password needed)
psql postgres

Create a user if it doesn't exist:

-- Create your user with a password (run this inside PostgreSQL)
CREATE USER YOUR_USER_NAME WITH SUPERUSER PASSWORD 'pwd';

-- Exit PostgreSQL
\q

Or if you already have a user, set the password:

ALTER USER YOUR_USER_NAME WITH PASSWORD 'pwd';

Replace YOUR_USER_NAME with your actual username.

Quick SSL verification:

# One-liner to test SSL connection
psql "host=localhost port=5432 dbname=postgres user=$(whoami) sslmode=require" -c "SELECT version();"

Basic SSL connection test:

# Connect with SSL encryption only
psql "host=localhost port=5432 dbname=postgres user=$(whoami) sslmode=require"

SSL connection with CA verification:

# Connect with CA certificate verification
psql "host=localhost port=5432 dbname=postgres user=$(whoami) sslmode=require sslrootcert=ca-cert.pem"

SSL connection with client certificates (advanced - requires additional configuration):

# This requires PostgreSQL to be configured for client certificate authentication
# See troubleshooting section below for setup
psql "host=localhost port=5432 dbname=postgres user=$(whoami) sslmode=require sslcert=client-cert.pem sslkey=client-key.pem sslrootcert=ca-cert.pem"

Verify SSL Status#

Once connected, check SSL details:

-- Check if SSL is enabled
SHOW ssl;

-- Show SSL cipher and version
\conninfo

Test without SSL (should fail)#

# This should fail if SSL is properly configured
psql "host=localhost port=5432 dbname=postgres user=$(whoami) sslmode=disable"

Summary#

You have successfully configured SSL for PostgreSQL:

Generated CA, server, and client certificates
Configured PostgreSQL with SSL settings
Established secure encrypted connections

Your PostgreSQL server now supports:

SSL Encryption: ✅ Working (sslmode=require)
CA Verification: ✅ Working (sslrootcert=ca-cert.pem)
Client Certificate Authentication: Optional (requires additional configuration)

Your SSL connection shows:

Protocol: TLSv1.3
Cipher: TLS_AES_256_GCM_SHA384
Encryption: 256-bit

This provides excellent security for data in transit. Client certificate authentication is an additional security layer that's optional for most use cases.

If you find this tutorial helpful, you might be interested in Bytebase, an open-source schema change management tool for PostgreSQL.

How to Configure PostgreSQL SSL Connection

Prerequisites#

Generate SSL Related Files#

OpenSSL Config#

Generate Certificates#

Configure PostgreSQL Server#

Test SSL Connection#

Prerequisites Check#

Test SSL Connection#

Verify SSL Status#

Test without SSL (should fail)#

Summary#