Setting Tenant Context in asyncpg Connections

← Back to Row-Level Security for Multi-Tenant PostGIS

Set app.tenant_id per request so PostGIS row-level security has a tenant to filter on — using SET LOCAL inside the transaction so the value can never leak onto the next client that borrows the same pooled connection.

# Context & when to use

This is the operational half of row-level security for multi-tenant PostGIS: the policies are inert until each request establishes its tenant on the connection running the query. The subtlety is connection pooling. Under PgBouncer in transaction mode, a single PostgreSQL backend is handed to different clients transaction by transaction, so any state you set outside a transaction — a session-level SET app.tenant_id — persists on that backend and is inherited by the next, unrelated request. In a multi-tenant system that is a direct cross-tenant data breach: tenant B’s request runs against a backend still carrying tenant A’s context.

Use SET LOCAL (or its function form set_config(name, value, true)) which binds the value to the current transaction only and is discarded automatically on COMMIT/ROLLBACK. The pooling-mode caveat is exactly the one described in connection pooling & PgBouncer setup — transaction pooling does not preserve session state, which is what makes SET LOCAL both necessary and safe. Apply this whenever RLS is enabled and you sit behind PgBouncer transaction pooling, pgcat, RDS Proxy, or Supavisor in transaction mode.

Preconditions: RLS policies already reference app.tenant_id; the app connects as a non-owner, non-BYPASSRLS role; asyncpg runs with statement_cache_size=0 behind the pooler; and every request has a validated tenant_id (typically a JWT claim resolved by a FastAPI dependency).

# SET vs SET LOCAL under transaction pooling

The diagram contrasts the two: session SET bleeds tenant A’s context onto tenant B’s borrowed backend, while SET LOCAL is scoped to the transaction and released before the backend is reused.

# Runnable implementation

The robust pattern binds the tenant per transaction with set_config(..., true) — the function form of SET LOCAL that accepts a bind parameter, so the tenant UUID never touches an interpolated SQL string. Here it is wired as a FastAPI dependency over a SQLAlchemy AsyncSession.

# app/dependencies.py
from typing import AsyncGenerator
from fastapi import Depends, Request, HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text
from .database import AsyncSessionLocal
from .auth import decode_jwt   # your verified-claims helper


def get_tenant_id(request: Request) -> str:
    """Resolve and validate the tenant from the request's JWT."""
    claims = decode_jwt(request.headers.get("authorization", ""))
    tid = claims.get("tenant_id")
    if not tid:
        raise HTTPException(status_code=403, detail="No tenant in token.")
    return tid  # a UUID string; cast to ::uuid happens in the policy


async def get_db(
    tenant_id: str = Depends(get_tenant_id),
) -> AsyncGenerator[AsyncSession, None]:
    """
    Yield a session whose transaction is pinned to the caller's tenant.
    set_config(..., is_local => true) == SET LOCAL: transaction-scoped, so
    the value cannot survive onto the next client of a pooled backend.
    """
    async with AsyncSessionLocal() as session:
        # begin() opens ONE transaction; the whole request runs inside it.
        async with session.begin():
            # Parameterised — the tenant is a bind value, never string-formatted.
            await session.execute(
                text("SELECT set_config('app.tenant_id', :tid, true)"),
                {"tid": tenant_id},
            )
            yield session
        # Exiting session.begin() commits (or rolls back) → SET LOCAL cleared.

If you prefer to guarantee the context is applied on every checkout regardless of route code, hook it at the transaction-begin event so it is impossible to forget:

# app/database.py  (event-driven variant)
from sqlalchemy import event, text
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from contextvars import ContextVar

current_tenant: ContextVar[str | None] = ContextVar("current_tenant", default=None)

engine = create_async_engine(
    "postgresql+asyncpg://api_app:***@pgbouncer:6432/gis_db",
    pool_size=5, max_overflow=0,
    connect_args={"statement_cache_size": 0},  # required under txn pooling
)
AsyncSessionLocal = async_sessionmaker(engine, class_=AsyncSession,
                                       expire_on_commit=False)

