At begin some citations from PostgreSQL documentation:
<citation>
34.4. Rules and Privileges
<skip/>
Rewrite rules don't have a separate owner. The owner of a relation
(table or view) is automatically the owner of the rewrite rules that are
defined for it. The PostgreSQL rule system changes the behavior of the
default access control system. Relations that are used due to rules get
checked against the privileges of the rule owner, not the user invoking
the rule. <note>This means that a user only needs the required
privileges for the tables/views that he names explicitly in his
queries</note>.
<skip/>
<note>This mechanism also works for update rules</note>. In the examples
of the previous section, the owner of the tables in the example database
could grant the privileges SELECT, INSERT, UPDATE, and DELETE on the
shoelace view to someone else, but only SELECT on shoelace_log. The rule
action to write log entries will still be executed successfully, and
that other user could see the log entries. But he cannot create fake
entries, nor could he manipulate or remove existing ones.
</citation>
Next -- test and it's output, that shows, that if view has INSERT,
UPDATE and DELETE rules then _ANY_ user can insert, update and delete
data in tables, that affected by this rules even user has no INSERT,
UPDATE and DELETE privileges on view and table.
This problem exists for at least 7.3.4 and 7.4.1 PostgreSQL versions.
This is very strange and I'm not sure that I understand all true.
P.S. Please help me solve this problem ASAP.
P.P.S. Sorry for my bad english, but I hope You understand me.
--
Sergey N. Yatskevich <[EMAIL PROTECTED]>
GosNIIAS
-- Test view/rule privileges
SET client_min_messages = ERROR;
\! echo "Show full PostgreSQL version."
SELECT version ();
\! echo "Create user with name user1"
CREATE USER user1;
\! echo "Create user with name user2"
CREATE USER user2;
\! echo
\! echo
\! echo "Switch to user1 with set session authrization"
SET SESSION AUTHORIZATION user1;
\! echo
\! echo "user1 creates private table with name user1_table"
CREATE TABLE user1_table (
id INTEGER PRIMARY KEY,
data TEXT
);
\! echo "and revokes all rights on it from public."
REVOKE ALL ON user1_table FROM PUBLIC;
\! echo "Next user1 creates private view for table user1_table with name user1_view"
\! echo "and appropriate insert, update and delete rules on it."
CREATE VIEW user1_view AS SELECT data FROM user1_table;
CREATE RULE user1_view_insert AS
ON INSERT TO user1_view
DO INSTEAD INSERT INTO user1_table
VALUES (COALESCE ((SELECT max (id) + 1 FROM user1_table), 0), new.data);
CREATE RULE user1_view_update AS
ON UPDATE TO user1_view
DO INSTEAD UPDATE user1_table SET data = new.data;
CREATE RULE user1_view_delete AS
ON DELETE TO user1_view
DO INSTEAD DELETE FROM user1_table;
\! echo "and revokes all rights on it from public."
REVOKE ALL ON user1_view FROM PUBLIC;
\! echo
\! echo
\! echo "Switch to user2 with set session authrization"
SET SESSION AUTHORIZATION user2;
\! echo
\! echo "user2 tries to select data from user1_table."
\! echo "Must be error becouse user2 don't have SELECT privilege"
\! echo "on user1_table and this is true. This is good."
SELECT * FROM user1_table;
\! echo
\! echo "user2 tries to insert data into user1_table."
\! echo "Must be error becouse user2 don't have INSERT privilege"
\! echo "on user1_table and this is true. This is good."
INSERT INTO user1_table VALUES (10000, 'test data');
\! echo
\! echo "user2 tries to update data in user1_table."
\! echo "Must be error becouse user2 don't have UPDATE privilege"
\! echo "on user1_table and this is true. This is good."
UPDATE user1_table SET data = data || USER;
\! echo
\! echo "user2 tries to delete data from user1_table."
\! echo "Must be error becouse user2 don't have DELETE privilege"
\! echo "on user1_table and this is true. This is good."
DELETE FROM user1_table;
\! echo
\! echo "user2 tries to select data from user1_view."
\! echo "Must be error becouse user2 don't have SELECT privilege"
\! echo "on user1_view and this is true. This is good."
SELECT * FROM user1_view;
\! echo
\! echo "user2 tries to insert data into user1_view."
\! echo "Must be error becouse user2 don't have INSERT privilege"
\! echo "on user1_view but this is false. This is WRONG!!!!"
INSERT INTO user1_view VALUES ('test data');
\! echo
\! echo "user2 tries to update data in user1_view."
\! echo "Must be error becouse user2 don't have UPDATE privilege"
\! echo "on user1_view and this is true. This is good."
UPDATE user1_view SET data = data || USER;
\! echo
\! echo "user2 tries to delete data from user1_view."
\! echo "Must be error becouse user2 don't have DELETE privilege"
\! echo "on user1_view but this is false. This is WRONG!!!!"
DELETE FROM user1_view;
\! echo
\! echo
\! echo
\! echo "Switch back to user1 with set session authrization"
SET SESSION AUTHORIZATION user1;
\! echo "user1 grants SELECT privilege on user1_view to user2"
GRANT SELECT ON user1_view TO user2;
\! echo
\! echo
\! echo "Switch back to user2 with set session authrization"
SET SESSION AUTHORIZATION user2;
\! echo
\! echo "user2 tries to select data from user1_view."
\! echo "Must be ok becouse user2 has SELECT privilege"
\! echo "on user1_view and this is true. This is good."
SELECT * FROM user1_view;
\! echo
\! echo "user2 tries to insert data into user1_view."
\! echo "Must be error becouse user2 don't have INSERT privilege"
\! echo "on user1_view but this is false. This is WRONG!!!!"
INSERT INTO user1_view VALUES ('test data');
\! echo
\! echo "user2 tries to update data in user1_view."
\! echo "Must be error becouse user2 don't have UPDATE privilege"
\! echo "on user1_view but this is false. This is WRONG!!!!"
\! echo "This is also very strange becouse user1 grants SELECT privelege"
\! echo "on user1_view to user2 but he also gets UPDATE privelege on it."
UPDATE user1_view SET data = data || USER;
\! echo
\! echo "user2 tries to delete data from user1_view."
\! echo "Must be error becouse user2 don't have DELETE privilege"
\! echo "on user1_view but this is false. This is WRONG!!!!"
DELETE FROM user1_view;
SET
Show full PostgreSQL version.
version
---------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 запись)
Create user with name user1
CREATE USER
Create user with name user2
CREATE USER
Switch to user1 with set session authrization
SET
user1 creates private table with name user1_table
CREATE TABLE
and revokes all rights on it from public.
REVOKE
Next user1 creates private view for table user1_table with name user1_view
and appropriate insert, update and delete rules on it.
CREATE VIEW
CREATE RULE
CREATE RULE
CREATE RULE
and revokes all rights on it from public.
REVOKE
Switch to user2 with set session authrization
SET
user2 tries to select data from user1_table.
Must be error becouse user2 don't have SELECT privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:51: ERROR: user1_table: permission denied
user2 tries to insert data into user1_table.
Must be error becouse user2 don't have INSERT privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:57: ERROR: user1_table: permission denied
user2 tries to update data in user1_table.
Must be error becouse user2 don't have UPDATE privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:63: ERROR: user1_table: permission denied
user2 tries to delete data from user1_table.
Must be error becouse user2 don't have DELETE privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:69: ERROR: user1_table: permission denied
user2 tries to select data from user1_view.
Must be error becouse user2 don't have SELECT privilege
on user1_view and this is true. This is good.
psql:privileges_test.sql:75: ERROR: user1_view: permission denied
user2 tries to insert data into user1_view.
Must be error becouse user2 don't have INSERT privilege
on user1_view but this is false. This is WRONG!!!!
INSERT 16990 1
user2 tries to update data in user1_view.
Must be error becouse user2 don't have UPDATE privilege
on user1_view and this is true. This is good.
psql:privileges_test.sql:87: ERROR: user1_view: permission denied
user2 tries to delete data from user1_view.
Must be error becouse user2 don't have DELETE privilege
on user1_view but this is false. This is WRONG!!!!
DELETE 1
Switch back to user1 with set session authrization
SET
user1 grants SELECT privilege on user1_view to user2
GRANT
Switch back to user2 with set session authrization
SET
user2 tries to select data from user1_view.
Must be ok becouse user2 has SELECT privilege
on user1_view and this is true. This is good.
data
------
(записей: 0)
user2 tries to insert data into user1_view.
Must be error becouse user2 don't have INSERT privilege
on user1_view but this is false. This is WRONG!!!!
INSERT 16991 1
user2 tries to update data in user1_view.
Must be error becouse user2 don't have UPDATE privilege
on user1_view but this is false. This is WRONG!!!!
This is also very strange becouse user1 grants SELECT privelege
on user1_view to user2 but he also gets UPDATE privelege on it.
UPDATE 1
user2 tries to delete data from user1_view.
Must be error becouse user2 don't have DELETE privilege
on user1_view but this is false. This is WRONG!!!!
DELETE 1
SET
Show full PostgreSQL version.
version
---------------------------------------------------------------
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 запись)
Create user with name user1
CREATE USER
Create user with name user2
CREATE USER
Switch to user1 with set session authrization
SET
user1 creates private table with name user1_table
CREATE TABLE
and revokes all rights on it from public.
REVOKE
Next user1 creates private view for table user1_table with name user1_view
and appropriate insert, update and delete rules on it.
CREATE VIEW
CREATE RULE
CREATE RULE
CREATE RULE
and revokes all rights on it from public.
REVOKE
Switch to user2 with set session authrization
SET
user2 tries to select data from user1_table.
Must be error becouse user2 don't have SELECT privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:51: ERROR: permission denied for relation user1_table
user2 tries to insert data into user1_table.
Must be error becouse user2 don't have INSERT privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:57: ERROR: permission denied for relation user1_table
user2 tries to update data in user1_table.
Must be error becouse user2 don't have UPDATE privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:63: ERROR: permission denied for relation user1_table
user2 tries to delete data from user1_table.
Must be error becouse user2 don't have DELETE privilege
on user1_table and this is true. This is good.
psql:privileges_test.sql:69: ERROR: permission denied for relation user1_table
user2 tries to select data from user1_view.
Must be error becouse user2 don't have SELECT privilege
on user1_view and this is true. This is good.
psql:privileges_test.sql:75: ERROR: permission denied for relation user1_view
user2 tries to insert data into user1_view.
Must be error becouse user2 don't have INSERT privilege
on user1_view but this is false. This is WRONG!!!!
INSERT 154629 1
user2 tries to update data in user1_view.
Must be error becouse user2 don't have UPDATE privilege
on user1_view and this is true. This is good.
psql:privileges_test.sql:87: ERROR: permission denied for relation user1_view
user2 tries to delete data from user1_view.
Must be error becouse user2 don't have DELETE privilege
on user1_view but this is false. This is WRONG!!!!
DELETE 1
Switch back to user1 with set session authrization
SET
user1 grants SELECT privilege on user1_view to user2
GRANT
Switch back to user2 with set session authrization
SET
user2 tries to select data from user1_view.
Must be ok becouse user2 has SELECT privilege
on user1_view and this is true. This is good.
data
------
(записей: 0)
user2 tries to insert data into user1_view.
Must be error becouse user2 don't have INSERT privilege
on user1_view but this is false. This is WRONG!!!!
INSERT 154630 1
user2 tries to update data in user1_view.
Must be error becouse user2 don't have UPDATE privilege
on user1_view but this is false. This is WRONG!!!!
This is also very strange becouse user1 grants SELECT privelege
on user1_view to user2 but he also gets UPDATE privelege on it.
UPDATE 1
user2 tries to delete data from user1_view.
Must be error becouse user2 don't have DELETE privilege
on user1_view but this is false. This is WRONG!!!!
DELETE 1
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
