Multi-tenancy on Supabase: policy patterns that scale
Multi-tenancy on Supabase converges on one shape sooner or later: every tenant-owned row carries an organisation id, and every policy asks whether the calling user belongs to that organisation. The pattern is sound. What varies wildly between codebases is execution — membership joins written inconsistently, exceptions bolted on with OR chains, and one copy-pasted policy drifting away from the rest.
Because RLS is evaluated per query, policy shape is performance shape too. A clean membership join backed by indexes scales with tenants and users alike; a sprawling predicate forces the planner into contortions that surface months later as mystery latency. Writing these policies well once is cheaper than tuning them forever.
This is a craft page rather than a checklist: three patterns, their failure modes, and the conventions that keep a growing schema honest. RowShield does not automate pattern selection — a person designs tenancy — but the page pairs naturally with scanning, which watches that what you designed remains deployed.
RowShield does not detect this yet. This guide gives you the catalog queries to check it yourself. The nine rules that do ship are listed on the rules index.
Pattern one: the membership join
Keep a memberships table mapping users to organisations, carrying a role column. Tenant policies then reduce to one traversal: the row’s organisation id must appear among the caller’s memberships. Express it as a subquery on the link table, wrap auth.uid() so the value computes once per statement, and index both sides of the link.
The pattern’s virtue is centralisation. Role changes, offboarding and organisation merges all happen in one table, and every downstream policy inherits the correction instantly. Contrast per-table hardcoded user lists: each is a small lie waiting for its first stale row, and together they guarantee drift across the schema.
CREATE POLICY "members_read_org_orders"
ON public.orders
FOR SELECT
TO authenticated
USING (organisation_id IN (
SELECT m.organisation_id FROM public.memberships m
WHERE m.user_id = (SELECT auth.uid())
));Pattern two: owner override inside a tenant
Within an organisation some rows are personal — drafts, assignments, salary bands. Layer a second predicate: members see tenant rows generally, owners always see their own. Write it as separate policies per audience rather than one OR chain, because separate grants document themselves and can be revoked independently when a product decision changes.
Beware OR growth. Every added clause widens the blast radius of a mistake and burdens the planner, and six months later nobody recalls which clause justified which exception. If a table accrues more than a couple of exceptions, promote them into the membership model — teams, groups, delegations — where they belong and can be audited.
Pattern three: shared and public rows
Real tenants share things: templates, marketplace items, public profiles. Handle them explicitly with a visibility column and a policy arm granting it broadly — including anon where genuinely public. The discipline matters less for the grant than for the negative space: everything not marked shared stays tenant-private by default, with no policy required.
Default-deny is the pattern’s quiet engine. With RLS forced, a table lacking applicable policies returns nothing to any web role, so a forgotten grant errs towards silence rather than exposure. Preserve that bias: resist blanket policies created to rescue one broken screen, and fix that screen’s policy instead.
Conventions that keep it sane
Adopt naming and structure conventions early: one policy per role per action, names stating role and verb, membership traversal identical everywhere. Consistency converts review from archaeology into diff-reading — a reviewer comparing two similar policies should find only the expected difference, never a novel traversal style.
Then watch deployment reality. Designs drift from migrations, hotfixes disable enforcement during incidents, and new tables arrive without the traversal. RowShield reads catalogue metadata only — never tenant rows — and is an independent product unaffiliated with Supabase; its scheduled scans flag disabled RLS, world-readable tables and policy gaps as transitions, usually the earliest honest signal that the pattern has been bypassed.
Frequently asked
- One database with RLS, or one database per tenant?
- For most products, one database with enforced RLS: operations stay tractable, migrations run once, and centralised policy gives a single place to reason about access. Separate databases per tenant buy strong isolation at punishing operational cost — a trade only the largest or most regulated tenants usually justify.
- How do admins see across tenants safely?
- Deliberately and rarely. Prefer a dedicated support role with narrowly-scoped policies and audit trails, rather than a BYPASSRLS shortcut or an always-true admin clause sprinkled through every policy. Cross-tenant access should be a visible, logged act — not a permanent hole shaped like a feature.
- Do policy subqueries hurt performance?
- They can, if the membership table lacks indexes or the traversal hides per-row evaluation. Index memberships on user id and organisation id, wrap auth.uid() in a scalar subselect so it evaluates once per statement, and explain a representative tenant query before declaring the pattern fast.
Check your project in about ten seconds
Paste a URL. No signup, no writes, nothing stored.
Run the free audit