Multi-Tenant SaaS with Supabase Row-Level Security: The Complete Pattern

Multi-tenancy is one of those features that's easy to get working and hard to get right. The naive version — adding a user_id column to every table and filtering on it — works until you need organisations, teams, and invitations. Then the data model you chose on day one starts working against you.

Here's the pattern I've settled on after building two multi-tenant SaaS products on Supabase, and the RLS policies that make it actually secure.

The data model: organisations as the tenant boundary#

The key decision is whether your tenancy boundary is the user or the organisation. For a B2C app where each user's data is private, user-level tenancy is fine. For anything B2B — where multiple people collaborate on the same account — you need an organisation (or workspace, or team) as the tenant.

create table organisations (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  slug       text unique not null,
  created_at timestamptz default now()
);
 
create table organisation_members (
  organisation_id  uuid references organisations(id) on delete cascade,
  user_id          uuid references auth.users(id) on delete cascade,
  role             text not null default 'member', -- 'owner' | 'admin' | 'member'
  joined_at        timestamptz default now(),
  primary key (organisation_id, user_id)
);

Every tenant-scoped table then references organisations(id):

create table projects (
  id               uuid primary key default gen_random_uuid(),
  organisation_id  uuid references organisations(id) on delete cascade not null,
  name             text not null,
  created_at       timestamptz default now()
);

This is the shape that makes RLS tractable. You're not checking "is this user's ID the right user?" — you're checking "is this user a member of the organisation that owns this row?"

The core RLS helper function#

Repeat the membership check in every policy and it gets noisy fast. Extract it into a function:

create or replace function is_org_member(org_id uuid)
returns boolean
language sql
security definer
stable
as $$
  select exists (
    select 1
    from organisation_members
    where organisation_id = org_id
      and user_id = auth.uid()
  );
$$;

security definer means the function runs with the privileges of its creator (usually the Postgres superuser), not the calling user — this allows the RLS check to read organisation_members even when the calling user's RLS on that table might otherwise block it. Use this carefully and only in trusted functions.

Now your policies read cleanly:

alter table projects enable row level security;
 
create policy "members can view org projects"
  on projects for select
  using (is_org_member(organisation_id));
 
create policy "admins can insert org projects"
  on projects for insert
  with check (
    exists (
      select 1 from organisation_members
      where organisation_id = projects.organisation_id
        and user_id = auth.uid()
        and role in ('owner', 'admin')
    )
  );
 
create policy "admins can update org projects"
  on projects for update
  using (is_org_member(organisation_id))
  with check (
    exists (
      select 1 from organisation_members
      where organisation_id = projects.organisation_id
        and user_id = auth.uid()
        and role in ('owner', 'admin')
    )
  );
 
create policy "owners can delete org projects"
  on projects for delete
  using (
    exists (
      select 1 from organisation_members
      where organisation_id = projects.organisation_id
        and user_id = auth.uid()
        and role = 'owner'
    )
  );

You'll write a version of this policy set for every tenant-scoped table. It's repetitive, but it's safe — the security check lives at the database layer, not in application code that could be bypassed.

RLS on organisation_members itself#

This table needs its own policies, and they're trickier. A member needs to see their own organisation's member list, but shouldn't be able to add themselves to organisations they don't belong to:

alter table organisation_members enable row level security;
 
-- Anyone can see the members of organisations they belong to
create policy "members can view their org members"
  on organisation_members for select
  using (is_org_member(organisation_id));
 
-- Only admins/owners can add members
create policy "admins can insert members"
  on organisation_members for insert
  with check (
    exists (
      select 1 from organisation_members
      where organisation_id = organisation_members.organisation_id
        and user_id = auth.uid()
        and role in ('owner', 'admin')
    )
  );
 
-- Only owners can change roles
create policy "owners can update member roles"
  on organisation_members for update
  using (is_org_member(organisation_id))
  with check (
    exists (
      select 1 from organisation_members
      where organisation_id = organisation_members.organisation_id
        and user_id = auth.uid()
        and role = 'owner'
    )
  );

Invitation flow without breaking RLS#

Invitations are where most implementations get messy. The naive approach — create a member record before the invitee has a user account — breaks because you don't have their auth.users ID yet.

The cleaner pattern: a separate invitations table:

create table invitations (
  id               uuid primary key default gen_random_uuid(),
  organisation_id  uuid references organisations(id) on delete cascade,
  email            text not null,
  role             text not null default 'member',
  invited_by       uuid references auth.users(id),
  token            text unique not null default encode(gen_random_bytes(32), 'hex'),
  expires_at       timestamptz default now() + interval '7 days',
  accepted_at      timestamptz,
  created_at       timestamptz default now()
);

The invite flow:

1. Admin creates an invitations row with the invitee's email and a secure random token.
2. Send the invitee an email with a link containing the token.
3. When the invitee clicks the link, they sign up or log in, then your app reads the invitation by token and creates the organisation_members row.
4. Mark the invitation accepted_at and you're done.

The invitation record doesn't require a user_id upfront, which keeps the flow correct for new users who don't have accounts yet.

The service_role key for admin operations#

Some operations — like the server-side step of accepting an invitation and creating the member row — need to bypass RLS. For these, use the Supabase service_role key, which bypasses all RLS policies. Keep it server-side only, never in client code or environment variables that are exposed to the browser.

// Server-side only (Server Action, API route, Edge Function)
import { createClient } from '@supabase/supabase-js';
 
const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // never expose this
);
 
// Insert member record bypassing RLS
await supabaseAdmin.from('organisation_members').insert({
  organisation_id: invitation.organisation_id,
  user_id: userId,
  role: invitation.role,
});

Common gotchas#

Forgetting enable row level security: RLS does nothing until you run alter table X enable row level security. A table with policies but without RLS enabled is fully open. Check every table.

Recursive RLS loops: If your is_org_member() function reads a table that itself has RLS, and those policies call is_org_member(), you'll hit infinite recursion. The security definer attribute breaks the loop but requires care about what the function can access.

N+1 on membership checks: A naive is_member check per row adds a subquery per row. For large result sets, use a join instead of the helper function directly in the using clause, or ensure the organisation_members join path is indexed.

Anon key vs service_role key in the wrong place: The anon/public Supabase key respects RLS — that's what the browser client should use. The service_role key bypasses RLS — that belongs only on your server. Mixing them up is a security hole.

FAQ#

Can I use Supabase auth with RLS and an external auth provider? Yes — Supabase supports custom JWTs from providers like Clerk or Auth0. The auth.uid() function returns the sub claim from the JWT. As long as your JWT's sub matches the user IDs in your tables, RLS works the same way.

How do I handle personal accounts alongside org accounts? Either treat each personal account as an org-of-one (same schema, simpler mental model), or add a user_id nullable column alongside organisation_id and handle both cases in your policies. The org-of-one approach is simpler to maintain.

Is RLS a performance bottleneck? For well-indexed tables and simple membership checks, no. The subquery in a using clause is optimised by Postgres. Use explain analyze to verify, and index the organisation_members(organisation_id, user_id) pair.

Can I test RLS policies locally? Yes — use set role authenticated; set local request.jwt.claims to '{"sub": "user-uuid-here"}'; in a Supabase local dev session to impersonate a user and verify your policies behave correctly.