Isolate tenants in a multi-tenant FastAPI app

Approach

Resolve the tenant from the authenticated user (never from an untrusted header alone), expose it through a dependency, and scope every query by tenant_id via repository helpers. Add PostgreSQL Row-Level Security as defense in depth, setting the tenant per transaction with SET LOCAL.

Solution

from sqlalchemy import text


async def get_tenant_db(user: CurrentUser) -> AsyncIterator[AsyncSession]:
    async with SessionLocal() as session:
        async with session.begin():
            # RLS policies read this setting: USING (tenant_id = current_setting('app.tenant_id')::bigint)
            await session.execute(text("SELECT set_config('app.tenant_id', :tid, true)"), {"tid": str(user.tenant_id)})
            yield session


TenantDB = Annotated[AsyncSession, Depends(get_tenant_db)]


@router.get("/projects")
async def list_projects(db: TenantDB, user: CurrentUser) -> list[ProjectOut]:
    stmt = select(Project).where(Project.tenant_id == user.tenant_id).order_by(Project.name)   # app-level scoping too
    return list(await db.scalars(stmt))

# Migration:
# ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
# CREATE POLICY tenant_isolation ON projects USING (tenant_id = current_setting('app.tenant_id')::bigint);
# (connect as a role without BYPASSRLS; table owners bypass RLS unless FORCE ROW LEVEL SECURITY is set)

Pitfalls

• Trusting an X-Tenant-Id header without checking membership.
• Background jobs and admin scripts forgetting tenant scoping.
• Connection poolers in transaction mode with session-level SET (use SET LOCAL/set_config(..., true)).

Variations

• Schema-per-tenant or database-per-tenant for stronger isolation at higher operational cost.

Further reading: PostgreSQL: Row security policies