database auth_error ai_generated true

psycopg2.OperationalError: 无法连接到服务器：SSL错误：证书验证失败

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

ID: database/ssl-certificate-verify-failed

其他格式: JSON · Markdown 中文 · English
85%修复率
85%置信度
1证据数
2024-03-12首次发现

版本兼容性

版本状态引入弃用备注
PostgreSQL 14 active — — —
PostgreSQL 15 active — — —
PostgreSQL 16 active — — —
psycopg2 2.9.9 active — — —
libpq 15.4 active — — —

根因分析

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

English

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

官方文档

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

解决方案

  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.

无效尝试

常见但无效的做法:

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

    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% 失败

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