Обсуждение: Row level security - notes and questions

Hello

I have been testing the new row level security feature of 9.5 and I have
some notes and questions on it.

This is  a simple table for the test, with 2 rows and a user named john, who
is granted access to the table through a group named users.

CREATE TABLE testrls.accounts (
  id integer,
  username text,
  userinfo text
);

INSERT INTO testrls.accounts
VALUES (1,'john','Main accountant'),
       (2,'fred','Practitioner');

CREATE ROLE users NOLOGIN;
CREATE ROLE john LOGIN PASSWORD 'xxx';

GRANT USAGE ON SCHEMA testrls TO users;
GRANT SELECT, INSERT, UPDATE, DELETE ON testrls.accounts TO users;
GRANT users TO john;

My first test is to enable row level security on the table without a policy
in place. According to the documentation this leads to a general deny
access.

ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY;

When user john tries to interact with the table he receives an empty result
set, instead of a policy violation error. There is no policy yet, so this
may be acceptable. I find it however confusing.

john@test=> SELECT * FROM testrls.accounts ;
 id | username | userinfo
----+----------+----------
(0 rows)

Since the result set is empty UPDATE and DELETE also do nothing.

In the case of an INSERT john receives a policy violation error. Still there
is no policy yet for the table. This seems not consistent with the behaviour
for the other commands.

john@test=> INSERT INTO testrls.accounts VALUES (3,'lucy','Secretary');
ERROR:  new row violates row level security policy for "accounts"

For the next example I created a policy that allows users to read all rows,
but only change those "belonging" to them, identified by the column
username.

CREATE POLICY accounts_policy ON testrls.accounts
FOR ALL
TO users
USING (true)
WITH CHECK (username = SESSION_USER);

john@test=> SELECT * FROM testrls.accounts ;
 id | username |    userinfo
----+----------+-----------------
  1 | john     | Main accountant
  2 | fred     | Practitioner
(2 rows)

john@test.localhost=> INSERT INTO testrls.accounts VALUES
(3,'lucy','Secretary');
ERROR:  new row violates row level security policy for "accounts"
john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE id = 2;
ERROR:  new row violates row level security policy for "accounts"
john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE username
= 'fred';
ERROR:  new row violates row level security policy for "accounts"

Up to this point everything is fine. The user can, however do the following:

john@test.localhost=> UPDATE testrls.accounts SET username = 'john' WHERE
username = 'fred';
UPDATE 1
john@test.localhost=> SELECT * FROM testrls.accounts ;
 id | username |    userinfo
----+----------+-----------------
  1 | john     | Main accountant
  2 | john     | Practitioner
(2 rows)

john@test.localhost=> DELETE FROM testrls.accounts WHERE id = 2;
DELETE 1
john@test.localhost=> SELECT * FROM testrls.accounts ;
 id | username |    userinfo
----+----------+-----------------
  1 | john     | Main accountant
(1 row)

The policy suggests that users can only modify rows where their name is in
the username field. In the UPDATE case the condition is tested against the
new values for the row, leading to a chance for any user to modify and
delete any row.

Obvioulsy there is a number of solutions to this issue. It would be enough
e.g. to modify the UPDATE grant on the table to avoid users in general to
modify this field as in the example in the documentation. In that case
everything would work correctly and the user would get a permission denied
message from the authorization system. I point this out to avoid answers to
my mail suggesting how to solve the problem. What I don't know or don't
understand is the following:

- Why is there not a consistent policy violation message when one would
apply as mentioned above?
- Why is the WITH CHECK condition only used on the values in the new record
in the case of an update?

Thank you.
Charles

Charles,

* Charles Clavadetscher (clavadetscher@swisspug.org) wrote:
> I have been testing the new row level security feature of 9.5 and I have
> some notes and questions on it.

Great!  Glad to hear it.

> My first test is to enable row level security on the table without a policy
> in place. According to the documentation this leads to a general deny
> access.
>
> ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY;
>
> When user john tries to interact with the table he receives an empty result
> set, instead of a policy violation error. There is no policy yet, so this
> may be acceptable. I find it however confusing.

A permissions error would be thrown if the user didn't have access to
the table through the GRANT system.  If no policy is found for a user
(which could happen multiple ways- no policies exist, policies exist but
none apply to this user, policies exist but none apply to this command,
etc) then a default-deny policy is used which results in an empty set.

This is all documented, of course.  Specific suggestions for improving
the docs to help clarify this would certainly be appreciated.

> Since the result set is empty UPDATE and DELETE also do nothing.

Right, the default deny policy applies to all commands.

