supabase / PostgreSQL RLS Rule leads to "infinite recursion detected in policy for relation \"profiles\""

Question

I am currently familiarizing myself with Supabase and Postgres and RLS-Rules. But these rules lead to an error: response 400 "infinite recursion detected in policy for relation "profiles"

I have in a nutshell 2 tables. One is Auth.Users, which ultimately contains the ID for the current user and is managed by supabase.

To extend the user data, I have another table Profiles. This looks something like this.

id =(same as ID of Auth.Users)	userName	role	...	...
46e3bda0-7186-4002-93f3-607074f305d7	xyz	admin	-	-
a4c34891-6045-4d39-8b68-b41ecd2a5d6f	abc	manager	-	-
...	...	...	...	...

Now I've quickly created an Angular-WebApp, where users can log in. And of course every user should be able to see his profile but not the profile of other users. That was simple done with RLS.

CREATE POLICY "Enable read access for current user only" ON "public"."profiles"
AS PERMISSIVE FOR SELECT
TO authenticated
USING (auth.uid() = id)

This rule was simple and worked well. Authenticated users are only able to see records where the id corresponds to their UID, which is at least always just their own profile.

Now I wanted to create a second rule. A user whose role is admin should be allowed to see all records from profiles-table. My idea was this additional rule.

CREATE POLICY "Enable read for admins to all profiles" ON "public"."profiles"
AS PERMISSIVE FOR SELECT
TO authenticated
USING ((EXISTS ( SELECT 1 FROM profiles WHERE (id = auth.uid()) AND (role = 'admin'))))

My idea was letting this rule check for each line whether there is at least one record in the Profiles table where the Id corresponds to the current auth.uid and where the user has the role admin.

However, as soon as I set this rule additionally, I get an error message in my Angular app or better to say via the API. It gives me response 400 "infinite recursion detected in policy for relation "profiles"

It's not really clear to me why I should have an infinite recursion here. It would be rather strange if the inner select statement itself would be restricted to its outer rule.

Anyway, can anyone help me here? Where is the recursion? How to achive this everyday task of role based rights?

• Adrian Klaver: The problem is here ...AS PERMISSIVE FOR SELECT... and SELECT 1 FROM .... On a select you run the policy which does a SELECT which runs the policy which does a SELECT which runs the policy, which ... into an infinite recursion.
• Adrian Klaver: See also here Policy recursion for what the OP did to solve it.
• Henner: @AdrianKlaver Thanks for your comments! Just for my understanding: In a rule that restricts a SELECT to a specific table, I cannot nest a SELECT on this same table because it would fall under the same rule and check the rule and check the rule and check the rule....? In other words, I would have to somehow outsource the inner query (is user admin) to a function that has the right to query this table indefinitely?
• Adrian Klaver: Or as the OP did, in the link I posted, move the information to another table and query it.
• Henner: Yes, after playing around and trying some more today, I have to say that using multiple tables is probably the best solution. When I wrote my last comment, I thought I could save myself these tables with a "function" solution. But now I've realized that I need certain information with different levels of severity in many places. So moving them into several tables is much better - well, I'm still learning :-)

Answer 1

Check that you have set the RLS policy correctly:

Supabase -> Table editor -> [Name Table] -> RLS policy (or Auth policy)

enter image description here