0

System design: a multi-tenant schema

Interviewused in ~80% of apps

"Design a multi-tenant schema for a SaaS with orgs, members and roles" is the single most common whiteboard question a Postgres-backed shop asks. Everyone can draw three boxes and an arrow. What separates a hire from a rejection is what happens next: can you show WHERE the isolation is actually enforced, defend it against the one attack that always comes up (a user reassigning a row into an org they do not belong to), and explain the error you will hit the first time you write the naive version.

Core idea. A multi-tenant schema is three pieces that work together, not three independent decisions: an orgs table (the tenant), a memberships join table (who belongs to which org, and with what role), and every tenant-owned table carrying an org_id foreign key. The isolation guarantee is enforced exactly once — in a SECURITY DEFINER helper function that returns the caller's org ids — and every policy on every tenant table reuses that same helper, so changing the isolation rule means changing one function, not N policies.
Playground, this isn't a screenshot
policyviewing as
membershipsselect * from memberships
42P17

infinite recursion detected in policy for relation "memberships" — the policy reads memberships to decide who may read memberships. Every query fails, for every user, including a bare select *.

documentsselect title from documents
Acme roadmap
Globex roadmap

The helper doesn't exist yet, so there's no working policy to reuse here either — this card is only unblocked once the left one is fixed.

↑ play a little first, then dig in