> In the case of an INSERT john receives a policy violation error. Still there
> is no policy yet for the table. This seems not consistent with the behaviour
> for the other commands.

INSERTs can fail where SELECTs, UPDATEs, and DELETEs do not- even when
policies have been defined on the relation, and so this is consistent
within the overall policy system.  It would be inconsistent for SELECTs
to fail in all cases where INSERTs do.

The reason for this is that RLS is about filtering the rows returned,
but we suspend that for data which is being added to the system as we
don't wish to accept and then throw away data (which is what filtering
on an INSERT, or the result of an UPDATE, would do).

> For the next example I created a policy that allows users to read all rows,
> but only change those "belonging" to them, identified by the column
> username.

While I appreciate that your goal was to create such a policy, that's
not what this command does:

> CREATE POLICY accounts_policy ON testrls.accounts
> FOR ALL
> TO users
> USING (true)
> WITH CHECK (username = SESSION_USER);

This command says "allow all commands to operate on all rows, but new
rows being added to the system must have (username = SESSION_USER)".

A policy to allow users to read all rows would be:

CREATE POLICY accounts_policy ON testrls.accounts
FOR SELECT
TO users
USING (true);

The following policy would then allow users to update rows which have
(username = SESSION_USER):

CREATE POLICY accounts_policy_update ON testrls.accounts
FOR UPDATE
TO users
USING (username = SESSION_USER);
-- Note that with no WITH CHECK, the USING clause will be used

Further, the "passwd" example in the documentation covers exactly this
policy of "read all, modify only same-user".

If you wanted to also allow INSERT and DELETE commands on rows which
have (username = SESSION_USER), you could create policies for them, as
so:

CREATE POLICY accounts_policy_insert ON testrls.accounts
FOR INSERT
TO users
WITH CHECK (username = SESSION_USER);

CREATE POLICY accounts_policy_delete ON testrls.accounts
FOR DELETE
TO users
USING (username = SESSION_USER);

> The policy suggests that users can only modify rows where their name is in
> the username field. In the UPDATE case the condition is tested against the
> new values for the row, leading to a chance for any user to modify and
> delete any row.

... which is what the policy was defined to allow by having a USING
clause of "true".

> - Why is there not a consistent policy violation message when one would
> apply as mentioned above?

Hopefully, my answers above explain this.

> - Why is the WITH CHECK condition only used on the values in the new record
> in the case of an update?

Both the USING and WITH CHECK clauses are checked for UPDATE commands-
the USING clause is "what *existing* records does this policy allow
modification of" while the WITH CHECK clause is "what *new* records are
allowed to be added through this policy".

Consider a case where you wish to allow users to UPDATE existing rows in
the table, but the result of that UPDATE must meet a different condition
to be allowed to be added to the table.  A simple case of this is "Joe
can modify all records, but the result of that modification must update
the last-modified-by column to be set to Joe."

    Thanks!

        Stephen

On 07/10/2015 10:28 PM, Charles Clavadetscher wrote:
> Hello
>
> I have been testing the new row level security feature of 9.5 and I have
> some notes and questions on it.
>
> This is  a simple table for the test, with 2 rows and a user named john, who
> is granted access to the table through a group named users.
>
> CREATE TABLE testrls.accounts (
>    id integer,
>    username text,
>    userinfo text
> );
>
> INSERT INTO testrls.accounts
> VALUES (1,'john','Main accountant'),
>         (2,'fred','Practitioner');
>
> CREATE ROLE users NOLOGIN;
> CREATE ROLE john LOGIN PASSWORD 'xxx';
>
> GRANT USAGE ON SCHEMA testrls TO users;
> GRANT SELECT, INSERT, UPDATE, DELETE ON testrls.accounts TO users;
> GRANT users TO john;
>
> My first test is to enable row level security on the table without a policy
> in place. According to the documentation this leads to a general deny
> access.
>
> ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY;
>
> When user john tries to interact with the table he receives an empty result
> set, instead of a policy violation error. There is no policy yet, so this
> may be acceptable. I find it however confusing.
>
> john@test=> SELECT * FROM testrls.accounts ;
>   id | username | userinfo
> ----+----------+----------
> (0 rows)
>
> Since the result set is empty UPDATE and DELETE also do nothing.
>
> In the case of an INSERT john receives a policy violation error. Still there
> is no policy yet for the table. This seems not consistent with the behaviour
> for the other commands.

To me it makes sense, with UPDATE and  DELETE you are working on an
empty set(after RLS filtering) so nothing happens. With INSERT you are
trying to create a new set and are being told it not possible.

