SSL verify-full for RDS PostgreSQL on macOS

Download the Correct AWS RDS CA Bundle

AWS provides a global CA bundle at a single URL that covers all regions and all four CA types. This is the file you need:

curl -o ~/global-bundle.pem \
  "https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem"

This global-bundle.pem file contains approximately 121 certificates spanning all four current CAs across every AWS region: rds-ca-rsa2048-g1 (the default since January 2024), rds-ca-rsa4096-g1, rds-ca-ecc384-g1, and the now-expired rds-ca-2019. If you only need a specific region, regional bundles follow the pattern https://truststore.pki.rds.amazonaws.com/{region}/{region}-bundle.pem.

Do not use the old rds-combined-ca-bundle.pem from s3.amazonaws.com/rds-downloads/. That legacy file only contained rds-ca-2019 root certificates, which expired in August 2024. The new global-bundle.pem from the truststore PKI endpoint is the current, AWS-recommended replacement. You can verify which CA your RDS instance uses with:

aws rds describe-db-instances \
  --db-instance-identifier mydbinstance \
  --query 'DBInstances[0].CACertificateIdentifier'

One important nuance from AWS documentation: only register the root CA certificate in your trust store, not intermediate certificates, as RDS automatically rotates server certificates and intermediates may change.

Where to Store the Certificate on macOS

The PostgreSQL libpq library checks a default path for the root CA: ~/.postgresql/root.crt on all Unix systems including macOS. Placing the AWS bundle there eliminates the need to specify sslrootcert in every connection:

mkdir -p ~/.postgresql
cp ~/global-bundle.pem ~/.postgresql/root.crt
chmod 644 ~/.postgresql/root.crt

If you also use client certificates (for IAM database authentication, for example), the default paths are ~/.postgresql/postgresql.crt and ~/.postgresql/postgresql.key. The private key must have 0600 permissions — libpq refuses to use it otherwise:

chmod 600 ~/.postgresql/postgresql.key

Alternatively, you can store the bundle anywhere and reference it explicitly via the sslrootcert parameter or the PGSSLROOTCERT environment variable. Many teams store it in a project directory or /etc/ssl/certs/ for shared access.

On Apple Silicon Macs, be aware that Homebrew installs to /opt/homebrew/ rather than /usr/local/. This affects all PostgreSQL binary and library paths but does not change the ~/.postgresql/ convention for user certificates.

Configure and Connect with verify-full

The verify-full SSL mode performs two critical checks: it validates the server's certificate chain against a trusted root CA, and it verifies the hostname matches a Subject Alternative Name (or CN) in the certificate. This guards against both eavesdropping and man-in-the-middle attacks — making it the only mode suitable for production use with a public CA like AWS's.

psql key-value format

psql "host=myinstance.abc123.us-east-1.rds.amazonaws.com \
      port=5432 \
      dbname=mydb \
      user=myuser \
      sslmode=verify-full \
      sslrootcert=$HOME/.postgresql/root.crt"

psql URI format

psql "postgresql://myuser:mypass@myinstance.abc123.us-east-1.rds.amazonaws.com:5432/mydb?sslmode=verify-full&sslrootcert=/Users/you/.postgresql/root.crt"

Environment variables (useful in shell profiles or CI)

export PGSSLMODE=verify-full
export PGSSLROOTCERT="$HOME/.postgresql/root.crt"
psql -h myinstance.abc123.us-east-1.rds.amazonaws.com -U myuser -d mydb

Python (psycopg2)

import psycopg2
conn = psycopg2.connect(
    host="myinstance.abc123.us-east-1.rds.amazonaws.com",
    port=5432, database="mydb", user="myuser", password="mypass",
    sslmode="verify-full",
    sslrootcert="/Users/you/.postgresql/root.crt"
)

Node.js (node-postgres)

Node-postgres uses Node's built-in TLS rather than libpq, so the certificate must be passed as a string in the ssl.ca option:

const { Pool } = require('pg');
const fs = require('fs');
const pool = new Pool({
    host: 'myinstance.abc123.us-east-1.rds.amazonaws.com',
    port: 5432, database: 'mydb', user: 'myuser', password: 'mypass',
    ssl: {
        rejectUnauthorized: true,
        ca: fs.readFileSync('/Users/you/.postgresql/root.crt').toString()
    }
});

Critical detail: Always use the full RDS endpoint hostname (e.g., myinstance.abc123.us-east-1.rds.amazonaws.com) — not a Route 53 CNAME alias or an IP address. The RDS server certificate's SAN matches the RDS-issued endpoint. A custom CNAME will cause hostname verification to fail.

How to Verify SSL Is Working

Once connected, confirm the SSL session with any of these methods:

\conninfo in psql — the quickest check

mydb=> \conninfo
You are connected to database "mydb" as user "myuser" on host
"myinstance.abc123.us-east-1.rds.amazonaws.com" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

