What is the difference between verify-ca and verify-full?

verify-ca checks that the server certificate was issued by a CA you trust. verify-full also checks that the host name you connected to matches the certificate's SAN (or CN). Only verify-full stops an attacker who holds any certificate from the same CA.

Where do I get the CA file for a managed database?

Managed PostgreSQL services (AWS RDS, Azure Database for PostgreSQL, Google Cloud SQL, Alibaba Cloud RDS and others) publish a CA bundle in their documentation or console. Download it from the provider's official page and reference it with sslrootcert.

Does sslmode=require protect against man-in-the-middle attacks?

Not by itself: it encrypts but, without a CA file, does not verify the server. The libpq documentation notes that if a root CA file exists, require behaves like verify-ca for backward compatibility. Use verify-full explicitly.

How can I see the server certificate PostgreSQL presents?

With OpenSSL 1.1.1 or later: openssl s_client -starttls postgres -connect db.example.com:5432 -showcerts </dev/null. Paste the result into the certificate decoder to read its names and dates.

PostgreSQL sslmode=verify-full errors: root.crt and host name

This error means you asked libpq to verify the server (sslmode verify-ca or verify-full) but gave it no CA file to verify against. The related errors certificate verify failed and server certificate for "..." does not match host name "..." mean the CA file is wrong or the certificate does not cover the host name you used.

Quick fix

Get the CA that issued the server certificate (the certificate decoder shows the issuer) and point libpq at it:

psql "host=db.example.com dbname=app user=app sslmode=verify-full sslrootcert=/etc/ssl/certs/db-ca.pem"

Managed database: download the provider's CA bundle from its documentation.
Self-managed server with a private CA: use that CA's root certificate.
Server with a publicly trusted certificate (PostgreSQL 16+ client): sslrootcert=system uses the OS trust store.

Or install the file at the default path: ~/.postgresql/root.crt (on Windows, %APPDATA%\postgresql\root.crt).

Causes

1. No CA file

Confirm: the exact message above, naming the path libpq checked.
Fix: provide sslrootcert or place the CA at the default path for the user running the client.

2. The CA file does not match the server

Confirm: SSL error: certificate verify failed. Check who issued the server certificate:

openssl s_client -starttls postgres -connect db.example.com:5432 -servername db.example.com </dev/null 2>/dev/null | openssl x509 -noout -subject -issuer -enddate

Fix: use the root of that issuer. If the server certificate is self-signed (subject equals issuer), that certificate itself is the CA file. If the server omits intermediates, add them to server.crt on the server or to the client CA file.

3. Host name mismatch (verify-full)

Confirm: server certificate for "db-primary.internal" does not match host name "203.0.113.10".
Fix: connect using a name in the certificate's SAN, or reissue the server certificate with every name and IP clients use. When connecting through an IP, the IP must appear as an IP SAN.

4. Expired server certificate

Fix: renew server.crt and run SELECT pg_reload_conf(); (PostgreSQL reloads SSL files on reload).

5. Server certificate files misconfigured

Confirm: server log shows could not load server certificate file or SSL is off.
Fix: set ssl = on, ssl_cert_file, ssl_key_file in postgresql.conf; the key must be owned by the postgres user with mode 600 (or root-owned with group access as the docs describe).

Fixes by environment

Every driver needs the same two things: the CA file that signed the server certificate, and a host name listed in its SAN. MySQL clients follow the same model, see MySQL ERROR 2026.

JDBC (pgJDBC): jdbc:postgresql://db.example.com/app?sslmode=verify-full&sslrootcert=/path/db-ca.pem.

Python (psycopg): pass sslmode="verify-full", sslrootcert="/path/db-ca.pem"; the same libpq rules apply.

Node.js (pg): ssl: { ca: fs.readFileSync("/path/db-ca.pem").toString(), rejectUnauthorized: true }; the driver checks the host name unless you override checkServerIdentity.

Docker / Kubernetes: mount the CA as a Secret or ConfigMap and set PGSSLROOTCERT and PGSSLMODE=verify-full as environment variables.

Managed databases: connect with the endpoint host name the provider shows, not a resolved IP address, because the provider's certificate lists the endpoint names. Read-replica and proxy endpoints may use different names; check that each one is covered.

Checking what a client negotiated: in psql, \conninfo prints whether SSL is in use and the protocol and cipher. On the server, SELECT * FROM pg_stat_ssl; shows which sessions use TLS.

Connection poolers (PgBouncer): TLS from client to pooler and from pooler to server are configured separately (client_tls_* and server_tls_*); verify both legs.

Don't do this

Downgrade to sslmode=require or prefer to make the error go away. You keep encryption but lose server authentication, so credentials can be intercepted by an impersonating server.
Set rejectUnauthorized: false in Node.js or equivalents in other drivers. Same result.
Connect by IP with verify-ca to dodge a name mismatch. Fix the certificate's names instead.