>
> john@test=> INSERT INTO testrls.accounts VALUES (3,'lucy','Secretary');
> ERROR:  new row violates row level security policy for "accounts"
>
> For the next example I created a policy that allows users to read all rows,
> but only change those "belonging" to them, identified by the column
> username.
>
> CREATE POLICY accounts_policy ON testrls.accounts
> FOR ALL
> TO users
> USING (true)
> WITH CHECK (username = SESSION_USER);
>
> john@test=> SELECT * FROM testrls.accounts ;
>   id | username |    userinfo
> ----+----------+-----------------
>    1 | john     | Main accountant
>    2 | fred     | Practitioner
> (2 rows)
>
> john@test.localhost=> INSERT INTO testrls.accounts VALUES
> (3,'lucy','Secretary');
> ERROR:  new row violates row level security policy for "accounts"
> john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE id = 2;
> ERROR:  new row violates row level security policy for "accounts"
> john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE username
> = 'fred';
> ERROR:  new row violates row level security policy for "accounts"
>
> Up to this point everything is fine. The user can, however do the following:
>
> john@test.localhost=> UPDATE testrls.accounts SET username = 'john' WHERE
> username = 'fred';
> UPDATE 1
> john@test.localhost=> SELECT * FROM testrls.accounts ;
>   id | username |    userinfo
> ----+----------+-----------------
>    1 | john     | Main accountant
>    2 | john     | Practitioner
> (2 rows)
>
> john@test.localhost=> DELETE FROM testrls.accounts WHERE id = 2;
> DELETE 1
> john@test.localhost=> SELECT * FROM testrls.accounts ;
>   id | username |    userinfo
> ----+----------+-----------------
>    1 | john     | Main accountant
> (1 row)
>
> The policy suggests that users can only modify rows where their name is in
> the username field. In the UPDATE case the condition is tested against the
> new values for the row, leading to a chance for any user to modify and
> delete any row.

Actually no:

http://www.postgresql.org/docs/9.5/static/sql-createpolicy.html

ALL

     Using ALL for a policy means that it will apply to all commands,
regardless of the type of command. ..... As an example, if an UPDATE is
issued, then the ALL policy will be applicable to both what the UPDATE
will be able to select out as rows to be updated (with the USING
expression being applied), and it will be applied to rows which result
from the UPDATE statement, to check if they are permitted to be added to
the table (using the WITH CHECK expression, if defined, and the USING
expression otherwise). If an INSERT or UPDATE command attempts to add
rows to the table which do not pass the ALL WITH CHECK expression, the
entire command will be aborted. Note that if only a USING clause is
specified then that clause will be used for both USING and WITH CHECK cases.


or in more detail:

UPDATE

     Using UPDATE for a policy means that it will apply to UPDATE
commands (or auxiliary ON CONFLICT DO UPDATE clauses of INSERT
commands). As UPDATE involves pulling an existing record and then making
changes to some portion (but possibly not all) of the record, the UPDATE
policy accepts both a USING expression and a WITH CHECK expression. The
USING expression will be used to determine which records the UPDATE
command will see to operate against, while the WITH CHECK expression
defines what rows are allowed to be added back into the relation
(similar to the INSERT policy). Any rows whose resulting values do not
pass the WITH CHECK expression will cause an ERROR and the entire
command will be aborted. Note that if only a USING clause is specified
then that clause will be used for both USING and WITH CHECK cases.


So your USING (true) is allowing the selection of row 2 which you then
modify to have username='john' which passes the CHECK.

>
> Obvioulsy there is a number of solutions to this issue. It would be enough
> e.g. to modify the UPDATE grant on the table to avoid users in general to
> modify this field as in the example in the documentation. In that case
> everything would work correctly and the user would get a permission denied
> message from the authorization system. I point this out to avoid answers to
> my mail suggesting how to solve the problem. What I don't know or don't
> understand is the following:

The solution is to use a more restrictive USING.

See here for examples:

http://www.depesz.com/2014/10/02/waiting-for-9-5-row-level-security-policies-rls/

http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-row-level-security/

>
> - Why is there not a consistent policy violation message when one would
> apply as mentioned above?
> - Why is the WITH CHECK condition only used on the values in the new record
> in the case of an update?

See above.

>
> Thank you.
> Charles
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Stephen, hi Adrian

Thank you a lot! My huge mistake in understanding how policies work was to
assume that within a single policy FOR ALL the USING clause would be used
*only* for SELECT while WITH CHECK would be used by the modifying commands.
Now it is clear why it did not work as I wanted. I just checked it out on my
test environment and everything works as you said.

I thought I read the documentation carefully but I must have missed that
point. I will recheck to see if it really needs improvement, althought I
must admit that I am not an English native speaker.

