Supabase RLS for Multi-Tenant SaaS: Four Policies and One Deny Test

Multi-tenant means one database holding more than one customer's rows, with a rule that stops customer A from reading customer B. In Supabase that rule is Row Level Security, and the official docs describe a policy as "adding a WHERE clause to every query". The clause lives in Postgres, so it applies whether the query came from your app, from someone's curl command, or from a browser console with your publishable key pasted in.

Before anything else, run this in the Supabase SQL editor:

select
  t.tablename,
  t.rowsecurity                as rls_enabled,
  count(p.policyname)::int     as policies
from pg_tables t
left join pg_policies p
  on p.schemaname = t.schemaname
 and p.tablename  = t.tablename
where t.schemaname = 'public'
group by t.tablename, t.rowsecurity
order by policies asc, t.tablename;

Three results matter. rls_enabled = false is a table anyone with your publishable key can read and write. rls_enabled = true with policies = 0 is a table nobody can touch through the API, which is the state that makes a builder want to turn RLS back off. The most common result of all is a table with RLS on and exactly one policy, which in generated schemas is a select policy with no write protection behind it.

One question drives everything below: how do you stop tenant A reading tenant B in a database an AI wrote. For the wider picture, the security gaps in vibe-coded apps post covers the leak taxonomy and how to vibe code a SaaS covers where this step sits in the build.

Why your first-customer app is not multi-tenant

The policy your builder wrote on day one probably looks like this:

create policy "users read own rows"
on public.projects
for select
using ( auth.uid() = user_id );

That is correct for a personal-notes app. Every row belongs to the person who made it, and auth.uid(), which the docs define as returning "the ID of the user making the request", is the whole authorization model. It works flawlessly through your first customer, because your first customer is one person and every row in the table is theirs.

Customer two arrives as a company. Three people at that company need to see the same rows. The moment you widen the policy so a colleague can see a shared project, auth.uid() = user_id stops being the boundary and you have to say which rows a person may see that they did not create. That is the sentence that makes the app multi-tenant, and it needs a membership table to answer.

The failure has a shape worth naming, because it is not the obvious one. Wendel put it well on X: "Most Supabase multi-tenant bugs aren't caused by 'RLS is off.' They're caused by an access path you forgot to test." (@WendelAndrady) A Reddit thread on a FORCE RLS boilerplate says the same from the other direction: "one missed filter can create a cross-tenant data exposure" (r/Supabase). The table with the tidy select policy and no insert policy is an access path you forgot to test.

Supabase is direct about the conditional. Its features page lists "Multi-tenant applications requiring data isolation" among the cases RLS is valuable for, and its B2B SaaS page says "RLS enforces tenant isolation at the database layer." Both sentences assume policies exist. Switching RLS on and writing none of them gets you a locked table, not an isolated one.

The official model: grants first, then policies

Postgres checks two things before a row reaches your app, and confusing them is why a policy sometimes appears to do nothing.

First, table grants: whether the role making the request has permission on the table at all. Supabase ships three roles that matter here. anon is an unauthenticated visitor holding the publishable key. authenticated is a signed-in user. service_role is your backend, and the grants table in the RLS docs describes it as "Full access. It bypasses RLS, so keep it server-side."

Second, policies: which rows that role may see or write. Policies only run after the grant passes, and only for roles that are not exempt. service_role never evaluates a policy. Neither does the table owner unless you turn on force row level security.

Two consequences follow from that ordering.

A service-role key anywhere the browser can reach makes every policy in this article decorative. The Supabase features page lists the same escape hatch in its own words: "Bypass options: Use service keys or create roles with bypassrls privilege for administrative tasks." Administrative tasks means a server you control. The reason this warning keeps getting repeated to vibe coders is that AI builders reach for the service key the moment a policy blocks a query, and a file named server.ts in a client-side bundle is still a client-side bundle.

The publishable key is the opposite case. The Securing your data guide says to "Turn on Row Level Security (RLS) for your tables and properly configure your access policies", and that "Your publishable key is safe to expose with RLS enabled". Safe to expose is conditional on the policies being right, and the four policies below are what makes it true.

