-- 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;