迁移至Contabo服务器后调用Google Sheets遇SSL证书验证失败求助

DevOps 小满

2026-6-27

SSL证书验证失败问题（AWS迁移至Contabo服务器后）

我的代码在AWS上运行完全正常，但迁移至Contabo服务器后出现SSL证书验证失败的错误。已搜索StackOverflow但未得到有效解答，旧解决方案也不适用。我用Streamlit开发，需要调用Google Sheets表格，复现代码如下：

import streamlit as st
import gspread

gc = gspread.service_account(filename="google.json")
planilha = gc.open_by_url(
        "my_url"
    )

出现的错误如下：

TransportError: HTTPSConnectionPool(host='oauth2.googleapis.com', port=443): Max retries exceeded with url: /token (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate (_ssl.c:1000)')))
Traceback:
File "/usr/local/lib/python3.12/dist-packages/streamlit/runtime/scriptrunner/script_runner.py", line 542, in _run_script
    exec(code, module.__dict__)
File "/root/mcenter_streamlit/app/1_📤_BUENOSHOPS_-_FULFILLMENT.py", line 2106, in <module>
    planilha = gc.open_by_url(
               ^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/gspread/client.py", line 227, in open_by_url
    return self.open_by_key(extract_id_from_url(url))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/gspread/client.py", line 206, in open_by_key
    spreadsheet = Spreadsheet({"id": key})
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/gspread/spreadsheet.py", line 37, in __init__
    metadata = self.fetch_sheet_metadata()
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/gspread/spreadsheet.py", line 245, in fetch_sheet_metadata
    r = self.client.request("get", url, params=params)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/gspread/client.py", line 80, in request
    response = getattr(self.session, method)(
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/requests/sessions.py", line 602, in get
    return self.request("GET", url, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/google/auth/transport/requests.py", line 537, in request
    self.credentials.before_request(auth_request, method, url, request_headers)
File "/usr/local/lib/python3.12/dist-packages/google/auth/credentials.py", line 230, in before_request
    self._blocking_refresh(request)
File "/usr/local/lib/python3.12/dist-packages/google/auth/credentials.py", line 193, in _blocking_refresh
    self.refresh(request)
File "/usr/local/lib/python3.12/dist-packages/google/oauth2/service_account.py", line 445, in refresh
    access_token, expiry, _ = _client.jwt_grant(
                              ^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/google/oauth2/_client.py", line 308, in jwt_grant
    response_data = _token_endpoint_request(
                    ^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/google/oauth2/_client.py", line 268, in _token_endpoint_request
    response_status_ok, response_data, retryable_error = _token_endpoint_request_no_throw(
                                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/google/oauth2/_client.py", line 215, in _token_endpoint_request_no_throw
    request_succeeded, response_data, retryable_error = _perform_request()
                                                        ^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/google/oauth2/_client.py", line 191, in _perform_request
    response = request(
               ^^^^^^^^
File "/usr/local/lib/python3.12/dist-packages/google/auth/transport/requests.py", line 192, in __call__
    raise new_exc from caught_exc

环境信息：

Ubuntu 20.04.6 LTS
Python 3.12.2
gspread 5.12.4

解决方案建议

1. 更新系统根证书

Contabo服务器可能根证书过旧，无法验证Google的SSL证书，执行以下命令更新：

sudo apt update && sudo apt install --reinstall ca-certificates
sudo update-ca-certificates

2. 强制Python使用系统证书

Python 3.12可能未正确加载系统根证书，可通过环境变量指定证书路径：

export SSL_CERT_FILE=/etc/ssl/certs/ca-certificates.crt

或在代码开头添加：

import os
os.environ['SSL_CERT_FILE'] = '/etc/ssl/certs/ca-certificates.crt'

3. 排查代理/防火墙设置

Contabo服务器可能存在透明代理或防火墙拦截HTTPS请求，导致证书被替换为自签名证书。可临时关闭防火墙测试：

sudo ufw disable

测试完成后记得重新开启防火墙，或联系Contabo客服确认服务器是否有默认代理配置。

4. 切换Python版本（临时兼容方案）

Python 3.12在Ubuntu 20.04上可能存在兼容性问题，尝试切换到Python 3.9或3.10：

sudo apt install python3.9 python3.9-venv
python3.9 -m venv myenv
source myenv/bin/activate
pip install streamlit gspread

5. 临时禁用证书验证（仅测试用，生产环境禁止）

修改gspread会话的证书验证参数：

import streamlit as st
import gspread

gc = gspread.service_account(filename="google.json")
gc.session.verify = False

planilha = gc.open_by_url("my_url")

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