The multi-tenant spine: adversarial RLS design, then 13 impersonation tests (one caught a real cascade bug)

The web seam worked, but built for one user. The moment a second person signed up they'd share one board and see each other's everything — the prototype RLS was for all to authenticated using (true), i.e. no tenant isolation at all. "Build it like real people use it" means that has to die.

The model

Founder's structure, mapped to the existing desktop depth (Entity → Service):

• entities (org, has an owner_id) → services (within an entity, own owner_id) → work data (tickets/records/needs_you/widget_kv) scoped by service_id.
• memberships(service_id, user_id, role) with role ∈ {viewer, contributor, admin, owner}. Invite = insert a row. Entity owner gets full access to every service in their entity.
• Permissions enforced in the database, not app code: viewer reads; contributor does work (create/claim tickets, add records, edit own widgets); admin manages members; owner deletes/transfers.

Designed + audited by a workflow

An 8-agent workflow read the existing structure, designed the schema + RLS, then ran five adversarial lenses hunting data-isolation holes — and found 28 candidates, 11 critical/high. The non-obvious root cause it surfaced: RLS WITH CHECK can't see OLD.*, so a pure policy can't express "you may not raise your own role" or "owner_id is immutable." Those invariants moved into BEFORE triggers (memberships_guard, services_guard, tickets_guard) with a caller_role_rank() helper; RLS stays the coarse gate, the trigger enforces the tier boundary. Lock columns are writable only inside the claim_ticket / release_ticket SECURITY DEFINER RPCs (gated by a transaction-local GUC), so the atomic lock is real, not decorative. Every definer function is search_path = ''

• schema-qualified, and create on public is revoked.

Then actually run it

Drafted ≠ verified. Applied it to the live database, then impersonated each role in psql (set local role authenticated; set local request.jwt.claims = '{"sub":"…"}') and asserted:

• non-member reads 0 tickets / claim → FORBIDDEN
• viewer reads but can't claim or insert
• contributor claims; a raw UPDATE of the lock columns → USE_CLAIM_RPC
• admin can't self-promote to owner (CANNOT_GRANT_ROLE_ABOVE_OWN_RANK), can't grant owner to an ally, can't rewrite services.owner_id (OWNER_TRANSFER_FORBIDDEN)
• widget_kv is per-user isolated

12/12 passed. Then the cleanup raised CANNOT_REMOVE_HIGHER_ROLE — a real bug the audit missed: deleting an entity cascades to its services and then their memberships, but by the time the membership-delete trigger runs the parent service is already gone, so caller_role_rank() returns 0 and the guard blocks its own cascade. Net effect: you could never delete a service/entity that had an admin or owner member. Fix: the DELETE branch allows the cascade when the parent service no longer exists (only an owner can delete a service, and an owner outranks every membership, so it opens no hole). Re-verified: direct admin-delete-of-owner still blocked; owner cascade-delete now works; DB left clean.

The lesson is the old one — an adversarial design review is not a substitute for running it. The panel caught the subtle privilege holes a human would miss; a 13th test caught the operational bug the panel would miss. You need both.

Next: the web and desktop still speak the old flat startup_id/'s1' model, so they're broken against this schema until rewired to entities/services/memberships (signup → auto-create a workspace, a service picker, role-aware UI).