Re: Row security policies documentation question

["Alexander M. Sauer-Budge" <ambudge@xxxxxxxxxxxx>]

On May 31, 2016, at 5:16 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Tue, May 31, 2016 at 4:59 PM, Alexander M. Sauer-Budge <ambudge@xxxxxxxxxxxx> wrote:

Hello,

Section 5.7. on Row Security Policies (https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html) for 9.5 says:

 

[...]

 

​

CREATE POLICY user_policy ON users
    USING (user = current_user);

---
Is this a table column in a `users` table the example doesn’t define or does PostgreSQL keep track of what user/role inserted a row and allow policies to use it?

​It assumes the user can envision a trivial "users" table having at least a column named "user" that represents the user's name/id and which the names of said users are identical to those assigned to them in the PostgreSQL database and accessible via the "pg_authid" catalog (rolname) and its related views: namely "pg_user" (usename).

​​So, in effect the following works, and returns a single row.

SELECT *

FROM users

JOIN pg_user ON (user = usename)

WHERE user = current_user;

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;

SELECT * FROM mytable;

 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