How to set up Supabase Row Level Security for a multi-tenant SaaS

Multi-tenant SaaS is the most common SaaS pattern: each customer (a “workspace,” “team,” or “organization”) has their own data, and users belong to one or more workspaces. Get the isolation wrong, and one customer’s API call can read another customer’s data — the worst kind of bug a SaaS can ship. Row Level Security in Supabase moves that isolation from your application layer into the database, so even an API mistake can’t leak data.

Below is the exact eight-step procedure used in production. Every snippet is real SQL you can paste into the SQL editor.

1 Define your multi-tenant model

Before writing a single policy, the data model needs to encode the relationship between users and tenants. The cleanest pattern is three tables: workspaces (the tenants), auth.users (Supabase’s built-in users table), and memberships (the join table that says which user belongs to which workspace and with what role).

-- workspaces table
create table public.workspaces (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz default now(),
  owner_id uuid references auth.users(id) on delete restrict
);

-- memberships join table
create table public.memberships (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null check (role in ('owner','admin','member')),
  created_at timestamptz default now(),
  unique (workspace_id, user_id)
);

-- example tenant-scoped table
create table public.projects (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  name text not null,
  created_by uuid references auth.users(id),
  created_at timestamptz default now()
);

create index on public.memberships (user_id);
create index on public.projects (workspace_id);

Two principles to internalize: every tenant-scoped table has a workspace_id column, and the memberships table is the single source of truth for “does user X belong to workspace Y?”

2 Add helper functions

Repeating the membership-lookup subquery inside every policy is verbose and slow. Instead, encapsulate it in a SECURITY DEFINER function that returns the workspaces the current user belongs to. Inside RLS policies, auth.uid() returns the JWT-authenticated user’s id.

create or replace function public.get_user_workspace_ids()
returns setof uuid
language sql
stable
security definer
set search_path = public
as $$
  select workspace_id
  from public.memberships
  where user_id = auth.uid();
$$;

revoke all on function public.get_user_workspace_ids() from public;
grant execute on function public.get_user_workspace_ids() to authenticated;

The stable keyword lets PostgreSQL cache results within a query, and security definer means the function runs with the privileges of its owner — bypassing RLS on memberships itself, which would otherwise create infinite recursion when policies on memberships call this function. The explicit search_path closes a known privilege escalation vector.

3 Enable RLS on every table

This is the single most overlooked step. Creating policies on a table that does not have RLS enabled gives you nothing — rows remain readable by all authenticated users. Enable it explicitly on every tenant-scoped table.

alter table public.workspaces enable row level security;
alter table public.memberships enable row level security;
alter table public.projects enable row level security;

-- optional but recommended: also force it for the table owner
alter table public.workspaces force row level security;
alter table public.memberships force row level security;
alter table public.projects force row level security;

Run a sanity-check query after to confirm:

select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public';

Every row should have rowsecurity = true. If even one of your tenant-scoped tables shows false, that table is wide open. For background on why RLS matters compared to alternative auth setups, see Clerk vs Supabase Auth.

4 Write SELECT policies

Read policies are the foundation: every other operation builds on the assumption that you can correctly identify which rows belong to the current user. The policy template is “a row is readable if the user is a member of its workspace.”

-- workspaces: members can read their workspaces
create policy "select_workspaces_for_members"
on public.workspaces
for select
to authenticated
using (
  id in (select public.get_user_workspace_ids())
);

-- memberships: a user can see their own membership rows
create policy "select_memberships_for_self"
on public.memberships
for select
to authenticated
using (
  user_id = auth.uid()
  or workspace_id in (select public.get_user_workspace_ids())
);

-- projects: members can read projects in their workspaces
create policy "select_projects_for_members"
on public.projects
for select
to authenticated
using (
  workspace_id in (select public.get_user_workspace_ids())
);

Note that USING is the filter applied on read — only rows where the expression returns true become visible.

5 Write INSERT policies with WITH CHECK

This is where most developers trip up. USING filters which rows the policy applies to. WITH CHECK validates new or modified rows. INSERT policies must use WITH CHECK — if you only write USING on an INSERT policy, PostgreSQL will reject every row, because there is no existing row for USING to evaluate against.

create policy "insert_projects_for_members"
on public.projects
for insert
to authenticated
with check (
  workspace_id in (select public.get_user_workspace_ids())
  and created_by = auth.uid()
);

