From 13cd9eb9b85f871d3ae88c92765001fbb2080fe6 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sun, 19 May 2002 15:13:20 +0000 Subject: [PATCH] Remove bitrotten view_perms test. Add some similar test cases to privileges test. --- src/test/regress/expected/privileges.out | 30 ++++++ src/test/regress/expected/view_perms.out | 101 ------------------- src/test/regress/sql/privileges.sql | 18 ++++ src/test/regress/sql/view_perms.sql | 121 ----------------------- 4 files changed, 48 insertions(+), 222 deletions(-) delete mode 100644 src/test/regress/expected/view_perms.out delete mode 100644 src/test/regress/sql/view_perms.sql diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index adc4533250..e1fe2f6024 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -174,7 +174,10 @@ SELECT * FROM atestv1; -- ok 1 | two (2 rows) +SELECT * FROM atestv2; -- fail +ERROR: atest2: permission denied GRANT SELECT ON atestv1, atestv3 TO regressuser4; +GRANT SELECT ON atestv2 TO regressuser2; SET SESSION AUTHORIZATION regressuser4; SELECT * FROM atestv1; -- ok a | b @@ -183,11 +186,37 @@ SELECT * FROM atestv1; -- ok 1 | two (2 rows) +SELECT * FROM atestv2; -- fail +ERROR: atestv2: permission denied SELECT * FROM atestv3; -- ok one | two | three -----+-----+------- (0 rows) +CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view +SELECT * FROM atestv4; -- ok + one | two | three +-----+-----+------- +(0 rows) + +GRANT SELECT ON atestv4 TO regressuser2; +SET SESSION AUTHORIZATION regressuser2; +-- Two complex cases: +SELECT * FROM atestv3; -- fail +ERROR: atestv3: permission denied +SELECT * FROM atestv4; -- ok (even though regressuser2 cannot access underlying atestv3) + one | two | three +-----+-----+------- +(0 rows) + +SELECT * FROM atest2; -- ok + col1 | col2 +------+------ + bar | t +(1 row) + +SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying atest2) +ERROR: atest2: permission denied -- privileges on functions, languages -- switch to superuser \c - @@ -521,6 +550,7 @@ DROP TABLE atest3; DROP VIEW atestv1; DROP VIEW atestv2; DROP VIEW atestv3; +DROP VIEW atestv4; DROP GROUP regressgroup1; DROP GROUP regressgroup2; DROP USER regressuser1; diff --git a/src/test/regress/expected/view_perms.out b/src/test/regress/expected/view_perms.out deleted file mode 100644 index b2632c8dfd..0000000000 --- a/src/test/regress/expected/view_perms.out +++ /dev/null @@ -1,101 +0,0 @@ -QUERY: CREATE FUNCTION viewperms_nextid () RETURNS int4 AS ' - SELECT max(usesysid) + 1 AS ret FROM pg_user; - ' LANGUAGE 'sql'; -QUERY: CREATE FUNCTION viewperms_testid () RETURNS oid AS ' - SELECT oid(textin(int4out(usesysid))) FROM pg_user - WHERE usename = ''viewperms_testuser''; - ' LANGUAGE 'sql'; -QUERY: INSERT INTO pg_shadow VALUES ( - 'viewperms_testuser', - viewperms_nextid(), - false, true, false, true, - NULL, NULL - ); -QUERY: CREATE TABLE viewperms_t1 ( - a int4, - b text - ); -QUERY: CREATE TABLE viewperms_t2 ( - a int4, - b text - ); -QUERY: INSERT INTO viewperms_t1 VALUES (1, 'one'); -QUERY: INSERT INTO viewperms_t1 VALUES (2, 'two'); -QUERY: INSERT INTO viewperms_t1 VALUES (3, 'three'); -QUERY: INSERT INTO viewperms_t2 VALUES (1, 'one'); -QUERY: INSERT INTO viewperms_t2 VALUES (2, 'two'); -QUERY: INSERT INTO viewperms_t2 VALUES (3, 'three'); -QUERY: CREATE VIEW viewperms_v1 AS SELECT * FROM viewperms_t1; -QUERY: CREATE VIEW viewperms_v2 AS SELECT * FROM viewperms_t2; -QUERY: CREATE VIEW viewperms_v3 AS SELECT * FROM viewperms_t1; -QUERY: CREATE VIEW viewperms_v4 AS SELECT * FROM viewperms_t2; -QUERY: CREATE VIEW viewperms_v5 AS SELECT * FROM viewperms_v1; -QUERY: CREATE VIEW viewperms_v6 AS SELECT * FROM viewperms_v4; -QUERY: CREATE VIEW viewperms_v7 AS SELECT * FROM viewperms_v2; -QUERY: UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_t1'; -QUERY: UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_v3'; -QUERY: UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_v4'; -QUERY: UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_v7'; -QUERY: SELECT * FROM viewperms_v1; -a|b --+----- -1|one -2|two -3|three -(3 rows) - -QUERY: SELECT * FROM viewperms_v2; -a|b --+----- -1|one -2|two -3|three -(3 rows) - -QUERY: SELECT * FROM viewperms_v3; -a|b --+----- -1|one -2|two -3|three -(3 rows) - -QUERY: SELECT * FROM viewperms_v4; -ERROR: viewperms_t2: Permission denied. -QUERY: SELECT * FROM viewperms_v5; -a|b --+----- -1|one -2|two -3|three -(3 rows) - -QUERY: SELECT * FROM viewperms_v6; -ERROR: viewperms_t2: Permission denied. -QUERY: SELECT * FROM viewperms_v7; -ERROR: viewperms_v2: Permission denied. -QUERY: GRANT SELECT ON viewperms_v2 TO PUBLIC; -QUERY: SELECT * FROM viewperms_v7; -a|b --+----- -1|one -2|two -3|three -(3 rows) - -QUERY: DROP VIEW viewperms_v1; -QUERY: DROP VIEW viewperms_v2; -QUERY: DROP VIEW viewperms_v3; -QUERY: DROP VIEW viewperms_v4; -QUERY: DROP VIEW viewperms_v5; -QUERY: DROP VIEW viewperms_v6; -QUERY: DROP VIEW viewperms_v7; -QUERY: DROP TABLE viewperms_t1; -QUERY: DROP TABLE viewperms_t2; -QUERY: DROP FUNCTION viewperms_nextid (); -QUERY: DROP FUNCTION viewperms_testid (); -QUERY: DELETE FROM pg_shadow WHERE usename = 'viewperms_testuser'; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 21ef1efe82..e53b600d75 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -118,13 +118,30 @@ CREATE VIEW atestv2 AS SELECT * FROM atest2; CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok SELECT * FROM atestv1; -- ok +SELECT * FROM atestv2; -- fail GRANT SELECT ON atestv1, atestv3 TO regressuser4; +GRANT SELECT ON atestv2 TO regressuser2; SET SESSION AUTHORIZATION regressuser4; SELECT * FROM atestv1; -- ok +SELECT * FROM atestv2; -- fail SELECT * FROM atestv3; -- ok +CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view +SELECT * FROM atestv4; -- ok +GRANT SELECT ON atestv4 TO regressuser2; + +SET SESSION AUTHORIZATION regressuser2; + +-- Two complex cases: + +SELECT * FROM atestv3; -- fail +SELECT * FROM atestv4; -- ok (even though regressuser2 cannot access underlying atestv3) + +SELECT * FROM atest2; -- ok +SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying atest2) + -- privileges on functions, languages @@ -282,6 +299,7 @@ DROP TABLE atest3; DROP VIEW atestv1; DROP VIEW atestv2; DROP VIEW atestv3; +DROP VIEW atestv4; DROP GROUP regressgroup1; DROP GROUP regressgroup2; diff --git a/src/test/regress/sql/view_perms.sql b/src/test/regress/sql/view_perms.sql deleted file mode 100644 index aed0ac7ce5..0000000000 --- a/src/test/regress/sql/view_perms.sql +++ /dev/null @@ -1,121 +0,0 @@ --- --- Create a new user with the next unused usesysid --- -CREATE FUNCTION viewperms_nextid () RETURNS int4 AS ' - SELECT max(usesysid) + 1 AS ret FROM pg_user; - ' LANGUAGE 'sql'; - -CREATE FUNCTION viewperms_testid () RETURNS oid AS ' - SELECT oid(textin(int4out(usesysid))) FROM pg_user - WHERE usename = ''viewperms_testuser''; - ' LANGUAGE 'sql'; - -INSERT INTO pg_shadow VALUES ( - 'viewperms_testuser', - viewperms_nextid(), - false, true, false, true, - NULL, NULL - ); - --- --- Create tables and views --- -CREATE TABLE viewperms_t1 ( - a int4, - b text - ); - -CREATE TABLE viewperms_t2 ( - a int4, - b text - ); - -INSERT INTO viewperms_t1 VALUES (1, 'one'); -INSERT INTO viewperms_t1 VALUES (2, 'two'); -INSERT INTO viewperms_t1 VALUES (3, 'three'); - -INSERT INTO viewperms_t2 VALUES (1, 'one'); -INSERT INTO viewperms_t2 VALUES (2, 'two'); -INSERT INTO viewperms_t2 VALUES (3, 'three'); - -CREATE VIEW viewperms_v1 AS SELECT * FROM viewperms_t1; -CREATE VIEW viewperms_v2 AS SELECT * FROM viewperms_t2; -CREATE VIEW viewperms_v3 AS SELECT * FROM viewperms_t1; -CREATE VIEW viewperms_v4 AS SELECT * FROM viewperms_t2; -CREATE VIEW viewperms_v5 AS SELECT * FROM viewperms_v1; -CREATE VIEW viewperms_v6 AS SELECT * FROM viewperms_v4; -CREATE VIEW viewperms_v7 AS SELECT * FROM viewperms_v2; - --- --- Change ownership --- t1 tuser --- t2 pgslq --- v1 pgslq --- v2 pgslq --- v3 tuser --- v4 tuser --- v5 postgres --- v6 postgres --- v7 tuser --- -UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_t1'; -UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_v3'; -UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_v4'; -UPDATE pg_class SET relowner = viewperms_testid() - WHERE relname = 'viewperms_v7'; - --- --- Now for the tests. --- - --- View v1 owner postgres has access to t1 owned by tuser -SELECT * FROM viewperms_v1; - --- View v2 owner postgres has access to t2 owned by postgres (of cause) -SELECT * FROM viewperms_v2; - --- View v3 owner tuser has access to t1 owned by tuser -SELECT * FROM viewperms_v3; - --- View v4 owner tuser has NO access to t2 owned by postgres --- MUST fail with permission denied -SELECT * FROM viewperms_v4; - --- v5 (postgres) can access v2 (postgres) can access t1 (tuser) -SELECT * FROM viewperms_v5; - --- v6 (postgres) can access v4 (tuser) CANNOT access t2 (postgres) -SELECT * FROM viewperms_v6; - --- v7 (tuser) CANNOT access v2 (postgres) wanna access t2 (pgslq) -SELECT * FROM viewperms_v7; - -GRANT SELECT ON viewperms_v2 TO PUBLIC; --- but now --- v7 (tuser) can access v2 (postgres via grant) can access t2 (postgres) -SELECT * FROM viewperms_v7; - --- --- Tidy up - we remove the testuser below and we don't let --- objects lay around with bad owner reference --- -DROP VIEW viewperms_v1; -DROP VIEW viewperms_v2; -DROP VIEW viewperms_v3; -DROP VIEW viewperms_v4; -DROP VIEW viewperms_v5; -DROP VIEW viewperms_v6; -DROP VIEW viewperms_v7; -DROP TABLE viewperms_t1; -DROP TABLE viewperms_t2; -DROP FUNCTION viewperms_nextid (); -DROP FUNCTION viewperms_testid (); - --- --- Remove the testuser --- -DELETE FROM pg_shadow WHERE usename = 'viewperms_testuser'; - -- 2.40.0