PostgreSQL Client Certificate Authentication: Complete Setup Guide for CN and one to one connection

Client certificate authentication in PostgreSQL provides a secure, passwordless way to authenticate users. Instead of relying on passwords, clients present valid X.509 certificates to prove their identity.

What to Configure

Server-Side Requirements

SSL certificates : Server certificate + client certificates
PostgreSQL SSL settings : Enable SSL and configure certificate paths
Authentication rules : Configure pg_hba.conf for certificate-based auth
User mapping : Link certificate Common Names to database users

Client-Side Requirements

Client certificate : Valid X.509 certificate for the user
Private key : Matching private key for the certificate
Root certificate : Server’s certificate for verification
Connection parameters : Proper SSL mode and certificate paths

How to Configure

1. Generate SSL Certificates

# Create SSL directory
mkdir -p /var/lib/postgresql/17/main/ssl
cd /var/lib/postgresql/17/main/ssl

# Generate server certificate
openssl genrsa -out server.key 4096
openssl req -new -key server.key -out server.csr \
  -subj "/C=US/ST=State/L=City/O=Org/OU=OrgUnit/CN=localhost"
openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt

# Generate client certificate for user 'appuser'
openssl genrsa -out appuser.key 4096
openssl req -new -key appuser.key -out appuser.csr \
  -subj "/C=US/ST=State/L=City/O=Org/OU=OrgUnit/CN=appuser"
openssl x509 -req -days 365 -in appuser.csr \
  -CA server.crt -CAkey server.key -CAcreateserial -out appuser.crt

# Set permissions
chown postgres:postgres *.crt *.key
chmod 600 *.key
chmod 644 *.crt