PostgreSQL SSL Modes Explained for Node.js and Prisma

TLS can encrypt a PostgreSQL connection, but encryption alone does not prove which server received your credentials. For strong server authentication, the client must also validate the certificate chain and confirm that the certificate matches the requested hostname.

PostgreSQL's native sslmode names are a useful vocabulary, but clients do not all implement every mode identically. Treat libpq, the Node.js driver, Prisma, and a hosted provider as separate configuration layers.

Encryption and identity verification solve different problems

Encryption makes network traffic unreadable to a passive observer. Certificate-authority (CA) verification checks whether a trusted issuer signed the server certificate. Hostname verification checks whether the certificate was issued for the host in the connection configuration.

Without identity verification, a TLS connection can still be encrypted to an impersonating server. That is why disabling certificate verification is not a proper production fix for a certificate error.

Native PostgreSQL sslmode semantics

The following table describes current libpq behavior. It is not a promise that every ORM or JavaScript URL parser supports the same set.

Two libpq details matter:

• prefer tries TLS first and then a non-TLS connection; allow tries non-TLS first and then TLS.
• With a root CA file present, libpq can make require behave like verify-ca for backward compatibility. Do not rely on that nuance as a portable contract across clients.

Local development

For PostgreSQL on the same trusted development machine, sslmode=disable may be an intentional choice if the local server does not offer TLS. It should remain environment-specific. A local setting copied into a hosted deployment can silently remove transport protection.

When development must reproduce production certificate behavior, use a development CA and hostname configuration rather than switching verification off globally. Never reuse production credentials in a local example.

Production selection needs provider context

For traffic across an untrusted network, require encryption. Prefer full CA and hostname verification when the client and provider expose a supported configuration. Some hosted services distribute a CA certificate; others terminate TLS behind provider-managed infrastructure and document sslmode=require as the supported URL setting.

Copy the provider's current connection string and TLS instructions. Do not infer certificate paths, server names, or accepted modes from another provider. A mode that works with libpq may not be accepted by an ORM connector.

The PostgreSQL Connection URL Builder can assemble an sslmode parameter, but it cannot decide which trust policy your provider supports.

Node.js with node-postgres

node-postgres accepts an ssl object that is passed to Node's TLS socket. A CA-verifying configuration can look like this:

import { readFileSync } from "node:fs";
import { Pool } from "pg";
 
const pool = new Pool({
  host: "db.example.com",
  port: 5432,
  database: "example_database",
  user: "app_user",
  password: process.env.DEMO_DATABASE_PASSWORD,
  ssl: {
    ca: readFileSync("/path/to/provider-root.crt", "utf8"),
    rejectUnauthorized: true,
  },
});

The certificate path is a placeholder. Use the CA file and hostname specified by the provider.

Do not combine an ssl object with sslmode, sslcert, sslkey, or sslrootcert in the same connection string without checking the driver behavior. The official node-postgres documentation warns that those URL parameters replace the ssl object, which can discard the CA or other TLS options you supplied in code.

Why rejectUnauthorized: false is not the fix

This setting suppresses certificate-chain and hostname failures:

// Unsafe as a general production solution.
const unsafeTlsOption = { rejectUnauthorized: false };

It makes a connection succeed by removing authentication of the server. Correct the hostname, install the documented CA, or use the provider's supported endpoint instead.

Prisma connection URL behavior

This repository does not install Prisma, so it has no project-specific Prisma version to test. Current Prisma 7 documentation places the datasource URL in prisma.config.ts; Prisma 6 commonly reads it from the datasource block in schema.prisma.

A placeholder Prisma URL can be:

DATABASE_URL="postgresql://app_user:demo_password@db.example.com:5432/example_database?schema=public&sslmode=require"

Current Prisma PostgreSQL connector documentation lists prefer, disable, and require for its sslmode URL argument, plus certificate-related arguments such as sslcert and sslidentity. It does not document native libpq's complete six-mode behavior as a portable Prisma URL contract. Therefore:

• do not assume allow, verify-ca, or verify-full will work merely because libpq supports them;
• follow the Prisma connector documentation for the installed Prisma version;
• follow the provider's Prisma-specific setup when it differs from a generic psql example.

Connection strings contain credentials and must not be logged. For encoding rules, see How to URL-Encode Special Characters in PostgreSQL Connection Strings.

TLS settings are independent of whether the endpoint is direct or pooled. The PostgreSQL connection pooling comparison explains how to choose that endpoint without assuming a port number defines its behavior.

Hosted provider requirements vary

As one verified example, Prisma Postgres currently supplies pooled and direct TCP URLs with sslmode=require and requires TLS. That describes Prisma Postgres, not every PostgreSQL host. A provider may require a different hostname, CA bundle, client certificate, query parameter, or transport.

If a provider issues a ready-to-use URL, start with that exact endpoint. Changing the mode can conflict with its proxy or certificate setup.

Safe troubleshooting sequence

1. Identify the exact client: psql/libpq, node-postgres, Prisma's connector, or another driver.
2. Check the provider's official TLS instructions for that client and endpoint.
3. Confirm the hostname without printing the username or password.
4. Confirm whether a CA file is required and that the running process can read the intended non-secret certificate file.
5. Check the certificate error. An unknown issuer and a hostname mismatch have different fixes.
6. Test from a non-production environment with placeholder or limited-scope credentials.
7. Keep verification enabled and record the final supported configuration.

Do not "troubleshoot" by publishing the URL, adding it to a command line, or committing a certificate private key. A public CA certificate is not normally secret; client keys and database URLs are.

Common mistakes

• Treating sslmode=require as equivalent to hostname verification in every client.
• Assuming the libpq default is a safe production default.
• Copying sslmode=disable from local development into deployment configuration.
• Mixing URL SSL parameters and a node-postgres ssl object so one overwrites the other.
• Using an IP address when the certificate covers only a DNS hostname.
• Disabling verification instead of fixing trust or hostname configuration.

References

Documentation checked on 2026-08-08:

• PostgreSQL: SSL mode protections and certificate verification
• PostgreSQL: sslmode connection parameter
• node-postgres: SSL configuration
• Prisma: PostgreSQL connector URL arguments
• Prisma Postgres: direct and pooled connection strings