pg_stat_ssl view — detailed per-connection SSL status

SELECT ssl, version, cipher, bits
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

Returns ssl = t, the TLS version, cipher suite, and key bits.

sslinfo extension — function-based queries (must be created by an admin)

CREATE EXTENSION IF NOT EXISTS sslinfo;
SELECT ssl_is_used();    -- returns t
SELECT ssl_cipher();     -- e.g., TLS_AES_256_GCM_SHA384
SELECT ssl_version();    -- e.g., TLSv1.3

Audit all connections to see which are using SSL

SELECT datname, usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid
ORDER BY ssl;

If you want to force SSL server-side, RDS PostgreSQL 15 and later defaults rds.force_ssl to 1 (SSL required). For PostgreSQL 14 and earlier, set rds.force_ssl=1 in your DB parameter group to reject non-SSL connections.

Common Errors and How to Fix Them

SSL error: certificate verify failed

This is the most common error. It means libpq cannot build a valid certificate chain from the RDS server certificate to a trusted root. Causes and fixes:

• The sslrootcert path is wrong or the file doesn't exist. Double-check the path and that the file is readable.
• You're using the old rds-combined-ca-bundle.pem but your RDS instance now uses rds-ca-rsa2048-g1. Switch to global-bundle.pem.
• A stale ~/.postgresql/root.crl file exists and contains an outdated CRL. Remove or update it.
• On macOS with Postgres.app or MacPorts, sslrootcert=system doesn't work because OpenSSL's default cert directory is misconfigured. Use an explicit path instead.

server certificate for “X” does not match host name “Y”

Hostname verification failed. This happens when:

• You connect using an IP address instead of the RDS endpoint hostname.
• You connect via a custom CNAME/Route 53 alias that doesn't match the certificate SAN. Solution: use the RDS endpoint directly, or downgrade to sslmode=verify-ca (loses MITM protection).

sslmode value “require” invalid when SSL support is not compiled in

Your psql or libpq binary was built without SSL. On macOS, this typically means you're accidentally using the system /usr/lib/libpq.5.dylib instead of Homebrew's. Diagnose with otool -L $(which psql) and ensure it shows a path under /opt/homebrew/ (Apple Silicon) or /usr/local/ (Intel). Fix: brew install libpq or brew install postgresql@17 and ensure Homebrew's bin is first in your PATH.

psql crashes on TLS connect

Reported after Homebrew upgraded to openssl@3.2.0. The fix is brew upgrade to a patched OpenSSL version, or pin the OpenSSL formula temporarily.

macOS-Specific Nuances You Need to Know

Homebrew's libpq uses OpenSSL, not LibreSSL or Secure Transport. The libpq and postgresql Homebrew formulas depend on openssl@3. Running openssl version in your terminal shows macOS's system LibreSSL, but that is not what psql uses. The PostgreSQL project considered adding a Secure Transport backend for macOS (which would have enabled Keychain integration), but this was never merged upstream. Consequently, libpq ignores the macOS Keychain entirely — it reads PEM files from disk, period.

The sslrootcert=system feature (PostgreSQL 16+) is unreliable on macOS. This shortcut tells libpq to load the OS default certificate store. It works on Homebrew installations because Homebrew bootstraps CA certs from the macOS SystemRoots keychain into OpenSSL's cert directory during installation. However, it fails on Postgres.app and MacPorts with certificate verify failed: unable to get local issuer certificate. For RDS connections, always specify the AWS CA bundle explicitly rather than relying on system.

Apple Silicon path differences matter. Homebrew on M-series Macs installs everything under /opt/homebrew/ instead of /usr/local/. This changes the OpenSSL cert directory to /opt/homebrew/etc/openssl@3/cert.pem and the PostgreSQL prefix to /opt/homebrew/opt/postgresql@17/. When building Python packages like psycopg2 from source, ensure pg_config resolves to Homebrew's version:

export PATH="/opt/homebrew/opt/libpq/bin:$PATH"
pip install psycopg2  # now links against Homebrew's SSL-enabled libpq

After brew upgrade, test your SSL connections. OpenSSL major version bumps through Homebrew have historically broken psql TLS connectivity. A quick psql connection test after upgrades can save hours of debugging.

Conclusion

The complete setup reduces to four commands: download global-bundle.pem, copy it to ~/.postgresql/root.crt, set sslmode=verify-full in your connection, and verify with \conninfo. The macOS-specific insight that matters most is that libpq operates entirely through OpenSSL and PEM files — the Keychain, Secure Transport, and sslrootcert=system are all irrelevant or unreliable for RDS connections. Always use explicit certificate paths, always connect via the RDS endpoint hostname (not a CNAME or IP), and always use Homebrew's PostgreSQL rather than the macOS system libraries. With these practices, verify-full provides strong protection against both eavesdropping and man-in-the-middle attacks with no meaningful performance overhead.