PostgreSQL sslmode Explained: require vs verify-full

Connect with certificate and hostname verification

psql "host=db.example.com port=5432 dbname=app user=app_reader \
sslmode=verify-full sslrootcert=$HOME/.postgresql/root.crt"

Keep the original hostname from the provider. Replacing it with an IP address usually breaks verify-full unless that IP is present in the certificate.

What each sslmode does

The six libpq modes form a policy, not a quality score. allow and prefer can fall back between encrypted and unencrypted TCP. require refuses an unencrypted connection. verify-ca adds certificate-chain validation. verify-full also checks that the requested hostname matches the certificate.

• disable: use only an unencrypted TCP connection.
• allow: try unencrypted first, then TLS if the first attempt fails.
• prefer: try TLS first, then allow an unencrypted fallback. This is the libpq default.
• require: require TLS, without normal hostname verification.
• verify-ca: require TLS and a certificate issued by a trusted CA.
• verify-full: require TLS, trust the issuing CA, and match the server hostname.

Encryption is not server identity

An encrypted connection prevents passive observers from reading traffic. It does not by itself prove who is at the other end. That is why verify-full checks both the certificate chain and host name.

PostgreSQL retains a compatibility behavior where require can act like verify-ca when a root CA file is present. Do not build a security policy around that implicit switch. State verify-ca or verify-full when verification is intended.

Check the negotiated connection

After connecting, pg_stat_ssl reports whether the current backend uses SSL, along with the protocol and cipher when your role can see them. This confirms encryption. It does not replace the client-side certificate and hostname checks performed during connection setup.

Inspect SSL for the current session

select ssl, version, cipher, bits
from pg_stat_ssl
where pid = pg_backend_pid();

Run the query in the same connection you are verifying.

Diagnose common verification failures

Certificate verify failed usually means the CA file is missing, unreadable, expired, or not the CA that issued the server certificate. Hostname mismatch means the connection used a name or address absent from the certificate. Fix the trust material or hostname; do not silently downgrade production to require.

sslmode is ignored for Unix-domain socket connections because those are not TCP connections. For local sockets, filesystem permissions control access to the socket while PostgreSQL authentication still controls the database session.

Apply the mode to provider connections

Start with the connection string issued by the provider. Supabase, Neon, RDS, and proxies can have different CA and pooler behavior. Preserve provider parameters unless their documentation tells you to change them. The RDS verify-full guide covers the extra root-certificate setup for Amazon RDS.

Before pasting a URL into a client, review its parameters with the PostgreSQL connection string builder. Remove credentials before sharing screenshots or support logs.