Supabase RLS policies for multi-tenant apps (auth.uid() patterns)

Start from the single-user pattern

This is the policy shape Supabase documents for "each user sees their own rows" (Row Level Security):

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

• to authenticated stops the policy from running for anon at all. Supabase's performance guide says never to rely on auth.uid() alone to rule out anon (RLS performance).
• Signed out, auth.uid() is null, and null = user_id is never true, so the row is hidden, not exposed.
• (select auth.uid()) lets Postgres evaluate it once per statement (an initPlan) instead of once per row.

1. Model tenants explicitly

create table public.orgs (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

create table public.org_members (
  org_id uuid not null references public.orgs (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 ('member', 'admin')),
  primary key (org_id, user_id)
);
-- The primary key only indexes org_id (its leading column). Policies filter on user_id:
create index org_members_user_id_idx on public.org_members using btree (user_id);

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.orgs (id) on delete cascade,
  name text not null,
  created_by uuid not null default auth.uid() references auth.users (id)
);
create index projects_org_id_idx on public.projects using btree (org_id);

Supabase notes that a column is indexed only when it comes first in a btree index, so a composite primary key on (org_id, user_id) does not index user_id (Add indexes). Its performance guide measured an unindexed auth.uid() = user_id policy at 171 ms vs under 0.1 ms with the index on a 100K-row table (source).

2. Enable RLS and set minimal grants

alter table public.orgs enable row level security;
alter table public.org_members enable row level security;
alter table public.projects enable row level security;

revoke all on table public.orgs, public.org_members, public.projects from anon, authenticated;
grant select on table public.orgs to authenticated;
grant select, insert, update, delete on table public.org_members to authenticated;
grant select, insert, update, delete on table public.projects to authenticated;

With RLS on and no policy, client roles see no rows. Postgres calls this "default deny" (CREATE POLICY). Adding policies does not take grants back, so revoke first. anon gets nothing here. From Oct 30, 2026, new public tables on existing projects get no automatic grants at all (our guide, Supabase changelog). Users cannot insert into orgs. Create an org and its first admin membership server-side.

3. Add security definer helpers in a private schema

create schema if not exists private;

create function private.user_org_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select org_id from public.org_members
  where user_id = (select auth.uid())
$$;

create function private.user_admin_org_ids()
returns setof uuid
language sql
security definer
set search_path = ''
stable
as $$
  select org_id from public.org_members
  where user_id = (select auth.uid()) and role = 'admin'
$$;

revoke execute on function private.user_org_ids(), private.user_admin_org_ids() from public;
grant usage on schema private to authenticated;
grant execute on function private.user_org_ids(), private.user_admin_org_ids() to authenticated;

• Why a function: an org_members policy that queries org_members itself, or two tables whose policies read each other, makes Postgres raise 42P17 infinite recursion detected in policy (we reproduced the self-referencing case on Postgres 17). A security definer function owned by postgres (which has bypassrls on Supabase) reads membership as its owner and breaks the cycle (Avoid recursive policies).
• Why set search_path = '': without a pinned search path, a caller can shadow an unqualified name and run code with the owner's privileges (Supabase, PostgreSQL CREATE FUNCTION). Every name inside is schema-qualified.
• Why private: Supabase warns that a security definer function in an exposed schema is callable over the Data API with the creator's privileges. Never put one in a schema listed under "Exposed schemas".
• Why revoke from public: Postgres grants execute on new functions to PUBLIC by default (CREATE FUNCTION). Only authenticated needs it.

4. Write one policy per operation

Tenant-owned table (projects): members read, create and edit; only admins delete.

create policy "members read org projects" on public.projects
  for select to authenticated
  using ( org_id in (select private.user_org_ids()) );

create policy "members create projects in their orgs" on public.projects
  for insert to authenticated
  with check (
    org_id in (select private.user_org_ids())
    and created_by = (select auth.uid())
  );

create policy "members update org projects" on public.projects
  for update to authenticated
  using ( org_id in (select private.user_org_ids()) )
  with check ( org_id in (select private.user_org_ids()) );

create policy "admins delete org projects" on public.projects
  for delete to authenticated
  using ( org_id in (select private.user_admin_org_ids()) );

Membership and org tables: members see their org and co-members; only admins change membership.

create policy "members read their orgs" on public.orgs
  for select to authenticated
  using ( id in (select private.user_org_ids()) );

create policy "members read co-members" on public.org_members
  for select to authenticated
  using ( org_id in (select private.user_org_ids()) );

create policy "admins add members" on public.org_members
  for insert to authenticated
  with check ( org_id in (select private.user_admin_org_ids()) );

create policy "admins change member roles" on public.org_members
  for update to authenticated
  using ( org_id in (select private.user_admin_org_ids()) )
  with check ( org_id in (select private.user_admin_org_ids()) );

