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

Commit7fc380f

Browse files
committed
Add a regression test for allow_system_table_mods
Add a regression test file that exercises the kinds of commands thatallow_system_table_mods allows.This is put in the "unsafe_tests" suite, so it won't accidentallycreate a mess if someone runs the normal regression tests against aninstance that they care about.Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://www.postgresql.org/message-id/flat/8b00ea5e-28a7-88ba-e848-21528b632354%402ndquadrant.com
1 parentc4a7a39 commit7fc380f

File tree

5 files changed

+354
-9
lines changed

5 files changed

+354
-9
lines changed

‎src/test/modules/unsafe_tests/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# src/test/modules/unsafe_tests/Makefile
22

3-
REGRESS = rolenames
3+
REGRESS = rolenames alter_system_table
44

55
ifdefUSE_PGXS
66
PG_CONFIG = pg_config
Lines changed: 168 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,168 @@
1+
--
2+
-- Tests for things affected by allow_system_table_mods
3+
--
4+
-- We run the same set of commands once with allow_system_table_mods
5+
-- off and then again with on.
6+
--
7+
-- The "on" tests should where possible be wrapped in BEGIN/ROLLBACK
8+
-- blocks so as to not leave a mess around.
9+
CREATE USER regress_user_ast;
10+
SET allow_system_table_mods = off;
11+
-- create new table in pg_catalog
12+
CREATE TABLE pg_catalog.test (a int);
13+
ERROR: permission denied to create "pg_catalog.test"
14+
DETAIL: System catalog modifications are currently disallowed.
15+
-- anyarray column
16+
CREATE TABLE t1x (a int, b anyarray);
17+
ERROR: column "b" has pseudo-type anyarray
18+
-- index on system catalog
19+
ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
20+
ERROR: permission denied: "pg_namespace" is a system catalog
21+
-- write to system catalog table as superuser
22+
-- (allowed even without allow_system_table_mods)
23+
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 0, 'foo');
24+
-- write to system catalog table as normal user
25+
GRANT INSERT ON pg_description TO regress_user_ast;
26+
SET ROLE regress_user_ast;
27+
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 1, 'foo');
28+
ERROR: permission denied for table pg_description
29+
RESET ROLE;
30+
-- policy on system catalog
31+
CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%');
32+
ERROR: permission denied: "pg_description" is a system catalog
33+
-- reserved schema name
34+
CREATE SCHEMA pg_foo;
35+
ERROR: unacceptable schema name "pg_foo"
36+
DETAIL: The prefix "pg_" is reserved for system schemas.
37+
-- drop system table
38+
DROP TABLE pg_description;
39+
ERROR: permission denied: "pg_description" is a system catalog
40+
-- truncate of system table
41+
TRUNCATE pg_description;
42+
ERROR: permission denied: "pg_description" is a system catalog
43+
-- rename column of system table
44+
ALTER TABLE pg_description RENAME COLUMN description TO comment;
45+
ERROR: permission denied: "pg_description" is a system catalog
46+
-- ATSimplePermissions()
47+
ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL;
48+
ERROR: permission denied: "pg_description" is a system catalog
49+
-- SET STATISTICS
50+
ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1;
51+
ERROR: permission denied: "pg_description" is a system catalog
52+
-- foreign key referencing catalog
53+
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description);
54+
ERROR: permission denied: "pg_description" is a system catalog
55+
-- RangeVarCallbackOwnsRelation()
56+
CREATE INDEX pg_descripton_test_index ON pg_description (description);
57+
ERROR: permission denied: "pg_description" is a system catalog
58+
-- RangeVarCallbackForAlterRelation()
59+
ALTER TABLE pg_description RENAME TO pg_comment;
60+
ERROR: permission denied: "pg_description" is a system catalog
61+
ALTER TABLE pg_description SET SCHEMA public;
62+
ERROR: permission denied: "pg_description" is a system catalog
63+
-- reserved tablespace name
64+
CREATE TABLESPACE pg_foo LOCATION '/no/such/location';
65+
ERROR: unacceptable tablespace name "pg_foo"
66+
DETAIL: The prefix "pg_" is reserved for system tablespaces.
67+
-- triggers
68+
CREATE FUNCTION tf1() RETURNS trigger
69+
LANGUAGE plpgsql
70+
AS $$
71+
BEGIN
72+
RETURN NULL;
73+
END $$;
74+
CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1();
75+
ERROR: permission denied: "pg_description" is a system catalog
76+
ALTER TRIGGER t1 ON pg_description RENAME TO t2;
77+
ERROR: permission denied: "pg_description" is a system catalog
78+
--DROP TRIGGER t2 ON pg_description;
79+
-- rules
80+
CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
81+
ERROR: permission denied: "pg_description" is a system catalog
82+
ALTER RULE r1 ON pg_description RENAME TO r2;
83+
ERROR: permission denied: "pg_description" is a system catalog
84+
--DROP RULE r2 ON pg_description;
85+
SET allow_system_table_mods = on;
86+
-- create new table in pg_catalog
87+
BEGIN;
88+
CREATE TABLE pg_catalog.test (a int);
89+
ROLLBACK;
90+
-- anyarray column
91+
BEGIN;
92+
CREATE TABLE t1 (a int, b anyarray);
93+
ROLLBACK;
94+
-- index on system catalog
95+
BEGIN;
96+
ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
97+
ROLLBACK;
98+
-- write to system catalog table as superuser
99+
BEGIN;
100+
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 2, 'foo');
101+
ROLLBACK;
102+
-- write to system catalog table as normal user
103+
-- (not allowed)
104+
SET ROLE regress_user_ast;
105+
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 3, 'foo');
106+
ERROR: permission denied for table pg_description
107+
RESET ROLE;
108+
-- policy on system catalog
109+
BEGIN;
110+
CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%');
111+
ROLLBACK;
112+
-- reserved schema name
113+
BEGIN;
114+
CREATE SCHEMA pg_foo;
115+
ROLLBACK;
116+
-- drop system table
117+
-- (This will fail anyway because it's pinned.)
118+
BEGIN;
119+
DROP TABLE pg_description;
120+
ERROR: cannot drop table pg_description because it is required by the database system
121+
ROLLBACK;
122+
-- truncate of system table
123+
BEGIN;
124+
TRUNCATE pg_description;
125+
ROLLBACK;
126+
-- rename column of system table
127+
BEGIN;
128+
ALTER TABLE pg_description RENAME COLUMN description TO comment;
129+
ROLLBACK;
130+
-- ATSimplePermissions()
131+
BEGIN;
132+
ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL;
133+
ROLLBACK;
134+
-- SET STATISTICS
135+
BEGIN;
136+
ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1;
137+
ROLLBACK;
138+
-- foreign key referencing catalog
139+
BEGIN;
140+
ALTER TABLE pg_description ADD PRIMARY KEY USING INDEX pg_description_o_c_o_index;
141+
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description);
142+
ROLLBACK;
143+
-- RangeVarCallbackOwnsRelation()
144+
BEGIN;
145+
CREATE INDEX pg_descripton_test_index ON pg_description (description);
146+
ROLLBACK;
147+
-- RangeVarCallbackForAlterRelation()
148+
BEGIN;
149+
ALTER TABLE pg_description RENAME TO pg_comment;
150+
ROLLBACK;
151+
BEGIN;
152+
ALTER TABLE pg_description SET SCHEMA public;
153+
ROLLBACK;
154+
-- reserved tablespace name
155+
CREATE TABLESPACE pg_foo LOCATION '/no/such/location';
156+
ERROR: directory "/no/such/location" does not exist
157+
-- triggers
158+
CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1();
159+
ALTER TRIGGER t1 ON pg_description RENAME TO t2;
160+
DROP TRIGGER t2 ON pg_description;
161+
-- rules
162+
CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
163+
ALTER RULE r1 ON pg_description RENAME TO r2;
164+
DROP RULE r2 ON pg_description;
165+
-- cleanup
166+
REVOKE ALL ON pg_description FROM regress_user_ast;
167+
DROP USER regress_user_ast;
168+
DROP FUNCTION tf1;
Lines changed: 185 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,185 @@
1+
--
2+
-- Tests for things affected by allow_system_table_mods
3+
--
4+
-- We run the same set of commands once with allow_system_table_mods
5+
-- off and then again with on.
6+
--
7+
-- The "on" tests should where possible be wrapped in BEGIN/ROLLBACK
8+
-- blocks so as to not leave a mess around.
9+
10+
CREATEUSERregress_user_ast;
11+
12+
SET allow_system_table_mods= off;
13+
14+
-- create new table in pg_catalog
15+
CREATETABLEpg_catalog.test (aint);
16+
17+
-- anyarray column
18+
CREATETABLEt1x (aint, b anyarray);
19+
20+
-- index on system catalog
21+
ALTERTABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
22+
23+
-- write to system catalog table as superuser
24+
-- (allowed even without allow_system_table_mods)
25+
INSERT INTO pg_description (objoid, classoid, objsubid, description)VALUES (0,0,0,'foo');
26+
27+
-- write to system catalog table as normal user
28+
GRANT INSERTON pg_description TO regress_user_ast;
29+
SET ROLE regress_user_ast;
30+
INSERT INTO pg_description (objoid, classoid, objsubid, description)VALUES (0,0,1,'foo');
31+
RESET ROLE;
32+
33+
-- policy on system catalog
34+
CREATE POLICY fooON pg_description FORSELECT USING (description NOTLIKE'secret%');
35+
36+
-- reserved schema name
37+
CREATESCHEMApg_foo;
38+
39+
-- drop system table
40+
DROPTABLE pg_description;
41+
42+
-- truncate of system table
43+
TRUNCATE pg_description;
44+
45+
-- rename column of system table
46+
ALTERTABLE pg_description RENAME COLUMN description TO comment;
47+
48+
-- ATSimplePermissions()
49+
ALTERTABLE pg_description ALTER COLUMN descriptionSETNOT NULL;
50+
51+
-- SET STATISTICS
52+
ALTERTABLE pg_description ALTER COLUMN descriptionSET STATISTICS-1;
53+
54+
-- foreign key referencing catalog
55+
CREATETABLEfoo (aoid, boid, cint,FOREIGN KEY (a, b, c)REFERENCES pg_description);
56+
57+
-- RangeVarCallbackOwnsRelation()
58+
CREATEINDEXpg_descripton_test_indexON pg_description (description);
59+
60+
-- RangeVarCallbackForAlterRelation()
61+
ALTERTABLE pg_description RENAME TO pg_comment;
62+
ALTERTABLE pg_descriptionSET SCHEMA public;
63+
64+
-- reserved tablespace name
65+
CREATETABLESPACEpg_foo LOCATION'/no/such/location';
66+
67+
-- triggers
68+
CREATEFUNCTIONtf1() RETURNS trigger
69+
LANGUAGE plpgsql
70+
AS $$
71+
BEGIN
72+
RETURNNULL;
73+
END $$;
74+
75+
CREATETRIGGERt1 BEFORE INSERTON pg_description EXECUTE FUNCTION tf1();
76+
ALTERTRIGGER t1ON pg_description RENAME TO t2;
77+
--DROP TRIGGER t2 ON pg_description;
78+
79+
-- rules
80+
CREATERULEr1ASON INSERT TO pg_description DO INSTEAD NOTHING;
81+
ALTERRULE r1ON pg_description RENAME TO r2;
82+
--DROP RULE r2 ON pg_description;
83+
84+
85+
SET allow_system_table_mods=on;
86+
87+
-- create new table in pg_catalog
88+
BEGIN;
89+
CREATETABLEpg_catalog.test (aint);
90+
ROLLBACK;
91+
92+
-- anyarray column
93+
BEGIN;
94+
CREATETABLEt1 (aint, b anyarray);
95+
ROLLBACK;
96+
97+
-- index on system catalog
98+
BEGIN;
99+
ALTERTABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
100+
ROLLBACK;
101+
102+
-- write to system catalog table as superuser
103+
BEGIN;
104+
INSERT INTO pg_description (objoid, classoid, objsubid, description)VALUES (0,0,2,'foo');
105+
ROLLBACK;
106+
107+
-- write to system catalog table as normal user
108+
-- (not allowed)
109+
SET ROLE regress_user_ast;
110+
INSERT INTO pg_description (objoid, classoid, objsubid, description)VALUES (0,0,3,'foo');
111+
RESET ROLE;
112+
113+
-- policy on system catalog
114+
BEGIN;
115+
CREATE POLICY fooON pg_description FORSELECT USING (description NOTLIKE'secret%');
116+
ROLLBACK;
117+
118+
-- reserved schema name
119+
BEGIN;
120+
CREATESCHEMApg_foo;
121+
ROLLBACK;
122+
123+
-- drop system table
124+
-- (This will fail anyway because it's pinned.)
125+
BEGIN;
126+
DROPTABLE pg_description;
127+
ROLLBACK;
128+
129+
-- truncate of system table
130+
BEGIN;
131+
TRUNCATE pg_description;
132+
ROLLBACK;
133+
134+
-- rename column of system table
135+
BEGIN;
136+
ALTERTABLE pg_description RENAME COLUMN description TO comment;
137+
ROLLBACK;
138+
139+
-- ATSimplePermissions()
140+
BEGIN;
141+
ALTERTABLE pg_description ALTER COLUMN descriptionSETNOT NULL;
142+
ROLLBACK;
143+
144+
-- SET STATISTICS
145+
BEGIN;
146+
ALTERTABLE pg_description ALTER COLUMN descriptionSET STATISTICS-1;
147+
ROLLBACK;
148+
149+
-- foreign key referencing catalog
150+
BEGIN;
151+
ALTERTABLE pg_description ADDPRIMARY KEY USING INDEX pg_description_o_c_o_index;
152+
CREATETABLEfoo (aoid, boid, cint,FOREIGN KEY (a, b, c)REFERENCES pg_description);
153+
ROLLBACK;
154+
155+
-- RangeVarCallbackOwnsRelation()
156+
BEGIN;
157+
CREATEINDEXpg_descripton_test_indexON pg_description (description);
158+
ROLLBACK;
159+
160+
-- RangeVarCallbackForAlterRelation()
161+
BEGIN;
162+
ALTERTABLE pg_description RENAME TO pg_comment;
163+
ROLLBACK;
164+
BEGIN;
165+
ALTERTABLE pg_descriptionSET SCHEMA public;
166+
ROLLBACK;
167+
168+
-- reserved tablespace name
169+
CREATETABLESPACEpg_foo LOCATION'/no/such/location';
170+
171+
-- triggers
172+
CREATETRIGGERt1 BEFORE INSERTON pg_description EXECUTE FUNCTION tf1();
173+
ALTERTRIGGER t1ON pg_description RENAME TO t2;
174+
DROPTRIGGER t2ON pg_description;
175+
176+
-- rules
177+
CREATERULEr1ASON INSERT TO pg_description DO INSTEAD NOTHING;
178+
ALTERRULE r1ON pg_description RENAME TO r2;
179+
DROPRULE r2ON pg_description;
180+
181+
182+
-- cleanup
183+
REVOKE ALLON pg_descriptionFROM regress_user_ast;
184+
DROPUSER regress_user_ast;
185+
DROPFUNCTION tf1;

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

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3309,10 +3309,6 @@ WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL;
33093309

33103310
-- Checks on creating and manipulation of user defined relations in
33113311
-- pg_catalog.
3312-
--
3313-
-- XXX: It would be useful to add checks around trying to manipulate
3314-
-- catalog tables, but that might have ugly consequences when run
3315-
-- against an existing server with allow_system_table_mods = on.
33163312
SHOW allow_system_table_mods;
33173313
allow_system_table_mods
33183314
-------------------------

‎src/test/regress/sql/alter_table.sql

Lines changed: 0 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2079,10 +2079,6 @@ WHERE c.oid IS NOT NULL OR m.mapped_oid IS NOT NULL;
20792079

20802080
-- Checks on creating and manipulation of user defined relations in
20812081
-- pg_catalog.
2082-
--
2083-
-- XXX: It would be useful to add checks around trying to manipulate
2084-
-- catalog tables, but that might have ugly consequences when run
2085-
-- against an existing server with allow_system_table_mods = on.
20862082

20872083
SHOW allow_system_table_mods;
20882084
-- disallowed because of search_path issues with pg_dump

0 commit comments

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