Why not query memberships inside its own policy?

Because PostgreSQL has to evaluate that policy to read the rows the policy itself reads, and raises 42P17. The membership policy on this page compares a column with the signed-in user's id and never loops.

Does a new member see the tenant's documents right away?

Yes. The lookup runs on every request, so as soon as a membership row exists the member reads the tenant's rows. A document written by one member is visible to another member of the same tenant on the next read.

Should the tenant id live in the JWT instead?

Supabase also supports custom claims, but this page proves only the membership-table pattern, where the database is the source of truth and a change in membership takes effect on the next request.

Supabase · multi-tenant row level security

One membership table, three policies, no leak between tenants.

Multi-tenant row level security in Supabase means every row carries a tenant id and a memberships table says which users belong to which tenant. Three policies read that table: members see their own memberships, and they read and write only documents whose tenant they belong to. Tested on a real PostgreSQL with two tenants.

the shape

What a tenant is, to the database.

What is multi-tenant RLS? It is row level security where the row's owner is a tenant rather than a user, so the policy cannot compare auth.uid() with the row directly. It has to ask a second table whether the signed-in user belongs to the row's tenant. That second table is the whole pattern. With row level security on and no policy, nothing is visible at all; PostgreSQL: "If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified." Proven: before any policy, a member sees 0 documents and no error.

public.tenants      (id, name)
public.memberships  (tenant_id, user_id, role)      the source of truth
public.documents    (id, tenant_id, title)          every row carries its tenant

the policies

Three policies. Apply them in this order.

Members see their own membership rows

A direct comparison with the signed-in user, and nothing else. This is the policy that must not query its own table: a membership policy that reads memberships to decide who may read memberships is the cycle that raises 42P17. Written as a column comparison it cannot loop. Proven: a member sees exactly their own row (1) and no recursion error.

Members read documents of the tenants they belong to

The documents policy is the one that asks the memberships table. Following Supabase's guide, auth.uid() is wrapped as (select auth.uid()). Proven: a member of tenant one reads exactly tenant one's document, a member of tenant two reads exactly tenant two's, and a request with no session reads nothing.

Members write only into their own tenants

The insert policy uses the same membership lookup as a WITH CHECK, so the client cannot choose a tenant it does not belong to. Proven: a member of tenant one is refused when inserting a document into tenant two (42501), succeeds into tenant one, and the new document is visible to another member of that tenant.

create policy "members see their own memberships"
on public.memberships for select to authenticated
using (user_id = (select auth.uid()));

create policy "members read their tenants' documents"
on public.documents for select to authenticated
using (
  tenant_id in (
    select m.tenant_id from public.memberships m
    where m.user_id = (select auth.uid())
  )
);

create policy "members write their tenants' documents"
on public.documents for insert to authenticated
with check (
  tenant_id in (
    select m.tenant_id from public.memberships m
    where m.user_id = (select auth.uid())
  )
);

the function variant

When the lookup should not run as the caller.

If the memberships table is itself protected in ways that would hide rows from the documents lookup, or you want one place that answers "which tenants am I in", move the lookup into a function in a private schema. Supabase explains the mechanism: a "security definer" function runs using the same role that created it, so it reads memberships with the owner's rights, and an empty search path plus a schema the API cannot reach keeps it from being called directly. This is the same shape as the 42P17 fix. Proven: with the plain read policy removed and only this one left, a member of tenant two still reads exactly tenant two's document.

create schema if not exists private;

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

revoke all on function private.my_tenant_ids() from public;
grant usage on schema private to authenticated;
grant execute on function private.my_tenant_ids() to authenticated;

create policy "members read their tenants' documents (function)"
on public.documents for select to authenticated
using (tenant_id in (select private.my_tenant_ids()));

what not to do

Two shortcuts that let a tenant read another.

Do not trust a tenant id the client sends. A documents policy of with check (true), or a lookup keyed on a tenant id from the request body instead of the signed-in user, lets any signed-in user write into any tenant. The membership lookup above is keyed on auth.uid(), which the client cannot forge.

Do not let the memberships policy query memberships. "Members of my teams can see the team's members" written as a subquery on the same table raises 42P17, infinite recursion detected in policy. Keep the membership policy a column comparison, and put any wider rule in the function variant.

questions

Straight answers.

Why not query memberships inside its own policy?: Because PostgreSQL has to evaluate that policy to read the rows the policy itself reads, and raises 42P17. The membership policy on this page compares a column with the signed-in user's id and never loops; the tested fix for an existing loop is on the 42P17 page.
Does a new member see the tenant's documents right away?: Yes. The lookup runs on every request, so as soon as a membership row exists the member reads the tenant's rows. Proven: a document written by one member is visible to another member of the same tenant on the next read.
Should the tenant id live in the JWT instead?: Supabase also supports custom claims, but this page proves only the membership-table pattern, where the database is the source of truth and a change in membership takes effect on the next request. If you use a claim, the same three policies apply with the claim in place of the lookup, and the page's proofs do not cover that.

One policy is rarely the only one. Check every table.

Our free auditor reads your policies and lists the holes worst first, including a tenant table with row level security switched off. It runs in your own SQL editor in about a minute and never touches your rows.

Get the free RLS auditor

Also seeing 42501, new row violates row-level security policy, 42P17, infinite recursion detected in policy, or auth.uid() null on every request?

Tenants still leaking after this? Paste the policy and the error into the free diagnosis at rescue.ticassociation.com and get the cause and the fix path back in about a minute.