Supabase CEO Paul Copplestone has said the quiet part on X: "do not disable RLS on the 'public' schema. It's enabled by default for a reason" (@kiwicopple).

Schema: organizations, memberships, and a tenant column on every table

Three pieces. A table of tenants, a join table saying who belongs to which tenant, and a tenant column on every table that holds customer data.

create table public.organizations (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  created_at timestamptz not null default now()
);

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

-- the composite primary key indexes (organization_id, user_id) in that order,
-- which does not serve a lookup by user_id alone. RLS does exactly that lookup.
create index memberships_user_id_idx on public.memberships (user_id);

create table public.projects (
  id              uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations (id) on delete cascade,
  title           text not null,
  created_at      timestamptz not null default now()
);

create index projects_organization_id_idx on public.projects (organization_id);

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

Every tenant table gets organization_id uuid not null, including child tables. A comments table that reaches its tenant through comments -> projects -> organizations forces every policy evaluation into a two-hop join, and the r/Supabase thread on child tables and RLS lands on denormalising the tenant id down the tree for exactly that reason. Copy organization_id onto the child and keep it in sync with a trigger or on insert.

Now the helper, which is the piece most tutorials skip and then run into:

create or replace function public.is_org_member(org uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1
    from public.memberships m
    where m.organization_id = org
      and m.user_id = (select auth.uid())
  );
$$;

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

security definer is what stops the recursion. A policy on memberships that queries memberships makes Postgres evaluate the policy to evaluate the policy, and you get error 42P17, infinite recursion detected in policy. A definer function runs as its owner, skips the policy on the table it reads, and breaks the loop. The set search_path = '' is not decoration: without it, a definer function can be pointed at a different schema by whoever calls it, which is how a helper becomes a privilege escalation. Fully qualify every table inside the body.

When one project per tenant is the better call

Shared-database tenancy is the wrong choice when you have a small number of tenants who each need their own backup schedule, their own data residency, or a contractual guarantee that their rows never sit in a table with anyone else's. Ricus Nortje describes running production on separate Supabase projects rather than a shared multi-tenant database, which removes the class of bug this entire article exists to prevent. The trade is real: one migration run per tenant, one set of keys per tenant, and cross-tenant reporting becomes a job rather than a query. That cost climbs with tenant count, so the split pays off for a handful of enterprise accounts and stops paying off somewhere in the low dozens of self-serve ones.

The four policies: SELECT, INSERT, UPDATE, DELETE

Read this table before the SQL. Two-thirds of tenant leaks come from a policy that used the wrong one of these two clauses.

USING looks at the row as it is. WITH CHECK looks at the row as it will be. The docs state the consequence for writes: "The with check expression ensures that any new row adheres to the policy constraints, so a user cannot create a row that belongs to someone else."

Here are the four, scoped to authenticated so an anonymous request never evaluates the body:

create policy "projects_select_own_org"
on public.projects
for select
to authenticated
using ( public.is_org_member(organization_id) );

create policy "projects_insert_own_org"
on public.projects
for insert
to authenticated
with check ( public.is_org_member(organization_id) );

create policy "projects_update_own_org"
on public.projects
for update
to authenticated
using      ( public.is_org_member(organization_id) )
with check ( public.is_org_member(organization_id) );

create policy "projects_delete_own_org"
on public.projects
for delete
to authenticated
using ( public.is_org_member(organization_id) );

The update policy is the one to look at twice. With using alone, Postgres checks that the row you are editing is yours, then writes whatever you sent. Send organization_id set to someone else's tenant and the row leaves your organization and lands in theirs. That is the relocate attack, and it passes every test suite that only checks reads. The author of Lomi, a kennel SaaS built on dual tenancy, reduced the whole lesson to one line: "Use with check and using together, never serve user data with the service-role key, and write the cross-tenant test that proves another tenant sees nothing."

organizations and memberships need their own policies, because RLS is per table and a locked projects table sitting next to a readable memberships table still tells an attacker who your customers are:

create policy "organizations_select_member"
on public.organizations
for select
to authenticated
using ( public.is_org_member(id) );

