Supabase RLS Infinite Recursion Bug in Multi-Tenant Apps

The Supabase RLS Gotcha Nobody Warns You About: Infinite Recursion in Multi-Tenant Policies

Building multi-tenant applications on Supabase often involves Row Level Security (RLS) to ensure users can only access data within their designated workspaces or teams. A seemingly straightforward setup for managing workspace memberships, however, can unexpectedly trigger infinite recursion errors in your RLS policies. This isn't a bug in Supabase itself, but a subtle trap in how certain RLS policies interact, particularly when the membership table also has RLS enabled.

Imagine you have a workspaces table and a workspace_members table. A row in any table related to a workspace should only be accessible by users who are members of that workspace. This is a standard pattern. You create an RLS policy on your main data table (e.g., projects) that checks if the current user ID matches the user_id in the related workspace_members table for that project's workspace. The problem arises when the workspace_members table itself has RLS enabled, and its policies also reference the users table or other related data in a way that creates a circular dependency.

Understanding the Recursion Trap

Let's break down a typical setup that leads to this issue. Consider a projects table with a foreign key to workspaces, and a workspace_members table linking users to workspaces.

create table workspaces (
  id uuid primary key default gen_random_uuid(),
  name text
);

create table workspace_members (
  workspace_id uuid not null references workspaces(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  primary key (workspace_id, user_id)
);

-- Enable RLS for workspaces and workspace_members tables
alter table workspaces enable row level security;
alter table workspace_members enable row level security;

Now, let's add a policy to workspace_members. A common requirement is that a user should only see their own membership rows. This policy might look something like this:

-- Policy on workspace_members table
create policy "Users can see their own membership" on workspace_members
for select using (auth.uid() = user_id);

This policy is straightforward. It ensures that when a user queries the workspace_members table, they only retrieve rows where their authenticated user ID (obtained via auth.uid()) matches the user_id column in the table.

The trouble begins when you try to secure a related table, say projects, which belongs to a workspace. You want users to access projects only if they are members of the project's workspace. A naive RLS policy on the projects table might look like this:

-- Policy on projects table
create policy "Users can see projects in their workspaces" on projects
for select using (
  exists (
    select 1
    from workspace_members
    where workspace_members.workspace_id = projects.workspace_id
      and workspace_members.user_id = auth.uid()
  )
);

This policy checks if the current authenticated user exists as a member in the workspace_members table for the specific workspace the project belongs to. On the surface, this seems correct. However, when Supabase evaluates this policy for a SELECT operation on the projects table, it needs to verify that the user is a member. To do this, it might internally query the workspace_members table. Because RLS is enabled on workspace_members, Supabase must then evaluate the RLS policies on workspace_members before allowing access to that data.

If the RLS policies on workspace_members, or tables it depends on (like auth.users or even indirectly back to workspaces), involve conditions that re-evaluate the original query's context or involve joins that lead back to the projects table or its related data in a recursive manner, Supabase can enter an infinite loop. This often happens if a policy on workspace_members implicitly or explicitly requires checking permissions that themselves depend on the projects table or other data that is ultimately gated by the initial policy.

The Root Cause: Policy Evaluation Chain

The issue is not that the workspace_members table itself is inherently problematic, but how its RLS policies are evaluated in conjunction with policies on other tables. When Supabase executes a query, it doesn't just check one policy; it evaluates a chain of policies based on table relationships and the RLS rules in place. If this chain creates a circular dependency where Policy A needs to check data protected by Policy B, and Policy B needs to check data protected by Policy A (or a path that leads back to A), you get recursion.

The specific trigger is often when a policy on a table like projects requires checking membership via workspace_members, and a policy on workspace_members (or a table it references) needs to check user context that, in turn, requires re-evaluating access to projects or related workspace data. This creates a loop: project access requires membership check, membership check requires user context, user context check indirectly requires project access check. The database can't resolve this and throws an error, often manifesting as a timeout or a specific