Row security policies documentation question

On Tue, May 31, 2016 at 4:59 PM, Alexander M. Sauer-Budge <
ambudge@alum.mit.edu> 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);

---

I’m trying understand the example as it references both an `accounts`
table and a `users` table which isn’t defined. Is this a mishmash of
example fragments or should the CREATE POLICY statement reference the
`accounts` table instead of `users`? Specifically, what does `user`
reference in the statement "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;

David J.​

On 05/31/2016 01:59 PM, Alexander M. Sauer-Budge wrote:

Hello,

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

As a simple example, here is how to create a policy on the account relation to allow only members of the managers role to access rows, and only rows of their accounts:

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
USING (manager = current_user);

If no role is specified, or the special user name PUBLIC is used, then the policy applies to all users on the system. To allow all users to access their own row in a users table, a simple policy can be used:

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

---

I’m trying understand the example as it references both an `accounts` table and a `users` table which isn’t defined. Is this a mishmash of example fragments or should the CREATE POLICY statement reference the `accounts` table instead of `users`? Specifically, what does `user` reference in the statement "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?

For a good review of what is possible with RLS take a look at this blog:

http://blog.2ndquadrant.com/application-users-vs-row-level-security/

Thanks!
Alex

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On Tue, May 31, 2016 at 4:59 PM, Alexander M. Sauer-Budge <ambudge@alum.mit.edu <mailto:ambudge@alum.mit.edu>> wrote:
Hello,

Section 5.7. on Row Security Policies (https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html <https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html&gt;) 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

On May 31, 2016, at 7:48 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

For a good review of what is possible with RLS take a look at this blog:

http://blog.2ndquadrant.com/application-users-vs-row-level-security/ <http://blog.2ndquadrant.com/application-users-vs-row-level-security/&gt;

Fantastic! Thanks!