Verified Postgres TLS when no host chains to a public root

The problem

Search almost any Node.js codebase that talks to a managed Postgres and you will find ssl: { rejectUnauthorized: false }. It usually arrives the day someone first connects over TLS and gets self-signed certificate in certificate chain. The flag makes the error go away, the connection is still encrypted, and the work looks finished.

TLS does two jobs: it encrypts, and it proves the server is the one you meant to reach. With verification off you keep the first and lose the second. Anyone who can sit between your app and the database (a compromised network hop, a poisoned DNS answer, a misrouted cloud peering) can present any certificate and read every query, password included.

We found this in a production application, in the shared database module, applied unconditionally. It had been added for a development database, and it covered production too.

The flag usually stays off for a real reason: the server's certificate does not chain to anything in the system trust store. This is common:

• Managed providers often sign with their own CA. AWS RDS ships a CA bundle you download; some connection poolers present a chain that ends at the provider's own root. openssl reports verify code 19 (self-signed certificate in certificate chain).
• Self-hosted databases usually carry a self-signed certificate. openssl reports code 18 (self-signed certificate).

Flip the flag to true and change nothing else, and every connection fails at once. The fix needs the six steps below.

Step 1: find out what each server presents

Before touching code, look at each host. OpenSSL 1.1.1 and later can speak Postgres's STARTTLS, so one command shows the chain and the verify result:

openssl s_client -starttls postgres -connect db.example.com:5432 -showcerts </dev/null 2>/dev/null \
  | grep -E 'subject=|issuer=|Verify return code'

Record three things per host:

1. The verify return code. 0 means a public chain: turn verification on and you are done. 18 or 19 means you need to pin a CA.
2. The issuer at the top of the chain. For a managed provider, download that root from the provider's documentation, not from the handshake, so you are not trusting what an attacker could have shown you. For a self-signed server, the certificate is its own CA; take it from the server's filesystem or your infrastructure repository.
3. The names on the certificate. Check the Subject Alternative Names. If the certificate names db.example.com and you connect to 203.0.113.10, verification fails even with the right CA. Connect by the name the certificate carries.

For a self-signed certificate to work as a pinned CA it should also carry CA:TRUE in its basic constraints. Check with openssl x509 -noout -text.

If you run the server yourself, check its side as well. Use hostssl entries with scram-sha-256 in pg_hba.conf, so remote connections must use TLS and a password, and keep their address ranges as narrow as you can.

Step 2: pin the CAs in code

With node-postgres, pass the CA certificates explicitly and turn verification on:

const pool = new pg.Pool({
  connectionString: pgConnectionString,   // sslmode stripped, see step 3
  ssl: sslDisabled ? false : { ca: TRUSTED_DB_CAS, rejectUnauthorized: true },
  max: 1,
});

Three decisions sit behind those lines.

Commit the certificates. CA certificates are public. Putting them in an environment variable feels tidier, but then one environment where the variable was never set loses verification without any error. Committed, they cannot go missing.

Inline them as strings. On serverless platforms the bundler decides which files ship with a function, and arbitrary files read at runtime are not reliably included. A module that exports the PEM text always ships:

/**
 * CAs trusted for Postgres connections. No host here has a public chain:
 *   app-prod  -> provider root CA   (openssl verify code 19)
 * Public certificates, committed on purpose.
 * Expiry: provider root <yyyy-mm-dd>, self-signed <yyyy-mm-dd>.
 */
const POOLER_ROOT_CA = `-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----`;

// Passed as ssl.ca. This replaces the system trust store for these connections.
export const TRUSTED_DB_CAS: string[] = [POOLER_ROOT_CA, SELF_SIGNED_DB_CA];

Know that ca replaces the system store. Supplying ca means Node trusts only those certificates for this connection. That narrows trust to your own databases, which is what you want. It also means that if the provider ever moves to a public chain, you will need to add that root.

Keep one explicit escape: sslmode=disable for a CI service container on localhost that speaks plaintext. Make it the only way to turn TLS off, and make it visible in the connection string, where a reviewer will see it.

Step 3: strip sslmode from the connection string

This trap undoes the work in step 2, and it is not documented where you would look for it.

In node-postgres, an sslmode in the connection string overrides the ssl object you pass. We measured it before we understood it: the same pinned-CA configuration failed with ?sslmode=verify-full in the URL and succeeded without it.

The mechanism is in the library. node-postgres parses the connection string and merges the result over your config object, so anything the string sets wins. The parser, on seeing any sslmode (or sslrootcert, sslcert, sslkey), starts a fresh ssl object. The relevant library lines, trimmed:

// pg/lib/connection-parameters.js
if (config.connectionString) {
  config = Object.assign({}, config, parse(config.connectionString))
}

