🚀 New: chi (χ) — an open-source autoresearch harness for fleets of LLM coding agents. Read the announcement.
Kuldeep Pisda
fastapi-rls: Tenant Isolation for Async Python, Enforced by Postgres

fastapi-rls: Tenant Isolation for Async Python, Enforced by Postgres

How fastapi-rls enforces PostgreSQL row-level security in FastAPI and SQLAlchemy apps — SET LOCAL per request, sync and async, leak-proof by design.

10 min read

Contents

When I built django-rls, the pitch was simple: stop trusting every developer to remember a WHERE tenant_id = ... clause, and let PostgreSQL enforce tenant isolation on every query, no exceptions. That worked well enough that I immediately wanted it outside Django — because the codebases where I’d seen the scariest tenant-filtering bugs weren’t Django monoliths. They were async FastAPI services with SQLAlchemy sessions flying across a connection pool. So I built fastapi-rls: the FastAPI/SQLAlchemy sibling of django-rls, with the same policy model, the same session-variable context, the same generated DDL — only the framework integration differs. (There’s a Flask sibling too, written up the same day, for the same reason.)

It’s on PyPI as fastapi-rls, BSD-3-Clause, docs at fastapi-rls.com, and it runs against Python 3.10–3.13, PostgreSQL 13–17, SQLAlchemy 2.0, and FastAPI ≥0.100 — every combination exercised in CI, not just claimed in the README.

The problem: your WHERE clause is a security control

In a multi-tenant application, the query that lists documents for tenant 42 and the query that leaks every tenant’s documents differ by one clause. Application-level filtering means that clause has to be present in every queryset, every raw SQL string, every new endpoint written by every future teammate, forever. That’s not a security boundary; that’s a code-review habit. One forgotten filter in an admin endpoint, one select(Document) without the scope applied, and you’ve shipped a cross-tenant data leak that no test caught because every test seeds exactly one tenant.

PostgreSQL has had the right answer since 9.5: Row Level Security. You attach a policy to the table, and the database itself refuses to return — or accept — rows that don’t satisfy the predicate, on every SELECT, INSERT, UPDATE, and DELETE, regardless of who wrote the query or how. The filter stops being something you remember and becomes something the database enforces. That’s the entire thesis of this family of libraries: move tenant isolation from discipline to construction.

Async and pooling make it worse, not better

Here’s why I wanted this specifically for FastAPI. RLS policies read their identity from PostgreSQL session variables — something like current_setting('rls.tenant_id'). So the integration problem is: how do you get this request’s tenant onto this request’s connection, and guarantee it never bleeds onto anyone else’s?

In a synchronous, one-connection-per-request world that’s merely fiddly. In an async FastAPI app it’s actively dangerous. Your connections live in a pool. A connection that served tenant 42 on the last request gets checked out for tenant 7 on the next one. Requests interleave on the event loop, sync dependencies and sync handlers run on different threadpool threads, and any identity state you keep in process memory is one await away from being observed by the wrong task. Application-level filtering was fragile before; under async plus pooling, any stateful approach to identity is fragile — including naive RLS integrations.

The conventional move here — and it’s what most session-variable RLS write-ups suggest — is middleware: run SET rls.tenant_id = ... when the request starts and RESET it when the request ends. I refused to build on that, because it fails exactly when you need it most. If the reset doesn’t run — an unhandled exception, a client disconnect mid-response, a background task holding the session, a worker killed between the two statements — the connection returns to the pool still carrying tenant 42’s identity, and the next request to check it out inherits it silently. A security library whose guarantee depends on cleanup code running is not a guarantee.

So fastapi-rls does it differently: identity is applied with SET LOCAL inside a per-request transaction. SET LOCAL is scoped to the transaction by PostgreSQL itself — when the transaction commits or rolls back, the setting is gone, unconditionally, enforced by the database rather than by a finally block. There is no cleanup step to forget because there is no cleanup step. And because I don’t fully trust anything in front of real data, there’s a second line of defense anyway: a pool-checkout scrub that clears RLS variables whenever a connection is handed out. One guarantee plus a backstop, for a library whose only job is isolation.

