Supabase RLS Policy error "stack depth limit exceeded"

Question

I am trying to create a RLS policy that checks if the user's role matches criteria to allow full access. I have a solution that is already working in a different project, but it won't work here for a reason that I am unable to find. I always get the error

"stack depth limit exceeded"

with the hint

"Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate."

The table I am working on is

create table user_profile (
id serial primary key,
name text,
user_id uuid references auth.users(id),
role_id bigint references public.user_roles(id));

The solution that I had found previously on stackoverflow is a functions that returns a bool as follows

create or replace function is_member_of(_user_id uuid, _role_id bigint) 
returns boolean as $$
SELECT EXISTS (
 SELECT 1
 FROM user_profiles om
 WHERE om.role_id = _role_id
 AND om.user_id = _user_id
);
$$ language sql;

Then create the RLS policy to run the function enter image description here

Now I always get the same error every time I try to query the table. I do understand that there is infinite recursion but I dunno where is it coming from. Especially that I am using the same solution in another project and it is working accordingly.

Answer 1

It seems that it was a permissions issue, I just needed to set the function to run by the creator not the invoker.

A detailed solution can be found below.

https://github.com/supabase/supabase/discussions/1138#discussioncomment-604345