ADR-002: Multi-Tenancy via PostgreSQL Row-Level Security¶

Status¶

Accepted

Context¶

GrinSystem is a SaaS application requiring multi-tenancy. Each tenant's data must be isolated from other tenants.

Requirements: - Strong data isolation (no cross-tenant data access) - Simple implementation for developers - Good performance - Auditability

Decision¶

We will use PostgreSQL Row-Level Security (RLS) for tenant isolation.

Implementation: - Every tenant-scoped table has a tenant_id column - RLS policies automatically filter queries by tenant - Tenant context set via session variable: SET app.current_tenant = :tenant_id - Middleware sets tenant context on each request

ALTER TABLE recipes ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON recipes
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

Consequences¶

Positive¶

• Strong isolation at database level (cannot be bypassed by application bugs)
• Simplified application code (no manual tenant filtering)
• Works with ORM (SQLAlchemy)
• Performance: PostgreSQL optimizes RLS queries

Negative¶

• Database-coupled (harder to switch databases)
• All queries must include tenant_id
• Requires care with migrations and raw SQL
• Testing requires proper RLS setup

Neutral¶

• DBAs can audit tenant isolation via policy inspection

Alternatives Considered¶

References¶

• PostgreSQL RLS Documentation
• Multi-Tenancy with RLS