sql connector through "Server refuses SSL" error when pg8000 updated to 1.31.1
amethystdu12 opened this issue · 4 comments
Question
We have a Python-based FastAPI application deployed on Google Cloud Run, which interfaces with a Cloud SQL PostgreSQL database. This week, we started experiencing unexpected failures during database operations. We found that these failures were related to an automatic upgrade of the pg8000 python library to version 1.31.1 a couple of days ago. The cloud sql connector throw "Server refuses SSL" exception when trying to connect:
Traceback (most recent call last):
File "database.py", line 56, in <module>
with pg_engine.connect() as connection:
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 3315, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 96, in __init__
else engine.raw_connection()
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 3394, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 3364, in _wrap_pool_connect
Connection._handle_dbapi_exception_noconnection(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 2198, in _handle_dbapi_exception_noconnection
util.raise_(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 3361, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 327, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 894, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 493, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
self._dec_overflow()
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 273, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 388, in __init__
self.__connect()
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 691, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 686, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/pool/base.py", line 250, in <lambda>
return lambda crec: creator()
File "database.py", line 18, in getconn
conn: pg8000.dbapi.Connection = connector.connect(
File "/usr/local/lib/python3.8/dist-packages/google/cloud/sql/connector/connector.py", line 199, in connect
return _default_connector.connect(instance_connection_string, driver, **kwargs)
File "/usr/local/lib/python3.8/dist-packages/google/cloud/sql/connector/connector.py", line 151, in connect
raise (e)
File "/usr/local/lib/python3.8/dist-packages/google/cloud/sql/connector/connector.py", line 147, in connect
return icm.connect(driver, ip_type, timeout, **kwargs)
File "/usr/local/lib/python3.8/dist-packages/google/cloud/sql/connector/instance_connection_manager.py", line 494, in connect
connection = connect_future.result(timeout)
File "/usr/lib/python3.8/concurrent/futures/_base.py", line 444, in result
return self.__get_result()
File "/usr/lib/python3.8/concurrent/futures/_base.py", line 389, in __get_result
raise self._exception
File "/usr/local/lib/python3.8/dist-packages/google/cloud/sql/connector/instance_connection_manager.py", line 542, in _connect
return await self._loop.run_in_executor(None, connect_partial)
File "/usr/lib/python3.8/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "/usr/local/lib/python3.8/dist-packages/google/cloud/sql/connector/instance_connection_manager.py", line 605, in _connect_with_pg8000
return pg8000.dbapi.connect(
File "/usr/local/lib/python3.8/dist-packages/pg8000/dbapi.py", line 211, in connect
return Connection(
File "/usr/local/lib/python3.8/dist-packages/pg8000/dbapi.py", line 651, in __init__
super().__init__(*args, **kwargs)
File "/usr/local/lib/python3.8/dist-packages/pg8000/core.py", line 320, in __init__
self.channel_binding, self._usock = _make_socket(
File "/usr/local/lib/python3.8/dist-packages/pg8000/core.py", line 243, in _make_socket
raise InterfaceError("Server refuses SSL")
sqlalchemy.exc.InterfaceError: (pg8000.exceptions.InterfaceError) Server refuses SSL
(Background on this error at: https://sqlalche.me/e/14/rvf5)``
Code
def getconn() -> pg8000.dbapi.Connection:
conf = get_secret('postgres')
conn: pg8000.dbapi.Connection = connector.connect(
conf["host"],
"pg8000",
user=conf["user"],
password=conf["password"],
db=conf["database"],
)
return conn
def connect_postgres() -> sqlalchemy.engine.base.Engine:
pg_connection = sqlmodel.create_engine(
"postgresql+pg8000://",
creator=getconn,
pool_recycle=3600,
future=False
)
pg_engine = connect_postgres()
with pg_engine.connect() as connection:
result = connection.execute(text(f"SELECT * FROM {pg_schema}.product"))
rows = result.fetchall()Additional Details
- Python version: 3.9
- Cloud SQL Python Connector version: 0.5.2
- container image: python:3.9-slim
Hi @amethystdu12 thanks for raising an issue on the Cloud SQL Python Connector 😄 Happy to try and help you out.
First things first you are using a version of the Python Connector that is over 2 years old (v0.5.2). This version was pre-GA release (v1.0.0.) and thus we definitely do not recommend using it.
I would recommend upgrading your version to the latest release (v1.8.0), it will be a lot more stable and I think you will see better performance as we have fixed several performance bugs over the years.
Our tests are currently running using pg8000 v1.31.1 #1053 and we have not seen any issues. This makes me think this error is probably isolated to the out of date version of the Python Connector you are using.
Hope this information is helpful. Feel free to let me know if you have any issues migrating to the latest version of the Cloud SQL Python Connector or check out our README for the latest usage instructions. Have a great day 😄
In addition, you'll want to set ssl_mode to disable, as the Connector handles the SSL for you.
@amethystdu12 took a look at pg8000's code and looks like they updated their SSL default logic a bit in v1.31.1:
This is probably the reason you are seeing the issue with your current version of the Cloud SQL Python Connector.
The Python Connector used to (the version you are using) rely on pg8000 to create the SSL socket but more recently we changed that to have the Python Connector create the socket itself and pass it to pg8000 #789.
This is most likely why the latest version of the Connector is unaffected while older versions are.
Going to close this as the latest version of the Python Connector is unaffected and we recommend always updating to latest.
If you want to continue to use your old version of the Python Connector than you can pin your dependency on pg8000==1.30.5