Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8772241

Browse files
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 parent50e4120 commit8772241

File tree

2 files changed

+222
-0
lines changed

2 files changed

+222
-0
lines changed
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+
aint4,
16+
btext
17+
);
18+
QUERY: CREATE TABLE viewperms_t2 (
19+
aint4,
20+
btext
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+
CREATEFUNCTIONviewperms_nextid () RETURNS int4AS'
5+
SELECT max(usesysid) + 1 AS ret FROM pg_user;
6+
' LANGUAGE'sql';
7+
8+
CREATEFUNCTIONviewperms_testid () RETURNSoidAS'
9+
SELECT oid(textin(int4out(usesysid))) FROM pg_user
10+
WHERE usename =''viewperms_testuser'';
11+
' LANGUAGE'sql';
12+
13+
INSERT INTO pg_shadowVALUES (
14+
'viewperms_testuser',
15+
viewperms_nextid(),
16+
false, true, false, true,
17+
NULL,NULL
18+
);
19+
20+
--
21+
-- Create tables and views
22+
--
23+
CREATETABLEviewperms_t1 (
24+
aint4,
25+
btext
26+
);
27+
28+
CREATETABLEviewperms_t2 (
29+
aint4,
30+
btext
31+
);
32+
33+
INSERT INTO viewperms_t1VALUES (1,'one');
34+
INSERT INTO viewperms_t1VALUES (2,'two');
35+
INSERT INTO viewperms_t1VALUES (3,'three');
36+
37+
INSERT INTO viewperms_t2VALUES (1,'one');
38+
INSERT INTO viewperms_t2VALUES (2,'two');
39+
INSERT INTO viewperms_t2VALUES (3,'three');
40+
41+
CREATEVIEWviewperms_v1ASSELECT*FROM viewperms_t1;
42+
CREATEVIEWviewperms_v2ASSELECT*FROM viewperms_t2;
43+
CREATEVIEWviewperms_v3ASSELECT*FROM viewperms_t1;
44+
CREATEVIEWviewperms_v4ASSELECT*FROM viewperms_t2;
45+
CREATEVIEWviewperms_v5ASSELECT*FROM viewperms_v1;
46+
CREATEVIEWviewperms_v6ASSELECT*FROM viewperms_v4;
47+
CREATEVIEWviewperms_v7ASSELECT*FROM viewperms_v2;
48+
49+
--
50+
-- Change ownership
51+
-- t1tuser
52+
-- t2pgslq
53+
-- v1pgslq
54+
-- v2pgslq
55+
-- v3tuser
56+
-- v4tuser
57+
-- v5pgsql
58+
-- v6pgsql
59+
-- v7tuser
60+
--
61+
UPDATE pg_classSET relowner= viewperms_testid()
62+
WHERE relname='viewperms_t1';
63+
UPDATE pg_classSET relowner= viewperms_testid()
64+
WHERE relname='viewperms_v3';
65+
UPDATE pg_classSET relowner= viewperms_testid()
66+
WHERE relname='viewperms_v4';
67+
UPDATE pg_classSET 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+
GRANTSELECTON 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+
DROPVIEW viewperms_v1;
106+
DROPVIEW viewperms_v2;
107+
DROPVIEW viewperms_v3;
108+
DROPVIEW viewperms_v4;
109+
DROPVIEW viewperms_v5;
110+
DROPVIEW viewperms_v6;
111+
DROPVIEW viewperms_v7;
112+
DROPTABLE viewperms_t1;
113+
DROPTABLE viewperms_t2;
114+
DROPFUNCTION viewperms_nextid ();
115+
DROPFUNCTION viewperms_testid ();
116+
117+
--
118+
-- Remove the testuser
119+
--
120+
DELETEFROM pg_shadowWHERE usename='viewperms_testuser';
121+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp