Technical Analysis: Cyclic RLS Policy / Infinite Recursion Bug in PostgreSQL

The Core Problem

Row-Level Security (RLS) policies in PostgreSQL are implemented through the query rewriter. When a table has an RLS policy, the rewriter appends the policy's qualifying expression (a WHERE clause) to any query accessing that table. The problem arises when RLS policies create cyclic dependencies — for example, when Table A's RLS policy references Table B, and Table B's RLS policy references Table A (or more subtly, when a table's own RLS policy references itself).

When the rewriter encounters such a cycle, it attempts to recursively expand the policy expressions indefinitely, eventually hitting a recursion limit and throwing an "infinite recursion" error. This is a known limitation acknowledged in PostgreSQL's own regression test suite (specifically in the rowsecurity test file), where test cases explicitly demonstrate and expect this error behavior.

Why This Matters Architecturally

The rewriter's approach to RLS is fundamentally substitution-based: it inlines policy expressions into the query tree. This design choice means:

1. No memoization or cycle detection at the policy expansion level — the rewriter doesn't track which policies it has already expanded in the current expansion path.
2. The workaround is SECURITY DEFINER functions — wrapping the subquery in a SECURITY DEFINER UDF causes the inner query to execute under a different security context (typically the function owner), bypassing RLS expansion for that inner reference and breaking the cycle.
3. This limits expressiveness — legitimate use cases where authorization decisions are inherently mutual or self-referential cannot be expressed directly in RLS policies without the SECURITY DEFINER escape hatch.

Technical Depth: The Rewriter's Role

The rewriter (src/backend/rewrite/) processes RLS policies in rewrite/rowsecurity.c. The function get_row_security_policies() retrieves applicable policies and attaches them as security quals. When these quals reference other tables that also have RLS policies, the rewriter recursively processes those references. Without cycle detection or a fixed-point evaluation strategy, mutual references lead to unbounded recursion.

Potential Solution Approaches (Not Proposed in Thread, but Architecturally Relevant)

1. Cycle detection in the rewriter: Track the expansion path and detect when a table+policy combination is being re-expanded. This could error more gracefully or potentially allow a fixed-point semantics.

2. Materialization boundaries: Automatically introduce optimization barriers (similar to what SECURITY DEFINER achieves) when cycles are detected, evaluating one side of the cycle under a restricted context.

3. Iterative fixed-point evaluation: Borrow from recursive CTE semantics to iteratively evaluate cyclic policies until a stable result is reached. This would be a major architectural change.

4. Subquery isolation: Convert cyclic policy references into subqueries that are evaluated independently without further RLS expansion, similar to how security_barrier views work.

Each approach has significant tradeoffs around correctness (can rows "leak" during iteration?), performance (materialization costs), and semantic clarity (what does a cyclic policy mean?).

Assessment of the Thread

This thread is in its earliest stage — essentially a request for guidance rather than a technical proposal. No patch has been submitted, no concrete solution is proposed, and no use cases are demonstrated. Ashutosh Bapat's response provides a clear roadmap for what would be needed to advance this as a community contribution:

1. Motivation: Real-world use cases demonstrating why the SECURITY DEFINER workaround is insufficient
2. Competitive analysis: How other DBMSes (Oracle VPD, SQL Server RLS, etc.) handle equivalent scenarios
3. Root cause understanding: Demonstrating deep comprehension of why the restriction exists
4. Hazard analysis: Security implications of any proposed fix (RLS is a security feature — any change must not introduce information leakage)
5. Concrete proposal: A specific technical approach with implementation details

Key Concerns

Any fix to this problem must be evaluated against strict security guarantees:

• RLS policies must never allow unauthorized row access during intermediate evaluation steps
• The fix must not introduce timing side-channels or observable differences that leak row existence
• Performance characteristics of the solution must be acceptable for the common (non-cyclic) case