Row level security - notes and questions - Mailing list pgsql-general

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