guys i have an issue, i have a row level security policy on a table that runs a query on the table itself so whenever i run a select command on that table it uses the policy which runs a query which uses the policy which runs the query which uses the policy... and so on until it hits a recursion limit. is this a bug or a feature? and if it is a feature is there a workaround to this?

Can you post an example of the code?

I have a similar problem https://discord.com/channels/839993398554656828/869405720934744086/900493182045925417

yes so i have a table called

members

made using

sql
create table if not exists public.members (
  id          bigint generated by default as identity primary key not null,
  nickname    text default '',
  user_id     uuid references public.profiles not null,
  group_id    bigint references public.groups on delete cascade not null,
  joined_at   timestamp without time zone default now(),
  unique (user_id, group_id)
);

the policy should give an authorised user all members that are in groups that the current user is in (mutual members) this would require selecting all the groups the current user is in and then using that query to select all members in those groups. something like:

sql
create policy "Allow mutual members to be able to see each other" on public.members for select using (
  members.user_id in (select user_id from public.members where group_id in (select group_id from public.members where user_id = auth.uid()))
);

there is probably a more efficient way but the point of this code is to show you that the policy queries its on table and that causes a recursion error

u can take a look at https://discord.com/channels/839993398554656828/900493182045925417 to get an idea of why this is happening, it's the same issue

yes i understand why its happening

yeah so the solution is very close to the sample code provided there. for the documentation, you can check out https://supabase.io/docs/guides/auth/row-level-security#policies-with-security-definer-functions

bruh i used this technique a few minutes before u sent the solution and yes it defintley works

nice