Question 184857 in Flutter Question posted in
The official Flutter documentation can be found here.

Flutter – Supabase RLS policy turns into recursive loop when selecting same table

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);
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    Would this work?

    select exists(
  select 1
  from community_members
  where community_id = community_members.community_id and user_id = auth.uid()
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search