RLS Policy Recursion in Tables with Self-Referencing Foreign Keys

Recursive Policy Evaluation

In PostgreSQL, Row Level Security (RLS) recursion occurs when a policy's USING or WITH CHECK clause performs a query against the same table it is protecting. In self-referencing tables (like a category tree with a parent_id), a policy that checks if a user has access to a parent will trigger the policy again for that parent, leading to an infinite loop.

Strategies to Prevent Infinite Loops

To break the cycle, you must prevent the policy from re-evaluating itself during the permission check. Here are the most effective structural methods:

1. Using a SECURITY DEFINER Function

The most common solution is to move the permission logic into a function marked with SECURITY DEFINER. Because the function runs with the privileges of the function creator (usually a superuser) rather than the user, it bypasses RLS for its internal query.

CREATE OR REPLACE FUNCTION check_category_access(cat_id UUID) 
RETURNS BOOLEAN AS $$
BEGIN
  -- This query does not trigger RLS because of SECURITY DEFINER
  RETURN EXISTS (
    SELECT 1 FROM categories 
    WHERE id = cat_id AND owner_id = auth.uid()
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Then, use the function in your policy:

CREATE POLICY category_access_policy ON categories SELECT USING (check_category_access(parent_id));

2. Session-Variable-Based Depth Tracking

If you want to avoid DEFINER functions, you can use a session-level variable to track recursion. This is more complex but keeps the logic within the RLS context.

• Set a variable when the application session starts: SET local.rls_depth = 0;
• In the policy, check if the variable is below a threshold: (current_setting('local.rls_depth')::int) < 10
• Increment the variable in a trigger, though this is difficult to manage purely within RLS.

3. Denormalized Paths

Instead of traversing the tree recursively, store the entire path in a column (e.g., an ltree type or a string like /id1/id2/id3). This allows you to check permissions using a single row match without joining or referencing the parent rows.

CREATE POLICY path_policy ON categories SELECT USING (path @> (SELECT path FROM categories WHERE id = some_root_id));

Assumptions and Uncertainty

These solutions assume you are using a standard PostgreSQL-based environment (like Supabase). It is important to note that SECURITY DEFINER functions must be created with a secure search_path to prevent search-path attacks. There is no built-in "depth limit" setting for RLS in PostgreSQL; it must be handled via the schema or logic patterns mentioned above.