create policy "memberships_select_same_org"
on public.memberships
for select
to authenticated
using ( public.is_org_member(organization_id) );

-- only owners and admins change who belongs to the org
create policy "memberships_write_admin"
on public.memberships
for all
to authenticated
using (
  exists (
    select 1
    from public.memberships m
    where m.organization_id = memberships.organization_id
      and m.user_id = (select auth.uid())
      and m.role in ('owner', 'admin')
  )
)
with check (
  exists (
    select 1
    from public.memberships m
    where m.organization_id = memberships.organization_id
      and m.user_id = (select auth.uid())
      and m.role in ('owner', 'admin')
  )
);

That last one is written as a subquery rather than a helper call on purpose, to show the shape. If it throws 42P17, wrap it in a second security definer function the way is_org_member is wrapped, and call that instead.

One more rule for the whole set: never write using (true) to get past a broken screen. A widely-viewed FlutterFlow multi-tenant walkthrough uses it during the build, which is fine in a tutorial and fatal in a repository, because nobody remembers to take it out. Run the audit query from the top of this article after every build session and look for a policy body that is just true.

JWT claims or membership lookup

Two ways to answer "which tenants does this request belong to", and the argument between them is the one real disagreement in the published material.

Membership lookup is what is_org_member does: hit the memberships table on every policy evaluation. It is current the instant a role changes. Remove someone from an organization and their next query returns nothing. The cost is a lookup per statement, which an index and an initPlan make cheap but not free.

JWT claim puts the tenant id in the token, then reads it from the claims in the policy body:

create policy "projects_select_jwt_claim"
on public.projects
for select
to authenticated
using (
  organization_id = ((select auth.jwt()) -> 'app_metadata' ->> 'organization_id')::uuid
);

No table lookup at all, so it is the faster of the two. The cost is staleness: the claim is fixed at token issue, so a revoked membership stays valid until the token refreshes. Supabase documents the pattern under custom claims and RBAC with an auth hook that stamps the claim at sign-in.

The part that is not optional: the claim goes in raw_app_meta_data, never raw_user_meta_data. The docs are unambiguous about why. raw_user_meta_data "can be updated by the authenticated user" and "is not a good place to store authorization data"; raw_app_meta_data "cannot be updated by the user, so it's a good place to store authorization data". A tenant id or an admin flag in user metadata is a field your users can set themselves, which turns your policy into a self-service permission grant.

A practical split: membership lookup as the default, because correctness on a role change matters more than a millisecond; a JWT claim on the two or three highest-traffic tables once you have measured a problem, with a short token lifetime so revocation lands quickly.

If your auth lives outside Supabase, this decision is made for you. A r/Supabase thread on external providers states the failure mode plainly: "the moment you move auth to an external provider, every RLS policy that uses auth.uid() breaks" (r/Supabase). Supabase now supports third-party auth directly, and announced the Clerk integration as a way to "use Clerk as the auth provider while securely accessing your Supabase database using RLS". With that path, the tenant id arrives as a claim in the external token and your policies read it from auth.jwt().

Prove isolation before you invite customer two

You do not need a second live account to test this. Postgres will impersonate a user for the length of a transaction, which means the whole cross-tenant test runs in the SQL editor and rolls back when it finishes.

The short version, paste-and-run:

begin;

-- become a signed-in user from org A
set local role authenticated;
set local request.jwt.claims = '{"sub":"<alice-uuid>","role":"authenticated"}';

-- 1. can Alice see org B at all?
select count(*) from public.projects
where organization_id = '<org-b-uuid>';      -- expect 0

-- 2. can Alice plant a row in org B?
insert into public.projects (organization_id, title)
values ('<org-b-uuid>', 'planted');          -- expect ERROR 42501

-- 3. can Alice move her own row into org B?
update public.projects
set organization_id = '<org-b-uuid>'
where id = '<alice-project-uuid>';           -- expect ERROR 42501

-- 4. can Alice delete an org B row?
delete from public.projects
where organization_id = '<org-b-uuid>';      -- expect DELETE 0

