We tested sslmode against 8 Postgres clients. Only one value works everywhere.

There is a warning sitting in Node projects right now that most people have scrolled past:

SECURITY WARNING: The SSL modes 'prefer', 'require', and 'verify-ca' are
treated as aliases for 'verify-full'.
In the next major version (pg-connection-string v3.0.0 and pg v9.0.0), these
modes will adopt standard libpq semantics, which have weaker security
guarantees.

Read that carefully, because it is the opposite of the usual deprecation. Nothing you wrote is wrong. Your connection string will keep parsing, keep connecting, and keep looking exactly the same. It will simply stop verifying the certificate.

Today, in pg, sslmode=require is treated as verify-full: chain and hostname are both checked. Under real libpq semantics, require means encrypt the connection and verify nothing about who is on the other end. (There is one wrinkle: hand libpq a root certificate file and require has historically been documented to check the chain the way verify-ca does. Hostname checking still needs verify-full, so this is not a substitute.) Same string, same code, different security posture, delivered by a dependency bump.

The obvious response is to write something stricter into your connection string ahead of time. That is what we set out to do, and it turns out to be much harder than it sounds, because the four candidate strings behave differently on almost every client.

So we measured all of them.

What we tested

Four variants:

• V1 sslmode=require
• V2 sslmode=verify-full
• V3 sslmode=verify-full&sslrootcert=system
• V4 uselibpqcompat=true&sslmode=require

Against eight clients, at pinned versions, all pointed at the same Postgres behind a publicly-trusted certificate. We ran each variant against three different endpoints for the same database, and the results were byte-identical across all three. Across those three endpoints, only the client and the variant mattered. A different certificate, a different issuer, or a self-signed setup would be its own run.

The matrix

One column has no failures. It is the one that is about to change meaning.

The failures, with their actual error strings

¹ Stock libpq has no trusted root store. verify-full fails on psql and psycopg not because anything is wrong with the certificate, but because libpq looks for ~/.postgresql/root.crt and that file does not exist on a normal machine:

psql: error: connection to server at "..." failed: root certificate file
"/Users/you/.postgresql/root.crt" does not exist

This surprises people who assume verify-full uses the system trust store the way a browser or curl would. It does not. It uses a Postgres-specific file that nothing creates for you. A publicly-trusted, perfectly valid certificate fails exactly as hard as a self-signed one.

² sslrootcert=system needs libpq 16 or newer. On libpq 14 the word system is not special, so it is read as a filename:

root certificate file "system" does not exist

That is the floor, measured. psql 17 and 18 pass V3; psql 14 cannot.

³ uselibpqcompat is not a libpq parameter. It belongs to pg-connection-string, the JavaScript library. Hand it to anything built on real libpq and you get:

psql: error: invalid URI query parameter: "uselibpqcompat"

⁴ node-postgres crashes at parse time on sslrootcert=system. Not at connect. At parse:

Error: ENOENT: no such file or directory, open 'system'
    at pg-connection-string/index.js:99 (readFileSync)

pg has no system special value and treats the string as a literal path, so the process dies before a socket is opened. This is the single most important cell in the table for anyone planning a migration, because verify-full&sslrootcert=system is the advice you will find for psql, and it is actively fatal in Node.

⁵ psycopg's binary wheel is a packaging artifact, not a certificate problem. psycopg[binary] bundles its own OpenSSL with an unpopulated CA directory:

OperationalError: connection failed: ... SSL error: certificate verify failed

The control proves it: the same V2 string with sslrootcert=<certifi cacert.pem> passes. A psycopg[c] install against system libpq behaves like psql 18. If you use the binary wheel, point sslrootcert at certifi.

⁶ Prisma does not verify at all, under any variant. This is the finding that should worry people most, so we controlled it hard. We pointed Prisma at a bare IP address, against a certificate with no IP SAN, with sslmode=verify-full. It connected happily. With Prisma's own sslaccept=strict the same URL correctly fails with P1011 Error opening a TLS connection: The certificate was not trusted, and a correct hostname passes.

So Prisma ignores sslmode semantics entirely. Its only verification knob is sslaccept. That is why its four cells say "connected," not "verified," and if you are relying on sslmode=verify-full in a Prisma URL to give you certificate verification, you do not have it.

⁷ pgx has the same literal-path problem as node-postgres:

failed to configure TLS (unable to read CA file: open system: no such file
or directory)

⁸ pgx forwards unknown parameters to the server, which kills the connection:

FATAL: unsupported startup parameter: uselibpqcompat (SQLSTATE 08P01)

Note the difference from libpq here. psql rejects the unknown parameter locally with a clear message. pgx does not recognize it as a connection parameter, decides it must be a server startup parameter, sends it, and Postgres terminates the session. Same typo, two very different failure modes.

What this means in practice

There is no single string that is both strict and portable today. verify-full is the correct instruction and it fails on every stock libpq client for a reason that has nothing to do with your certificate. sslrootcert=system fixes libpq 16+ and breaks Node and Go. uselibpqcompat is JavaScript-only and is worse than useless elsewhere, because on pgx it reaches the server.

That leaves require, which works everywhere and is precisely the value whose meaning is scheduled to change.

The resolution we landed on, and the one we would suggest generally, is to stop looking for one string. Split by audience. A human copying a connection string into psql, TablePlus, or a GUI needs require, because it is the only value that works across the versions real people have installed. A machine-written environment variable, where you control the client, can carry the strict form appropriate to that client.

Treat require as the compatibility fallback it is, not the target. Where the client can do it, verify-full plus a root certificate you point at explicitly is the value you actually want, and it is the only combination that checks both the chain and the hostname.

If you are on Node specifically, you have a second option worth knowing: pg accepts an ssl config object, and specifying verification there sidesteps the connection-string semantics question entirely. That is the path we use where a Node client is the known consumer. One catch: sslmode, sslcert, sslkey and sslrootcert in the connection string are parsed into that same ssl config, so leaving them in means two things are writing the same object. Strip them from the URL before you set ssl.ca or rejectUnauthorized by hand.

And if you use Prisma, none of the above applies to you. Set sslaccept=strict and verify it fails against a bad certificate, because your sslmode is decorative.

Reproducing this

Every result above is one command against a real server. The method is worth copying, because the interesting failures are the ones that happen before a connection is attempted, and a test harness that only checks "did it connect" will miss them entirely.

For each client, run the four variants against a host with a publicly-trusted certificate, and record the exact stderr. Then run two controls: a bare IP against a certificate with no IP SAN, which must fail if verification is real, and a known-good hostname, which must pass. The Prisma finding only exists because of the first control. Without it, four PASS cells look like a well-behaved client.

Check versions before trusting any of this against your own stack. pg-connection-string behavior is version-specific by definition here, and psycopg's result depends on which wheel you installed rather than on psycopg itself.

We hand out Postgres connection strings for a living at Layerbase Cloud, which is why we had to settle this rather than guess. If you want a Postgres instance with a publicly-trusted certificate to run your own version of this matrix against, creating one takes about a minute.