database auth_error ai_generated true

psycopg2.OperationalError: FATAL: connection requires a valid client certificate

ID: database/postgresql-ssl-certificate-expired

Also available as: JSON · Markdown · 中文
82%Fix Rate
88%Confidence
1Evidence
2024-06-20First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
PostgreSQL 15 active — — —
PostgreSQL 16 active — — —
PostgreSQL 17 active — — —

Root Cause

PostgreSQL's SSL configuration requires a client certificate, but the provided certificate is missing, expired, or not trusted by the server.

generic

中文

PostgreSQL 的 SSL 配置要求客户端证书，但提供的证书缺失、过期或不被服务器信任。

Official Documentation

https://www.postgresql.org/docs/16/ssl-tcp.html

Workarounds

  1. 90% success Verify client certificate expiry: openssl x509 -in client.crt -noout -dates; if expired, regenerate with: openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout client.key -out client.csr; then have the CA sign it and copy the new client.crt and client.key to the client machine. 
    Verify client certificate expiry: openssl x509 -in client.crt -noout -dates; if expired, regenerate with: openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout client.key -out client.csr; then have the CA sign it and copy the new client.crt and client.key to the client machine.
  2. 85% success Ensure the server's root.crt contains the CA certificate that signed the client certificate: cat ca.crt >> $(pg_config --sysconfdir)/root.crt; then reload pg_hba.conf with pg_ctl reload. 
    Ensure the server's root.crt contains the CA certificate that signed the client certificate: cat ca.crt >> $(pg_config --sysconfdir)/root.crt; then reload pg_hba.conf with pg_ctl reload.

中文步骤

  1. Verify client certificate expiry: openssl x509 -in client.crt -noout -dates; if expired, regenerate with: openssl req -new -newkey rsa:2048 -days 365 -nodes -keyout client.key -out client.csr; then have the CA sign it and copy the new client.crt and client.key to the client machine.
  2. Ensure the server's root.crt contains the CA certificate that signed the client certificate: cat ca.crt >> $(pg_config --sysconfdir)/root.crt; then reload pg_hba.conf with pg_ctl reload.

Dead Ends

Common approaches that don't work:

  1. Disable SSL entirely in the client connection string (sslmode=disable) 100% fail

    The server enforces SSL with client certificate requirement; disabling SSL will be rejected by the server.

  2. Regenerate the client certificate without updating the server's root certificate trust store 70% fail

    If the new certificate is not signed by a CA trusted by the server, or if the server's root.crt is outdated, the connection still fails.