@event.listens_for(engine.sync_engine, "begin")
def _pin_tenant(conn):
    tid = current_tenant.get()
    if tid is None:
        # Fail closed: no tenant context => refuse to run the transaction.
        raise RuntimeError("No tenant bound for this transaction.")
    # Executed at BEGIN, inside the transaction → behaves as SET LOCAL.
    conn.exec_driver_sql(
        "SELECT set_config('app.tenant_id', %(tid)s, true)", {"tid": tid}
    )

With the ContextVar set from a middleware that decodes the JWT, every transaction the engine opens is tenant-pinned before any query runs, and the value is released at commit.

# Key parameters & options

# Gotchas & failure modes

• Session SET leaks across tenants. A bare SET app.tenant_id = ... (or set_config(..., false)) runs outside transaction scope and stays on the pooled backend. The next client inherits it and reads foreign rows. Symptom: intermittent, load-dependent cross-tenant results that vanish when you switch PgBouncer to session mode. Fix: always SET LOCAL.

• SET LOCAL outside a transaction is a silent no-op. Issued in autocommit mode, SET LOCAL emits WARNING: SET LOCAL can only be used in transaction blocks and the value is discarded immediately — so RLS then sees an unset context and your query errors or returns nothing. Ensure session.begin() (or an explicit BEGIN) wraps it.

• SQL injection via interpolated tenant id. Building f"SET LOCAL app.tenant_id = '{tid}'" lets a crafted claim like x'; SET app.tenant_id='victim alter context. SET LOCAL cannot take a bind parameter, but set_config('app.tenant_id', $1, true) can — always use the function form with a parameter.

• unrecognized configuration parameter "app.tenant_id" (SQLSTATE 42704). The query ran on a connection where the context was never set and the policy used strict current_setting('app.tenant_id'). Either guarantee the context via the begin-event hook above, or use current_setting('app.tenant_id', true) in the policy for NULL-and-no-rows behaviour. Decide which is your fail-closed default.

• asyncpg prepared statement "__asyncpg_..." does not exist behind PgBouncer. Unrelated to tenancy but co-occurs: transaction pooling breaks asyncpg’s statement cache. Set statement_cache_size=0 in connect_args, as detailed in connection pooling & PgBouncer setup.

# Verification

Prove the context is transaction-local and does not survive a hand-off. Connect through PgBouncer (port 6432) as the app role and run two transactions on what the pool may reuse as one backend:

-- Transaction 1 sets LOCAL context, reads it, commits.
BEGIN;
SELECT set_config('app.tenant_id','11111111-1111-1111-1111-111111111111',true);
SELECT current_setting('app.tenant_id', true);  -- 1111...
COMMIT;

-- Transaction 2: the LOCAL value must be gone (NULL/empty), NOT 1111...
BEGIN;
SELECT current_setting('app.tenant_id', true);  -- '' or NULL  ✓ no leak
COMMIT;

If transaction 2 still reports 1111..., you used session SET somewhere — hunt it down before shipping. Then confirm the end-to-end path through the API returns only the caller’s rows:

# Two tenants, same coordinates — each must see only its own features
curl -s localhost:8000/features/nearby?lon=0.1\&lat=0.1 \
  -H "Authorization: Bearer $TENANT_A_JWT" | jq '.features | length'
curl -s localhost:8000/features/nearby?lon=0.1\&lat=0.1 \
  -H "Authorization: Bearer $TENANT_B_JWT" | jq '.features | length'
# The two result sets must share no feature ids.

A final assertion in PostgreSQL log review: enable log_statement = 'all' briefly and grep for any SET app.tenant_id (without LOCAL) — there should be zero matches from the application role.

# Related

• Row-Level Security for Multi-Tenant PostGIS — the policies this context activates, with the RLS-vs-alternatives decision matrix
• Enforcing Tenant Geometry Isolation with PostGIS RLS — the concrete policy set that reads app.tenant_id
• Connection Pooling & PgBouncer Setup — why transaction pooling mandates SET LOCAL and statement_cache_size=0

← Back to Row-Level Security for Multi-Tenant PostGIS