PostgreSQL sslmode explained: prefer, require, verify-full

sslmode decides two separate things: whether the connection to PostgreSQL is encrypted at all, and whether the client checks that the server on the other end is the one it meant to reach. Only two of the six values answer yes to both - verify-ca and verify-full - and only verify-full also checks the host name. The libpq default, prefer, answers "maybe" to the first and "no" to the second, and it does so silently. If the connection crosses any network you do not own, set sslmode=verify-full, give the client the right root certificate in sslrootcert, and make the server refuse anything that is not TLS with hostssl lines in pg_hba.conf.

The rest of this post is what each mode does on the wire, where the certificate files go, how to turn TLS on at the server, what each driver does with the same setting (they do not agree), and how to read the errors. The basics of connecting from elsewhere - connection strings, pg_hba.conf order, psql commands - are in PostgreSQL remote connections; this post goes deeper on the TLS part only.

The six modes, and what each one checks#

PostgreSQL starts a TLS connection in an unusual way. The client opens a plain TCP connection to port 5432 and sends a small SSLRequest packet asking whether the server will speak TLS. The server answers with one byte: S for yes, N for no. Everything sslmode does happens around that byte - what the client does when it gets N, and how much it checks once the handshake starts.

And the second question, which is the one that matters against an attacker:

In more detail:

• `disable` never sends the TLS request. Only sensible on a Unix socket or a loopback connection on the same machine, where there is no network to protect.
• `allow` tries a plain connection first, and only retries with TLS if the server rejects the plain one - typically because pg_hba.conf only has hostssl lines for you. It exists for compatibility and almost nobody should choose it.
• `prefer` asks for TLS, uses it if the server says yes, and quietly carries on in plain text if the server says no or the handshake fails. It checks nothing about the certificate.
• `require` insists on encryption and fails if it cannot get it, but accepts any certificate the server presents, including a self-signed one made by somebody in the middle.
• `verify-ca` insists on encryption and checks that the server certificate chains up to a CA in the root file you trust.
• `verify-full` does all of that and checks that the name you connected to (the host parameter) matches the certificate's Subject Alternative Name, or its Common Name if there is no SAN.

Why the default, prefer, is weak#

prefer encrypts when it can, so it feels safe. The problem is that the decision to encrypt is made in clear text, before any certificate is involved. Anyone who can tamper with the traffic between you and the database - a hostile Wi-Fi network, a compromised router, a misconfigured proxy - can answer the SSLRequest with N on the server's behalf. The client shrugs and sends the authentication exchange and every query in plain text. Nothing is logged on the client side and nothing errors.

Even when prefer does get a TLS session, it never looks at whose certificate it received, so an attacker can just as easily terminate TLS themselves and open a second connection to the real server. Encryption against a party you have not identified protects you from people passively capturing traffic and nobody else.

That is not a theoretical concern for the password, either. With scram-sha-256 authentication the password itself never crosses the wire, which helps, but the session that follows - every row you read and write - does. And with the older md5 method or password, a downgraded connection hands over credentials that can be replayed or cracked.

Why is the default still prefer? Because it makes connections work out of the box against servers with and without TLS, and changing it would break a vast number of existing setups. It is a compatibility choice, not a security recommendation, and the PostgreSQL documentation says as much. The fix is simple: never rely on it. Put the mode in every connection string, or in PGSSLMODE, so the behaviour is written down rather than inherited.

The rough rule:

Setting sslmode: URI, keywords and PGSSLMODE#

There are three places a libpq-based client picks the mode up, and an explicit value always beats a fallback.

# URI form - parameters go after the ?$ psql "postgresql://appuser@db.example.com:5432/shop?sslmode=verify-full&sslrootcert=/etc/ssl/pg/root.crt"# Keyword form - easier to read in scripts$ psql "host=db.example.com dbname=shop user=appuser sslmode=verify-full sslrootcert=/etc/ssl/pg/root.crt"# Environment variables - used when the string leaves them out$ export PGSSLMODE=verify-full$ export PGSSLROOTCERT=/etc/ssl/pg/root.crt$ psql -h db.example.com -U appuser -d shop