rollback;

Note the asymmetry in step 3 and step 4, because it is the thing that fools people. A with check violation raises 42501, "new row violates row-level security policy", loudly. A using violation is silent: the row was simply never visible, so delete reports zero rows and update reports zero rows. Silence is a pass here. A test that only asserts "no error" passes against a table with no policies at all, so assert row counts, not the absence of an exception.

The durable version of the same thing is a pgTAP file under supabase/tests/database/, run with supabase test db in CI on every push:

begin;
select plan(6);

insert into public.organizations (id, name) values
  ('a0000000-0000-0000-0000-000000000001', 'Acme'),
  ('b0000000-0000-0000-0000-000000000002', 'Globex');

insert into public.memberships (organization_id, user_id, role) values
  ('a0000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-00000000000a', 'owner'),
  ('b0000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-00000000000b', 'owner');

insert into public.projects (id, organization_id, title) values
  ('11111111-1111-1111-1111-111111111111', 'a0000000-0000-0000-0000-000000000001', 'Acme roadmap'),
  ('22222222-2222-2222-2222-222222222222', 'b0000000-0000-0000-0000-000000000002', 'Globex roadmap');

set local role authenticated;
set local request.jwt.claims = '{"sub":"00000000-0000-0000-0000-00000000000a","role":"authenticated"}';

select results_eq(
  'select count(*)::int from public.projects',
  array[1],
  'SELECT is scoped: Alice sees only Acme rows'
);

select is_empty(
  $$ select 1 from public.projects
     where organization_id = 'b0000000-0000-0000-0000-000000000002' $$,
  'SELECT never returns Globex rows to Alice'
);

select throws_ok(
  $$ insert into public.projects (organization_id, title)
     values ('b0000000-0000-0000-0000-000000000002', 'planted') $$,
  '42501',
  null,
  'INSERT is scoped by WITH CHECK: Alice cannot plant a row in Globex'
);

select throws_ok(
  $$ update public.projects
        set organization_id = 'b0000000-0000-0000-0000-000000000002'
      where id = '11111111-1111-1111-1111-111111111111' $$,
  '42501',
  null,
  'UPDATE cannot relocate an Acme row into Globex'
);

select results_eq(
  $$ with removed as (
       delete from public.projects
       where organization_id = 'b0000000-0000-0000-0000-000000000002'
       returning 1
     ) select count(*)::int from removed $$,
  array[0],
  'DELETE cannot remove a Globex row'
);

set local role anon;
select is_empty(
  'select 1 from public.projects',
  'anon sees nothing'
);

select * from finish();
rollback;

Six assertions, one per access path, and the anon case at the end because unauthenticated access is the path nobody writes a test for. The seed users need to exist in auth.users first; in a local supabase start stack you can insert them directly in the test, in a hosted project create them once as fixtures.

If you would rather read a working version than write one, gastonlopezl/supabase-rls-multi-tenant is a repo whose README states the same two assertions in the same words: "SELECT is scoped: Alice sees only org A rows, never org B" and "INSERT is scoped by WITH CHECK: Alice cannot plant a row in org B". There is also a free CI tool from r/Supabase that runs cross-tenant probes against a live project. Either way, own the assertions. The tool can change; the six access paths do not.

Performance: initPlan, indexes, and TO authenticated

A membership lookup inside a policy runs for every row the planner considers, which is why RLS that felt instant at 500 rows gets slow at 500,000. Three fixes, in order of payoff.

Wrap the auth helpers in a subquery. Write (select auth.uid()), not auth.uid(). The docs explain the mechanism: the wrapped form "causes an initPlan" that lets Postgres "cache" the result per statement "rather than calling the function on each row". Same result, one evaluation instead of one per row. Apply it inside helper functions too, which is why is_org_member above uses it.

Index every column a policy touches. Two indexes carry most of the load here: projects(organization_id), because the policy filters on it, and memberships(user_id), because the composite primary key (organization_id, user_id) indexes that pair in that order and gives the planner nothing for a lookup by user_id alone. That second one is the index people miss, and the symptom is a sequential scan of the entire memberships table on every single query.

