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:

@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:

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:

@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:

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:

# 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:

# 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.