A PostgreSQL client connects with libpq's default sslmode; what does that actually guarantee, and what do sslmode=verify-full, sslrootcert=system and sslnegotiation=direct change?
answer
- prefer falls back silently
- require checks only with root.crt
- verify-full checks the host name
- system roots force verify-full
- direct TLS skips SSLRequest
basics
~20 slibpq's default sslmode=prefer tries TLS but silently falls back to plaintext and never checks the server certificate. verify-full adds CA and host-name checks, sslrootcert=system uses the OS trust store and forces verify-full, and sslnegotiation=direct starts TLS immediately against 17+ servers.
solid answer
~50 sWith `sslmode=prefer`, the default, libpq tries SSL first and quietly falls back to plaintext. It does not verify the server's certificate, so it stops passive sniffing at best and does nothing against an active attacker. `require` insists on TLS but verifies the certificate only if a root CA file such as `~/.postgresql/root.crt` exists. `verify-ca` checks the chain, and `verify-full` also checks the host name. `sslrootcert=system` loads the platform's trusted roots and makes `verify-full` the default, rejecting weaker modes, because anyone can get a publicly trusted certificate for a name they control. `sslnegotiation=direct` (libpq 17 and later) skips the `SSLRequest` round trip, needs a 17+ server and ALPN, and is allowed only with `sslmode=require` or stricter. None of this is server policy: the server enforces TLS with `hostssl` records and no `host` records, and `pg_stat_ssl` shows what each session negotiated.
code
bash · 1 linepsql "host=db1.clinic.internal dbname=clinic user=clinic_app sslmode=verify-full sslrootcert=/etc/clinic/pg-ca.crt sslnegotiation=direct"go deeper
Know that prefer is the default and does not guarantee encryption, and that verify-full is the mode that checks the server's identity.
Explain the six sslmode values, require's root.crt behaviour, and why GSSAPI encryption can take precedence over SSL.
Show the full lockdown: verify-full with a pinned CA or sslrootcert=system on clients, hostssl-only records on the server, and pg_stat_ssl to prove every session is encrypted.
Decide how certificates for database endpoints are issued and rotated, and whether direct TLS lets standard proxies and load balancers sit in front of PostgreSQL.
## The client decides how hard it tries In PostgreSQL, TLS has two sides. The **server** decides what it accepts, through `ssl = on` and `hostssl` records in `pg_hba.conf`. The **client** decides what it insists on, through libpq connection parameters that most drivers mirror. The general question of what TLS protects belongs to the engine-neutral security topic. This answer covers what libpq's parameters do and how their defaults catch people out. The answer assumes PostgreSQL 18's libpq. ## The six sslmode values | `sslmode` | TLS used | Plaintext fallback | Checks CA chain | Checks host name | |---|---|---|---|---| | `disable` | never | n/a | no | no | | `allow` | only if plaintext is refused | starts in plaintext | no | no | | `prefer` (default) | if the server offers it | yes | no | no | | `require` | always | no | only if a root CA file exists | no | | `verify-ca` | always | no | yes | no | | `verify-full` | always | no | yes | yes | Some details matter in interviews: - The default, `prefer`, gives **no protection against an active attacker**. Someone in the path can refuse TLS (the client falls back) or present any certificate (nothing is checked). - `require` verifies the certificate as `verify-ca` would **if** a root CA file is present (`sslrootcert`, default `~/.postgresql/root.crt`). Without one, it encrypts but authenticates nobody. - `sslmode` is ignored on Unix-domain sockets. - If GSSAPI encryption is possible (Kerberos credentials, `gssencmode=prefer` by default), libpq uses it in preference to SSL whatever `sslmode` says. Set `gssencmode=disable` to force SSL. - Each parameter has an environment-variable twin (`PGSSLMODE`, `PGSSLROOTCERT`, `PGSSLNEGOTIATION`). A cron job or container that sets `PGSSLMODE` can quietly weaken a connection string you reviewed, so audit the environment too. ## sslrootcert=system Normally `sslrootcert` points to a file with the CA that signed the database's certificate, often a private CA. The special value `system` loads the trust store of the SSL library instead. Because any public CA will issue a certificate for a host name its holder controls, a CA check alone would be worthless here. libpq therefore changes the default `sslmode` to `verify-full` and reports an error for any weaker mode. ## sslnegotiation=direct - By default (`sslnegotiation=postgres`), the client first sends an `SSLRequest` packet and waits for the server to answer before starting the TLS handshake. - With `direct`, the client starts a standard TLS handshake as soon as the TCP connection opens. That saves a round trip and lets ordinary TLS tooling (proxies, inspectors) understand the stream. - Direct TLS was added in PostgreSQL 17, so the server must be 17 or later. The client must use ALPN with the protocol name `postgresql`, which protects against protocol-confusion attacks. - libpq allows `direct` only with `sslmode=require` or stricter, because a weaker mode could fall back to plaintext authentication against a server that does not support it. ## Where TLS becomes mandatory: the server Client settings protect only the clients that set them. To make TLS compulsory for the clinic-records database: 1. Set `ssl = on` with a certificate and key (`ssl_cert_file`, `ssl_key_file`, defaults `server.crt` and `server.key`), and reload. 2. Use `hostssl` for every remote record, and remove `host` records or follow them with `hostnossl ... reject`. 3. Leave `ssl_min_protocol_version` at its default, `TLSv1.2`, or raise it. Since 18, key-exchange groups are set with `ssl_groups` (renamed from `ssl_ecdh_curve`, default `X25519:prime256v1`). 4. Give clients the CA file and set `sslmode=verify-full` in their connection strings. 5. Prove the result with `pg_stat_ssl`, whose `ssl`, `version` and `cipher` columns show what each backend negotiated. With the server refusing plaintext and the clients verifying the host name, both failure modes in the table are closed: plaintext fallback and an impostor server. For the clinic audit this produces two separate pieces of evidence: the server's `pg_hba.conf` shows that plaintext cannot get in, and each client's connection string shows that it checks who it is talking to. An auditor should ask for both, because either one alone leaves a gap.
- How do you prove after the change that no clinic session still runs in plaintext?Join `pg_stat_ssl` to `pg_stat_activity` on `pid` and look for client backends with `ssl = false`. The `version` and `cipher` columns show what the others negotiated. In the server log, `no pg_hba.conf entry ... no encryption` messages reveal clients that still try plaintext after the `host` records were removed.
- What does channel_binding=require add on top of TLS?It makes SCRAM bind to the TLS channel (SCRAM-SHA-256-PLUS), so the server proves it knows the role's secret on this very connection. That defeats a man-in-the-middle even if the certificate check is weak. It works only with SCRAM over SSL; if the server uses another method, the connection fails.
sslmode=prefer is a courier who seals your envelope if the recipient has sealing wax, hands it over open if not, and never checks the recipient's ID. verify-full seals every envelope and checks the ID against the name on the address.
- The default sslmode=prefer protects against a man-in-the-middle.
- sslmode=require always verifies the server certificate.
- verify-ca also checks that the certificate names the host.
- Setting sslmode=require in one app's config forces TLS for every client.
- sslnegotiation=direct works against any PostgreSQL server version.
Assumes PostgreSQL 18 libpq; sslnegotiation=direct exists since 17 and needs a 17 or later server, and ssl_groups replaced ssl_ecdh_curve in 18.