pgmonkey (3/8) — Securing Connections with SSL/TLS

Summary: Configure SSL/TLS encryption for your PostgreSQL connections — from basic encryption with require through full certificate verification with verify-full and client certificate authentication.

KeyValue
pgmonkey version4.0.0
Python version3.12
Config filepgmonkey_config.yaml
Database hostdb.example.com
SSL root cert~/certs/ca.crt
SSL client cert~/certs/client.crt
SSL client key~/certs/client.key

0. Prerequisites

Note: If your PostgreSQL server is local and does not have SSL configured, you can still follow the disable and prefer modes. The require, verify-ca, and verify-full modes require an SSL-enabled server.


1. SSL Modes at a Glance

PostgreSQL supports six SSL modes. Each one increases the level of verification.

ModeEncryptedVerifies server certVerifies hostnameUse case
disableNoNoNoLocal dev only — no encryption
allowMaybeNoNoConnect encrypted if available, plain if not
preferMaybeNoNoTry encrypted first, fall back to plain (default)
requireYesNoNoEnforce encryption, trust any certificate
verify-caYesYesNoEncryption + verify the server certificate was signed by a trusted CA
verify-fullYesYesYesEncryption + 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: ''
FieldPurpose
sslmodeThe SSL mode from the table above
sslcertPath to the client certificate (for client cert auth)
sslkeyPath to the client private key (for client cert auth)
sslrootcertPath 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, the host value 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 your host value.


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 pgserverconfig CLI command, which can generate pg_hba.conf recommendations 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 PGConnectionManager
def 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

SymptomLikely causeFix
Connection refused with requireServer SSL not enabledSet ssl = on in postgresql.conf
Certificate verify failed with verify-caWrong or missing CA certCheck sslrootcert path; ensure the CA cert matches the server cert’s issuer
Hostname mismatch with verify-fullhost value does not match certificate CN/SANUse the hostname in the certificate, not an IP address
Client cert rejectedServer not configured for client certsAdd clientcert=verify-full to the pg_hba.conf entry
Permission denied on key fileKey file has wrong permissionsRun chmod 600 ~/certs/client.key

Tip: Use pgmonkey pgconfig test --connconfig pgmonkey_config.yaml --connection-type pool to 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.

  • disable and prefer for local development
  • require for encrypted connections without certificate checks
  • verify-ca to verify the server certificate against a trusted CA
  • verify-full for 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.

Leave a Reply