RLS Policy Recursion in Tables with Self-Referencing Foreign Keys

Recursive Policy Evaluation

Supabase leverages PostgreSQL Row Level Security (RLS) to enforce data access via auth.uid(). In schemas where a table contains a self-referencing foreign key—such as a categories table with a parent_id column—policies often need to check the permissions of the parent record to determine access to the child record.

Constraint and Behavior

When a policy on a table references the same table within its USING or WITH CHECK clause, PostgreSQL may trigger a recursive loop. This occurs if the policy evaluation for a row requires evaluating the policy for another row in the same table, which in turn triggers the original policy.

While the service_role key bypasses these checks entirely, standard authenticated users are subject to these evaluation cycles, which can lead to query failure or performance degradation in deep hierarchies.

• How can recursive RLS policies be structured to prevent infinite loops in self-referencing tables?
• Is there a documented method to limit the depth of policy recursion for hierarchical data?