Multi-Tenant SaaS on Supabase: Row-Level Security Done Right

The most dangerous line in a multi-tenant SaaS is the one you didn't write: the WHERE organization_id = ? a developer forgot. Get it wrong once and Org A reads Org B's data. Most teams defend against this in application code — a middleware, a base query class, a convention — which means the defense is exactly as strong as the least careful query anyone writes for the life of the product.

Postgres Row-Level Security moves that boundary into the database, where it's enforced on every query whether the app remembers to filter or not. Here's the pattern we use on Supabase (from QuoteRescue's multi-tenant model), and the two rules that make it hold up.

The data model: memberships decide visibility

Three tables: organizations, organization_members (the join table — who belongs to which org), and the tenant-scoped data (estimates, events, …) each carrying an organization_id. Visibility is decided by a single question: is this user a member of this row's org?

We answer it with one SECURITY DEFINER function:

create or replace function public.is_org_member(p_org_id uuid, p_user_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.organization_members
    where organization_id = p_org_id and user_id = p_user_id
  );
$$;

Then every tenant table's read policy is the same one-liner:

create policy estimates_select_member
  on public.estimates
  for select
  using (public.is_org_member(organization_id, auth.uid()));

auth.uid() is the current authenticated user (Supabase sets it from the JWT). The policy runs on every select against estimates, appended to the query by Postgres itself. A developer who writes select * from estimates with no filter gets only their org's rows — the database enforced it. The forgotten WHERE is now impossible to forget, because it isn't in the application at all.

Rule 1: reads via RLS, writes via service role

Notice the policy above is for select only. There is deliberately no insert/update/delete policy. That's not an oversight — it's the pattern:

-- No insert/update/delete policy → only the service role (which bypasses RLS)
-- can mutate rows. Webhook + checkout handlers run with the service role key.

Client-side code (using the anon key) can read its own org's data directly — fast, no API hop. But all writes go through your API using the service-role key, which bypasses RLS. Why funnel writes through the server?

• Writes usually have invariants a policy can't express: "you can only create an estimate if your org's plan allows it," "a deposit can only be marked paid by a verified Stripe webhook."
• A Stripe or Resend webhook has no user JWT — there's no auth.uid(). It authenticates as the service and writes on the tenant's behalf.
• It keeps mutation logic in one auditable place instead of scattered across client calls.

So: RLS protects reads; the service-role boundary protects writes. Clients read directly and safely; the server owns every mutation.

Rule 2: SECURITY DEFINER + locked-down search_path

Two details in that function are load-bearing, not boilerplate:

• security definer runs the function with the owner's privileges, so the membership check works even when the calling user can't directly read organization_members. Without it you'd need a policy on the join table that itself risks recursion.
• set search_path = public pins the schema. A SECURITY DEFINER function without a fixed search_path is a classic privilege-escalation vector — a malicious user manipulates their session search_path so the function resolves to their table instead of yours. Pinning it closes that hole.

And the grants are explicit — revoke from public, grant only to the roles that need it:

revoke all on function public.is_org_member(uuid, uuid) from public;
grant execute on function public.is_org_member(uuid, uuid) to authenticated, anon, service_role;

Why the function, instead of inlining the subquery?

You could inline exists (select 1 from organization_members …) into every policy. The function wins for three reasons: one place to change the membership rule (add role-based tiers later without touching ten policies), no recursion headaches (the definer rights sidestep needing a policy on the join table), and readability — is_org_member(organization_id, auth.uid()) says exactly what it means. When your membership logic grows (org roles, suspended accounts, trial expiry), you edit one function and every table inherits it.

Testing it (do not skip this)

RLS bugs are invisible until they're a breach, so test the boundary explicitly:

1. Create two orgs, a user in each.
2. As user A (anon key + A's JWT), query the shared table — assert you see only A's rows.
3. As user A, try to read a row you know belongs to B by its exact id — assert you get nothing, not an error.
4. Confirm the service role sees everything (it should — it bypasses RLS by design).

That "query B's id and get zero rows" test is the one that proves isolation. Run it in CI.

What to copy

1. Put tenant isolation in RLS, not app code. The database enforces it on every query; the app can't forget.
2. Reads through RLS, writes through the service role. Clients read their own data directly; the server owns all mutations and the invariants around them.
3. Centralize the membership check in a SECURITY DEFINER function with a pinned search_path and explicit grants.
4. Test the negative case in CI — query another tenant's row by id and assert zero rows.

Multi-tenancy done in application code is a bet that every developer, forever, writes every query correctly. RLS is the same isolation as a property of the database. One of those bets you can actually win.

Standing up a multi-tenant SaaS and want the isolation right from day one? Let's build it.