The Supabase RLS gotcha nobody warns you about: infinite recursion in multi-tenant policies

You're building teams/workspaces on Supabase. A row belongs to a workspace, and a user can touch it if they're a member. Simple enough — until your policies start throwing infinite recursion and you have no idea why.

Here's the trap and the pattern that avoids it.

The setup

A membership table, with RLS on it too (of course):

create table workspace_members (
  workspace_id uuid references workspaces,
  user_id uuid references auth.users,
  primary key (workspace_id, user_id)
);
alter table workspace_members enable row level security;

create policy "read own memberships" on workspace_members
  for select using (user_id = (select auth.uid()));

The trap

Now you scope a tenant table by asking "which workspaces is this user in?" — by querying workspace_members inside the policy:

-- ⚠️ this can recurse
create policy "members read projects" on projects
  for select using (
    workspace_id in (
      select workspace_id from workspace_members
      where user_id = (select auth.uid())
    )
  );

The policy on projects queries workspace_members, which has its own RLS policy, which re-evaluates… you've built a loop.

The fix: a security-definer function

Resolve membership with a function that runs with the definer's rights, so reading workspace_members doesn't re-trigger RLS. The policy becomes a plain IN check — no recursion:

create or replace function public.user_workspace_ids()
  returns setof uuid
  language sql
  security definer
  set search_path = ''       -- pin it: this is the safety bit
as $$
  select workspace_id from public.workspace_members
  where user_id = auth.uid();
$$;

create policy "members read projects" on projects
  for select using (
    workspace_id in (select public.user_workspace_ids())
  );

Why it's safe: the function is read-only, returns only the caller's own workspace ids, and search_path = '' prevents search-path hijacking (the classic SECURITY DEFINER footgun). It never exposes another user's memberships.

Do this for every tenant table (reads and writes — remember WITH CHECK on inserts), and every row is scoped to the caller's workspaces by construction.

Full pattern — including the write policies and a tenant-isolation test — here: Multi-tenant RLS with workspaces in Supabase →

🦭