SQLAlchemy and PostgreSQL row-level security, measured

Row-level security in PostgreSQL needs to know the tenant, and the usual way to tell it is a setting on the connection: set_config('app.tenant', …). In SQLAlchemy that line can go in three places — at the start of each request, in a session event, or in the pool’s reset hook — and which one you pick decides whether a later request sees no rows, the right rows, or another customer’s rows.

We measured them on SQLAlchemy 2.1 with both PostgreSQL drivers. With the default pool, a session-level tenant leaked into every later request that did not set one: in two runs with eight threads, all but two and all but one of about 660 requests without a tenant counted another customer’s invoices. The reset hook from SQLAlchemy’s own PostgreSQL documentation did not stop it — with psycopg 3 it ran without an error and the leak stayed; with psycopg2 it failed and the pool threw every connection away, and rewritten so that it runs, it leaked there too. What held everywhere was a transaction-local setting from an after_begin event, and it cost no more than the leaking variant.

The setup

SQLAlchemy 2.1.3 with psycopg 3.3.6 and psycopg2 2.9.13, Python 3.12; PostgreSQL 17.10 in a throwaway container. Measured on 4 October 2026.

• One table invoices, 20 rows for tenant 1 and 10 for tenant 2, row-level security forced, policy using (tenant_id = nullif(current_setting('app.tenant', true), '')::int). SQLAlchemy connects as an ordinary role, not the table owner.
• A “request” is what a web framework does around a view: open a Session from a sessionmaker, set the tenant if there is one, run select count(*) from invoices, commit, close. The engine is created once, with its default QueuePool.
• Four requests in a row: tenant 1, no tenant, tenant 2, no tenant. “No tenant” stands for any path that does not set one — a public page, a health check, a view someone forgot.

What did the request without a tenant see?

Rows counted per request; the same on psycopg 3 and psycopg2 unless the row says otherwise. Bold is another customer’s data.

Why does the session-level setting leak?

Because the setting belongs to the PostgreSQL session, and the pool keeps the session. When a Session closes, SQLAlchemy returns the connection to the pool and calls rollback() on it — “reset on return”, in the pooling documentation. A rollback ends the transaction; a setting made with set_config(…, false) and committed is not part of any transaction, so it stays. In our four sequential requests the pool held one connection and every request got the same backend (one process ID for all four), and the requests without a tenant counted whatever the previous one had set.

Under concurrency it looks random rather than sequential. Eight threads sharing a pool of five connections, each request picking tenant 1, tenant 2 or none at random: 674 of 676 requests without a tenant saw rows in one run, 656 of 657 in another; a rerun gives different counts, the same picture. A connection that has served a tenant keeps one, so a request without a tenant only gets a clean connection when the pool has just opened it.

NullPool opens a new connection per request and has no leak, at a price shown below. The SET statement with a bound parameter is not an alternative: psycopg 3 binds parameters on the server, and SET does not accept one (syntax error at or near "$1"); psycopg2 interpolates the value on the client, so it works — and leaks exactly like set_config(…, false).

Why does SQLAlchemy’s own reset example not help?

The SQLAlchemy documentation, in the PostgreSQL dialect chapter, shows how to replace reset-on-return with PostgreSQL’s own commands: set pool_reset_on_return=None and register a reset event that runs CLOSE ALL, RESET ALL and DISCARD TEMP on the DBAPI connection, followed by rollback(). RESET ALL is exactly what would clear the tenant. We copied the example as printed.

With psycopg 3 it ran without an error and the leak stayed: 20, 20, 10, 10, on the same backend. A DBAPI connection that is not in autocommit — psycopg 3 and psycopg2 alike — begins a transaction on the first statement, so RESET ALL ran inside one, and the rollback() at the end of the hook undid it. The documentation says the hook “will end transactions in progress”; with these drivers it starts one. PostgreSQL documents that the effects of a SET in a transaction that is rolled back disappear, and RESET is “an alternative spelling” of SET … TO DEFAULT. We checked it on a bare psycopg 3 connection: after CLOSE ALL the connection was in a transaction, and after RESET ALL and rollback() the tenant was back.

With psycopg2 the example raised AttributeError: 'psycopg2.extensions.connection' object has no attribute 'execute' on every return — the example is written for postgresql+psycopg2, but execute() on the connection exists only in psycopg 3. SQLAlchemy logged “Exception during reset or similar” and discarded the connection. The table shows no leak, but each request ran on a new backend (process IDs 734, 735, 736, 737), as with NullPool, plus an error in the log per request. Change the example to use a cursor so that it runs, and psycopg2 leaks exactly like psycopg 3: 20, 20, 10, 10.

The order fixes it — roll back first, then reset, then commit the reset:

