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
…
Sign in
Question

infinite recursion detected in policy for relation "profiles"

Solved · 5411 viewsasked by renata_builds
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')
);

The console on every page:

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
This question should be pinned somewhere. Lovable does this every single time you ask for admin roles. gabriel_ss
+1, spent a whole evening on exactly this jessbuilds

3 answers

Marked as helpful by the asker
mira_dev

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
The last line of this answer is the most important one. Good catch. postgres_pete
hannah_reyes

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
postgres_pete

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
My admin page is just hidden from the menu, is that protection?solved
Google login says 'continue to xkqzjwp.supabase.co', customers think it's a scamsolved
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