Python连接PostgreSQL报ssl.SSLError: [SSL] PEM lib错误求解

问题场景 #

尝试通过SSL的verify-ca模式连接PostgreSQL，使用的Python代码如下：

import os
import ssl

DATABASE_URL = os.environ["DATABASE_URL"]

# using ssl connection for connecting to DB
sslctx = ssl.create_default_context(ssl.Purpose.SERVER_AUTH, cafile='/etc/config/root.pem')
sslctx.verify_mode = ssl.CERT_REQUIRED
sslctx.load_cert_chain(certfile='/etc/config/server.pem', keyfile='/etc/config/pkey.key')

# databases query builder
database = Database(DATABASE_URL, ssl = sslctx)

运行后抛出错误：

File "/usr/local/bin/uvicorn", line 8, in <module>
sys.exit(main())
File "/usr/local/lib/python3.9/site-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/uvicorn/main.py", line 362, in main
    run(**kwargs)
  File "/usr/local/lib/python3.9/site-packages/uvicorn/main.py", line 386, in run
    server.run()
  File "/usr/local/lib/python3.9/site-packages/uvicorn/server.py", line 48, in run
    loop.run_until_complete(self.serve(sockets=sockets))
  File "/usr/local/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "/usr/local/lib/python3.9/site-packages/uvicorn/server.py", line 55, in serve
    config.load()
  File "/usr/local/lib/python3.9/site-packages/uvicorn/config.py", line 308, in load
    self.loaded_app = import_from_string(self.app)
  File "/usr/local/lib/python3.9/site-packages/uvicorn/importer.py", line 20, in import_from_string
    module = importlib.import_module(module_str)
  File "/usr/local/lib/python3.9/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1030, in _gcd_import
  File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
  File "<frozen importlib._bootstrap>", line 986, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 680, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 790, in exec_module
  File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
  File "./app/main.py", line 3, in <module>
    from app.db.db import database
  File "./app/db/db.py", line 11, in <module>
    sslctx.load_cert_chain(certfile='/etc/config/server.pem', keyfile='/etc/config/pkey.key')
ssl.SSLError: [SSL] PEM lib (_ssl.c:4048)

已确认证书挂载到容器中，但程序仍无法读取，以下是错误含义和解决方法：

错误含义 #

ssl.SSLError: [SSL] PEM lib 表示Python的SSL模块在解析PEM格式的证书或私钥文件时失败，核心原因是文件不符合PEM格式规范、内容损坏、权限不足，或者私钥存在未处理的加密密码。

解决方法 #

• 验证证书/密钥文件格式：确保server.pem是标准PEM格式（文件开头为-----BEGIN CERTIFICATE-----，结尾为-----END CERTIFICATE-----），pkey.key是PEM格式的私钥（开头为-----BEGIN PRIVATE KEY-----或-----BEGIN RSA PRIVATE KEY-----）。如果是DER等其他格式，使用OpenSSL转换：

  # 转换证书为PEM格式
  openssl x509 -in /etc/config/server.der -out /etc/config/server.pem -inform DER
  # 转换私钥为PEM格式
  openssl rsa -in /etc/config/pkey.der -out /etc/config/pkey.key -inform DER
  

• 检查文件权限：容器内运行Python进程的用户需要拥有读取证书文件的权限，执行以下命令调整权限：

  chmod 644 /etc/config/server.pem /etc/config/pkey.key /etc/config/root.pem
  

  如果是Docker挂载卷，确保挂载时没有限制读取权限（如避免使用:ro同时未设置正确的用户权限）。

• 确认文件内容完整性：在容器内直接查看文件内容，确保没有截断或格式错误：

  cat /etc/config/server.pem
  cat /etc/config/pkey.key
  

  检查是否有多余的空白行、特殊字符，或证书/密钥内容不完整。

• 处理加密私钥：如果私钥设置了密码保护，调用load_cert_chain时需传入password参数：

  sslctx.load_cert_chain(
      certfile='/etc/config/server.pem',
      keyfile='/etc/config/pkey.key',
      password=b'your_private_key_password'
  )
  

  若不确定私钥是否加密，可执行openssl rsa -in /etc/config/pkey.key -check验证，若提示输入密码则说明已加密。

• 确认客户端证书是否必要：如果PostgreSQL服务器未配置为要求客户端证书，可移除sslctx.load_cert_chain这一行，仅保留CA证书验证即可：

  sslctx = ssl.create_default_context(ssl.Purpose.SERVER_AUTH, cafile='/etc/config/root.pem')
  sslctx.verify_mode = ssl.CERT_REQUIRED
  

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