database auth_error ai_generated true

psycopg2.OperationalError: could not connect to server: SSL error: certificate verify failed

ID: database/ssl-certificate-verify-failed

Also available as: JSON · Markdown · 中文
85%Fix Rate
85%Confidence
1Evidence
2024-03-12First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
PostgreSQL 14 active — — —
PostgreSQL 15 active — — —
PostgreSQL 16 active — — —
psycopg2 2.9.9 active — — —
libpq 15.4 active — — —

Root Cause

PostgreSQL client cannot verify the server's SSL certificate because the CA certificate is missing, expired, or the server hostname does not match the certificate's CN/SAN.

generic

中文

PostgreSQL客户端无法验证服务器的SSL证书，因为CA证书缺失、过期或服务器主机名与证书的CN/SAN不匹配。

Official Documentation

https://www.postgresql.org/docs/current/libpq-ssl.html

Workarounds

  1. 80% success Set the sslrootcert parameter to the correct CA certificate file. Example: sslrootcert=/etc/ssl/certs/ca-certificates.crt in the connection string or ~/.pgpass. 
    Set the sslrootcert parameter to the correct CA certificate file. Example: sslrootcert=/etc/ssl/certs/ca-certificates.crt in the connection string or ~/.pgpass.
  2. 85% success If the server uses a self-signed certificate, add the server's certificate to the client's trust store. Command: echo 'my_server_cert_pem' >> ~/.postgresql/root.crt && chmod 600 ~/.postgresql/root.crt 
    If the server uses a self-signed certificate, add the server's certificate to the client's trust store. Command: echo 'my_server_cert_pem' >> ~/.postgresql/root.crt && chmod 600 ~/.postgresql/root.crt
  3. 90% success Verify the server hostname matches the certificate's Common Name (CN) or Subject Alternative Name (SAN). Use openssl s_client -connect host:5432 to check the certificate. 
    Verify the server hostname matches the certificate's Common Name (CN) or Subject Alternative Name (SAN). Use openssl s_client -connect host:5432 to check the certificate.

中文步骤

  1. Set the sslrootcert parameter to the correct CA certificate file. Example: sslrootcert=/etc/ssl/certs/ca-certificates.crt in the connection string or ~/.pgpass.
  2. If the server uses a self-signed certificate, add the server's certificate to the client's trust store. Command: echo 'my_server_cert_pem' >> ~/.postgresql/root.crt && chmod 600 ~/.postgresql/root.crt
  3. Verify the server hostname matches the certificate's Common Name (CN) or Subject Alternative Name (SAN). Use openssl s_client -connect host:5432 to check the certificate.

Dead Ends

Common approaches that don't work:

  1. Disable SSL verification by setting sslmode=disable in the connection string. 90% fail

    This bypasses security entirely and may violate compliance requirements (e.g., PCI-DSS). It also fails if the server requires SSL.

  2. Reinstall PostgreSQL server without changing certificate configuration. 95% fail

    Reinstalling the server does not fix the certificate trust chain; the root cause is the client's CA store or certificate mismatch.