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.
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.
First, obtain the correct root certificate from your database provider. Common sources:
For Azure PostgreSQL:
wget https://cacerts.digicert.com/DigiCertGlobalRootCA.crtFor AWS RDS PostgreSQL:
wget https://truststore.pki.rds.amazonaws.com/rds-ca-2019-root.pem
wget https://truststore.pki.rds.amazonaws.com/rds-ca-2024-root.pemFor Google Cloud SQL:
wget https://www.google.com/cumulus/download_dlm/Cloud%20SQL%20Client%20Certificate%20CA.pemVerify the certificate was downloaded successfully:
file root.crt
openssl x509 -in root.crt -text -nooutOn Linux and macOS, create the PostgreSQL configuration directory and place the root certificate there:
mkdir -p ~/.postgresql
chmod 700 ~/.postgresql
cp root.crt ~/.postgresql/root.crt
chmod 600 ~/.postgresql/root.crtOn Windows, place the certificate in:
C:\Users\<YourUsername>\AppData\Roaming\postgresql\root.crtCreate the directory if it doesn't exist.
Configure your client to use certificate verification. Update your connection string to include sslmode and sslrootcert parameters:
For psql (command line):
psql "postgresql://user:password@host:5432/database?sslmode=verify-full&sslrootcert=~/.postgresql/root.crt"For libpq-based applications, add to connection string:
sslmode=verify-full
sslrootcert=~/.postgresql/root.crtFor JDBC (Java):
String url = "jdbc:postgresql://host:5432/database?sslmode=verify-full&sslrootcert=C:/Users/Username/AppData/Roaming/postgresql/root.crt";For psycopg2 (Python):
conn = psycopg2.connect(
host="host",
database="database",
user="user",
password="password",
sslmode="verify-full",
sslrootcert=os.path.expanduser("~/.postgresql/root.crt")
)Verify the connection now works with certificate verification:
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.
If running in a Docker container, the ca-certificates package may be missing:
FROM postgres:latest
RUN apt-get update && apt-get install -y ca-certificatesFor Node.js or other application containers that connect to PostgreSQL:
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-fullIf issues persist, check the certificate validity and chain:
# 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 -showcertsIf 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.
SSL Modes Explained: PostgreSQL supports multiple sslmode values: "disable" (no encryption), "allow" (encryption optional), "prefer" (encryption preferred, fallback to unencrypted), "require" (encryption mandatory, no certificate verification), and "verify-ca"/"verify-full" (encryption required with certificate verification). For production, always use "verify-full" which verifies both certificate validity and that the hostname matches the certificate CN/SAN fields.
Certificate Rotation and Updates: Database providers periodically rotate their root certificates. Services like AWS RDS publish new root certificates (rds-ca-2024-root.pem, etc.) well in advance. Check your provider's documentation for the latest certificates. Some tools like Certbot can help automate certificate management.
Multiple Root Certificates: If your PostgreSQL servers use certificates from different CAs, you can combine multiple root certificates into a single PEM file:
cat root1.crt root2.crt > combined_root.crtThen reference the combined file in your connection string.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL