From: "Charles Clavadetscher" <clavadetscher@swisspug.org>
To: <pgsql-docs@postgresql.org>
Subject: Suggestions on Chapter 5.7 Row Security Policies and CREATE POLICY
Date: Sat, 18 Jul 2015 11:58:46 +0200
Message-ID: <00b101d0c140$5babae30$13030a90$@swisspug.org>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Thread-Index: AdDBPtyff77CRpSySwmu6BqDGQdy8w==
Content-Language: de-ch
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

Hello

I would like to point out some potential misunderstandings in the
documentation chapters mentioned below.

Chapter 5.7 Row Security Policies
(http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html)

- First paragraph: "... and rows which can be added through data
modification commands." This probably refers to INSERT and UPDATE. While the
first actually does add a row to the data, the second does not. Strictly
speaking in a UPDATE a row is created, but not added to the data. For
consistency with the description in chapter CREATE POLICY ("... while new
rows that would be created via INSERT or UPDATE are checked..."). I think
that it would be preferable to always speak of "rows created" when referring
to WITH CHECK.

- To that point I think that it would help having a short information about
the difference between adding a row and creating a row. I was quite confused
by the mixed usage of "add" and "create". This could be in a note or, if the
information is available somewhere else in the documentation, simply linked
in.

- Later on there are two "CREATE POLICY" examples on a table users, which is
not defined. This is confusing as well, because the second example states
that "This would allow all users to view all rows in the users table, but
only modify their own" and is defined as

CREATE POLICY user_policy ON users
USING (true)
WITH CHECK (user = current_user);

But user=current_user is always true.

See: http://www.postgresql.org/docs/9.1/static/functions-info.html Chapter
9.23:

------+------+----------------------------+
 user | name | equivalent to current_user |
------+------+----------------------------+

If the test is supposed to be against a field in the table containing the
user name, the example also has a flow as I was informed on the general
mailing list, with pretty much the same example. Here the example:

CREATE TABLE users (id SERIAL, username TEXT, comments TEXT);
INSERT INTO users (username, comments) VALUES ('user1','accountant'),
('admin','The Boss'), ('user2','sales manager');
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy ON users
USING (true)
WITH CHECK (username = current_user);

GRANT SELECT, UPDATE, DELETE ON users TO user1, user2;

INSERT INTO users (username, comments) VALUES ('user1','accountant'),
('admin','The Boss'), ('user2','sales manager');

And now acting as user1:

user1@uci.localhost=> SELECT * FROM users;
 id | username |   comments
----+----------+---------------
  7 | user1    | accountant
  8 | admin    | The Boss
  9 | user2    | sales manager
(3 rows)

user1@uci.localhost=> UPDATE users SET comments = 'I am the boss now' WHERE
id = 7;
UPDATE 1
user1@uci.localhost=> UPDATE users SET username = 'user1' WHERE id = 8;
UPDATE 1
user1@uci.localhost=> DELETE FROM users WHERE id = 8;
DELETE 1
user1@uci.localhost=> SELECT * FROM users;
 id | username |     comments
----+----------+-------------------
  9 | user2    | sales manager
  7 | user1    | I am the boss now
(2 rows)

Chapter CREATE POLICY
(http://www.postgresql.org/docs/9.5/static/sql-createpolicy.html): Same
problem as in previous note: mixed usage of add and create.

Let me know if I am supposed to provide concrete passages for what I
mentioned above. I don't know, what the normal process in these cases is.

Thank you and bye

Charles


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