supabase-multitenant-starter

Multi-tenant Postgres schema on Supabase where tenant isolation is enforced by the database, not by the application layer — with tests that prove it.

Most multi-tenant examples filter by org_id in the API and call it done. That works until one endpoint forgets the where clause. This one pushes isolation into Row Level Security, so a bug in the API still can't leak another tenant's rows.

organizations ──< memberships >── auth.users
      │              (role: owner | admin | member)
      ├──< projects
      └──< audit_log   (append-only)

The tenant key is the join through memberships — not a denormalized user_id on every table. Any member of an org sees the org's projects regardless of who created them, which is what "multi-tenant" actually means as opposed to "per-user rows."

Every table has enable row level security and force row level security. The second one matters: without it, the table owner silently bypasses every policy you wrote, and your tests pass while production leaks.

A policy on memberships that queries memberships re-triggers RLS on the same table and recurses until Postgres raises infinite recursion detected in policy for relation. This is the first wall everyone hits with RLS on a join table.

is_org_member() and has_org_role() are SECURITY DEFINER, so they run as the owner and skip RLS inside the function body, breaking the cycle.

SECURITY DEFINER is a privilege escalation by design, so these functions are deliberately narrow:

• They answer one boolean question about auth.uid() — never return tenant data.
• search_path is pinned to public, pg_temp so a caller can't shadow memberships with their own table.
• execute is revoked from public and granted only to authenticated.

Policies run on every row access. memberships.user_id, memberships.org_id, and projects.org_id are indexed because an unindexed policy predicate turns every query into a sequential scan once the table grows — RLS performance is a real problem, not a footnote.

A trigger inserts the creator as owner in the same transaction as the org. Without it, a fresh org has zero members and RLS immediately locks the creator out of the row they just wrote.

audit_log has SELECT and INSERT policies and nothing else. In RLS, no policy means the command is denied — so UPDATE and DELETE are refused for every authenticated user including owners. An audit trail anyone can rewrite isn't an audit trail.

The INSERT policy also pins actor_id = auth.uid(), so a user can't write entries attributed to someone else.

The tests assert behavior, not configuration. Fixtures: two orgs (Acme, Globex), three users (owner, plain member, outsider). Then:

• The outsider's select on another org's project returns 0 rows — by org_id filter, by direct primary key, and through a join.
• Their update / delete against another org's row affects 0 rows (RLS filters silently rather than erroring — worth knowing, since code that checks for exceptions won't notice).
• A plain member reads org projects but is refused on insert.
• Nobody can attribute a row to another user (created_by = auth.uid() in the WITH CHECK).
• An owner cannot rewrite the audit log.

supabase start
supabase test db

supabase start          # migrations apply automatically
supabase test db        # run the isolation tests

Requires the Supabase CLI.

Deliberately small: schema, policies, tests. No frontend, no auth UI, no API layer — those are per-project decisions. This is the part that's easy to get subtly wrong and expensive to discover in production.

Built by Juan Merma — github.com/Zarenk · zentryxapp.com

The patterns here come from running a multi-tenant ERP in production with paying customers, where per-tenant data isolation on Postgres is the backbone.