flask-rls: Postgres Row-Level Security That Survives the Connection Pool

After I built django-rls, the obvious question was whether the idea belonged to Django at all. It doesn’t. “Let PostgreSQL enforce tenant isolation instead of trusting every developer to remember a .filter()” is a database argument, not a framework argument. So I ported it: flask-rls is the Flask/SQLAlchemy sibling — same concepts, same policy names, same fail-closed philosophy — rebuilt on SQLAlchemy Core and the Flask request lifecycle. It’s on PyPI as flask-rls (BSD-3-Clause), with docs at flask-rls.com, and it runs on Python 3.10+, SQLAlchemy 2.0+, Flask 2.2+, and PostgreSQL 12+.

The port was not a find-and-replace. Flask/SQLAlchemy has a sharper version of the exact failure RLS is supposed to prevent, and it lives in the connection pool. Most of the design work went into making sure the library couldn’t be betrayed by it.

The problem RLS actually solves#

Every multi-tenant app has the same invisible contract: every query that touches tenant data must be scoped to the current tenant. In application-layer terms, that means every queryset, every raw SQL string, every admin script, every future teammate’s late-night hotfix must remember the WHERE tenant_id = ?. One omission and tenant A reads tenant B’s invoices. You will not catch that in code review forever.

PostgreSQL’s row-level security moves the contract into the database. Enable RLS on a table, attach a policy, and Postgres itself filters every statement against that table — SELECT, INSERT, UPDATE, DELETE, raw SQL included. The application’s job shrinks to one thing: telling Postgres who is asking, once per transaction, through a session variable (a GUC) that policies read via current_setting(). A forgotten filter stops being a data breach and becomes a query that returns the same correctly-scoped rows as every other query.

That’s the whole pitch. The library exists because the plumbing — setting that context safely, authoring CREATE POLICY DDL without hand-writing SQL strings, and versioning it all through migrations — is exactly the kind of code everyone writes badly once and then copies between projects.

The Flask-specific danger: the pool remembers#

Here’s why the SQLAlchemy port needed real thought. SQLAlchemy pools connections. A pooled connection is a long-lived object that outlives any single request — request 1 for tenant A and request 2 for tenant B can be served, seconds apart, by the same underlying Postgres connection. Anything you SET on that connection at the session level persists across the checkout boundary. So the naive implementation — set rls.tenant_id when the request starts, reset it when the request ends — has a failure mode where the “reset” step is skipped (an exception in teardown, a code path that never registered the teardown, a background task using the engine directly) and the next request silently inherits the previous tenant’s identity. That’s not a hypothetical; it’s the default behavior of session-scoped GUCs plus pooling.

The conventional move is exactly that: set session-scoped context per request and scrub it on teardown. It’s what django-rls itself defaults to — set_config(..., is_local=False) with middleware teardown resetting the connection afterwards. I did the opposite in flask-rls, and it’s the one place I deliberately diverged from the sibling: context is set per transaction with set_config(name, value, true) — the is_local=true flag makes it behave like SET LOCAL, so Postgres itself discards the value at COMMIT or ROLLBACK. There is no scrub step because there is nothing to scrub. A pooled connection returns to the pool with no tenant identity on it, structurally, no matter how the request ended.

Two mechanical details make this work:

Why the function form and not literal SET LOCAL? Because SET LOCAL rls.tenant_id = %s is a syntax error — SET is DDL-like and cannot take a bind parameter. set_config() is an ordinary function call, so both the GUC name and the value travel as bound parameters. No string interpolation anywhere near an identity value.

Why does it fire at the right time? The extension listens on the SQLAlchemy engine’s begin event. SQLAlchemy 2.0 autobegins a transaction for every statement, so the event fires for explicit and implicit transactions alike, and the set_config call always lands inside the transaction it applies to. Because the hook is at the engine level, it’s ORM-agnostic: Flask-SQLAlchemy sessions and bare Core connections share the same engine and get the same behavior. The one documented constraint: is_local=true does nothing outside a transaction block, so driver-level autocommit code won’t carry context — normal SQLAlchemy usage is always transactional, but it’s stated rather than hidden.

Wiring it in#

The extension follows the standard Flask factory pattern. Your app resolves identity however it already does — session, JWT, subdomain — and drops it on g; flask-rls reads it at each transaction begin:

from flask import Flask, g
from flask_sqlalchemy import SQLAlchemy
from flask_rls import RLS

db = SQLAlchemy()
rls = RLS()

def create_app():
    app = Flask(__name__)
    app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql+psycopg://app@localhost/mydb"
    db.init_app(app)
    with app.app_context():
        rls.init_app(app, engine=db.engine)

    @app.before_request
    def set_tenant():
        g.tenant_id = current_tenant_id()  # however your app resolves it
        g.user_id = current_user_id()

    return app

Note what’s not here: no middleware ordering puzzle, no teardown handler, no per-view decorator. Identity in g, one init_app, done. The g keys are configurable (RLS_G_TENANT_KEY, RLS_G_USER_KEY), and you can register additional context providers for custom keys.

And the default when nothing is set matters most. Policies read the GUC with the missing-ok form — current_setting('rls.tenant_id', true) — which returns NULL when unset, and tenant_id = NULL matches zero rows. No context means no rows, by construction. Fail closed. For development, RLS_REQUIRE_CONTEXT = True upgrades the silent empty result into a loud RLSContextRequiredError, which turns “why is this page blank” into a stack trace pointing at the missing before_request.

Policies are Python that compiles to DDL#

