Tenancy & RLS¶

SAE Books is multi-tenant. Each domain table has a tenant_id UUID NOT NULL column, and Postgres row-level security (RLS) enforces that no query can read or write rows from another tenant.

Mechanism¶

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant', true)::uuid);

FORCE ROW LEVEL SECURITY is critical — without it, the table owner (the schema-owner role the app historically connected as) bypasses RLS entirely. WITH CHECK gates writes as well as reads, so an INSERT/UPDATE writing a foreign tenant_id is also blocked. Both were added by migration 0055_force_rls_and_complete_policies.

At the start of every SQLAlchemy transaction the app issues:

SET LOCAL app.current_tenant = '<uuid-from-jwt>';

This is wired via a process-wide after_begin SQLAlchemy event listener in saebooks/api/v1/deps.py. The listener reads the tenant UUID from session.info['tenant_id'], which is stamped by the get_session dependency (JSON API paths) or the auth middleware (HTML paths) after resolving the JWT. SET LOCAL is transaction-scoped and resets automatically on commit or rollback — no manual teardown needed.

Database roles¶

Two Postgres roles enforce the split (migrations 0056_split_db_role, 0128_app_role):

Role	Used by	`BYPASSRLS`	Purpose
`saebooks`	Alembic, CLI DDL	yes (owner)	Schema-owner role; not used for request-time queries
`saebooks_app`	Runtime API, CLI walker	no	`NOSUPERUSER NOBYPASSRLS` — FORCE RLS binds it

The runtime AsyncSessionLocal connects as saebooks_app (configured via SAEBOOKS_APP_DATABASE_URL). Alembic migrations still run as saebooks so DDL works without RLS friction. Pre-authentication lookups (login by email, signup uniqueness checks) use a separate LoginSessionLocal that connects as the owner role, because app.current_tenant is not yet known at login time and FORCE-RLS on the users table would otherwise return zero rows for every login.

Why RLS, not WHERE-clause filtering?¶

RLS is enforced at the planner level, so a forgotten WHERE tenant_id = ... (or a SQL injection that bypasses an ORM clause) still can't return another tenant's data. We treat it as the last line of defence — the application also filters explicitly on tenant_id, but RLS catches every missed case.

Audit¶

audit_log (migration 0076_audit_log) records hard-delete and other privileged operations. The log captures actor, target tenant, table, row IDs, and operation.