create policy "admins remove members" on public.org_members
  for delete to authenticated
  using ( org_id in (select private.user_admin_org_ids()) );

• with check on update is the tenant-hop guard. using picks which rows you may touch; with check validates the new row. Without it, Postgres reuses using, but writing both makes the intent explicit. In our test, moving a project to another org failed with "new row violates row-level security policy".
• Updates also need a select policy. Postgres applies SELECT policies to an UPDATE that reads columns (a WHERE or RETURNING), and Supabase's docs say an update needs a matching select policy (PostgreSQL, Supabase).
• Permissive policies are OR-ed. One stray using (true) policy for the same command opens the whole table, whatever the others say. Use as restrictive only to narrow access further (restrictive policies are AND-ed) (CREATE POLICY).
• Prefer org_id in (select …) over auth.uid() in (select user_id … where team_id = table.team_id). Supabase measured 9,000 ms vs 20 ms for that rewrite (RLS performance).

Alternative: tenant IDs in app_metadata

If you set a user's org IDs in app_metadata from the server or an auth hook, the policy can read them from the JWT without a lookup:

create policy "members read org projects (jwt claim)" on public.projects
  for select to authenticated
  using (
    org_id in (
      select jsonb_array_elements_text(
        (select auth.jwt()) -> 'app_metadata' -> 'org_ids'
      )::uuid
    )
  );

• app_metadata, never user_metadata. Supabase: raw_user_meta_data "can be updated by the authenticated user"; raw_app_meta_data "cannot be updated by the user, so it's a good place to store authorization data" (auth.jwt()).
• Claims are stale until refresh. Removing someone from an org is not reflected in auth.jwt() until their JWT is refreshed. Use the membership table when access must end immediately.
• Keep the JWT small. Supabase notes some browsers cap each cookie at 4096 bytes, which matters if you use cookie-based auth.
• For roles inside a tenant, Supabase's custom claims & RBAC guide adds a claim with a Custom Access Token Auth Hook.

5. Test allow and deny cases

Supabase's procedure is a pgTAP file per table under supabase/tests/, run with supabase test db. For shared tables, assert that a member who is not the owner can do what the policies allow and a non-member cannot (Secure a table with RLS). Cases worth one assertion each:

1. anon: every operation raises 42501 (no grant).
2. Member of org A: reads A's rows only, can insert into A, cannot insert into B (42501).
3. Member of A: updating a row's org_id to B raises 42501; updating B's row matches zero rows.
4. Non-admin member: delete matches zero rows; admin delete returns the row.
5. Non-admin: cannot add members or promote themselves.

Supabase's tip: a using denial raises nothing and matches zero rows, so assert with returning and then check the target row is intact. Never prove an allowed write with lives_ok.

Mistakes we see in AI-generated multi-tenant schemas

• using (true) added "temporarily" to silence an error.
• No to authenticated, so policies run for anon.
• Org ID or role read from user_metadata.
• security definer helper in public without set search_path = ''.
• Insert policy checks membership but update has no with check.
• Views over tenant tables without security_invoker = true (Postgres 15+). Views bypass RLS by default.
• No index on org_members(user_id) or projects(org_id).

To list policies in your project, see How to check if Supabase RLS is enabled. For the full pre-launch list, see the RLS checklist.

Copy-paste fix prompt

Audit my Supabase schema for multi-tenant isolation. Every tenant-owned table must have org_id, RLS enabled, minimal grants (nothing for anon unless public), and one policy per operation scoped "to authenticated" that checks org_id in (select private.user_org_ids()). Put security definer helpers in a non-exposed private schema with set search_path = '', revoke execute from public. Use with check on insert and update. Never use user_metadata for authorization. Add indexes on org_members(user_id) and every org_id. Write pgTAP tests for member, non-member, admin and anon. Output SQL migrations.

FAQ

Should I put the tenant or org ID in user_metadata?

No. Supabase warns that user_metadata (raw_user_meta_data) can be changed by the signed-in user, so it must not drive authorization. Use app_metadata, which users cannot update, or a membership table.

Why wrap auth.uid() in a select inside a policy?

Supabase documents that (select auth.uid()) makes Postgres run an initPlan and cache the value per statement instead of calling the function on every row. Only do this when the result does not depend on the row.

Why does my membership policy fail with infinite recursion (42P17)?

Two tables whose policies read each other never resolve. Supabase's fix is a security definer function, in a schema that is not exposed through the Data API, that reads the membership table as its owner.

Is a membership table or a JWT claim better for tenant checks?

A membership table takes effect on the next request. A JWT app_metadata claim avoids a lookup, but Supabase notes the JWT is not updated until it is refreshed, so a removed member keeps access until then.

Do I still need grants if RLS is on?

Yes. Grants decide whether a role can run an operation on the table at all and policies decide which rows. Supabase says adding policies does not remove existing grants, so revoke what anon should not have.