aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/test/regress/expected/privileges.out30
-rw-r--r--src/test/regress/expected/view_perms.out101
-rw-r--r--src/test/regress/sql/privileges.sql18
-rw-r--r--src/test/regress/sql/view_perms.sql121
4 files changed, 48 insertions, 222 deletions
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index adc45332509..e1fe2f60246 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 b2632c8dfdf..00000000000
--- 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 21ef1efe820..e53b600d753 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 aed0ac7ce5c..00000000000
--- 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';
-
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 20:18:44 +0000