The environment variables that go with TLS are PGSSLMODE, PGSSLROOTCERT, PGSSLCERT, PGSSLKEY and PGSSLCRL. They are read by psql, pg_dump, pg_restore, psycopg and everything else built on libpq. They are a convenient way to make a whole machine or container strict without editing every script, and a confusing one when a forgotten PGSSLMODE=disable in a shell profile makes a connection behave differently from the same string elsewhere. When behaviour differs between two machines, env | grep ^PG is the first thing to run. There is also an older PGREQUIRESSL variable, deprecated in favour of PGSSLMODE; if both are set, PGSSLMODE wins.

In a URI, the parameter values are percent-decoded like everything else. A Windows path such as C:\certs\root.crt survives as it is, but a path containing &, # or ? needs escaping, and spaces should be %20. The keyword form avoids all of that.

To confirm what you actually got, look at the banner when psql connects. A TLS session prints a line similar to:

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

Newer clients add more fields, such as the ALPN protocol on PostgreSQL 17. No such line means no TLS. \conninfo inside psql says the same thing, and from any client you can ask the server directly:

SELECT ssl, version, cipherFROM pg_stat_sslWHERE pid = pg_backend_pid();

Drop the WHERE clause and join on pg_stat_activity (as a role allowed to see other sessions) to check every connection at once - a quick way to find the one service still connecting in clear text.

The certificate files: sslrootcert, sslcert, sslkey#

Three client-side files are involved, and libpq has a default location for each:

On Windows the same names live under %APPDATA%\postgresql\. A revocation list can be given with sslcrl (default root.crl) if you run your own CA and revoke certificates.

sslrootcert is the one everybody needs for verify-ca and verify-full. What goes in it depends on who signed the server's certificate:

• Your own CA, or a host's private CA. The CA certificate file, in PEM format. If the server uses a single self-signed certificate with no CA, that certificate is its own root and goes in this file.
• A public CA such as Let's Encrypt. On PostgreSQL 16 and later clients, sslrootcert=system uses the operating system's trust store. Setting it also changes the default mode to verify-full and makes weaker modes an error, which is deliberate: trusting every public CA without checking the host name would let anybody with any public certificate impersonate your database. On older clients, point sslrootcert at the system bundle file, for example /etc/ssl/certs/ca-certificates.crt on Debian and Ubuntu.

sslcert and sslkey matter only when the server is configured to ask for a client certificate - either as the login method (cert) or as an extra check on top of a password. The key file must not be readable by anyone else: libpq refuses a key with group or world permissions on Unix, so chmod 0600 ~/.postgresql/postgresql.key is part of the setup. PostgreSQL 16 added sslcertmode (disable, allow, require) to control whether the client sends a certificate at all.

PostgreSQL 17 added one more connection option worth knowing about: sslnegotiation=direct skips the SSLRequest exchange and starts the TLS handshake immediately, saving a round trip. It needs a PostgreSQL 17 server and sslmode of require or stronger. The default, postgres, is the classic negotiation and works everywhere.

The server side: ssl = on, certificates and hostssl#

Nothing on the client matters if the server never offers TLS. On a server you administer, three settings in postgresql.conf turn it on:

ssl = onssl_cert_file = 'server.crt'ssl_key_file = 'server.key'ssl_min_protocol_version = 'TLSv1.2'

Relative paths are resolved against the data directory. Debian and Ubuntu packages ship with ssl = on pointing at a snakeoil certificate generated at install time, which is enough for require but useless for verification, because nothing trusts it and its name is the machine's host name. ssl_min_protocol_version defaults to TLSv1.2 on PostgreSQL 13 and later, so the line above only states the default.

A self-signed certificate for testing, straight from the PostgreSQL documentation's pattern:

$ openssl req -new -x509 -days 365 -nodes -text \    -out server.crt -keyout server.key -subj "/CN=db.example.com"$ chmod 0600 server.key$ chown postgres:postgres server.key server.crt

The server is as strict about its key as the client is: it refuses to start TLS if server.key is readable by group or others, unless the file is owned by root with mode 0640. On PostgreSQL 10 and later, the SSL settings take effect on a reload (SELECT pg_reload_conf();), but a mistake behaves differently depending on how it is applied: a reload logs SSL configuration was not reloaded, while a restart with ssl = on and an unreadable certificate fails to start at all. Check the server log after any change.

