Fixing "infinite recursion detected in policy" in Supabase (42P17)

infinite recursion detected in policy for relation code: 42P17 A row-level security policy on a table runs a query against the same table, or against a table whose policy queries back. To check the policy, Postgres needs the policy again, so it stops with 42P17. The fix I use: move the membership lookup into a security definer function in a schema the API does not expose, and call that function from the policy. It turns up in Lovable, Bolt and Supabase apps. In our work, team, organisation and role models are where AI builders most often leave data open or loop.

关系策略中检测到无限递归，代码：42P17。表上的行级安全策略对同一张表运行了查询，或者对另一张策略会反向查询的表进行了查询。为了检查该策略，Postgres 需要再次执行该策略，因此它会以 42P17 错误停止。我使用的解决方法是：将成员资格查找移动到 API 不公开的模式（schema）中的 security definer 函数中，并从策略中调用该函数。这种情况常出现在 Lovable、Bolt 和 Supabase 应用中。在我们的工作中，团队、组织和角色模型是 AI 构建者最容易导致数据泄露或循环引用的地方。

Why it happens A membership table checks itself. A policy on team_members asks “is this user a member of this team?” by selecting from team_members, which triggers the same policy. Two tables point at each other. projects checks team_members, and team_members checks projects. Each policy needs the other first. A roles table guards itself. A policy on profiles reads profiles.role to decide who is an admin.

为什么会发生这种情况？成员资格表检查自身。team_members 上的策略通过从 team_members 中选择数据来询问“该用户是否为该团队成员？”，这会触发相同的策略。两张表相互指向。projects 检查 team_members，而 team_members 检查 projects。每个策略都需要先执行另一个。角色表保护自身。profiles 上的策略读取 profiles.role 以决定谁是管理员。

Find the cause Note the relation named in the error: that is the table whose policy loops. Read every policy on that table and look for a subquery that selects from the same table. Follow any subquery to another table and read that table’s policies. A cycle of two tables loops the same way.

查找原因：注意错误中提到的关系名称：这就是策略发生循环的表。阅读该表上的每一条策略，寻找从同一张表中进行选择的子查询。跟随任何子查询到另一张表，并阅读该表的策略。两张表的循环以同样的方式发生。

Fix 1: look the membership up in a security definer function Create a private schema that is not listed under Exposed schemas in the API settings. Create the function as security definer with an empty search_path, and schema-qualify every name inside it. Replace the subquery in the policy with a call to the function.

解决方法 1：在 security definer 函数中查找成员资格。创建一个未在 API 设置的“公开模式”（Exposed schemas）中列出的私有模式。将该函数创建为 security definer，设置为空的 search_path，并对其中的每个名称进行模式限定。将策略中的子查询替换为对该函数的调用。

Fix 2: keep admin flags out of a table users can edit Store roles in a separate table that only the server writes, or in app_metadata on the user, which users cannot change. Read the role through the same kind of function, never from a column the user can update.

解决方法 2：不要将管理员标志放在用户可编辑的表中。将角色存储在仅由服务器写入的单独表中，或者存储在用户无法更改的用户的 app_metadata 中。通过相同类型的函数读取角色，永远不要从用户可以更新的列中读取。

Check the fix worked Run the failing query again as a signed-in member: it returns rows. Run it as a user who is not a member: it returns no rows, without errors. Confirm the private schema is not in the API’s exposed schemas, so the function cannot be called directly.

检查修复是否有效：以已登录成员身份再次运行失败的查询：它返回了行。以非成员用户身份运行：它不返回行，且没有错误。确认私有模式不在 API 的公开模式中，因此该函数无法被直接调用。

Stop it coming back Draw which policy reads which table before you write them. Any cycle will loop. Put every cross-table check behind one small function per question (is member, is admin).

防止问题复发：在编写策略之前，画出哪个策略读取哪张表。任何循环都会导致死循环。将每个跨表检查放在每个问题（是否为成员、是否为管理员）对应的一个小函数之后。

Quick answers Is a security definer function safe? Yes, when it lives in a schema the API does not expose, sets an empty search path, qualifies every name and returns only a yes or no. It runs as its owner, so keep it small. Why not just disable RLS on the membership table? Then anyone with your publishable key can list every team and its members. The function keeps the table protected.

快速问答：security definer 函数安全吗？是的，当它位于 API 不公开的模式中、设置了空搜索路径、限定了每个名称且仅返回“是”或“否”时，它是安全的。它以其所有者的身份运行，因此请保持其简洁。为什么不直接禁用成员资格表上的 RLS？那样的话，任何拥有你可发布密钥的人都可以列出每个团队及其成员。该函数使表保持受保护状态。