Infinite recursion RLS

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?

You could use a function in your policy as such :

 CREATE POLICY "select_comembers" ON "public"."person_groups"
AS PERMISSIVE FOR SELECT
TO public
USING (is_comember_of(auth.uid(), group_id))

The function would be a SECURITY DEFINER, defined as follows :

-- prefixed params prevent name clashes
CREATE FUNCTION is_comember_of(_person_id uuid, _group_id uuid) RETURNS bool AS $$
SELECT EXISTS (
  SELECT 1
  FROM person_groups pg
  WHERE pg.group_id = _group_id
  AND pg.person_id = _person_id
);
$$ LANGUAGE sql SECURITY DEFINER;
-- Function is owned by postgres which bypasses RLS