The policy says: a new project may be inserted only if its workspace_id is one the current user belongs to and its created_by matches the current user. Without the second clause, a malicious client could spoof created_by to impersonate another user.

6 Write UPDATE policies

UPDATE policies need both USING (which existing rows are eligible for update) and WITH CHECK (what the row must look like after update). Forgetting WITH CHECK means a member could re-assign a row to a workspace they don’t belong to.

create policy "update_projects_for_members"
on public.projects
for update
to authenticated
using (
  workspace_id in (select public.get_user_workspace_ids())
)
with check (
  workspace_id in (select public.get_user_workspace_ids())
);

-- restrict workspace updates to admins/owners only
create policy "update_workspaces_for_admins"
on public.workspaces
for update
to authenticated
using (
  id in (
    select workspace_id from public.memberships
    where user_id = auth.uid() and role in ('owner','admin')
  )
)
with check (
  id in (
    select workspace_id from public.memberships
    where user_id = auth.uid() and role in ('owner','admin')
  )
);

7 Write DELETE policies

Deletes only need USING (there is no “new” row to check). Be deliberate about who can delete — in most apps this is admins/owners, not regular members.

create policy "delete_projects_for_admins"
on public.projects
for delete
to authenticated
using (
  workspace_id in (
    select workspace_id from public.memberships
    where user_id = auth.uid() and role in ('owner','admin')
  )
);

create policy "delete_workspaces_for_owner"
on public.workspaces
for delete
to authenticated
using (
  owner_id = auth.uid()
);

8 Test policies in the Supabase SQL editor

Never trust policies you haven’t tested. Supabase’s SQL editor runs as the service role by default (which bypasses RLS), so to test policies you have to simulate a real authenticated user. Use set local role authenticated together with set_config('request.jwt.claims', ...) in a transaction.

-- replace with two real user UUIDs from auth.users
do $$
declare
  alice uuid := '11111111-1111-1111-1111-111111111111';
  bob   uuid := '22222222-2222-2222-2222-222222222222';
begin
  perform set_config(
    'request.jwt.claims',
    json_build_object('sub', alice, 'role', 'authenticated')::text,
    true
  );
end $$;

set local role authenticated;

-- now run a query as Alice
select id, name, workspace_id from public.projects;

Run the same query after switching sub to Bob’s id. Each user should see only the rows for the workspaces they belong to. If they see each other’s rows, a policy is wrong — usually the SELECT policy on the source table.

For automated tests, write the same pattern inside pgTAP or call your API from a test that signs in as a fixture user. The Supabase team also recommends pinning RLS testing into CI; you can find their pattern in the official docs.

Common mistakes

Forgetting to enable RLS

The single biggest mistake. Policies with no RLS enabled are decorative — the table is wide open. Always run the pg_tables check from Step 3 after creating new tables. Many teams add a CI assertion that fails the build if any public.* table has rowsecurity = false.

Confusing USING vs WITH CHECK

USING filters rows that already exist. WITH CHECK validates row state during INSERT or after UPDATE. INSERT policies need WITH CHECK only. UPDATE policies need both. Mixing these up produces the worst kind of bug: it appears to work because reads succeed, but writes fail or, worse, succeed when they should fail.

Not handling NULL workspace_id

If a tenant-scoped table has a nullable workspace_id, rows with NULL slip past the workspace_id in (...) filter (because NULL in (...) is NULL, not true). Either make workspace_id not null at the schema level, or write your policies as workspace_id is not null and workspace_id in (...).

Forgetting that the service role bypasses RLS

The service role key bypasses RLS entirely. This is by design — it’s what server-side code uses for admin operations. The mistake is using the service role key in client-side code (a Next.js server action that runs in the edge runtime can leak it), or forgetting that webhook handlers need to enforce their own auth because they typically use the service role. For a deeper backend comparison, see Supabase vs Firebase.

Recursive policies on memberships

If a policy on the memberships table itself calls a function that reads from memberships, and that function is not SECURITY DEFINER, you get infinite recursion. The helper function in Step 2 uses SECURITY DEFINER specifically to avoid this.

Related guides

• What is Row Level Security — the concept explained
• Supabase vs Firebase — security comparison
• Clerk vs Supabase Auth — which to pick
• How to build a SaaS with Claude — the full guide