Search Postgresql Archives

Re: Row level security - notes and questions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux