Recursive policy problem

[SebastianLJ]

I'm implementing RLS on three tables User, Organization, and Member. Member defines the role a User has in an Organization (if they are a member). We use the Supabase function auth.uid() to get the currently logged-in user.

If I want a user to only be able to select member rows of their coworkers (users who are members of the same organization). I would intuitively write a policy like this:

CREATE POLICY "user select coworkers" ON public.member 
        FOR SELECT
        USING (
            organization_id IN ( 
                SELECT M.organization_id
                FROM member AS M
                WHERE auth.uid() = M.user_id
            )
        );

This does not work as it's an infinite recursing policy. Right now I'm thinking that the schema should be changed, but I wanted to hear if any of you have had similar troubles and if so how you managed to overcome them?

[soedirgo]

@SebastianLJ can you try the answer suggested here: #3328? In general you'd need a SECURITY DEFINER function for things like this.

[soedirgo]

Does running SELECT is_member_of('d0915831-8c7b-41c7-b386-cfc989249919', '2ffb5183-8d2a-4928-84a0-28ee639dffbe'); return true?

[SebastianLJ]

That also returns true for all uuid inputs

[SebastianLJ]

as a test, I replaced SELECT EXISTS ... with SELECT false just to make sure that the function can return false, and in that case, it did return false when called.

[SebastianLJ]

changed the function to the following and it now works as expected

 CREATE OR REPLACE FUNCTION public.is_member_of(organization_id uuid) RETURNS bool AS $$
    SELECT organization_id IN (
        SELECT om.organization_id
        FROM member om
        WHERE om.user_id = auth.uid()
    );
    $$ LANGUAGE sql SECURITY DEFINER;

[soedirgo]

👍 Thanks! Will update the linked discussion.