为何psycopg2验证CA证书失败而DataGrip可正常连接？

问题现象 #

• 本地Windows PC连接局域网内Debian 11上的PostgreSQL 13，sslmode=require时Python应用（psycopg2/SQLAlchemy/pg8000）可正常连接，但设置sslmode=verify-ca并指定CA证书后连接失败
• DataGrip使用相同CA文件勾选SSL选项能正常连接，修改CA文件内容后DataGrip连接失败，改回后恢复正常
• pg_isready测试verify-ca模式立即返回无响应
• 服务器无防火墙，本地Windows已放行相关程序
• Postgres日志报错：LOG: could not accept SSL connection: tlsv1 alert unknown ca

失败的Python代码示例 #

SQLAlchemy连接代码 #

try:
    # Create a SQLAlchemy engine
    engine = create_engine(DB_URL, connect_args={
        #"sslmode": "require",
        "sslmode": "verify-ca",
        "sslrootcert": "chain.pem"
    })

psycopg2连接代码 #

db_params = {
    "dbname": "db",
    "user": "db_user",
    "password": "db_pwd",
    "host": "192.168.1.69",
    "port": "5432",
    "sslmode": "verify-ca",
    "sslrootcert": "chain.pem"  # Path to the CA certificate file
}
connection = psycopg2.connect(**db_params)

报错信息 #

psycopg2运行日志 #

[9104] psycopgmodule: initializing psycopg 2.9.7 (dt dec pq3 ext lo64)
[9104] psycopgmodule: configuring libpq libcrypto callbacks 
[9104] psycopgmodule: initializing module constants
[9104] psycopgmodule: initializing module types
[9104] psycopgmodule: initializing datetime module
[9104] psycopgmodule: initializing encodings table
[9104] psycopgmodule: initializing adapters
[9104] psycopgmodule: initializing basic exceptions
[9104] psycopgmodule: initializing sqlstate exceptions
[9104] psycopgmodule: module initialization complete
['bolt']
[9104] psyco_connect: dsn = 'dbname=db user=db_user password=pw host=192.168.1.69 port=5432 sslmode=verify-ca sslrootcert=chain.pem', async = 0
[9104] connection_setup: init connection object at 000001944844E908, async 0, refcnt = 1
[9104] con_connect: connecting in SYNC mode
[9104] conn_connect: new PG connection at 0000019447A4AF30
[9104] conn_connect: PQconnectdb(dbname=db user=db_user password=pw host=192.168.1.69 port=5432 sslmode=verify-ca sslrootcert=chain.pem) returned BAD
[9104] connection_init: FAILED
[9104] conn_close: PQfinish called
[9104] connection_dealloc: deleted connection object at 000001944844E908, refcnt = 0

注：['bolt']是调试语句，与连接无关

SQLAlchemy报错 #

postgresql+psycopg2://username:password@localhost/dbname -> postgresql+psycopg2://database_postgres_user:password@192.168.1.69:5432/table
[28112] psyco_connect: dsn = 'host=192.168.1.69 dbname=database user=database_postgres_user password=password port=5432 sslmode=verify-ca sslrootcert=chain.pem', async = 0
[28112] connection_setup: init connection object at 000001E95A3956D8, async 0, refcnt = 1
[28112] con_connect: connecting in SYNC mode
[28112] conn_connect: new PG connection at 000001E959645310
[28112] conn_connect: PQconnectdb(host=192.168.1.69 dbname=database user=database_postgres_user password=password port=5432 sslmode=verify-ca sslrootcert=chain.pem) returned BAD
[28112] connection_init: FAILED
[28112] conn_close: PQfinish called
[28112] connection_dealloc: deleted connection object at 000001E95A3956D8, refcnt = 0
(psycopg2.OperationalError) connection to server at "192.168.1.69", port 5432 failed: SSL error: certificate verify failed

pg_isready结果 #

192.168.1.69:5432 - no response

服务器配置 #

Certbot续期钩子脚本 #

cp /etc/letsencrypt/live/MY_DOMAIN/fullchain.pem /etc/postgresql/13/main/cert_copies/fullchain.pem
cp /etc/letsencrypt/live/MY_DOMAIN/privkey.pem /etc/postgresql/13/main/cert_copies/privkey.pem
cp /etc/letsencrypt/live/MY_DOMAIN/cert.pem /etc/postgresql/13/main/cert_copies/cert.pem
chown postgres:postgres /etc/postgresql/13/main/cert_copies/fullchain.pem /etc/postgresql/13/main/cert_copies/privkey.pem /etc/postgresql/13/main/cert_copies/cert.pem
chmod 700 /etc/postgresql/13/main/cert_copies/fullchain.pem /etc/postgresql/13/main/cert_copies/privkey.pem /etc/postgresql/13/main/cert_copies/cert.pem

pg_hba.conf配置 #

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32md5
host    replication     all             ::1/128                 md5

# MY CUSTOM STUFF
host    all             all             0.0.0.0/0               md5
hostssl all             all             0.0.0.0/0               md5

postgresql.conf相关配置 #

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
# - SSL -
ssl = on
#ssl_ca_file = ''
ssl_ca_file = '/etc/postgresql/13/main/cert_copies/fullchain.pem'
ssl_cert_file = '/etc/postgresql/13/main/cert_copies/cert.pem'
#ssl_crl_file = ''
ssl_key_file = '/etc/postgresql/13/main/cert_copies/privkey.pem'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on

Postgres日志错误 #

2023-08-18 16:29:31.483 PDT [5913] [unknown]@[unknown] LOG:  could not accept SSL connection: tlsv1 alert unknown ca

解决方案 #

1. 修正PostgreSQL的ssl_ca_file配置 #

PostgreSQL的ssl_ca_file用于验证客户端证书，你当前配置为fullchain.pem是错误的。服务器证书链应通过ssl_cert_file指定，ssl_ca_file若无需验证客户端证书可留空：

ssl_ca_file = ''
ssl_cert_file = '/etc/postgresql/13/main/cert_copies/fullchain.pem'

修改后重启PostgreSQL服务。

2. 匹配证书域名与连接主机 #

你使用的Let's Encrypt证书绑定的是MY_DOMAIN，但连接时用的是局域网IP192.168.1.69。verify-ca模式会验证证书域名与连接主机名是否匹配，DataGrip可能默认跳过了该验证。

• 本地Windows配置hosts文件，将MY_DOMAIN指向192.168.1.69
• Python连接时使用MY_DOMAIN作为host参数，而非IP

3. 确认CA证书路径与完整性 #

• 确保Python代码中sslrootcert使用绝对路径（Windows下格式如C:\\cert\\chain.pem）
• 验证本地chain.pem与服务器上的fullchain.pem内容完全一致

4. 验证证书链有效性 #

在本地Windows使用OpenSSL命令验证：

openssl s_client -connect 192.168.1.69:5432 -CAfile chain.pem

若输出Verify return code: 0 (ok)则证书链正常，否则根据提示排查问题。

5. 更新psycopg2依赖 #

Windows下psycopg2依赖的libpq库可能存在兼容性问题，尝试升级到最新版本或使用二进制包：

pip install --upgrade psycopg2-binary

内容的提问来源于stack exchange，提问作者brian_wukong