Re: Row level security - notes and questions

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