PostgreSQL Client Certificate Authentication: Complete Setup Guide

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

2. Configure PostgreSQL SSL Settings #

Add to postgresql.conf:

# SSL Configuration
ssl = on
ssl_cert_file = '/var/lib/postgresql/17/main/ssl/server.crt'
ssl_key_file = '/var/lib/postgresql/17/main/ssl/server.key'
ssl_ca_file = '/var/lib/postgresql/17/main/ssl/server.crt'
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

3. Configure Authentication Rules #

Update pg_hba.conf:

# SSL connections require client certificate authentication
hostssl all             all             0.0.0.0/0               cert clientcert=verify-full
hostssl all             all             ::/0                    cert clientcert=verify-full

# Non-SSL connections require password authentication
hostnossl all           all             0.0.0.0/0               scram-sha-256
hostnossl all           all             ::/0                    scram-sha-256

4. Create Database User #

The database user must match the certificate’s Common Name(CN):

CREATE USER appuser WITH LOGIN;
GRANT CONNECT ON DATABASE mydb TO appuser;
-- Grant additional permissions as needed

How to Connect from Client #

Critical Connection Parameters #

Parameter	Self-Managed Value	RDS Value	Purpose
`sslmode`	`require` or `verify-full`	`require` or `verify-full`	Force SSL connection
`sslcert`	`/path/to/client.crt`	N/A (not supported)	Client certificate file
`sslkey`	`/path/to/client.key`	N/A (not supported)	Client private key file
`sslrootcert`	`/path/to/server.crt`	`./global-bundle.pem`	Server certificate for verification
`host`	`localhost` or custom	`instance.region.rds.amazonaws.com`	Database server endpoint
`password`	Not required with client cert	Required (or IAM token)	Authentication credential

Connection Examples #

Self-Managed PostgreSQL with Client Certificates #

# psql - No password required
psql "host=localhost port=5432 dbname=mydb user=appuser \
      sslmode=require \
      sslcert=./certs/appuser.crt \
      sslkey=./certs/appuser.key \
      sslrootcert=./certs/server.crt"

// .NET - Client certificate authentication
var connectionString = "Host=localhost;Port=5432;Database=mydb;Username=appuser;" +
                      "SSL Mode=Require;" +
                      "Client Certificate=./certs/appuser.crt;" +
                      "Client Key=./certs/appuser.key;" +
                      "Root Certificate=./certs/server.crt;";

# Python - Client certificate authentication
import psycopg2
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="mydb",
    user="appuser",
    sslmode="require",
    sslcert="./certs/appuser.crt",
    sslkey="./certs/appuser.key",
    sslrootcert="./certs/server.crt"
)

// Java JDBC - Client certificate authentication
String url = "jdbc:postgresql://localhost:5432/mydb" +
            "?ssl=true" +
            "&sslmode=require" +
            "&sslcert=./certs/appuser.crt" +
            "&sslkey=./certs/appuser.key" +
            "&sslrootcert=./certs/server.crt" +
            "&user=appuser";

Connection conn = DriverManager.getConnection(url);

AWS RDS PostgreSQL with SSL (Password Required) #

# psql - Password required, verify server certificate
psql "host=mydb.cluster-xyz.us-east-1.rds.amazonaws.com \
      port=5432 dbname=postgres user=myuser password=mypassword \
      sslmode=verify-full sslrootcert=./global-bundle.pem"

// .NET - RDS with SSL verification
var connectionString = "Host=mydb.cluster-xyz.us-east-1.rds.amazonaws.com;" +
                      "Port=5432;Database=postgres;Username=myuser;Password=mypassword;" +
                      "SSL Mode=Require;Root Certificate=./global-bundle.pem;";

# Python - RDS with SSL verification  
import psycopg2
conn = psycopg2.connect(
    host="mydb.cluster-xyz.us-east-1.rds.amazonaws.com",
    port=5432,
    database="postgres",
    user="myuser",
    password="mypassword",
    sslmode="verify-full",
    sslrootcert="./global-bundle.pem"
)

// Java JDBC - RDS with SSL verification
String url = "jdbc:postgresql://mydb.cluster-xyz.us-east-1.rds.amazonaws.com:5432/postgres" +
            "?ssl=true" +
            "&sslmode=verify-full" +
            "&sslrootcert=./global-bundle.pem" +
            "&user=myuser" +
            "&password=mypassword";

Connection conn = DriverManager.getConnection(url);

Server Configuration vs Client Parameters #

Server Side (`pg_hba.conf`) #

# Authentication method MUST be 'cert'
hostssl all all 0.0.0.0/0 cert clientcert=verify-full

Client Side (Connection String) #

# SSL mode MUST be 'require' or 'verify-full'
sslmode=require
# All three certificate files MUST be provided
sslcert=./client.crt
sslkey=./client.key  
sslrootcert=./server.crt

Common Pitfalls #

Certificate CN mismatch: Client certificate CN must match database username
Missing clientcert=verify-full: Server won’t require client certificates
Wrong SSL mode: sslmode=disable or prefer may skip certificate auth
File permissions: Private keys must be readable only by the client process
Certificate expiration: Monitor and rotate certificates before expiry

