跳到正文
DEV Community· AppFirstAid·· 2 小時前AI 評分59

Supabase「infinite recursion detected in policy for relation」(42P17):如何找出循環並修復,而不關閉 RLS

Supabase "infinite recursion detected in policy for relation" (42P17): find the loop and break it without turning RLS off

AI 導讀

AppFirstAid 的指南説明,Supabase 42P17 錯誤源於兩條互相查詢的 RLS 政策,並介紹如何透過共用 security definer 函數解除循環而不關閉 RLS。

正文

AppFirstAid

Originally published on AppFirstAid.

Short answer: error 42P17 means two of your Supabase row-level security policies are checking each other. A policy on one table reads a second table, and that table's policy reads the first one back, so the check never finishes and Postgres stops the query. It usually shows up right after you (or your AI builder) add sharing, teams, members or admin roles. Supabase's fix is to move the lookup into a security definer function that both policies call. Don't turn RLS off to make it go away.

Signs this is your problem

  • The error says infinite recursion detected in policy for relation plus a table name, or the code is 42P17.
  • It started after adding sharing, teams, workspaces, members, invites or an admin role.
  • Pages that use those tables fail for everyone, logged in or not, while other pages still work.

Why it happens

RLS runs a policy every time someone reads a table. Supabase's docs describe the loop: a policy on lists checks list_members to see who a list is shared with, and a policy on list_members checks lists to see who owns it. Reading either table triggers the other's policy, which triggers the first again. A policy that looks up roles in the very table it protects (an "is admin" check on profiles inside a profiles policy) loops the same way.

How to find the loop

  1. Copy the table name from the error message.
  2. In the Supabase dashboard, read that table's policies and note every other table they mention (select ... from or exists (...)).
  3. Open those tables' policies. If one mentions the first table again, that's the loop.

The fix: one security definer function both policies call

The shape of the SQL, adapted from Supabase's example (rename to your tables):

create schema if not exists private;

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

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

create policy "members read lists" on lists for select
  to authenticated
  using ( id in (select private.user_list_ids()) );

create policy "members read membership" on list_members for select
  to authenticated
  using ( list_id in (select private.user_list_ids()) );

Drop or replace the old looping policies first, or both versions still apply.

Using Lovable, Bolt or an AI editor? The full guide has a copy-paste prompt that asks for exactly this and makes it show you the SQL before running it.

What not to do

  • Don't disable RLS. Anyone with your public key may then read or change every row.
  • Don't put the function in public or any schema exposed to the API. Supabase warns a security definer function there can be called over the Data API with its creator's privileges.
  • Don't skip set search_path = '', and write table names with their schema.
  • Don't switch to the service role key. It skips RLS entirely and must never be in the browser.

Check that it worked

  • A test user who is a member (not owner) of a shared item should see it.
  • A second test user who isn't a member should see nothing of it.
  • Logged out, nothing private should show.

Fixing policies by hand? AppFirstAid's free check looks at your live site from the outside, no sign-up. It flags leaked keys, files left in public and broken pages, and on a Supabase app it lists the tables your site's code queries, so you know which policies to review. It never logs in or reads your data.

來源:DEV Community · dev.to