from sqlalchemy import create_engine, event

engine = create_engine("postgresql+psycopg://…", pool_reset_on_return=None)

@event.listens_for(engine, "reset")
def reset_connection(dbapi_connection, connection_record, reset_state):
    dbapi_connection.rollback()
    if not reset_state.terminate_only:
        cursor = dbapi_connection.cursor()
        cursor.execute("RESET app.tenant")
        cursor.close()
        dbapi_connection.commit()

With psycopg 3 and with psycopg2 this gave 20, 0, 10, 0 on one reused backend. It also held when a request failed after setting the tenant, when a session was closed with its transaction still open, and when a connection came back in an aborted transaction: the next request counted 0 each time.

We reset only app.tenant on purpose. RESET ALL also clears the tenant, but it clears everything else that was set on the session too: a statement_timeout of 5 s set in a connect event was 5 s on the first request and 0 on every request after it. With RESET app.tenant it stayed 5 s.

The hook is a safety net, not the fix: it cleans up after the request, so a request that forgets to set the tenant is protected, but a request that sets it in the wrong place is not.

What works?

A transaction-local setting, made at the start of every transaction. In SQLAlchemy 2 a Session begins a transaction by itself on the first statement (“autobegin”) and fires after_begin when it does:

from sqlalchemy import event
from sqlalchemy.orm import sessionmaker

SessionLocal = sessionmaker(engine)

@event.listens_for(SessionLocal, "after_begin")
def set_tenant(session, transaction, connection):
    tenant = session.info.get("tenant")
    if tenant is not None:
        connection.exec_driver_sql(
            "select set_config('app.tenant', %s, true)", (str(tenant),)
        )

# per request
with SessionLocal() as session:
    session.info["tenant"] = tenant_id      # from the token, host name, …
    ...

The setting ends with the transaction, so a connection goes back to the pool without a tenant: 20, 0, 10, 0, and not one leak in about 660 requests without a tenant in each threaded run. It also survives what breaks the simpler variant. With set_config(…, true) once at the start of the request, the first query counted 20 and the same query after session.commit() counted 0 — the commit ended the transaction, the next statement began a new one without a tenant. With after_begin the counts before a commit, after it and after a rollback were 20, 20 and 20. The event also fires for a savepoint (begin_nested()), and the count stayed 20 inside one and after rolling one back.

Two ways to still get it wrong, both measured:

• Set session.info before the first statement. A session that first ran a query — loading the user to find out the tenant, say — had already begun its transaction. The tenant set after that counted 0 until the next commit, then 20. Resolve the tenant on a separate session or connection, or set info before you touch this one.
• Use a new Session per request. With scoped_session, four requests that committed but never called remove() shared one Session, and its info with it: 20, 20, 10, 10. With remove() at the end of the request: 20, 0, 10, 0. close() is not enough — it keeps info, and a session reused after close() counted 20 twice. If a framework integration manages the scoped_session, check that it calls remove() at the end of every request.

Code that works with a Connection instead of a Session — with engine.begin() as conn: — gets no after_begin; set the tenant there with set_config(…, true) as the first statement of the block. Jobs that need to work across tenants need their own route around the policy; the ways that go wrong are in row-level security in PostgreSQL: where tenants leak.

What does it cost?

Average over 300 requests alternating between tenant 1 and tenant 2, two runs each, psycopg 3:

after_begin costs what the leaking variant costs: one set_config per transaction, which in this benchmark is one per request. A request that commits three times, or opens savepoints, pays one per transaction and savepoint. The reset hook adds a RESET and a commit on every return. NullPool is safe because every request gets a new backend, and that cost about 8 ms per request here, on one machine with the database next door; over a network it is more.

What we did not measure

The asyncio extension and asyncpg, Flask-SQLAlchemy and FastAPI dependency wiring, multiple engines, and SQLAlchemy behind PgBouncer in transaction mode — the rules for a pooler in front of the database are measured in PgBouncer and row-level security. The same question for Django, where the middleware runs outside the view’s transaction, is in Django and PostgreSQL row-level security, and for Prisma, where the tenant and the query can end up on different connections, in Prisma and PostgreSQL row-level security. When a policy does not return what you expect, the checks are in debugging row-level security in PostgreSQL. The timings come from one machine and are for comparison.

Sources: the SQLAlchemy 2.1 documentation on connection pooling (reset on return, the reset event), the PostgreSQL dialect (the reset example, also in the 2.0 documentation), ORM events (after_begin) and session basics (autobegin), and the PostgreSQL 17 documentation on SET and RESET, all read on 4 October 2026. All measurements are our own, on SQLAlchemy 2.1.3 and PostgreSQL 17.10, on 4 October 2026.