Building Multi-Tenant Row-Level Security in PostgreSQL: A Production Pattern

The article describes a production pattern for implementing multi-tenant row-level security (RLS) in PostgreSQL, arguing that application-layer tenant isolation is unreliable because bugs like missing authorization checks or refactoring errors can expose data across tenants. The author presents a database-enforced approach where PostgreSQL uses session variables (set after authentication) to automatically filter rows by tenant_id, preventing unauthorized data access regardless of application code mistakes. The pattern includes enabling RLS on tables, creating policies that reference session settings like `app.current_tenant_id`, and wiring this into frameworks like FastAPI and SQLAlchemy to eliminate the need for manual tenant filtering in queries.

Building Multi-Tenant Row-Level Security in PostgreSQL: A Production Pattern Most multi-tenant SaaS applications implement tenant isolation in the application layer. You check request.tenant id before querying, validate ownership in your service layer, maybe add a middleware that throws if the IDs don't match. It works—until it doesn't. I've watched this pattern burn production systems. A junior developer forgets one authorization check. A refactor moves logic around and the guard rails disappear. A cron job runs with elevated privileges and suddenly exports competitor data. These aren't hypotheticals—I've debugged all three in CitizenApp. Database-enforced Row-Level Security RLS flips the model: the database itself refuses to return rows that don't belong to your tenant, regardless of what code tries to access them. This is belt and suspenders, but the belt actually works. Why Application-Layer Isolation Fails Let me be direct: application layer isolation is a suggestion, not a guarantee. Consider this typical FastAPI pattern: python @router.get "/users" async def list users current user: User = Depends get current user , db: Session = Depends get db : Authorization happens here return db.query User .filter User.tenant id == current user.tenant id .all This looks safe. But: - Forgotten filters : A new endpoint queries User without the tenant check. Easy mistake. - Scope creep : An admin panel needs to see all users across tenants—so you bypass the filter. Now that code path exists and someone copies it. - N+1 relationships : You load users, then loop through and load their audit logs. The second query forgets the tenant filter. - Background jobs : A Celery task runs as a "system user" with tenant id = None . Now it can see everything. The worst part? These bugs are invisible until they're exploited. Your tests pass because they run within a single tenant context. Your monitoring doesn't catch it because the data is technically being accessed correctly—just by the wrong person. PostgreSQL RLS: Enforcement at the Source RLS policies live in the database. PostgreSQL evaluates them before returning any row. You cannot read data you're not allowed to read—the database won't let you. Here's the pattern I use: -- Enable RLS on the users table ALTER TABLE users ENABLE ROW LEVEL SECURITY; -- Create a policy that only allows access to your own tenant CREATE POLICY users tenant isolation ON users FOR ALL USING tenant id = current setting 'app.current tenant id' ::uuid ; -- Create a separate policy for superusers if needed CREATE POLICY users admin all ON users FOR ALL USING current setting 'app.is admin' ::boolean = true ; -- Disable RLS for the database owner migration scripts need this ALTER TABLE users FORCE ROW LEVEL SECURITY; The key is current setting . This is a PostgreSQL function that reads session variables. Your application sets these after authentication, and the database uses them to filter queries automatically. Implementing with SQLAlchemy Here's how I wire this into FastAPI + SQLAlchemy: python from sqlalchemy import create engine, text, event from sqlalchemy.orm import sessionmaker, Session from typing import Optional engine = create engine "postgresql://...", echo=False SessionLocal = sessionmaker bind=engine def set rls context session: Session, tenant id: str, is admin: bool = False : """Set the RLS context before executing queries.""" session.execute text "SET app.current tenant id = :tenant id" , {"tenant id": tenant id} session.execute text "SET app.is admin = :is admin" , {"is admin": is admin} async def get db current user: User = Depends get current user - Session: """Dependency that creates a session with RLS context.""" session = SessionLocal try: set rls context session, tenant id=str current user.tenant id , is admin=current user.role == "admin" yield session finally: session.close Now your query is simple: python @router.get "/users" async def list users db: Session = Depends get db : No tenant filter needed—RLS handles it return db.query User .all PostgreSQL silently filters based on the session context. If the current user belongs to tenant abc-123 , they see only users where tenant id = 'abc-123' . Try to query SELECT FROM users , and you get only your tenant's rows. The SQLAlchemy Model Your models stay clean: python from sqlalchemy import Column, String, UUID, ForeignKey from sqlalchemy.orm import declarative base import uuid Base = declarative base class User Base : tablename = "users" id = Column UUID, primary key=True, default=uuid.uuid4 tenant id = Column UUID, ForeignKey "tenants.id" , nullable=False email = Column String, nullable=False role = Column String, default="user" No special ORM magic. SQLAlchemy doesn't need to know about RLS—that's the entire point. The database enforces it. Cascading RLS Across Relationships This is where it gets powerful. Your organizations , projects , audit logs , and invoices tables all need RLS, but you only set the context once: ALTER TABLE organizations ENABLE ROW LEVEL SECURITY; CREATE POLICY org tenant isolation ON organizations FOR ALL USING tenant id = current setting 'app.current tenant id' ::uuid ; ALTER TABLE projects ENABLE ROW LEVEL SECURITY; CREATE POLICY project tenant isolation ON projects FOR ALL USING tenant id = current setting 'app.current tenant id' ::uuid ; ALTER TABLE audit logs ENABLE ROW LEVEL SECURITY; CREATE POLICY audit tenant isolation ON audit logs FOR ALL USING tenant id = current setting 'app.current tenant id' ::uuid ; After set rls context , all queries across all tables respect the tenant boundary. A JOIN between projects and audit logs? Still filtered. A transaction that touches three tables? Still filtered. A future developer adds a new table and forgets the RLS policy? PostgreSQL will reject writes until they add it. What I Missed The Gotcha Migrations and script runners must disable RLS. Your Alembic migrations run as the database owner, and if RLS is enforced, some operations fail. I handle this: python alembic/env.py def run migrations online : with connectable.connect as connection: RLS doesn't apply to superuser if FORCE ROW LEVEL SECURITY isn't set But for safety, disable it during migrations connection.execute text "ALTER ROLE myapp user BYPASSRLS" with connection.begin : context.configure connection=connection, target metadata=target metadata with context.begin transaction : context.run migrations Also: current setting returns NULL if not set. This means a query with no context returns zero rows—which is actually the safe default, but confusing during local development. I always set a test context: python conftest.py for pytest @pytest.fixture def db with rls : session = SessionLocal set rls context session, tenant id="test-tenant-123" yield session session.close The Outcome In CitizenApp, implementing RLS was the moment I stopped worrying about authorization bugs. Not because I stopped making mistakes, but because the database makes those mistakes impossible. Every endpoint, every background job, every future feature—they all inherit the same ironclad guarantee: you cannot read or modify another tenant's data, no matter what code path you take. That's the only pattern worth building on.