Supabase RLS policy turns into recursive loop when selecting same table

Question

I am trying to retrieve data from a table, bit it is getting stuck in an infinite recursive loop due to my RLS policies, which look at the same table.

I have a table called 'community_members' that links 'community_id' to 'user_id'. The table is used to add users to communities. I want to create a policy that only allows a user to SELECT data when they are part of said community, so I need to SELECT the same table.

(AKA if there is a row that contains a community_id and the user_id of the auth user, then that user should be able to SELECT any row that contains the community_id)

So far I tried:

(community_id IN ( SELECT community_members.community_id FROM community_members WHERE ((auth.uid() = community_members.user_id) AND (community_id = community_members.community_id))))

Is it strange to look at the same table you're making policies for? Is there a better way of doing this without creating a recursive loop? I feel like this is a common use case so I hope that there is someone out there that stumbled onto the same issue and solved it.

Here is my Dart code that does not return anything due to the infinite loop.

return client
 .from('community_members')
 .stream(primaryKey: ['id'])
 .eq('user_id', userId);

Answer 1

I figured it out. You can create a security definer function and refer to that in the RLS policy. The documentation on those is found here: https://supabase.com/docs/guides/auth/row-level-security#policies-with-security-definer-functions

Basically I put the exact same SELECT function as above in the function and referred to that.

• wiesson: > You can create a security definer function Is there a way to do it without the definer function?

Answer 2

Would this work?

select exists(
 select 1
 from community_members
 where community_id = community_members.community_id and user_id = auth.uid()
);