Select with Row Level Security Settings

Question

I'm attempting to add a select within the permission check for a postgres' row level security policy.

I have 2 tables to care about here 1) group 2) group_member

• group - has a group_id and person_id (that is the person who made the group)
• group_member - has group_id and person_id

What I want to express is the ability for group owner's to kick a group_member or for a group_member to leave on their own.

Here's my policy:

CREATE POLICY delete_group_member ON public.group FOR DELETE TO hatch_contributor USING (
      person_id = current_setting('jwt.claims.person_id')::integer or
      EXISTS (
        SELECT * FROM group WHERE group.id = group_id AND group.person_id = current_setting('jwt.claims.person_id')::integer)
      ));

However, I get the following error - syntax error at or near "group"

I suspect, postgres doesn't understand where group_id is coming from. Maybe's confused about group in general in this case. My question is, what am I missing here? How can I get this working.

Answer 1

GROUP is a keyword in SQL. Don't use it for a table name. If you must use it, you must quote it, e.g.

ON public."group"

and

WHERE "group".id