Skip to content

Commit 8772241

Browse files
scrappyscrappy
committed
From: Jan Wieck <jwieck@debis.com>
 I thought it would be a good idea to ensure that the new view permission model will not get broken by subsequent fixes/changes. So I wrote a little regression test for it. There is an ugly thing in this regression test. It creates temporary a test user that is required for the tests. The user is removed at the end of the test, but if sometimes the regression suite is aborted or crashes exactly here, the test user will lay around in the pg_shadow. Don't have a clue how to get around.
1 parent 50e4120 commit 8772241

File tree

2 files changed

+222
-0
lines changed

2 files changed

+222
-0
lines changed

‎src/test/regress/expected/view_perms.out

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,101 @@
1+
QUERY: CREATE FUNCTION viewperms_nextid () RETURNS int4 AS '
2+
SELECT max(usesysid) + 1 AS ret FROM pg_user;
3+
' LANGUAGE 'sql';
4+
QUERY: CREATE FUNCTION viewperms_testid () RETURNS oid AS '
5+
SELECT oid(textin(int4out(usesysid))) FROM pg_user
6+
WHERE usename = ''viewperms_testuser'';
7+
' LANGUAGE 'sql';
8+
QUERY: INSERT INTO pg_shadow VALUES (
9+
'viewperms_testuser',
10+
viewperms_nextid(),
11+
false, true, false, true,
12+
NULL, NULL
13+
);
14+
QUERY: CREATE TABLE viewperms_t1 (
15+
a int4,
16+
b text
17+
);
18+
QUERY: CREATE TABLE viewperms_t2 (
19+
a int4,
20+
b text
21+
);
22+
QUERY: INSERT INTO viewperms_t1 VALUES (1, 'one');
23+
QUERY: INSERT INTO viewperms_t1 VALUES (2, 'two');
24+
QUERY: INSERT INTO viewperms_t1 VALUES (3, 'three');
25+
QUERY: INSERT INTO viewperms_t2 VALUES (1, 'one');
26+
QUERY: INSERT INTO viewperms_t2 VALUES (2, 'two');
27+
QUERY: INSERT INTO viewperms_t2 VALUES (3, 'three');
28+
QUERY: CREATE VIEW viewperms_v1 AS SELECT * FROM viewperms_t1;
29+
QUERY: CREATE VIEW viewperms_v2 AS SELECT * FROM viewperms_t2;
30+
QUERY: CREATE VIEW viewperms_v3 AS SELECT * FROM viewperms_t1;
31+
QUERY: CREATE VIEW viewperms_v4 AS SELECT * FROM viewperms_t2;
32+
QUERY: CREATE VIEW viewperms_v5 AS SELECT * FROM viewperms_v1;
33+
QUERY: CREATE VIEW viewperms_v6 AS SELECT * FROM viewperms_v4;
34+
QUERY: CREATE VIEW viewperms_v7 AS SELECT * FROM viewperms_v2;
35+
QUERY: UPDATE pg_class SET relowner = viewperms_testid()
36+
WHERE relname = 'viewperms_t1';
37+
QUERY: UPDATE pg_class SET relowner = viewperms_testid()
38+
WHERE relname = 'viewperms_v3';
39+
QUERY: UPDATE pg_class SET relowner = viewperms_testid()
40+
WHERE relname = 'viewperms_v4';
41+
QUERY: UPDATE pg_class SET relowner = viewperms_testid()
42+
WHERE relname = 'viewperms_v7';
43+
QUERY: SELECT * FROM viewperms_v1;
44+
a|b
45+
-+-----
46+
1|one
47+
2|two
48+
3|three
49+
(3 rows)
50+
51+
QUERY: SELECT * FROM viewperms_v2;
52+
a|b
53+
-+-----
54+
1|one
55+
2|two
56+
3|three
57+
(3 rows)
58+
59+
QUERY: SELECT * FROM viewperms_v3;
60+
a|b
61+
-+-----
62+
1|one
63+
2|two
64+
3|three
65+
(3 rows)
66+
67+
QUERY: SELECT * FROM viewperms_v4;
68+
ERROR: viewperms_t2: Permission denied.
69+
QUERY: SELECT * FROM viewperms_v5;
70+
a|b
71+
-+-----
72+
1|one
73+
2|two
74+
3|three
75+
(3 rows)
76+
77+
QUERY: SELECT * FROM viewperms_v6;
78+
ERROR: viewperms_t2: Permission denied.
79+
QUERY: SELECT * FROM viewperms_v7;
80+
ERROR: viewperms_v2: Permission denied.
81+
QUERY: GRANT SELECT ON viewperms_v2 TO PUBLIC;
82+
QUERY: SELECT * FROM viewperms_v7;
83+
a|b
84+
-+-----
85+
1|one
86+
2|two
87+
3|three
88+
(3 rows)
89+
90+
QUERY: DROP VIEW viewperms_v1;
91+
QUERY: DROP VIEW viewperms_v2;
92+
QUERY: DROP VIEW viewperms_v3;
93+
QUERY: DROP VIEW viewperms_v4;
94+
QUERY: DROP VIEW viewperms_v5;
95+
QUERY: DROP VIEW viewperms_v6;
96+
QUERY: DROP VIEW viewperms_v7;
97+
QUERY: DROP TABLE viewperms_t1;
98+
QUERY: DROP TABLE viewperms_t2;
99+
QUERY: DROP FUNCTION viewperms_nextid ();
100+
QUERY: DROP FUNCTION viewperms_testid ();
101+
QUERY: DELETE FROM pg_shadow WHERE usename = 'viewperms_testuser';