Scope every policy to authenticated. Without the clause, the policy body is evaluated for anon requests too, which spends work to reach a foregone conclusion. It also documents intent: a policy without to authenticated looks like an oversight to the next reader, including the AI reading your schema.

Two shape choices are worth knowing. exists (select 1 from memberships where ...) usually plans better than organization_id in (select organization_id from memberships where ...), because exists can stop at the first match; there is a walkthrough of that swap on YouTube if you want to watch the plan change. And AntStack published measured before-and-after numbers for RLS on Supabase if you need the case for spending an afternoon on this.

If your plan still looks wrong, explain analyze the query with set local role authenticated and the JWT claims set, exactly as in the deny test. RLS changes the plan, so measuring as postgres measures something you will never ship.

Footguns vibe-coded apps hit

These are the ones that appear in generated schemas specifically. The general list for AI-built apps lives in security gaps in vibe-coded apps; these six are the tenant-isolation subset.

Views bypass RLS by default. The docs: "Views bypass RLS by default because they are usually created with the postgres user." An AI asked to build a dashboard will reach for a view, and that view serves every tenant's rows to anyone who can select from it. On Postgres 15 and later, add with (security_invoker = true) so the view runs with the caller's permissions.

A security definer function without a pinned search_path. Same escape hatch as the view, reachable through any helper the builder writes for you. Every definer function needs set search_path = '' and fully qualified table names inside.

The service key in a file called server.ts. Hosted builders blur where code runs. If the file gets bundled for the browser, the key ships, and full access bypassing RLS ships with it. Check your built output for the key prefix before every deploy.

Storage objects. Files live in storage.objects and carry their own policies. A tenant-scoped app with tenant-scoped tables and a public bucket is still leaking, and Supabase has a troubleshooting note on hierarchical folder RLS about how awkward folder-per-tenant paths get at scale. Prefix object paths with the organization id and write policies against that prefix.

Realtime channels. Subscriptions are a second read path and need their own authorization, which Supabase documents under Realtime Authorization using policies on realtime.messages. A table locked down for select and open for realtime broadcasts changes to whoever is listening.

Table-owner bypass. RLS does not apply to the table owner. On a hosted project that rarely bites, but if you run migrations or jobs as the owning role, add alter table public.projects force row level security so the owner is subject to the policies too.

An 18-check vibe-code audit walks the wider app. For this list, the audit query at the top of this article plus a grep for service_role in your build output covers the first pass.

The prompt to give your AI builder

The usual failure is not malice, it is a model taking the shortest route to a screen that renders. A query returns nothing, so the model disables RLS or adds using (true), and the screen works. Lovable says this to its own users: RLS "lets you define rules like: 'Users can only read their own data'", and you should "think twice before asking Lovable to disable it" (@Lovable).

Put the rules in the context rather than in the correction. Paste this into your project instructions, CLAUDE.md, .cursorrules, or a Lovable Knowledge entry:

This app is multi-tenant. Tenants are rows in public.organizations. A user
belongs to one or more organizations through public.memberships
(organization_id, user_id, role) with primary key (organization_id, user_id).

Rules for every table you create in the public schema:

1. Add organization_id uuid not null references public.organizations(id).
2. Run "alter table <t> enable row level security" in the same migration as
   the create table.
3. Write four separate policies: for select, for insert, for update, for
   delete. Scope every one of them "to authenticated".
4. select, update and delete use using (public.is_org_member(organization_id)).
   insert uses with check (public.is_org_member(organization_id)).
   update uses BOTH using and with check, so a row cannot be relocated into
   another organization.
5. Call the helper public.is_org_member(uuid). It is a security definer
   function with "set search_path = ''". Do not inline a subquery against
   public.memberships inside a policy on public.memberships.
6. Wrap auth helpers as (select auth.uid()), never bare auth.uid().
7. Create an index on every organization_id column, and on
   memberships(user_id).
8. Never reference the service role key in any file that can be bundled for
   the browser. Server-only routes only.
9. Views must be created "with (security_invoker = true)".