Policies live on the model, enforcement lives in Postgres

Declaring a policy is one attribute on the model, via RLSMixin:

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from fastapi_rls import TenantPolicy
from fastapi_rls.adapters.sqlalchemy import RLSMixin

class Base(DeclarativeBase):
    pass

class Document(Base, RLSMixin):
    __tablename__ = "documents"
    __rls_policies__ = [
        TenantPolicy("tenant_isolation", column="tenant_id"),
    ]
    id: Mapped[int] = mapped_column(primary_key=True)
    tenant_id: Mapped[int] = mapped_column(index=True)
    title: Mapped[str]

RLSMixin binds each policy to the table name and registers it. Under the hood, that TenantPolicy compiles to a PostgreSQL predicate:

("tenant_id" = (SELECT NULLIF(current_setting('rls.tenant_id', true), '')::integer))

Two details in that one line carry real weight. The current_setting read is wrapped in a scalar subquery so the planner evaluates it once per statement as an InitPlan, rather than once per candidate row — the documented RLS performance pattern, ported straight from django-rls. And the NULLIF(..., '') means an unset context compares against NULL, which matches nothing. A request that arrives with no identity sees no rows — never everyone’s rows. The failure mode is an empty list, not a breach. If you’d rather fail loudly than empty, require_context=True raises before the query ever runs.

Beyond TenantPolicy there’s UserPolicy (per-user ownership against rls.user_id) and CustomPolicy for raw predicates the first two can’t express — with the caveat that custom expressions are screened for statement terminators, comments, and DML/DDL keywords, and every table, column, role, and operation name in the library passes a strict identifier allowlist before it gets anywhere near DDL. Policy DDL is built from user-supplied strings; that’s an injection surface, and it’s treated as one.

Getting the DDL into the database — two doors, deliberately

Policies are Python objects; PostgreSQL needs ALTER TABLE ... ENABLE ROW LEVEL SECURITY and CREATE POLICY statements. The conventional move for a SQLAlchemy library is to assume Alembic and stop there. I shipped Alembic support and a standalone CLI, because plenty of FastAPI services — internal tools, prototypes that grew up, teams that manage schema elsewhere — don’t run Alembic, and “adopt a migration framework first” is a terrible prerequisite for adopting a security control.

Alembic users get operation directives inside a normal migration:

import fastapi_rls.alembic_ops  # noqa: F401 — registers op.enable_rls / op.create_policy

def upgrade():
    op.enable_rls("documents")
    op.create_policy(
        TenantPolicy("tenant_isolation", column="tenant_id", table="documents")
    )

Everyone else gets the fastapi-rls CLI, which reconciles your registered policies against what’s actually in pg_policies:

fastapi-rls plan  --url "$DATABASE_URL" --policies myapp.models   # dry run
fastapi-rls sync  --url "$DATABASE_URL" --policies myapp.models   # apply
fastapi-rls audit --url "$DATABASE_URL" --policies myapp.models   # report drift

The audit command matters more than it looks: RLS that silently drifted out of sync with your models is worse than no RLS, because you believe you’re protected. Drift detection makes the database’s actual state checkable in CI.

One default worth calling out: fastapi-rls applies FORCE ROW LEVEL SECURITY, so the table owner is constrained too. And the docs are blunt about the one thing no library can fix — PostgreSQL superusers and BYPASSRLS roles ignore RLS entirely. Your application must connect as a role that is neither, or everything above is theater.

The request path: SET LOCAL is the whole trick

Wiring it into FastAPI is a dependency. You bring the identity — fastapi-rls never authenticates, it only propagates the principal you resolved:

from fastapi import Depends, FastAPI
from sqlalchemy.orm import Session
from fastapi_rls import RLS

rls = RLS(engine=engine)

def identity(user = Depends(get_current_user)) -> dict:
    return {"tenant_id": user.tenant_id}

