PostgreSQL Row-Level Security: Multi-Tenancy Without Leaking Tenant Data

#postgres #database #security #backend

Building a multi-tenant SaaS application is fraught with danger. In a traditional shared-database architecture, every single SQL query must remember to append a tenant filter:

SELECT * FROM invoices WHERE tenant_id = 'acme_corp';

If a developer writes just one query and forgets WHERE tenant_id = :id:

SELECT * FROM invoices WHERE id = :invoice_id; -- BUG!

Tenant Acme Corp can suddenly view Tenant Globex's financial invoices! This is one of the most common and catastrophic data leak vulnerabilities in SaaS platforms.

Instead of relying on fragile application-level WHERE clauses, PostgreSQL provides an ironclad, kernel-level defense: Row-Level Security (RLS).

What Is Row-Level Security?

Row-Level Security allows you to define security policies directly on tables inside the PostgreSQL engine. When RLS is enabled:

PostgreSQL inspects the current session's identity.
The query planner automatically injects the tenant filter into every SELECT, INSERT, UPDATE, and DELETE query.
Even if an application developer executes SELECT * FROM invoices;, PostgreSQL will only return rows belonging to the active tenant!

Step-by-Step Implementation

1. Enable RLS on the Table

CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(64) NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

2. Create the Tenant Isolation Policy

CREATE POLICY tenant_isolation_policy ON invoices
    AS RESTRICTIVE
    USING (tenant_id = current_setting('app.current_tenant_id', true))
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true));

3. Application Integration (Python / SQLAlchemy)

When a web request arrives, extract the tenant ID from the authenticated user's JWT and set the PostgreSQL local session variable within the transaction:

from sqlalchemy import text
from sqlalchemy.orm import Session

def get_tenant_invoices(db: Session, tenant_id: str):
    db.execute(
        text("SELECT set_config('app.current_tenant_id', :tenant, true)"),
        {"tenant": tenant_id}
    )

    # Notice: NO WHERE tenant_id = ... needed! RLS enforces it automatically!
    result = db.execute(text("SELECT id, amount, description FROM invoices")).fetchall()
    return result

def insert_tenant_invoice(db: Session, tenant_id: str, amount: float, desc: str):
    db.execute(
        text("SELECT set_config('app.current_tenant_id', :tenant, true)"),
        {"tenant": tenant_id}
    )

    db.execute(
        text("""
            INSERT INTO invoices (tenant_id, amount, description)
            VALUES (:tenant, :amount, :desc)
        """),
        {"tenant": tenant_id, "amount": amount, "desc": desc}
    )
    db.commit()

The Superuser Caveat

By default, PostgreSQL superusers (e.g. postgres) bypass Row-Level Security!

Always create a dedicated application role:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password';
GRANT ALL PRIVILEGES ON invoices TO app_user;

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

DEV Community