And obviously I will continue with some experiments and get back with new
questions if any arise.

PostgreSQL has really a great community ;-)
Enjoy
Charles

> -----Original Message-----
> From: Stephen Frost [mailto:sfrost@snowman.net]
> Sent: Samstag, 11. Juli 2015 15:22
> To: Charles Clavadetscher
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Row level security - notes and questions
>
> Charles,
>
> * Charles Clavadetscher (clavadetscher@swisspug.org) wrote:
> > I have been testing the new row level security feature of 9.5 and I
> > have some notes and questions on it.
>
> Great!  Glad to hear it.
>
> > My first test is to enable row level security on the table without a
> > policy in place. According to the documentation this leads to a
> > general deny access.
> >
> > ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY;
> >
> > When user john tries to interact with the table he receives an empty
> > result set, instead of a policy violation error. There is no policy
> > yet, so this may be acceptable. I find it however confusing.
>
> A permissions error would be thrown if the user didn't have access to the
> table through the GRANT system.  If no policy is found for a user (which
could
> happen multiple ways- no policies exist, policies exist but none apply to
this
> user, policies exist but none apply to this command,
> etc) then a default-deny policy is used which results in an empty set.
>
> This is all documented, of course.  Specific suggestions for improving the
docs
> to help clarify this would certainly be appreciated.
>
> > Since the result set is empty UPDATE and DELETE also do nothing.
>
> Right, the default deny policy applies to all commands.
>
> > In the case of an INSERT john receives a policy violation error. Still
> > there is no policy yet for the table. This seems not consistent with
> > the behaviour for the other commands.
>
> INSERTs can fail where SELECTs, UPDATEs, and DELETEs do not- even when
> policies have been defined on the relation, and so this is consistent
within
> the overall policy system.  It would be inconsistent for SELECTs to fail
in all
> cases where INSERTs do.
>
> The reason for this is that RLS is about filtering the rows returned, but
we
> suspend that for data which is being added to the system as we don't wish
to
> accept and then throw away data (which is what filtering on an INSERT, or
the
> result of an UPDATE, would do).
>
> > For the next example I created a policy that allows users to read all
> > rows, but only change those "belonging" to them, identified by the
> > column username.
>
> While I appreciate that your goal was to create such a policy, that's not
what
> this command does:
>
> > CREATE POLICY accounts_policy ON testrls.accounts FOR ALL TO users
> > USING (true) WITH CHECK (username = SESSION_USER);
>
> This command says "allow all commands to operate on all rows, but new
> rows being added to the system must have (username = SESSION_USER)".
>
> A policy to allow users to read all rows would be:
>
> CREATE POLICY accounts_policy ON testrls.accounts FOR SELECT TO users
> USING (true);
>
> The following policy would then allow users to update rows which have
> (username = SESSION_USER):
>
> CREATE POLICY accounts_policy_update ON testrls.accounts FOR UPDATE TO
> users USING (username = SESSION_USER);
> -- Note that with no WITH CHECK, the USING clause will be used
>
> Further, the "passwd" example in the documentation covers exactly this
> policy of "read all, modify only same-user".
>
> If you wanted to also allow INSERT and DELETE commands on rows which
> have (username = SESSION_USER), you could create policies for them, as
> so:
>
> CREATE POLICY accounts_policy_insert ON testrls.accounts FOR INSERT TO
> users WITH CHECK (username = SESSION_USER);
>
> CREATE POLICY accounts_policy_delete ON testrls.accounts FOR DELETE TO
> users USING (username = SESSION_USER);
>
> > The policy suggests that users can only modify rows where their name
> > is in the username field. In the UPDATE case the condition is tested
> > against the new values for the row, leading to a chance for any user
> > to modify and delete any row.
>
> ... which is what the policy was defined to allow by having a USING clause
of
> "true".
>
> > - Why is there not a consistent policy violation message when one
> > would apply as mentioned above?
>
> Hopefully, my answers above explain this.
>
> > - Why is the WITH CHECK condition only used on the values in the new
> > record in the case of an update?
>
> Both the USING and WITH CHECK clauses are checked for UPDATE
> commands- the USING clause is "what *existing* records does this policy
> allow modification of" while the WITH CHECK clause is "what *new* records
> are allowed to be added through this policy".
>
> Consider a case where you wish to allow users to UPDATE existing rows in
> the table, but the result of that UPDATE must meet a different condition
to
> be allowed to be added to the table.  A simple case of this is "Joe can
modify
> all records, but the result of that modification must update the
last-modified-
> by column to be set to Joe."
>
>     Thanks!
>
>         Stephen