Keyboard shortcuts

jk
move through the list
↵
open the selected item
a
ask a question
nucw
tabs: new, unanswered, active, top
⌘↵
post the answer you are writing
/or⌘K
search
t
switch theme
?
this list
AskVibe
AskSign in
Demo, all content is generated
Question

infinite recursion detected in policy for relation "profiles"

Solved · 5406 views · asked by renata_builds · edited
lovablesupabaseauthsecurity

Wanted admins to see all profiles. Lovable added this and now the whole app shows errors, even the login page loads nothing:

create policy "Admins can view all profiles" on profiles
for select using (
  exists (select 1 from profiles where id = auth.uid() and role = 'admin')
);

Error: infinite recursion detected in policy for relation "profiles"

What I’ve tried

Asked Lovable to fix it 4 times. It rewrites the policy slightly differently each time, same error. Deleted the policy and everything works, but then admins can't see profiles.

Comment
Classic one. The policy on profiles queries profiles, which triggers the policy on profiles, which... hannah_reyes · edited
This question should be pinned somewhere. Lovable does this every single time you ask for admin roles. gabriel_ss · edited
+1, spent a whole evening on exactly this jessbuilds · edited

3 answers

Marked as helpful by the asker
mira_dev · edited

The policy on profiles reads profiles, which runs the policy again, forever. Move the check into a security definer function. It runs as its owner, so its read of profiles doesn't trigger RLS:

create schema if not exists private;

create or replace function private.is_admin()
returns boolean
language sql stable security definer set search_path = ''
as $$
  select exists (
    select 1 from public.profiles
    where id = (select auth.uid()) and role = 'admin'
  );
$$;

create policy "Admins can view all profiles" on public.profiles
  for select to authenticated
  using ((select private.is_admin()));

Put it in a schema that isn't exposed over the API (private here), otherwise anyone can call it via RPC. set search_path = '' prevents someone from shadowing profiles with their own table.

And make sure users can't update their own role column, otherwise everyone can make themselves admin.

Comment
IT WORKS. And yes... users could update their own role. Fixed that too with a separate policy. renata_builds · edited
The last line of this answer is the most important one. Good catch. postgres_pete · edited
hannah_reyes · edited

Alternative if you don't want a function: store the role in the JWT via a custom access token hook and check auth.jwt() ->> 'user_role' in the policy. No table read at all. More setup though, the function is the quicker fix.

Comment
Going with the function for now, but bookmarking the JWT way renata_builds · edited
postgres_pete · edited

Same issue happens across two tables that point at each other (A's policy reads B, B's policy reads A). Same fix.

Comment

Related

Hundreds of fake signups overnight, all with random gmail addressessolved
Google login says 'continue to xkqzjwp.supabase.co', customers think it's a scamsolved
My admin page is just hidden from the menu, is that protection?solved
Users can give themselves unlimited credits by editing their own profile rowsolved
RLS for 'members of a team can see the team's projects', how do I write that?solved

Your answer

Sign in to write an answer