The Morning Françoise Sees Zero Rows, Again
It’s a Tuesday in April 2026. I’ve just added the agent_readonly role to the authenticated membership — a one-liner, meant to share a GRANT for a reporting job. First SELECT on cours, Sentry receives infinite recursion detected in policy for relation "user_roles", code 42P17. From the office next door, Françoise is already on the phone with the Maisons-Laffitte branch: "So they can't see anything over there — is that normal?" Foreman tone, not really a question. I read the error on my screen. This is the third time this month that a simple GRANT has broken everything. The first incident was a default GRANT ALL PRIVILEGES on a table that was supposed to be read-only. The second involved a missing ON CONFLICT DO UPDATE clause in a migration, which led to duplicate entries and a cascade of errors. This time, it’s Row Level Security (RLS) policies. Specifically, RLS policies that seem to be calling themselves, creating an infinite loop.
PostgreSQL's RLS is a powerful feature. It allows you to define policies that control access to rows within a table based on user roles or other conditions. For developers building multi-tenant applications or systems with complex user permissions, RLS can be a lifesaver, enforcing security directly at the database level. However, as I was about to discover, it can also be a labyrinth of recursive errors if not handled with extreme care. The error message infinite recursion detected in policy for relation "user_roles" is PostgreSQL’s way of telling you that a policy is triggering another policy, which in turn triggers the first one, creating an endless cycle. This usually happens when a policy on one table references another table, and a policy on that second table references the first table back, or when a policy incorrectly references itself indirectly through a chain of views or functions.
My initial thought was that I had made a simple mistake in the policy definition. Perhaps I had accidentally included the user_roles relation in a policy that was meant to operate on a different table. I spent hours meticulously checking each policy, looking for the offending line. I reviewed the GRANT statements, the table definitions, and the existing roles. The problem was that the error message, while specific about the relation involved (user_roles), didn’t pinpoint the exact policy or the exact condition that was causing the recursion. PostgreSQL doesn’t always give you the full call stack for RLS policy evaluation, making debugging a process of elimination. This is where the real pain began. It felt like trying to find a specific needle in a haystack, but the haystack was also on fire and occasionally changed shape.
The Root Cause: Implicit Dependencies and Default Grants
The third incident, the RLS recursion, was particularly insidious. It stemmed from a seemingly innocuous change: adding the agent_readonly role to the authenticated membership. This meant that any user who was authenticated could now assume the agent_readonly role. My intention was to grant read-only access to certain reporting tables for authenticated users. However, the user_roles table, which is central to managing permissions, also had an RLS policy. This policy was designed to ensure that users could only see their own roles and perhaps roles assigned to them. When the system tried to evaluate the SELECT query on the cours table, it needed to check the permissions of the authenticated user. To do this, it evaluated the RLS policy on user_roles. This policy, in its effort to restrict access, ended up triggering a check that, due to the new membership, led back to evaluating access for the agent_readonly role, which in turn required checking the user_roles table again. The cycle was complete.
The core issue wasn't just a misconfigured policy; it was the implicit dependencies created by the default GRANT statements and role memberships. When you grant a role membership, you're not just granting permissions directly; you're potentially altering how other policies, which might have been designed with a more restricted set of assumptions, will behave. The error felt like a betrayal of the principle of least privilege, where a simple addition of a role unexpectedly widened the attack surface or, in this case, the recursion surface. It’s like giving someone a key to the mailroom (the agent_readonly role) expecting them only to pick up their own mail, but they can now also access the master key cabinet (the user_roles table) because it’s in the mailroom, and from there, they can access everything.
Giving Up on Policies, Betting on JWT Custom Claims
After days of debugging, tracing query plans, and staring at the PostgreSQL documentation, I reached a breaking point. The RLS policies were proving to be brittle and difficult to manage, especially in a rapidly evolving application. Every new feature, every change in user roles, risked introducing another recursive loop or an unintended access grant. The mental overhead of ensuring all policies were correctly defined and didn't conflict with each other was immense. It felt like building a house of cards on a shaky foundation.
I started looking for alternatives. Could I enforce permissions at the application layer? Yes, but that means duplicating logic and potentially creating inconsistencies between the backend and the database. Could I use a different database? Perhaps, but migrating a large PostgreSQL database is a significant undertaking. Then, I remembered a technique I’d seen discussed for managing authorization in microservices: using JSON Web Tokens (JWTs) with custom claims.
The idea is simple: instead of relying on PostgreSQL's RLS policies to determine what a user can access, we embed the necessary authorization information directly into the JWT issued to the user upon authentication. This information could include user IDs, organization IDs, specific permissions (e.g., can_edit_course, is_admin), or even granular access rights. When a request comes into the backend API, the backend verifies the JWT and then uses the claims within it to authorize the request. For database operations, instead of complex RLS policies, we can use simpler, more direct checks within the application code or, crucially, use these claims to dynamically generate SQL queries that are inherently safe.
Implementing the JWT Custom Claims Hook
The shift involved a significant architectural change. The application backend now plays a much more central role in authorization. Upon successful user authentication, a JWT is generated. This JWT contains a set of custom claims that define the user's identity, their roles, and their permissions within the system. For example, a claim might look like "permissions": ["course:read", "course:write", "user:read"] or "org_id": "abc-123".
When the backend receives a request, it first validates the JWT's signature and expiry. If valid, it extracts the custom claims. These claims are then used to authorize the operation. For database interactions, this means constructing queries that filter data based on these claims. For instance, a query to fetch courses might become SELECT * FROM courses WHERE organization_id = :org_id, where :org_id is populated directly from the JWT claim.
This approach effectively bypasses the need for complex RLS policies for many common scenarios. The database still needs basic security (e.g., disallowing public access), but the fine-grained, row-level access control is now managed by the application's JWT-based authorization layer. This has several advantages:
- Simplicity: The database schema becomes cleaner, free from intricate RLS policies.
- Consistency: Authorization logic is centralized in the backend, reducing the risk of discrepancies.
- Performance: While JWT validation adds a small overhead, it can be faster than complex RLS policy evaluation, especially when RLS policies have implicit dependencies.
- Developer Experience: Debugging authorization issues becomes more straightforward, as the logic is contained within the application code and JWT claims, which are easier to inspect and manage than database policies.
The surprising detail here is not the complexity of the JWT implementation itself, but how much simpler the overall system becomes once the burden of intricate RLS policies is lifted. It’s like switching from a Rube Goldberg machine to a well-oiled lever. The custom claims act as a dynamic, verifiable passport for each user, and the backend service is the gatekeeper, consulting this passport for every action. This has allowed us to move forward with new features without the constant dread of breaking existing permissions.
The Unanswered Question: Scalability of JWT Claims
While this approach has resolved the immediate crisis of RLS recursion and simplified our architecture, one question remains: what is the ultimate scalability of embedding extensive authorization data within JWTs? As applications grow and user permissions become more granular and dynamic, the size of the JWT could increase significantly. This impacts token generation time, transmission size, and potentially verification performance. Storing large amounts of data in JWT claims can also be inefficient and lead to security concerns if not managed properly (e.g., sensitive data exposure if the token is compromised, though signed JWTs mitigate this). There’s a delicate balance between embedding enough information for efficient backend authorization and keeping the token lean and performant. Finding that sweet spot, and potentially evolving the strategy for very large-scale or highly dynamic permission models, is the next challenge.
For now, however, the move to JWT custom claims has been a resounding success. It has transformed a source of constant frustration and critical bugs into a predictable and manageable system. Françoise is happy. My Sentry dashboard is quiet. And I can sleep at night knowing that a simple GRANT won't bring down the entire reporting system.