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

> sslmode=require is the only value that connects on every client we tested. verify-full fails on all stock libpq. sslrootcert=system crashes node-postgres before it opens a socket. Here is the measured matrix, with exact versions and the real error strings.

Published: 2026-08-06
Canonical: https://layerbase.com/blog/postgres-sslmode-client-matrix

Short version: `sslmode=require` is the only value that connected on all eight clients we tested, and it is exactly the value whose meaning is scheduled to change. `sslmode=verify-full` fails on every stock libpq client, not because of your certificate but because libpq looks for `~/.postgresql/root.crt` instead of falling back to the operating system trust store. Adding `sslrootcert=system` fixes libpq 16 and newer and kills node-postgres and pgx at parse time, before a socket is even opened. `uselibpqcompat=true` is a JavaScript-only invention: psql rejects it locally, and pgx forwards it to the server, which terminates the session. Prisma ignores `sslmode` under every variant, so its only real verification knob is `sslaccept`. The conclusion we landed on is to stop hunting for one portable string and split by audience: `require` for a human pasting into a client you do not control, the strict form for a machine-written variable where you do.

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

```text
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

| Client (exact version) | V1 `require` | V2 `verify-full` | V3 `+sslrootcert=system` | V4 `uselibpqcompat` |
| --- | --- | --- | --- | --- |
| psql 14.12 (libpq 14) | **PASS** | FAIL ¹ | FAIL ² | FAIL ³ |
| psql 17.7 (libpq 17) | **PASS** | FAIL ¹ | **PASS** | FAIL ³ |
| psql 18.3 (libpq 18) | **PASS** | FAIL ¹ | **PASS** | FAIL ³ |
| node `pg` 8.22.0 | **PASS** | **PASS** | FAIL ⁴ | **PASS** |
| psycopg 3.3.4 (`psycopg[binary]`, libpq 18) | **PASS** | FAIL ¹ | FAIL ⁵ | FAIL ³ |
| drizzle-orm 0.45.2 (over node `pg`) | **PASS** | **PASS** | FAIL ⁴ | **PASS** |
| Prisma 7.9.1 | PASS (unverified) ⁶ | PASS (unverified) ⁶ | PASS (unverified) ⁶ | PASS (unverified) ⁶ |
| Go pgx v5.4.3 | **PASS** | **PASS** | FAIL ⁷ | FAIL ⁸ |

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:

```text
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:

```text
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:

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

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

```text
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:

```text
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:**

```text
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:**

```text
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.

## FAQ

### Which sslmode value should I actually put in my connection string?

It depends on who reads the string, which is the uncomfortable finding of the whole exercise. For a string a person will paste into psql, TablePlus, or a GUI, `require` is the only value that works across the client versions real people have installed. For an environment variable consumed by a client you control, use the strict form that client supports: `verify-full` with a root certificate you point at explicitly, or on Node, the `ssl` config object instead of URL parameters.

### Why does verify-full fail with "root certificate file does not exist"?

Because libpq does not use the system trust store. It looks for a Postgres-specific file, and on a normal machine that file was never created:

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

A publicly-trusted, perfectly valid certificate fails exactly as hard as a self-signed one. This is the single most common reason people conclude their managed provider's certificate is broken when it is not.

### Is sslrootcert=system safe to add?

Only if you know every client that will read the string. It works on psql 17 and 18, and it is the standard advice you will find for libpq. On psql 14 the word `system` is read as a filename and fails. In node-postgres it is worse than a failure: `pg-connection-string` calls `readFileSync('system')` and the process dies with `ENOENT: no such file or directory, open 'system'` before a socket is opened. Go pgx has the same literal-path problem.

### Does Prisma respect sslmode=verify-full?

No, and we controlled for it rather than assuming. We pointed Prisma at a bare IP against a certificate with no IP SAN, with `sslmode=verify-full` in the URL, and it connected happily. The same URL with Prisma's own `sslaccept=strict` correctly failed with `P1011 Error opening a TLS connection: The certificate was not trusted`. If you are relying on `sslmode` in a Prisma URL for certificate verification, you do not have it; set `sslaccept=strict` and prove it fails against a bad certificate.

### What happens if uselibpqcompat=true reaches a non-Node client?

Nothing good, and the failure modes differ in a way worth knowing. psql rejects it locally with `invalid URI query parameter: "uselibpqcompat"`, which is clear enough. pgx does not recognize it as a connection parameter, decides it must be a server startup parameter, sends it, and Postgres terminates the session with `FATAL: unsupported startup parameter: uselibpqcompat (SQLSTATE 08P01)`. Keep it out of any string something other than Node will read.

---

We hand out Postgres connection strings for a living at [Layerbase Cloud](https://cloud.layerbase.com/create), 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](https://layerbase.com/create/postgresql) takes about a minute.