Thanks David! I appreciate the clarification and the extra context. So if I wanted to establish a “row owner role” and only permit that role or any other role with direct or inherited membership in that role to access the row, then I’d do something explicit like this:
CREATE TABLE mytable (id integer, value text, owner_role text);
ALTER TABLE mytable ENABLE ROW LEVEL SECURITY;
CREATE POLICY mytable_policy ON mytable USING (pg_has_role(current_user, owner_role, 'member'));
CREATE ROLE mygroup NOLOGIN;
GRANT ALL ON mytable TO mygroup;
CREATE ROLE myuser NOLOGIN;
GRANT mygroup TO myuser;
SET ROLE mygroup;
INSERT INTO mytable VALUES (1, 'test value 1’, current_user);
SET ROLE myuser;
SELECT * FROM mytable;
id | value | owner_role
----+---------------+------------
1 | test value 1 | mygroup
(1 row)
RESET ROLE;
CREATE ROLE anotheruser NOLOGIN;
GRANT ALL ON mytable TO anotheruser;
SET ROLE anotheruser;
id | value | owner_role
----+---------------+------------
(0 rows)
Is this the most direct and performant way to use row security to establish a permission system that behaves similarly to table/column permissions?
Thanks!
Alex
