Skip to main content

Migrating an Existing App to RLS

Most apps that adopt Flask RLS aren't greenfield — they already filter by tenant_id in application code and want the database to enforce what convention currently does. The good news: RLS layers on top of your existing data model. Your schema doesn't change, your queries don't change, and you can keep your application filters during the transition. Adoption is incremental, table by table, with a one-line rollback per table.

Prerequisites​

  • Every multi-tenant table has a tenant column (tenant_id or similar), non-null, indexed.
  • Your app resolves the current tenant somewhere central (session, JWT, subdomain).
  • PostgreSQL 12+.

If some tables lack a tenant column, migrate those to have one first — that backfill is a separate, ordinary migration problem.

Step 1 — Split the database roles​

RLS does not apply to a table's owner (unless FORCEd) or to superusers. Most existing apps connect as the owner, so this is the first real change: create a non-owner role for the application and keep the owner role for migrations (why this matters):

CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Deploy this connection-string change alone, before any RLS. It is independently valuable (least privilege) and de-risks the rest.

Step 2 — Wire the extension, enforce nothing yet​

rls = RLS()
with app.app_context():
    rls.init_app(app, engine=db.engine)

@app.before_request
def set_rls_context():
    g.tenant_id = current_tenant_id()   # your existing resolution logic

With no policies applied, this is inert: it sets a GUC per transaction that nothing reads. Ship it. You are now populating context in production with zero enforcement risk, and you can log or sample current_setting('rls.tenant_id', true) to confirm coverage.

Audit non-request code paths now too — Celery tasks, CLI commands, cron jobs. Each needs an explicit scope: rls.override(tenant_id=...) when acting for one tenant, a BYPASSRLS engine for true cross-tenant work.

Step 3 — Enable RLS on one low-risk table​

Pick a table that is read in few places and never touched by background jobs. Apply the policy through Alembic:

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

Keep your existing application-layer filters. RLS and manual filtering produce identical results when both are correct — the database predicate is simply redundant while you build confidence. Any divergence (a page suddenly missing rows) means a code path with missing context, and it fails closed, visibly, rather than leaking.

Step 4 — Verify, then repeat​

For each migrated table, assert three things (run as app_user, testing guide):

def test_isolation(engine, rls):        # tenant A sees only A
def test_fail_closed(engine, rls):      # no context → zero rows
def test_with_check(engine, rls):       # cannot INSERT rows for another tenant

Then move down your table list, riskiest last. Tables behind heavy background-job traffic go last because they have the most non-request code paths to audit.

Step 5 — Retire the manual filters (optional)​

Once every table is covered and production has been quiet, the .filter(tenant_id=...) calls are dead weight — but there is no urgency. Many teams keep them: they cost one line each, make queries self-documenting, and use the index anyway. Delete them opportunistically rather than in a big bang. What you should not keep is any code path that depends on manual filtering being the only guard.

Rollback plan​

Per table, rollback is one migration:

def downgrade():
    op.drop_policy("audit_events", "tenant_isolation",
                   policy=TenantPolicy("tenant_isolation", "tenant_id"))
    op.no_force_rls("audit_events")
    op.disable_rls("audit_events")

Because your application filters are still in place through the transition, disabling RLS returns you exactly to the pre-migration behavior — no data changes, no schema changes, no application deploy required.

Timeline in practice​

For a mid-size app (20–40 tenant tables), the whole path is typically two to three weeks of calendar time, most of it soak time rather than work: roles + inert context in week one, the long tail of tables in week two, background-job audits in parallel. The code diff is small; the discipline is in the verification.