// pg-connection-string/index.js
if (config.sslcert || config.sslkey || config.sslrootcert || config.sslmode) {
  config.ssl = {}
}
// ...
case 'no-verify': {
  config.ssl.rejectUnauthorized = false

Two outcomes follow:

• sslmode=verify-full with no sslrootcert gives you ssl: {}. Your pinned CAs are gone, the system store is used, and the handshake fails. You will notice this one.
• sslmode=no-verify gives you rejectUnauthorized: false. Your pinned config is replaced by an unverified one and everything keeps working. You will not notice this one, which makes it the worse case.

The connection string usually lives outside your repository, in the hosting platform's settings and in CI secrets. Anyone can add ?sslmode=require "for clarity" months from now. Make the code authoritative by removing the parameter before the pool sees it:

// A connection-string sslmode replaces the ssl object in node-postgres.
// Remove it so the pinned-CA config always applies.
function stripSslMode(url: string): string {
  return url
    .replace(/([?&])sslmode=[^&]*&/gi, "$1")
    .replace(/[?&]sslmode=[^&]*$/gi, "");
}

const sslDisabled = connectionString?.includes("sslmode=disable");
const pgConnectionString =
  connectionString && !sslDisabled ? stripSslMode(connectionString) : connectionString;

Check the parser behavior in the version you run; it has changed across releases, and newer versions offer a libpq-compatible mode with different semantics. The safe position does not depend on the version: do not let the URL decide.

Step 4: verify on every path that connects, including CI migrations

Your application's pool is probably not the only client. Schema migration tools often use their own engine. With Prisma, for example, prisma migrate deploy runs through Prisma's schema engine, and the ssl object you gave the node-postgres adapter does not apply to it. That engine takes TLS settings from the URL, and it wants a path to a certificate file.

It also has its own parameter names. Prisma's engine does not read libpq's sslmode=verify-full or sslrootcert, and its default accepts any certificate. We tested it against a Postgres server with a certificate from a lab CA: with verify-full and sslrootcert in the URL, it connected through a wrong CA and through a hostname the certificate did not name. With its own parameters, sslcert for the CA file and sslaccept=strict, it refused both with error P1011 and connected with the right CA.

So the CA lives twice: inlined for the app, and as a .crt file for migrations. The migration step does the opposite of step 3. It strips every TLS setting the secret carries and then appends the engine's own:

URL="$(printf '%s' "$DIRECT_URL" | sed -E \
  -e 's/([?&])(sslmode|sslrootcert|sslcert|sslaccept)=[^&]*/\1/Ig' \
  -e 's/&&+/\&/g; s/\?&/?/; s/[?&]$//')"
case "$URL" in *\?*) SEP="&" ;; *) SEP="?" ;; esac
URL="${URL}${SEP}sslmode=require&sslcert=$PWD/certs/db-ca.crt&sslaccept=strict"
DATABASE_URL="$URL" DIRECT_URL="$URL" npx prisma migrate deploy

Then prove the step refuses what it should: point it once at a wrong CA file and confirm it fails with P1011.

Do this for every migration step, one per environment, production included. It is easy to fix the step you were looking at and leave another taking its mode from whatever the secret happens to contain. List every job, script and tool that opens a database connection (app, migrations, seed scripts, backups, admin consoles) and check each one.

Step 5: test that the two copies agree and have not expired

Two copies of a certificate will drift the first time someone rotates one and forgets the other. Then the app and the migrations trust different things, and you find out during a release. An expired CA is worse: every query fails at once. Both are cheap to test:

describe("database CA certificates", () => {
  const files = ["provider-root-ca.crt", "dev-db-ca.crt"];

  it.each(files)("%s matches its inlined copy", (file) => {
    const onDisk = normalize(fs.readFileSync(path.join(CERT_DIR, file), "utf8"));
    expect(TRUSTED_DB_CAS.map(normalize)).toContain(onDisk);
  });

  it("has not expired", () => {
    for (const file of files) {
      const cert = new X509Certificate(fs.readFileSync(path.join(CERT_DIR, file)));
      expect(new Date(cert.validTo).getTime()).toBeGreaterThan(Date.now());
    }
  });
});

Put the expiry dates in the module header and in a calendar too. A test that fails on the day of expiry is a last line of defense. A variant that fails 90 days ahead is easy to add and gives you time to rotate.

Step 6: pre-flight production without credentials

You can prove the pinned CA works against production before you deploy anything, and without production credentials. Connect with a deliberately wrong user. The two possible failures tell you which layer you reached:

• A certificate error (self-signed certificate in certificate chain, unable to get local issuer certificate, Hostname/IP does not match certificate's altnames) means the TLS handshake failed. Do not deploy.
• An authentication error (password authentication failed, or a pooler's "user not found") means the handshake succeeded and verification passed. The server proved its identity; only your fake login was refused.

A minimal pre-flight script:

// preflight.ts: run with the same CA module the app uses
const client = new pg.Client({
  host: "db.example.com", port: 5432, user: "preflight-nobody", password: "x", database: "postgres",
  ssl: { ca: TRUSTED_DB_CAS, rejectUnauthorized: true },
});
client.connect().then(
  () => console.log("connected (unexpected)"),
  (err) => console.log(err.message),   // expect an auth error, not a certificate error
);

We ran the equivalent check before merging. The handshake completed and the connection failed at the user lookup, which is the result you want.

Traps, collected

• The URL overrides the code. Strip sslmode before node-postgres sees it.
• The migration engine is a different client. It needs its own file copy and its own strict setting, in that engine's parameter names (sslcert and sslaccept=strict for Prisma), in every environment's migration step.
• Connect by name. The name check compares what you connected to with the certificate's SANs, and an IP literal will not match a DNS name.
• A private network may not be an option. If serverless previews or hosted CI runners must reach the database, they connect from outside and cannot join a tunnel, and a source-IP allowlist fails the same way. If a database must be reachable from the internet, verified TLS plus password authentication is the protection, with a least-privilege application role.
• Ask which TLS someone means. When a status page, dashboard or teammate says "the connection uses TLS", ask whether that means encrypted or verified.
• Do not trust what the handshake showed you. Download a provider's root from its documentation; take a self-signed certificate from the server's own files.

We used this in A multi-tenant ticketing platform: zero-downtime releases and a payment path tested against double charges.