For anything clients will verify, use a certificate whose Subject Alternative Name contains the exact host name clients connect with. A certificate for db.example.com fails verify-full when an application connects to the IP address, and the reverse.

Turning TLS on does not force anyone to use it. That is the job of pg_hba.conf:

# TYPE     DATABASE  USER     ADDRESS          METHODhostssl    shop      appuser  0.0.0.0/0        scram-sha-256hostssl    shop      backup   203.0.113.20/32  certhostnossl  all       all      0.0.0.0/0        reject

hostssl lines match only TLS connections, hostnossl lines only plain ones, and host lines both. With only hostssl lines for remote addresses, a prefer client that has been downgraded simply cannot log in - which turns the server into the backstop for clients you do not control. The cert method authenticates by client certificate alone, matching the certificate's Common Name to the role name (or through a map). To require a valid client certificate as well as a password, add clientcert=verify-ca or clientcert=verify-full to a hostssl line; the server then needs ssl_ca_file pointing at the CA that signs client certificates. The older clientcert=1 form was removed in PostgreSQL 14.

On a managed database you usually cannot edit any of this, so the question becomes what the host offers. Test it: psql "...?sslmode=require" either connects or fails with "server does not support SSL", and the banner tells you the protocol. Then ask the host which CA signed the certificate and which host name it covers, because that is what verify-full needs. On RE:NODE, a PostgreSQL server is reached on the host and port shown for the plan, with the superuser password generated for that server - check the TLS banner on your own connection rather than assuming either way, and keep the certificate files your application needs alongside its code, uploaded with the file manager or over SFTP. The hardening steps that sit around TLS are collected in the database security checklist.

Drivers: same words, different behaviour#

libpq-based clients all behave as described above. Drivers that implement the protocol themselves accept the same sslmode words and do not always mean the same thing by them.

psql and psycopg

psql, pg_dump, psycopg 2 and psycopg 3 all use libpq, so the modes, defaults, files and environment variables are identical. Any libpq parameter can be passed as a keyword argument:

import osimport psycopgconn = psycopg.connect(    host="db.example.com",    dbname="shop",    user="appuser",    password=os.environ["DB_PASSWORD"],    sslmode="verify-full",    sslrootcert="/app/certs/root.crt",)

node-postgres

node-postgres (pg) does not use libpq. Without any SSL setting it connects in plain text. In a connection string, its parser (pg-connection-string) treats prefer, require, verify-ca and verify-full all as "TLS on, with full verification by Node's default trust store" - stricter than libpq for the first three, which is why a string that works in psql with require fails in Node with self-signed certificate in certificate chain. Adding uselibpqcompat=true to the string switches to libpq meanings. sslmode=no-verify turns verification off entirely, which is the same as require in libpq and no better.

The right fix is not rejectUnauthorized: false, which is the most copied line in this whole area and disables the check that makes TLS worth having. Give Node the CA instead:

import fs from "node:fs";import { Pool } from "pg";const pool = new Pool({  host: "db.example.com",  database: "shop",  user: "appuser",  password: process.env.DB_PASSWORD,  ssl: { ca: fs.readFileSync("/app/certs/root.crt", "utf8") },});

One trap: if you pass both a connectionString containing sslmode and an ssl object, the SSL settings parsed from the string replace yours, and the ca you supplied is silently ignored. Use one or the other.

JDBC

The PostgreSQL JDBC driver (pgjdbc) has its own implementation too. In current versions its sslmode defaults to prefer, the same as libpq; setting ssl=true with no mode means verify-full. Its default files mirror libpq's but the key differs: it looks for postgresql.pk8, and reads PKCS-12 (.p12, .pfx) or PKCS-8 (.pem, .der) keys, not the traditional PEM key that libpq uses. Older driver versions behaved differently, so check the version in your build file before trusting a default.

spring.datasource.url=jdbc:postgresql://db.example.com:5432/shop?sslmode=verify-full&sslrootcert=/app/certs/root.crt

