How to fix Certificate verify failed in PostgreSQL

Obtain the root CA certificate from your database provider's official source. Always use the provider's documented download method rather than ad-hoc URLs.

For AWS RDS / Aurora PostgreSQL (use the current global bundle; rds-ca-2019 is expired/superseded):

wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem -O rds-ca-global.pem

For Azure Database for PostgreSQL (current servers chain to the DigiCert Global Root G2):

wget https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem -O azure-root.pem

For Google Cloud SQL, the per-instance server CA is not available from a fixed wget URL. Download it via the gcloud CLI (or the Cloud Console > your instance > Connections > Security):

gcloud sql instances describe INSTANCE_NAME \
  --format="value(serverCaCert.cert)" > cloudsql-server-ca.pem

Verify the file is a valid certificate before using it:

openssl x509 -in root.crt -noout -text
openssl x509 -in root.crt -noout -dates

On Linux and macOS, libpq looks for ~/.postgresql/root.crt by default:

mkdir -p ~/.postgresql
chmod 700 ~/.postgresql
cp root.crt ~/.postgresql/root.crt
chmod 600 ~/.postgresql/root.crt

On Windows, the default location is:

%APPDATA%\postgresql\root.crt

which typically resolves to C:\Users\<YourUsername>\AppData\Roaming\postgresql\root.crt. Create the postgresql directory if it does not exist.

If you place the file at the default path, libpq will use it automatically for verify-ca/verify-full without an explicit sslrootcert.

Use verify-full and point sslrootcert at the certificate. Important: libpq does NOT expand ~ in the sslrootcert connection parameter, so always use an absolute path (or the literal default-location keyword system on newer clients). Using sslrootcert=~/.postgresql/root.crt will silently fail to find the file.

For psql / libpq (use an absolute path):

psql "postgresql://user@host:5432/database?sslmode=verify-full&sslrootcert=/home/youruser/.postgresql/root.crt"

If the cert is at the default ~/.postgresql/root.crt location, you can omit sslrootcert entirely:

psql "postgresql://user@host:5432/database?sslmode=verify-full"

For JDBC (Java) — use an absolute, forward-slash path:

String url = "jdbc:postgresql://host:5432/database"
    + "?sslmode=verify-full"
    + "&sslrootcert=/home/youruser/.postgresql/root.crt";
// On Windows: sslrootcert=C:/Users/Username/AppData/Roaming/postgresql/root.crt

For psycopg / psycopg2 (Python) — expand the path yourself):

import os, psycopg2
conn = psycopg2.connect(
    host="host",
    dbname="database",
    user="user",
    sslmode="verify-full",
    sslrootcert=os.path.expanduser("~/.postgresql/root.crt"),
)

Avoid putting the password in the URL where possible; prefer a ~/.pgpass file, the PGPASSWORD environment variable, or your driver's credential mechanism.

Verify the connection now works with full verification (note the absolute path):

psql "postgresql://user@host:5432/database?sslmode=verify-full&sslrootcert=/home/youruser/.postgresql/root.crt"

If successful, you should reach the PostgreSQL prompt. You can confirm the session is encrypted with:

SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();

If the error persists, continue with the steps below.

Minimal base images often lack system trust roots and your provider CA. Install ca-certificates and copy the root certificate into the image:

FROM node:latest

RUN apt-get update \
 && apt-get install -y --no-install-recommends ca-certificates \
 && rm -rf /var/lib/apt/lists/*

# Ship the provider root CA with the image
COPY root.crt /etc/ssl/certs/postgres-ca.crt

# libpq reads these env vars; use an absolute path here too
ENV PGSSLROOTCERT=/etc/ssl/certs/postgres-ca.crt
ENV PGSSLMODE=verify-full

For security, mount or bake in the specific provider CA rather than relying on connection-string overrides at runtime.

If issues persist, inspect the certificate and the chain the server actually presents:

# Check the root cert's validity dates
openssl x509 -in root.crt -noout -dates

# Inspect the chain the server sends (PostgreSQL uses STARTTLS-style negotiation,
# so use -starttls postgres)
openssl s_client -starttls postgres -connect host:5432 -showcerts

If the certificate has expired or was superseded by a provider rotation, download the current root certificate (Step 1) and replace your root.crt. If the server's chain is signed by a different CA than your file, you have the wrong root certificate.

Disabling verification removes protection against man-in-the-middle attacks, so treat this as a temporary diagnostic step only — never as a fix for production traffic.

The secure resolution is always to install the correct root CA (Steps 1-3). If you must confirm that a verification mismatch (not a network/auth problem) is the cause, you can briefly test with encryption but no CA check:

# sslmode=require encrypts but does NOT verify the server identity — diagnostic only
psql "postgresql://user@host:5432/database?sslmode=require"

If this connects, the problem is specifically the trusted root certificate — go back to Step 1 and supply the correct CA, then switch back to sslmode=verify-full. Do not leave clients on require/disable in production.