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.
supabase
postgresql