Policies mirror django-rls: TenantPolicy, UserPolicy, CustomPolicy, and the Pythonic ExpressionPolicy (the analog of django-rls’s ModelPolicy). The interesting one is ExpressionPolicy, which takes a real SQLAlchemy Core expression:

from flask_rls import TenantPolicy, ExpressionPolicy, RLS
from sqlalchemy import column, true

rls.register("invoices", TenantPolicy("tenant_isolation", "tenant_id"))

# Pythonic policy: owner OR public
rls.register(
    "projects",
    ExpressionPolicy(
        "project_access",
        expr=(column("owner_id") == RLS.user_id()) | (column("is_public") == true()),
    ),
)

RLS.user_id() and RLS.tenant_id() return SQLAlchemy elements that read the corresponding GUC, so you compose access rules with |, &, and == the way you’d write any Core predicate. The expression is compiled to SQL by SQLAlchemy’s own PostgreSQL dialect — identifiers quoted, literals rendered by the compiler, nothing assembled by hand. CustomPolicy still exists as a raw-SQL escape hatch, but it’s validated against statement terminators, comments, and DDL/DML keywords, and the docs steer you to ExpressionPolicy precisely because the compiler is a better escaping engine than a regex.

There’s a performance decision buried in the generated SQL worth knowing about: 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 row — the documented Postgres pattern for keeping RLS predicates off your EXPLAIN ANALYZE naughty list.

One gotcha the library refuses to let you discover in production: a table’s owner (and superusers) bypass RLS entirely unless the table has FORCE ROW LEVEL SECURITY. Correct policies do exactly nothing if your app connects as the table owner. flask-rls emits FORCE for every registered table, and the docs say plainly that your app’s role should be a non-owner regardless. The integration tests run against real PostgreSQL with two roles — a non-owner app role and the table owner — because RLS is unenforceable on SQLite and a mocked database proves nothing about isolation.

Migrations are first-class, and honest about reversibility#

Policy DDL is schema, and schema belongs in migrations. The flask-rls[alembic] extra registers custom Alembic operations:

# migrations/env.py
from flask_rls.alembic import *  # noqa: F401,F403  registers op.enable_rls, ...

# a migration
from flask_rls import TenantPolicy

def upgrade():
    op.enable_rls("invoices")
    op.force_rls("invoices")
    op.create_policy("invoices", TenantPolicy("tenant_isolation", "tenant_id"))

def downgrade():
    op.drop_policy("invoices", "tenant_isolation")
    op.disable_rls("invoices")

Each operation reverses where reversal is honest: enable_rls ↔ disable_rls, create_policy ↔ drop_policy. Where it isn’t, the library says so instead of guessing — drop_policy is only auto-reversible if you hand it the original policy object, and alter_policy (implemented as drop-then-recreate, the portable path across Postgres versions) refuses to auto-reverse without the prior definition. A migration framework that silently fabricates a downgrade is worse than one that makes you write it.

If you don’t use Alembic, flask rls sql prints the full DDL — ENABLE, FORCE, CREATE POLICY — for every registered table, ready to review or pipe into whatever migration tool you do use. The policy classes are pure SQL generators underneath; Alembic is packaging, not a requirement.

Background jobs, and a bypass() that is deliberately not god-mode#

Outside a request there’s no g, so a Celery task or CLI script resolves no context and — fail closed — sees zero rows. That’s correct, and it forces you to say who a job runs as:

with rls.override(tenant_id=42):   # privileged identity switch (jobs, CLI, tests)
    generate_monthly_report()

with rls.bypass():                 # emit no context — for non-RLS tables
    create_new_tenant()

Both scopes are built on contextvars, so they behave under threads, gevent, and asyncio without leaking between concurrent requests.

The naming here is deliberate to the point of being opinionated. bypass() does not grant cross-tenant reads — on an RLS table, no context still means zero rows. It exists for non-RLS tables (the tenants registry itself, global settings) and for asserting isolation in tests. True cross-tenant access — an admin that reads every tenant’s rows — requires a PostgreSQL role with the BYPASSRLS attribute, and v1 intentionally does not wire that up; the documented pattern is a second engine bound to a BYPASSRLS role. I’d rather ship a bypass() that people discover is weaker than they assumed than one that’s stronger. Multi-tenant escape hatches should be annoying to open.

What’s next#

The design doc keeps an explicit list of what v1 deliberately left out, and that list is the roadmap rather than anything grander:

• Alembic autogenerate integration — detecting policy drift between the registry and the live database, the way autogenerate already detects column drift.
• Hierarchical policies via recursive CTEs for nested organizations — django-rls has this; the sibling should reach parity.
• A declarative mixin (__rls_policies__ on the model) as sugar over the registry, for people who want policies declared next to the table they protect.
• Tenant-membership validation in the request path, enforcing that the resolved user actually belongs to the resolved tenant before any query runs.
• Actually building the BYPASSRLS second-engine pattern that v1 only documents.

No promised dates, because it’s an open-source side project and I’d rather the README never lie.

The RLS family#

flask-rls is one of three siblings sharing the same concepts and the same conviction that tenant isolation belongs in the database: django-rls is where the idea started, and fastapi-rls adapts it to FastAPI’s async lifecycle. Learn one, and the other two are a naming exercise.

If you’re running multi-tenant Flask on Postgres and your isolation strategy is “we’re careful,” the code is at github.com/kdpisda/flask-rls, the docs are at flask-rls.com, and the shorter project page lives here. pip install flask-rls, register a TenantPolicy, run the migration, and let the database be the one thing in your stack that never forgets a WHERE clause.