CODEWITHSUNDEEP
postgresqlrow-level-security
Parris
Parris

Reputation: 18408

Select with Row Level Security Settings

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

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.

Upvotes: 0

Views: 307

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324375

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

Upvotes: 2

Related Questions