Infinite recursion RLS

Question

I have a table like:

table person_groups
   person_id: uuid
   group_id: uuid

and I want people for whom uid() = person_id to be able to get all of the other person_ids in all of their groups.

I tried to use

CREATE POLICY "select_comembers" ON "public"."person_groups"
AS PERMISSIVE FOR SELECT
TO public
USING (group_id in (select group_id from person_groups where person_id=uid()))

but this triggers an infinite recursion error when RLS is enforced (at edit time, the definer isn't subject to RLS, so no in that case this is fine apparently).

What's the appropriate way to allow finding all of the rows in a table that share a different column to the one you're filtering on, in this way? Is there an appropriate way to use JOINs to solve this?

Answer 1

That sounds like you should redesign your data model. A possible solution would be to replace the subquery with a call to a SECURITY DEFINER function that belongs to the table owner or another user that is exempt from row-level security.