Cheatsheet for Postgres RLS-based multi-tenancy. Long-form: Postgres textbook Ch 8 .

Enable RLS on a table

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

Tenant isolation policy

CREATE POLICY tenant_iso ON users
USING (tenant_id = current_setting('app.tenant_id')::bigint)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

USING for SELECT/UPDATE/DELETE. WITH CHECK for INSERT/UPDATE inserts.

Alembic migration

def upgrade():
    op.execute("ALTER TABLE users ENABLE ROW LEVEL SECURITY")
    op.execute("""
        CREATE POLICY tenant_iso ON users
        USING (tenant_id = current_setting('app.tenant_id')::bigint)
        WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint)
    """)

Application role (NO BYPASSRLS)

CREATE ROLE app_user WITH LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- DO NOT grant BYPASSRLS

App connects as app_user. RLS applies.

Admin role (BYPASSRLS) — restricted use

CREATE ROLE app_admin WITH LOGIN PASSWORD '...' BYPASSRLS;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_admin;

Use only for admin paths; audit every use.

Set tenant per request (FastAPI)

import contextvars
current_tenant: contextvars.ContextVar[int | None] = contextvars.ContextVar("tenant", default=None)

@event.listens_for(engine.sync_engine, "before_cursor_execute")
def set_tenant(conn, cur, stmt, params, ctx, executemany):
    tid = current_tenant.get()
    if tid is not None:
        cur.execute(f"SET LOCAL app.tenant_id = '{tid}'")

@app.middleware("http")
async def tenant_mw(request, call_next):
    tid = await resolve_tenant(request)
    token = current_tenant.set(tid)
    try:
        return await call_next(request)
    finally:
        current_tenant.reset(token)

Set tenant via dep

async def get_db(request: Request) -> AsyncSession:
    async with AsyncSessionLocal() as session:
        if tid := getattr(request.state, "tenant_id", None):
            await session.execute(text("SET LOCAL app.tenant_id = :t"), {"t": tid})
        yield session

Test isolation

async def test_rls(db_session):
    await db_session.execute(text("SET LOCAL app.tenant_id = '1'"))
    db_session.add(User(tenant_id=1, email="[email protected]"))
    await db_session.commit()
    
    await db_session.execute(text("SET LOCAL app.tenant_id = '2'"))
    rows = (await db_session.execute(select(User))).scalars().all()
    assert rows == []

CI test for cross-tenant access.

Policy for INSERT only

CREATE POLICY tenant_insert ON users FOR INSERT
WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

For tables where reads are global but writes are tenant-scoped.

Multiple policies

-- Tenant isolation
CREATE POLICY tenant_iso ON posts
USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- Author-only writes
CREATE POLICY author_only_writes ON posts FOR UPDATE
USING (author_id = current_setting('app.user_id')::bigint);

Multiple policies on the same table; combined with OR (default) or all-required (AS RESTRICTIVE).

FORCE RLS (apply to table owner too)

ALTER TABLE users FORCE ROW LEVEL SECURITY;

Without FORCE, the table owner bypasses RLS. With FORCE: owner is subject too.

Performance

RLS predicates added to every query. Make sure indexes cover them:

__table_args__ = (
    Index("ix_users_tenant_email", "tenant_id", "email"),
)

Tenant_id first in composite indexes since RLS prepends the filter.

Bypass for admin paths

async def get_admin_db() -> AsyncSession:
    async with AdminSessionLocal() as session:
        # AdminSessionLocal uses an engine with BYPASSRLS role
        yield session

@app.get("/admin/all-users", dependencies=[Depends(require_role("admin"))])
async def admin_list(db = Depends(get_admin_db)):
    return (await db.execute(select(User))).scalars().all()

Test for accidental bypass

def test_app_role_cannot_bypass():
    with engine.connect() as conn:
        role = conn.execute(text("SELECT rolbypassrls FROM pg_roles WHERE rolname = current_user")).scalar()
        assert not role, "App role has BYPASSRLS — RLS broken"

Common mistakes

  • App role with BYPASSRLS — RLS useless.
  • Missing WITH CHECK — inserts can specify other tenant_id.
  • RLS without indexes — slow seq scans.
  • SET app.tenant_id (no LOCAL) — persists across connection lifetime in pool.

Read this next

If you want my RLS + tenant-context + admin role pattern, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .