pgmonkey (3/8) — Securing Connections with SSL/TLS
Summary: Configure SSL/TLS encryption for your PostgreSQL connections — from basic encryption with
requirethrough full certificate verification withverify-fulland client certificate authentication.
| Key | Value |
|---|---|
| pgmonkey version | 4.0.0 |
| Python version | 3.12 |
| Config file | pgmonkey_config.yaml |
| Database host | db.example.com |
| SSL root cert | ~/certs/ca.crt |
| SSL client cert | ~/certs/client.crt |
| SSL client key | ~/certs/client.key |
0. Prerequisites
- Completed Part 1 (pgmonkey installed, config file working)
- A PostgreSQL server with SSL enabled
- For client certificate sections: a CA certificate and client certificate/key pair
Note: If your PostgreSQL server is local and does not have SSL configured, you can still follow the
disableandprefermodes. Therequire,verify-ca, andverify-fullmodes require an SSL-enabled server.
1. SSL Modes at a Glance
PostgreSQL supports six SSL modes. Each one increases the level of verification.
| Mode | Encrypted | Verifies server cert | Verifies hostname | Use case |
|---|---|---|---|---|
disable | No | No | No | Local dev only — no encryption |
allow | Maybe | No | No | Connect encrypted if available, plain if not |
prefer | Maybe | No | No | Try encrypted first, fall back to plain (default) |
require | Yes | No | No | Enforce encryption, trust any certificate |
verify-ca | Yes | Yes | No | Encryption + verify the server certificate was signed by a trusted CA |
verify-full | Yes | Yes | Yes | Encryption + verify certificate + verify hostname matches |
For production, verify-full is the gold standard. It prevents both eavesdropping and man-in-the-middle attacks.
2. Configure SSL in Your YAML
SSL settings live inside the connection_settings block — the same block shared by all four connection types.
connection_settings: user: 'app_user' password: '<YOUR_PASSWORD>' host: 'db.example.com' port: '5432' dbname: 'tutorial_db' sslmode: 'require' sslcert: '' sslkey: '' sslrootcert: ''
| Field | Purpose |
|---|---|
sslmode | The SSL mode from the table above |
sslcert | Path to the client certificate (for client cert auth) |
sslkey | Path to the client private key (for client cert auth) |
sslrootcert | Path to the CA root certificate (for verify-ca and verify-full) |
When sslcert, sslkey, and sslrootcert are empty strings, they are ignored. Set them only when you need certificate verification or client certificate authentication.
3. Test an SSL Connection
Set sslmode to require and test from the CLI.
connection_settings: sslmode: 'require'
pgmonkey pgconfig test --connconfig pgmonkey_config.yaml
Testing connection...Connection successful!
This confirms the connection is encrypted. The server’s certificate is accepted without verification — suitable for environments where you trust the network path but want encryption.
Tip: All four connection types use the same SSL settings. You do not need separate SSL configuration for sync vs. async connections.
4. Verify the Server Certificate
Upgrade to verify-ca to confirm the server’s certificate was signed by a CA you trust. This requires the sslrootcert path.
connection_settings: sslmode: 'verify-ca' sslrootcert: '~/certs/ca.crt'
pgmonkey pgconfig test --connconfig pgmonkey_config.yaml
With verify-ca, pgmonkey (via psycopg) checks that the server’s TLS certificate was signed by the CA certificate you provided. If the certificate chain does not match, the connection is refused.
5. Full Verification with verify-full
verify-full adds hostname verification on top of certificate verification. The Common Name (CN) or Subject Alternative Name (SAN) in the server certificate must match the host value in your config.
connection_settings: host: 'db.example.com' sslmode: 'verify-full' sslrootcert: '~/certs/ca.crt'
This is the highest level of protection. It guarantees three things: the connection is encrypted, the server certificate is legitimate, and you are connecting to the server you intended to reach.
Warning: With
verify-full, thehostvalue must exactly match the certificate’s CN or SAN entries. An IP address will not match a hostname-based certificate. If the connection fails, check the certificate details against yourhostvalue.
6. Client Certificate Authentication
Client certificates let the server verify the client’s identity — mutual TLS (mTLS). The server checks your certificate, and you check the server’s certificate.
connection_settings: host: 'db.example.com' sslmode: 'verify-full' sslcert: '~/certs/client.crt' sslkey: '~/certs/client.key' sslrootcert: '~/certs/ca.crt'
pgmonkey pgconfig test --connconfig pgmonkey_config.yaml
For this to work, the PostgreSQL server must be configured to request client certificates. The server’s pg_hba.conf needs an entry like:
hostssl all all 0.0.0.0/0 scram-sha-256 clientcert=verify-full
Note: Part 6 covers the
pgserverconfigCLI command, which can generatepg_hba.confrecommendations based on your YAML config — including the client certificate rules shown above.
7. Client Certificates with Pooled Connections
SSL settings apply equally to all four connection types. Here is a pool connection using client certificates.
from pgmonkey import PGConnectionManagerdef main(): manager = PGConnectionManager() pool = manager.get_database_connection('pgmonkey_config.yaml', 'pool') with pool as conn: with conn.cursor() as cur: cur.execute('SELECT current_user, ssl_is_used();') user, ssl = cur.fetchone() print(f"User: {user}, SSL: {ssl}")if __name__ == "__main__": main()
The ssl_is_used() function returns True when the connection is encrypted. Every connection borrowed from the pool inherits the same SSL settings — no per-connection configuration needed.
8. Troubleshooting SSL Issues
| Symptom | Likely cause | Fix |
|---|---|---|
Connection refused with require | Server SSL not enabled | Set ssl = on in postgresql.conf |
Certificate verify failed with verify-ca | Wrong or missing CA cert | Check sslrootcert path; ensure the CA cert matches the server cert’s issuer |
Hostname mismatch with verify-full | host value does not match certificate CN/SAN | Use the hostname in the certificate, not an IP address |
| Client cert rejected | Server not configured for client certs | Add clientcert=verify-full to the pg_hba.conf entry |
| Permission denied on key file | Key file has wrong permissions | Run chmod 600 ~/certs/client.key |
Tip: Use
pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --connection-type poolto test SSL with a specific connection type. SSL failures show up immediately in the test output.
Summary
You configured SSL/TLS for pgmonkey connections at every level of verification.
disableandpreferfor local developmentrequirefor encrypted connections without certificate checksverify-cato verify the server certificate against a trusted CAverify-fullfor full certificate and hostname verification- Client certificates for mutual TLS authentication
- All SSL settings are shared across all four connection types — configure once, use everywhere
Next up: pgmonkey (4/8) — Connection Pooling dives into pool sizing, health checks, lifecycle management, and concurrent access patterns.
