How to fix Certificate verify failed in PostgreSQL

PostgreSQLINTERMEDIATEHIGH

PostgreSQL "certificate verify failed" is an SSL/TLS error that occurs when the client cannot verify the server's SSL certificate. This typically happens when root certificates are missing, outdated, or misconfigured. Download the appropriate root certificate and configure your client to use it with proper SSL mode settings.

What this error means

The "certificate verify failed" error occurs during the SSL/TLS handshake when a PostgreSQL client attempts to establish a secure connection to the server. When sslmode is set to "verify-ca" or "verify-full", PostgreSQL verifies that the server's certificate was signed by a trusted Certificate Authority (CA). If the CA certificate file is missing, the certificate has expired, or the certificate was issued by an unknown authority, the connection fails with this error. This is a security mechanism designed to prevent man-in-the-middle attacks. The client refuses to connect to a server whose identity cannot be verified using a trusted root certificate.

How to fix "Certificate verify failed"

1Download the server's root certificate

First, obtain the correct root certificate from your database provider. Common sources:

For Azure PostgreSQL:

bash

wget https://cacerts.digicert.com/DigiCertGlobalRootCA.crt

For AWS RDS PostgreSQL:

bash

wget https://truststore.pki.rds.amazonaws.com/rds-ca-2019-root.pem
wget https://truststore.pki.rds.amazonaws.com/rds-ca-2024-root.pem

For Google Cloud SQL:

bash

wget https://www.google.com/cumulus/download_dlm/Cloud%20SQL%20Client%20Certificate%20CA.pem

Verify the certificate was downloaded successfully:

bash

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

2Create the .postgresql directory and place the certificate

On Linux and macOS, create the PostgreSQL configuration directory and place the root certificate there:

bash

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

On Windows, place the certificate in:

bash

C:\Users\<YourUsername>\AppData\Roaming\postgresql\root.crt

Create the directory if it doesn't exist.

3Update your connection string with sslmode and sslrootcert

Configure your client to use certificate verification. Update your connection string to include sslmode and sslrootcert parameters:

For psql (command line):

bash

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

For libpq-based applications, add to connection string:

bash

sslmode=verify-full
sslrootcert=~/.postgresql/root.crt

For JDBC (Java):

java

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

For psycopg2 (Python):

python

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

4Test the connection

Verify the connection now works with certificate verification:

bash

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

If successful, you should see the PostgreSQL prompt. If the error persists, check the next steps.

5For Docker, ensure ca-certificates package is installed

If running in a Docker container, the ca-certificates package may be missing:

dockerfile

FROM postgres:latest

RUN apt-get update && apt-get install -y ca-certificates

For Node.js or other application containers that connect to PostgreSQL:

dockerfile

FROM node:latest

RUN apt-get update && apt-get install -y ca-certificates

COPY root.crt /etc/ssl/certs/postgres-ca.crt

ENV PGSSLROOTCERT=/etc/ssl/certs/postgres-ca.crt
ENV PGSSLMODE=verify-full

6Verify certificate chain and expiration

If issues persist, check the certificate validity and chain:

bash

# Check certificate expiration date
openssl x509 -in root.crt -noout -dates

# View certificate details
openssl x509 -in root.crt -noout -text

# Verify the server certificate matches the root
openssl s_client -connect host:5432 -showcerts

If the certificate has expired, download an updated root certificate from your provider. If the certificate is valid but issued by a different CA, ensure you have the correct root certificate file.

How to fix Certificate verify failed in PostgreSQL

What this error means

Typical symptoms

Common causes

How to fix "Certificate verify failed"

Advanced notes

Related errors

Official resources & further reading