Security Benefits #

No password transmission: Eliminates password-based attacks
Strong authentication: Based on cryptographic proof of identity
Non-repudiation: Certificate-based audit trails
Mutual authentication: Both client and server verify each other

AWS RDS PostgreSQL Configuration #

RDS Limitations for Client Certificates #

AWS RDS PostgreSQL has limited support for client certificate authentication compared to self-managed PostgreSQL:

What’s NOT Available in RDS #

Custom client certificate generation: You cannot upload custom client certificates
Full cert authentication method: RDS doesn’t support the cert method in pg_hba.conf
Direct certificate file access: No access to /var/lib/postgresql/ directory

What IS Available in RDS #

1. RDS SSL Server Certificates & Configuration #

# Download RDS CA certificate bundle
wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

# Enable SSL in RDS parameter group
aws rds modify-db-parameter-group \
    --db-parameter-group-name mygroup \
    --parameters "ParameterName=ssl,ParameterValue=1,ApplyMethod=immediate"

2. Alternative: IAM Database Authentication #

For passwordless authentication in RDS, use IAM database authentication:

-- Create IAM-enabled database user
CREATE USER iamuser;
GRANT rds_iam TO iamuser;
GRANT CONNECT ON DATABASE postgres TO iamuser;

# Connect using IAM token (Python example)
import boto3
import psycopg2

rds_client = boto3.client('rds', region_name='us-east-1')
token = rds_client.generate_db_auth_token(
    DBHostname='mydb.xyz.us-east-1.rds.amazonaws.com',
    Port=5432,
    DBUsername='iamuser'
)

conn = psycopg2.connect(
    host="mydb.xyz.us-east-1.rds.amazonaws.com",
    port=5432,
    database="postgres",
    user="iamuser",
    password=token,  # IAM token as password
    sslmode="require",
    sslrootcert="./global-bundle.pem"
)

3. Hybrid Approach: ALB + Client Certificates + RDS #

For applications requiring client certificate authentication with RDS:

[Client with Client Cert] → [ALB with SSL termination] → [App Server] → [RDS with IAM Auth]

ALB handles client certificate verification
Application validates certificates in code
RDS connection uses IAM authentication

Summary & Verification #

Self-Managed vs AWS RDS Comparison #

Feature	Self-Managed	AWS RDS
Client Certificate Auth	✅ Full support	❌ Not supported
Server SSL Certificates	✅ Custom certificates	✅ AWS-managed certificates
Passwordless Authentication	✅ Client certificates	✅ IAM authentication
Certificate Management	❌ Manual rotation	✅ Automatic rotation
SSL Configuration Control	✅ Full control	❌ Limited parameters

Verification Commands #

-- Verify SSL is enabled
SHOW ssl;

-- Check active SSL connections with certificate details
SELECT datname, usename, client_addr, ssl, ssl_version, ssl_cipher,
       client_dn, issuer_dn
FROM pg_stat_ssl 
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid 
WHERE ssl = true;
-- Example output
pid|ssl |version|cipher                |bits|client_dn                                                      |client_serial                                   |issuer_dn                                                   |datid|datname       |pid|leader_pid|usesysid|usename       |application_name|client_addr|client_hostname|client_port|backend_start                |xact_start|query_start                  |state_change                 |wait_event_type|wait_event|state|backend_xid|backend_xmin|query_id           |query                                                                                                                        |backend_type  |
---+----+-------+----------------------+----+---------------------------------------------------------------+------------------------------------------------+------------------------------------------------------------+-----+--------------+---+----------+--------+--------------+----------------+-----------+---------------+-----------+-----------------------------+----------+-----------------------------+-----------------------------+---------------+----------+-----+-----------+------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------+--------------+
754|true|TLSv1.3|TLS_AES_256_GCM_SHA384| 256|/C=US/ST=State/L=City/O=Organization/OU=OrgUnit/CN=redgatemonit|608692775915102986323080169170088721995325147053|/C=US/ST=State/L=City/O=Organization/OU=OrgUnit/CN=localhost|16384|redgatemonitor|754|          |   16385|redgatemonitor|psql            |10.88.0.1  |               |      59168|2025-07-30 09:43:20.240 +0100|          |2025-07-30 09:43:58.869 +0100|2025-07-30 09:43:58.870 +0100|Client         |ClientRead|idle |           |            |8885888923103386772|SELECT datname, usename, client_addr, ssl,* FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;|client backend|

Key Takeaways #

Self-managed PostgreSQL: Use client certificates for true passwordless authentication
AWS RDS: Combine IAM authentication with SSL encryption for secure connections
Hybrid approach: Use ALB for client certificate verification with RDS backend
Security: Both approaches provide enterprise-grade security with different trade-offs

Client certificate authentication in PostgreSQL provides enterprise-grade security without the overhead of password management, making it ideal for automated systems and high-security environments.

References #

https://www.postgresql.org/docs/current/ssl-tcp.html