Row-Level Security and Data Partitioning
Enforce hard data boundaries with PostgreSQL row-level security and tenant-scoped query guards.
Why Hard Tenant Boundaries Matter
In a multi-tenant SaaS, the most catastrophic bug class is cross-tenant data leakage: tenant A reading tenant B's rows. A single missing WHERE tenant_id = ? in one endpoint is enough to leak everything.
There are three layers of defense:
- Application guards — every query scopes by
tenant_id. - Database row-level security (RLS) — PostgreSQL refuses to return rows that don't match a session policy, even if the app forgets.
- Partitioning — physically separate tenant data for performance and blast-radius control.
This lesson combines all three on FastAPI + PostgreSQL. The golden rule: never trust the application alone. RLS is the safety net that turns a leak into an empty result.
The Shared-Schema Tenant Model
We use the shared-schema model: one set of tables, every row carries a tenant_id column. It is the cheapest to operate and the easiest to migrate, but it has zero isolation by default — isolation is entirely your responsibility.
Every tenant-owned table follows the same shape: a tenant_id foreign key, indexed, and ideally part of a composite primary key so cross-tenant joins are impossible by construction.
from sqlalchemy import Column, BigInteger, String, ForeignKey, Index
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class Invoice(Base):
__tablename__ = "invoices"
# Composite PK (tenant_id, id) makes a row globally addressable
# only WITH its tenant -> no accidental cross-tenant lookups.
tenant_id = Column(BigInteger, ForeignKey("tenants.id"), primary_key=True)
id = Column(BigInteger, primary_key=True)
customer = Column(String, nullable=False)
amount_cents = Column(BigInteger, nullable=False)
__table_args__ = (
Index("ix_invoices_tenant", "tenant_id"),
)All lessons in this course
- Tenant Isolation Strategies and Trade-offs
- Tenant Context Resolution and Middleware
- Row-Level Security and Data Partitioning
- Usage Metering, Quotas and Billing Hooks