Make UPDATE and DELETE privileges distinct. Add REFERENCES and TRIGGER

privileges.  INSERT and COPY FROM now require INSERT (only).  Add
privileges regression test.

5 files changed, 360 insertions, 2 deletions

diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
new file mode 100644
index 00000000000..cb376e0bbc8
--- /dev/null
+++ b/src/test/regress/expected/privileges.out
@@ -0,0 +1,198 @@
+--
+-- Test access privileges
+--
+CREATE USER regressuser1;
+CREATE USER regressuser2;
+CREATE USER regressuser3;
+CREATE USER regressuser4;
+CREATE USER regressuser4;	-- duplicate
+ERROR:  CREATE USER: user name "regressuser4" already exists
+CREATE GROUP regressgroup1;
+CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
+ALTER GROUP regressgroup1 ADD USER regressuser4;
+ALTER GROUP regressgroup2 ADD USER regressuser2;	-- duplicate
+NOTICE:  ALTER GROUP: user "regressuser2" is already in group "regressgroup2"
+ALTER GROUP regressgroup2 DROP USER regressuser2;
+ALTER GROUP regressgroup2 ADD USER regressuser4;
+-- test owner privileges
+SET SESSION AUTHORIZATION regressuser1;
+SELECT session_user, current_user;
+ session_user | current_user 
+--------------+--------------
+ regressuser1 | regressuser1
+(1 row)
+
+CREATE TABLE atest1 ( a int, b text );
+SELECT * FROM atest1;
+ a | b 
+---+---
+(0 rows)
+
+INSERT INTO atest1 VALUES (1, 'one');
+DELETE FROM atest1;
+UPDATE atest1 SET a = 1 WHERE b = 'blech';
+LOCK atest1 IN ACCESS EXCLUSIVE MODE;
+REVOKE ALL ON atest1 FROM PUBLIC;
+SELECT * FROM atest1;
+ a | b 
+---+---
+(0 rows)
+
+GRANT ALL ON atest1 TO regressuser2;
+GRANT SELECT ON atest1 TO regressuser3;
+SELECT * FROM atest1;
+ a | b 
+---+---
+(0 rows)
+
+CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+GRANT SELECT ON atest2 TO regressuser2;
+GRANT UPDATE ON atest2 TO regressuser3;
+GRANT INSERT ON atest2 TO regressuser4;
+SET SESSION AUTHORIZATION regressuser2;
+SELECT session_user, current_user;
+ session_user | current_user 
+--------------+--------------
+ regressuser2 | regressuser2
+(1 row)
+
+-- try various combinations of queries on atest1 and atest2
+SELECT * FROM atest1; -- ok
+ a | b 
+---+---
+(0 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2 
+------+------
+(0 rows)
+
+INSERT INTO atest1 VALUES (2, 'two'); -- ok
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR:  atest2: Permission denied.
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
+UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fail
+ERROR:  atest2: Permission denied.
+SELECT * FROM atest1 FOR UPDATE; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atest2 FOR UPDATE; -- fail
+ERROR:  atest2: Permission denied.
+DELETE FROM atest2; -- fail
+ERROR:  atest2: Permission denied.
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
+ERROR:  LOCK TABLE: permission denied
+COPY atest2 FROM stdin; -- fail
+ERROR:  atest2: Permission denied.
+GRANT ALL ON atest1 TO PUBLIC; -- fail
+ERROR:  you do not own class "atest1"
+-- checks in subquery, both ok
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+ a | b 
+---+---
+(0 rows)
+
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+ col1 | col2 
+------+------
+(0 rows)
+
+SET SESSION AUTHORIZATION regressuser3;
+SELECT session_user, current_user;
+ session_user | current_user 
+--------------+--------------
+ regressuser3 | regressuser3
+(1 row)
+
+SELECT * FROM atest1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atest2; -- fail
+ERROR:  atest2: Permission denied.
+INSERT INTO atest1 VALUES (2, 'two'); -- fail
+ERROR:  atest1: Permission denied.
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR:  atest2: Permission denied.
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
+ERROR:  atest1: Permission denied.
+UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
+ERROR:  atest1: Permission denied.
+UPDATE atest2 SET col2 = NULL; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
+ERROR:  atest2: Permission denied.
+UPDATE atest2 SET col2 = true WHERE atest1.a = 5; -- ok
+SELECT * FROM atest1 FOR UPDATE; -- fail
+ERROR:  atest1: Permission denied.
+SELECT * FROM atest2 FOR UPDATE; -- fail
+ERROR:  atest2: Permission denied.
+DELETE FROM atest2; -- fail
+ERROR:  atest2: Permission denied.
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
+COPY atest2 FROM stdin; -- fail
+ERROR:  atest2: Permission denied.
+-- checks in subquery, both fail
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+ERROR:  atest2: Permission denied.
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+ERROR:  atest2: Permission denied.
+SET SESSION AUTHORIZATION regressuser4;
+COPY atest2 FROM stdin; -- ok
+-- groups
+SET SESSION AUTHORIZATION regressuser3;
+CREATE TABLE atest3 (one int, two int, three int);
+GRANT DELETE ON atest3 TO GROUP regressgroup2;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT * FROM atest3; -- fail
+ERROR:  atest3: Permission denied.
+DELETE FROM atest3; -- ok
+-- views
+SET SESSION AUTHORIZATION regressuser3;
+CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
+/* The next *should* fail, but it's not implemented that way yet. */
+CREATE VIEW atestv2 AS SELECT * FROM atest2;
+CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
+SELECT * FROM atestv1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+GRANT SELECT ON atestv1 TO regressuser4;
+GRANT SELECT ON atestv3 TO regressuser4;
+SET SESSION AUTHORIZATION regressuser4;
+SELECT * FROM atestv1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atestv3; -- ok
+ one | two | three 
+-----+-----+-------
+(0 rows)
+
+-- clean up
+\c regression
+DROP TABLE atest1;
+DROP TABLE atest2;
+DROP TABLE atest3;
+DROP VIEW atestv1;
+DROP VIEW atestv2;
+DROP VIEW atestv3;
+DROP GROUP regressgroup1;
+DROP GROUP regressgroup2;
+DROP USER regressuser1;
+DROP USER regressuser2;
+DROP USER regressuser3;
+DROP USER regressuser4;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f37b2054e40..08e97511ac9 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -61,6 +61,7 @@ test: select
 ignore: random
 test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index
 
+test: privileges
 test: misc
 
 # ----------
diff --git a/src/test/regress/pg_regress.sh b/src/test/regress/pg_regress.sh
index 8eb74b28010..552724a10f1 100644
--- a/src/test/regress/pg_regress.sh
+++ b/src/test/regress/pg_regress.sh
@@ -1,5 +1,5 @@
 #! /bin/sh
-# $Header: /cvsroot/pgsql/src/test/regress/Attic/pg_regress.sh,v 1.20 2001/03/24 23:32:25 petere Exp $
+# $Header: /cvsroot/pgsql/src/test/regress/Attic/pg_regress.sh,v 1.21 2001/05/27 09:59:30 petere Exp $
 
 me=`basename $0`
 : ${TMPDIR=/tmp}
@@ -433,6 +433,18 @@ fi
 
 
 # ----------
+# Remove regressuser* and regressgroup* user accounts.
+# ----------
+
+message "dropping regression test user accounts"
+"$bindir/psql" $psql_options -c 'drop group regressgroup1; drop group regressgroup2; drop user regressuser1, regressuser2, regressuser3, regressuser4;' $dbname 2>/dev/null
+if [ $? -eq 2 ]; then
+    echo "$me: could not drop user accounts"
+    (exit 2); exit
+fi
+
+
+# ----------
 # Install the PL/pgSQL language in it
 # ----------
 
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 14ad6f505e0..afb0090cdad 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.3 2000/11/22 13:37:44 petere Exp $
+# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.4 2001/05/27 09:59:30 petere Exp $
 # This should probably be in an order similar to parallel_schedule.
 test: boolean
 test: char
@@ -68,6 +68,7 @@ test: portals
 test: arrays
 test: btree_index
 test: hash_index
+test: privileges
 test: misc
 test: select_views
 test: alter_table
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
new file mode 100644
index 00000000000..1558273f7b2
--- /dev/null
+++ b/src/test/regress/sql/privileges.sql
@@ -0,0 +1,146 @@
+--
+-- Test access privileges
+--
+
+CREATE USER regressuser1;
+CREATE USER regressuser2;
+CREATE USER regressuser3;
+CREATE USER regressuser4;
+CREATE USER regressuser4;	-- duplicate
+
+CREATE GROUP regressgroup1;
+CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
+
+ALTER GROUP regressgroup1 ADD USER regressuser4;
+
+ALTER GROUP regressgroup2 ADD USER regressuser2;	-- duplicate
+ALTER GROUP regressgroup2 DROP USER regressuser2;
+ALTER GROUP regressgroup2 ADD USER regressuser4;
+
+
+-- test owner privileges
+
+SET SESSION AUTHORIZATION regressuser1;
+SELECT session_user, current_user;
+
+CREATE TABLE atest1 ( a int, b text );
+SELECT * FROM atest1;
+INSERT INTO atest1 VALUES (1, 'one');
+DELETE FROM atest1;
+UPDATE atest1 SET a = 1 WHERE b = 'blech';
+LOCK atest1 IN ACCESS EXCLUSIVE MODE;
+
+REVOKE ALL ON atest1 FROM PUBLIC;
+SELECT * FROM atest1;
+
+GRANT ALL ON atest1 TO regressuser2;
+GRANT SELECT ON atest1 TO regressuser3;
+SELECT * FROM atest1;
+
+CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+GRANT SELECT ON atest2 TO regressuser2;
+GRANT UPDATE ON atest2 TO regressuser3;
+GRANT INSERT ON atest2 TO regressuser4;
+
+
+SET SESSION AUTHORIZATION regressuser2;
+SELECT session_user, current_user;
+
+-- try various combinations of queries on atest1 and atest2
+
+SELECT * FROM atest1; -- ok
+SELECT * FROM atest2; -- ok
+INSERT INTO atest1 VALUES (2, 'two'); -- ok
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
+UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fail
+SELECT * FROM atest1 FOR UPDATE; -- ok
+SELECT * FROM atest2 FOR UPDATE; -- fail
+DELETE FROM atest2; -- fail
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
+COPY atest2 FROM stdin; -- fail
+GRANT ALL ON atest1 TO PUBLIC; -- fail
+
+-- checks in subquery, both ok
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+
+
+SET SESSION AUTHORIZATION regressuser3;
+SELECT session_user, current_user;
+
+SELECT * FROM atest1; -- ok
+SELECT * FROM atest2; -- fail
+INSERT INTO atest1 VALUES (2, 'two'); -- fail
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
+UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
+UPDATE atest2 SET col2 = NULL; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
+UPDATE atest2 SET col2 = true WHERE atest1.a = 5; -- ok
+SELECT * FROM atest1 FOR UPDATE; -- fail
+SELECT * FROM atest2 FOR UPDATE; -- fail
+DELETE FROM atest2; -- fail
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
+COPY atest2 FROM stdin; -- fail
+
+-- checks in subquery, both fail
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+
+SET SESSION AUTHORIZATION regressuser4;
+COPY atest2 FROM stdin; -- ok
+bar	true
+\.
+
+
+-- groups
+
+SET SESSION AUTHORIZATION regressuser3;
+CREATE TABLE atest3 (one int, two int, three int);
+GRANT DELETE ON atest3 TO GROUP regressgroup2;
+
+SET SESSION AUTHORIZATION regressuser1;
+
+SELECT * FROM atest3; -- fail
+DELETE FROM atest3; -- ok
+
+
+-- views
+
+SET SESSION AUTHORIZATION regressuser3;
+
+CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
+/* The next *should* fail, but it's not implemented that way yet. */
+CREATE VIEW atestv2 AS SELECT * FROM atest2;
+CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
+
+SELECT * FROM atestv1; -- ok
+GRANT SELECT ON atestv1 TO regressuser4;
+GRANT SELECT ON atestv3 TO regressuser4;
+
+SET SESSION AUTHORIZATION regressuser4;
+
+SELECT * FROM atestv1; -- ok
+SELECT * FROM atestv3; -- ok
+
+
+-- clean up
+
+\c regression
+DROP TABLE atest1;
+DROP TABLE atest2;
+DROP TABLE atest3;
+
+DROP VIEW atestv1;
+DROP VIEW atestv2;
+DROP VIEW atestv3;
+
+DROP GROUP regressgroup1;
+DROP GROUP regressgroup2;
+
+DROP USER regressuser1;
+DROP USER regressuser2;
+DROP USER regressuser3;
+DROP USER regressuser4;