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.