Question
SQLAlchemy QueuePool and expiring database credentials: authentication check at checkout
Rahul Vale
0 reputation · 26 Nov 2023, 08:20 UTC
107.3K views1
Loading…
0 reputation · 26 Nov 2023, 08:20 UTC
Design goal is to use SQLAlchemy Engine with least-privilege, short-lived database credentials while reusing connections via QueuePool.
Credentials are supplied to the Engine URL and passed through to the DBAPI driver. When a credential expires, connections already checked into the pool may become unusable for subsequent checkouts. pool_pre_ping is documented for connection health testing before checkout.
The boundary between SQLAlchemy pooling logic and driver-level authentication is unclear for server-side invalidation that occurs during an idle period or inside a long-running transaction.
Does pool_pre_ping reliably surface authentication expiry before a connection is handed to application code, and is a new DBAPI connection created with refreshed credentials on failure? Under what conditions does the pool retain a connection whose authentication state has been revoked by the database server?
34775 reputation · 26 Nov 2023, 15:19 UTC
No. pool_pre_ping=True is not a reliable authentication check for expiring or revoked credentials. It runs a probe query immediately before SQLAlchemy returns a pooled DBAPI connection to your application. If the probe fails and the dialect classifies the error as a disconnect, SQLAlchemy discards that connection and attempts one replacement connect using the Engine's currently configured credentials. It does not fetch or inject refreshed short-lived credentials. If the URL, connect args, or creator still contain the expired password or token, the replacement connect fails. A revoked credential can therefore remain inside a pooled connection until checkout, failed probe, invalidation, and replacement with refreshed credentials.
Your short-lived credential expires while a connection sits idle in QueuePool. The database server may invalidate the server-side session or privilege, but the TCP socket and the DBAPI connection object can remain open. SQLAlchemy does not track server-side authentication state inside the pool, so pool_pre_ping only knows whether its probe query succeeds. If the probe succeeds—common when revocation affects new connections or the next privileged statement rather than the idle session—the stale connection is handed to your application, and the first real query fails.
sqlalchemypool_pre_ping runs at checkout only, not at checkin and not during a long-running transaction.creator.pool_pre_ping cannot protect a transaction mid-flight.pool_recycle, pool_timeout, and reset_on_return manage idle age and transaction state, but none reauthenticate or discard a connection solely because server-side credentials expired.Assumptions: SQLAlchemy 1.4 or 2.x, default reset_on_return=True, no custom creator, and no driver-level connection pooling. Behavior varies by dialect, driver, and SQLAlchemy version; verify disconnect classification for your stack. This draft requires verification against your specific driver before production use.
create_engine if it rotates faster than the engine lifetime.creator factory or dynamic connect args that read the current secret from your credential source.pool_pre_ping=True as a liveness safety net only; treat it as unrelated to credential rotation.pool_recycle below your credential lifetime to replace idle connections before expiry, understanding that recycling is age-based, not auth-based.sqlalchemy.pool logger to DEBUG and attach listeners for checkout, connect, checkin, and connection_invalidated to observe whether a new DBAPI connect occurs after revocation.connection_invalidated and that the replacement connect succeeds only after refreshed credentials are supplied.Missing diagnostic detail: which database, DBAPI driver, and SQLAlchemy version are you using? Disconnect classification and when authentication is rechecked differ across psycopg2, psycopg, mysqlclient, PyMySQL, python-oracledb, and pyodbc, so the exact failure behavior depends on that stack.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 26 Nov 2023, 15:12 UTC
pool_pre_ping only runs a lightweight query when a connection is checked out; it does not proactively validate idle connections or fetch new credentials from a secrets manager. If the database revokes a password while a connection sits checked in, the pool returns that same DBAPI connection on the next checkout unless the ping query itself fails. A fresh connection is then created using the credentials still embedded in the Engine URL, so short-lived credentials must be rotated into the URL or the engine recreated before the next checkout. Revocation during an already checked-out, long-running transaction is not visible to pool_pre_ping; the error surfaces on the next execute or commit. Verify your SQLAlchemy version's pool_pre_ping default and log DBAPI exceptions at checkout.