get_session = rls.session_dependency(identity=identity)

app = FastAPI()

@app.get("/documents")
def list_documents(session: Session = Depends(get_session)):
    return session.scalars(select(Document)).all()   # Postgres filters by tenant

The session dependency is the integration point on purpose: it owns the request’s session and its transaction, so it is the only place that can reliably bind identity to the exact connection the handler queries on. It opens the transaction, runs SET LOCAL rls.tenant_id = ..., yields the session, and lets PostgreSQL clear everything at transaction end. The async version is the same shape with async_engine and AsyncSession — both are first-class, not one wrapping the other:

rls = RLS(async_engine=async_engine)
get_session = rls.async_session_dependency(identity=identity)

@app.get("/documents")
async def list_documents(session: AsyncSession = Depends(get_session)):
    result = await session.scalars(select(Document))
    return result.all()

Here’s the per-request lifecycle end to end:

Two more properties ride along, both aimed at async footguns. Identity is immutable mid-request: once user_id or tenant_id is set in a scope, attempting to overwrite it with a different value raises RLSContextImmutableError instead of silently switching tenants — a privileged system=True context exists for the legitimate cases, like background jobs and migrations. And the in-process mirror of the active context lives in a ContextVar, written copy-on-write, so concurrent asyncio tasks and threadpool threads never observe each other’s identity even in process memory. The enforcement never depended on that mirror — it rides the connection’s SET LOCAL — but the bookkeeping shouldn’t lie either.

Tests that try to steal another tenant’s rows

The test suite’s center of gravity is not unit tests over SQL strings — it’s integration tests against real PostgreSQL where one tenant actively tries to read another tenant’s rows. Cross-tenant leak tests, cross-user leak tests, context-immutability tests, SQL-injection tests against the policy DDL surface, sync and async, across the full Python × PostgreSQL matrix in CI. For most libraries, mocking the database is a reasonable economy. For a library whose entire value is “the database will stop the leak,” a mocked database tests nothing — the only test that counts is the one where Postgres itself returns zero rows to the wrong tenant. Those integration tests also connect as a dedicated non-superuser role, because testing RLS as a superuser silently tests nothing at all.

The core owes nothing to FastAPI

The design decision I’d defend hardest: the security-critical core — policies, context, DDL generation, the registry, identifier validation — has zero framework dependencies. No SQLAlchemy import, no FastAPI import, no driver import. Database round-trips are delegated to a tiny injected RLSExecutor protocol (two methods: set_config, get_config), so the context state machine is pure Python you can audit and test without a database in the room. SQLAlchemy, FastAPI, and Alembic each enter through exactly one optional adapter module, and the extras mirror that: pip install "fastapi-rls[fastapi]", [sqlalchemy], [alembic,cli], or [all].

That’s also why this is a family and not a fork. django-rls proved the model; fastapi-rls and flask-rls reuse it: the same policy classes, the same rls.* session-variable contract, the same generated DDL. If you understand one, you’ve understood all three, and a team running Django and FastAPI services against the same Postgres cluster gets one isolation model instead of two.

What’s deliberately not in v1

The design doc keeps an explicit “deferred to a later release” list, and I’d rather ship a small library whose guarantees I can defend than a big one with soft edges. On it: SQLAlchemy-expression policies (the analog of django-rls’ Q-object ModelPolicy, so you could write predicates in ORM terms instead of SQL); hierarchical RLS via recursive CTEs for org-tree visibility; context processors for injecting extra dynamic context variables beyond user and tenant; and a native async pool-hygiene scrub — today the async path relies on SET LOCAL alone, which is sufficient on its own, but I want the same belt-and-suspenders symmetry the sync path has.

If you’re running multi-tenant FastAPI on PostgreSQL and your isolation story is “we’re careful,” the docs at fastapi-rls.com will get you from pip install to enforced policies in an afternoon. The code is at github.com/kdpisda/fastapi-rls, the project page is here, and issues — especially “I tried to leak data and here’s what happened” issues — are very welcome.