Row Level Security, Ambiguous Reference

I'm trying to create a policy in postgres that results in a column reference "person_id" is ambiguous.

To make a small, similar example let's describe 3 tables: person, group and group_member.

Person has: id, name
Group has: id, person_id and name
Group Member has: id, person_id, group_id

I've simplified my policy, but the relevant portion looks something like this:

CREATE POLICY insert_group_member ON public."group" FOR INSERT TO user_role WITH CHECK (
  EXISTS (
    SELECT * FROM "group" AS g
    LEFT JOIN "group_member" AS gm ON
      gm.person_id = person_id
      AND g.id = gm.group_id
    WHERE
      g.id = group_id
  )
);

person_id in this case can refer to group.person_id or group_member.person_id from the sub-select, but what I really want it to be is the person_id coming from the "with check". This generally, works in other places.

Question: is there way to clearly specify that the person_id column comes from the policy and not from the select or the join. Note: I'd have expected that aliasing the table names solves this problem, but it seems to have not.

0 Comment

NO COMMENTS

LEAVE A REPLY

Captcha image