Thread: Row security policies documentation question

Row security policies documentation question

From

"Alexander M. Sauer-Budge"

Date:

01 June 2016, 00:00:09

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
toaccess 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.
Toallow 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
thisa 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
whatuser/role inserted a row and allow policies to use it? 

Thanks!
Alex

Re: Row security policies documentation question

From

"David G. Johnston"

Date:

01 June 2016, 00:16:10

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.

Re: Row security policies documentation question

From

Adrian Klaver

Date:

01 June 2016, 02:49:03

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
roleto 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.
Toallow 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.
Isthis 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
whatuser/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

Re: Row security policies documentation question

From

"Alexander M. Sauer-Budge"

Date:

01 June 2016, 17:28:59

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> 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

Re: Row security policies documentation question

From

"Alexander M. Sauer-Budge"

Date:

01 June 2016, 17:32:27

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/

Fantastic! Thanks!