If a query returns no rows and you think RLS is the cause: do NOT disable RLS
and do NOT write a policy whose body is "true". Show me the failing statement,
the policy, and the Postgres error code, and propose a narrower policy.

If a request cannot be satisfied without breaking rules 2, 4, 8 or 9, stop and
tell me which rule is in the way.

Rule 9 is the one that changes behaviour most, because it gives the model an approved action for the situation that otherwise produces the shortcut. Re-paste the block when you start a new session; context windows forget, and the next migration is the one that drops a table back to zero policies.

Copy-paste checklist

Run this before customer two gets an invite.

1. Audit query at the top of this article returns zero rows with rls_enabled = false in public.
2. Every tenant table has four policies, not one. Check pg_policies for the cmd column covering SELECT, INSERT, UPDATE and DELETE.
3. Every update policy has both using and with check.
4. No policy body is true. Query: select tablename, policyname, qual, with_check from pg_policies where schemaname = 'public'; and read every line.
5. Every policy is scoped to authenticated or narrower.
6. organization_id exists on every tenant table, including child tables, and is not null.
7. Indexes on every organization_id and on memberships(user_id).
8. All auth helper calls are wrapped as (select auth.uid()).
9. Every security definer function has set search_path = ''.
10. Every view is with (security_invoker = true).
11. Storage buckets are private, object paths are prefixed with the organization id, and storage.objects has policies.
12. Realtime has policies on realtime.messages if you broadcast tenant data.
13. The service role key appears nowhere in your built client bundle.
14. No tenant flag or admin flag lives in raw_user_meta_data.
15. The six-assertion deny test passes in CI, and fails when you delete a policy. Test the test.

Item 15 is the one to do first. A deny test that still passes after you drop a policy is measuring nothing.

FAQ

What does RLS actually do in Supabase? A policy is a rule Postgres applies to every query against the table. The docs describe it as "adding a WHERE clause to every query". Because it lives in the database, a client that omits a filter still gets nothing it should not have.

Is RLS enough for multi-tenant SaaS? Only with policies on all four operations that check membership rather than ownership. Supabase lists "Multi-tenant applications requiring data isolation" as a case RLS is valuable for; the isolation comes from the policies you write, not from the switch.

Why can the service role see every tenant? Because the grants table in the docs defines it that way: "Full access. It bypasses RLS, so keep it server-side." Anything holding that key is outside your policy model by design.

select works but another org can insert into my tenant. Why? insert is governed by with check, and most starter policies only cover select. From the docs: "The with check expression ensures that any new row adheres to the policy constraints, so a user cannot create a row that belongs to someone else."

Should the tenant id live in the JWT? It can, in raw_app_meta_data, which the docs say "cannot be updated by the user". Never in raw_user_meta_data, which the same page says the user can update and is "not a good place to store authorization data". Claims are faster; membership lookups are current when a role changes mid-session.

Why is my RLS slow? Most often an unwrapped helper. Wrapping causes an initPlan that lets Postgres "cache" the result per statement "rather than calling the function on each row". Then index organization_id and memberships(user_id), and scope policies to authenticated.

Do I still need .eq("organization_id") in the client? It is fine as a filter and worthless as a boundary. An r/Supabase thread on applying a condition to every query lands on the same conclusion: a client can omit the filter, so the rule belongs in the database. Keep it for the index, rely on RLS for safety.

Can a view leak other tenants? Yes. "Views bypass RLS by default because they are usually created with the postgres user." Add with (security_invoker = true) on Postgres 15 and later.

What does Supabase cost once I am ready for customer two? Per the pricing page: Free is "$ 0 /month", Pro is "$ 25 / month", Team is "$ 599 / month", and Enterprise is "Custom". Paid plans "include $10/mo in compute credits, enough to cover one Micro instance". RLS itself carries no separate line item.

Should I let my AI builder disable RLS so the UI works? No. Lovable's own guidance is to "think twice before asking Lovable to disable it", and the empty screen is expected: once RLS is on, no data comes through the API with a publishable key until policies exist. Write the policy the screen needs, then re-run the deny test.