sslfactory=org.postgresql.ssl.NonValidatingFactory turns verification off and is the JDBC equivalent of rejectUnauthorized: false. Treat any copy of it as a bug to remove.

Other drivers - Go's pgx, .NET's Npgsql, Rust's tokio-postgres - each have their own defaults and spellings. Read the driver's own documentation for the word sslmode before shipping, and keep the CA file in your deployment the same way you keep other configuration; environment variables and secrets covers where those belong.

SSL errors and what they mean#

`server does not support SSL, but SSL was required` - the client asked for require or stronger and the server answered N. Either ssl = off on the server, the certificate failed to load at startup (look for it in the server log), or something in the middle such as a TCP proxy is answering instead of PostgreSQL. node-postgres reports the same situation as The server does not support SSL connections. Do not "fix" it by lowering the mode; fix the server or ask the host.

`root certificate file "/home/app/.postgresql/root.crt" does not exist` - you asked for verify-ca or verify-full and gave no sslrootcert, so libpq looked in the default location and found nothing. Newer clients add a hint suggesting sslrootcert=system or a weaker mode. Point sslrootcert at the right file; in containers, remember that the home directory is often /root or does not exist at all.

`SSL error: certificate verify failed` - the server certificate does not chain to the CA in sslrootcert. Usually the wrong file (the server certificate of another machine, or a bundle missing an intermediate), or a public certificate checked against a private root. openssl s_client -starttls postgres -connect db.example.com:5432 (OpenSSL 1.1.1 and later) shows the chain the server actually sends.

`server certificate for "db.example.com" does not match host name "10.0.0.5"` - verify-full doing its job. Connect using a name the certificate covers, or reissue the certificate with the name or address you use in its Subject Alternative Name.

`SSL SYSCALL error: EOF detected` or `SSL SYSCALL error: Connection reset by peer` - the TCP connection under the TLS session closed without a proper TLS shutdown. It is rarely a certificate problem. Common causes: the server process was killed (look for an out-of-memory stop in the server log), the server restarted, or a NAT gateway, firewall or load balancer dropped an idle connection. If it happens to idle connections in a pool, set the pool's idle timeout below the network's and enable TCP keepalives - connection pools and limits goes through the settings.

`private key file "/home/app/.postgresql/postgresql.key" has group or world access` - chmod 0600 the file. In Docker, files mounted from a Windows or macOS host often arrive with loose permissions; copy the key into the image or a volume and set the mode there.

`no pg_hba.conf entry for host "...", user "appuser", database "shop", no encryption` - the server only accepts TLS for you and the client connected in plain text. Something set disable or allow, or a driver defaulted to no TLS. Set the mode explicitly.

For certificate failures in general - expired certificates, missing intermediates, wrong names - the same reasoning applies as for web servers; SSL certificate errors explained works through them.

FAQ#

What is the default sslmode in PostgreSQL?

For libpq and everything built on it (psql, psycopg, pg_dump), it is prefer: use TLS if the server offers it, fall back to plain text otherwise, never check the certificate. Drivers with their own implementation choose their own default, and node-postgres uses no TLS at all unless told to.

Is sslmode=require secure enough?

It stops passive eavesdropping, and on a private network you fully control that may be enough. It does not stop an active attacker, because any certificate is accepted. Over the internet, use verify-full.

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

Both check that the certificate was signed by a CA you trust. verify-full also checks that the certificate was issued for the host name you connected to. With a public CA, verify-ca alone is nearly worthless, since anyone can get a certificate from that CA for their own domain.

Where do I get root.crt?

From whoever runs the server. For a self-signed server it is the server's server.crt itself; for a private CA, the CA certificate; for a public CA, the system trust store via sslrootcert=system on PostgreSQL 16 and later clients.

Does sslmode apply to Unix socket connections?

No. TLS is only used over TCP. A connection through /var/run/postgresql ignores sslmode, which is why a local psql works even with PGSSLMODE=verify-full set and no certificate in sight.

Can I force TLS from the server instead of trusting clients?

Yes. Use only hostssl lines for remote addresses in pg_hba.conf, and add a hostnossl ... reject line for clarity. A plain-text client then fails to log in rather than leaking its session. This does not replace verify-full on the client, which is the only thing that stops impersonation.