‎src/test/regress/sql/view_perms.sql

Lines changed: 121 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,121 @@
1+
--
2+
-- Create a new user with the next unused usesysid
3+
--
4+
CREATE FUNCTION viewperms_nextid () RETURNS int4 AS '
5+
SELECT max(usesysid) + 1 AS ret FROM pg_user;
6+
' LANGUAGE 'sql';
7+
8+
CREATE FUNCTION viewperms_testid () RETURNS oid AS '
9+
SELECT oid(textin(int4out(usesysid))) FROM pg_user
10+
WHERE usename = ''viewperms_testuser'';
11+
' LANGUAGE 'sql';
12+
13+
INSERT INTO pg_shadow VALUES (
14+
'viewperms_testuser',
15+
viewperms_nextid(),
16+
false, true, false, true,
17+
NULL, NULL
18+
);
19+
20+
--
21+
-- Create tables and views
22+
--
23+
CREATE TABLE viewperms_t1 (
24+
a int4,
25+
b text
26+
);
27+
28+
CREATE TABLE viewperms_t2 (
29+
a int4,
30+
b text
31+
);
32+
33+
INSERT INTO viewperms_t1 VALUES (1, 'one');
34+
INSERT INTO viewperms_t1 VALUES (2, 'two');
35+
INSERT INTO viewperms_t1 VALUES (3, 'three');
36+
37+
INSERT INTO viewperms_t2 VALUES (1, 'one');
38+
INSERT INTO viewperms_t2 VALUES (2, 'two');
39+
INSERT INTO viewperms_t2 VALUES (3, 'three');
40+
41+
CREATE VIEW viewperms_v1 AS SELECT * FROM viewperms_t1;
42+
CREATE VIEW viewperms_v2 AS SELECT * FROM viewperms_t2;
43+
CREATE VIEW viewperms_v3 AS SELECT * FROM viewperms_t1;
44+
CREATE VIEW viewperms_v4 AS SELECT * FROM viewperms_t2;
45+
CREATE VIEW viewperms_v5 AS SELECT * FROM viewperms_v1;
46+
CREATE VIEW viewperms_v6 AS SELECT * FROM viewperms_v4;
47+
CREATE VIEW viewperms_v7 AS SELECT * FROM viewperms_v2;
48+
49+
--
50+
-- Change ownership
51+
-- t1 tuser
52+
-- t2 pgslq
53+
-- v1 pgslq
54+
-- v2 pgslq
55+
-- v3 tuser
56+
-- v4 tuser
57+
-- v5 pgsql
58+
-- v6 pgsql
59+
-- v7 tuser
60+
--
61+
UPDATE pg_class SET relowner = viewperms_testid()
62+
WHERE relname = 'viewperms_t1';
63+
UPDATE pg_class SET relowner = viewperms_testid()
64+
WHERE relname = 'viewperms_v3';
65+
UPDATE pg_class SET relowner = viewperms_testid()
66+
WHERE relname = 'viewperms_v4';
67+
UPDATE pg_class SET relowner = viewperms_testid()
68+
WHERE relname = 'viewperms_v7';
69+
70+
--
71+
-- Now for the tests.
72+
--
73+
74+
-- View v1 owner pgsql has access to t1 owned by tuser
75+
SELECT * FROM viewperms_v1;
76+
77+
-- View v2 owner pgsql has access to t2 owned by pgsql (of cause)
78+
SELECT * FROM viewperms_v2;
79+
80+
-- View v3 owner tuser has access to t1 owned by tuser
81+
SELECT * FROM viewperms_v3;
82+
83+
-- View v4 owner tuser has NO access to t2 owned by pgsql
84+
-- MUST fail with permission denied
85+
SELECT * FROM viewperms_v4;
86+
87+
-- v5 (pgsql) can access v2 (pgsql) can access t1 (tuser)
88+
SELECT * FROM viewperms_v5;
89+
90+
-- v6 (pgsql) can access v4 (tuser) CANNOT access t2 (pgsql)
91+
SELECT * FROM viewperms_v6;
92+
93+
-- v7 (tuser) CANNOT access v2 (pgsql) wanna access t2 (pgslq)
94+
SELECT * FROM viewperms_v7;
95+
96+
GRANT SELECT ON viewperms_v2 TO PUBLIC;
97+
-- but now
98+
-- v7 (tuser) can access v2 (pgsql via grant) can access t2 (pgsql)
99+
SELECT * FROM viewperms_v7;
100+
101+
--
102+
-- Tidy up - we remove the testuser below and we don't let
103+
-- objects lay around with bad owner reference
104+
--
105+
DROP VIEW viewperms_v1;
106+
DROP VIEW viewperms_v2;
107+
DROP VIEW viewperms_v3;
108+
DROP VIEW viewperms_v4;
109+
DROP VIEW viewperms_v5;
110+
DROP VIEW viewperms_v6;
111+
DROP VIEW viewperms_v7;
112+
DROP TABLE viewperms_t1;
113+
DROP TABLE viewperms_t2;
114+
DROP FUNCTION viewperms_nextid ();
115+
DROP FUNCTION viewperms_testid ();
116+
117+
--
118+
-- Remove the testuser
119+
--
120+
DELETE FROM pg_shadow WHERE usename = 'viewperms_testuser';
121+

0 commit comments

Comments
 (0)