1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
--
-- Tests for things affected by allow_system_table_mods
--
-- We run the same set of commands once with allow_system_table_mods
-- off and then again with on.
--
-- The "on" tests should where possible be wrapped in BEGIN/ROLLBACK
-- blocks so as to not leave a mess around.
CREATE USER regress_user_ast;
SET allow_system_table_mods = off;
-- create new table in pg_catalog
CREATE TABLE pg_catalog.test (a int);
-- anyarray column
CREATE TABLE t1x (a int, b anyarray);
-- index on system catalog
ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index;
-- write to system catalog table as superuser
-- (allowed even without allow_system_table_mods)
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 0, 'foo');
-- write to system catalog table as normal user
GRANT INSERT ON pg_description TO regress_user_ast;
SET ROLE regress_user_ast;
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 1, 'foo');
RESET ROLE;
-- policy on system catalog
CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%');
-- reserved schema name
CREATE SCHEMA pg_foo;
-- drop system table
DROP TABLE pg_description;
-- truncate of system table
TRUNCATE pg_description;
-- rename column of system table
ALTER TABLE pg_description RENAME COLUMN description TO comment;
-- ATSimplePermissions()
ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL;
-- SET STATISTICS
ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1;
-- foreign key referencing catalog
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description);
-- RangeVarCallbackOwnsRelation()
CREATE INDEX pg_description_test_index ON pg_description (description);
-- RangeVarCallbackForAlterRelation()
ALTER TABLE pg_description RENAME TO pg_comment;
ALTER TABLE pg_description SET SCHEMA public;
-- reserved tablespace name
CREATE TABLESPACE pg_foo LOCATION '/no/such/location';
-- triggers
CREATE FUNCTION tf1() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
RETURN NULL;
END $$;
CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1();
ALTER TRIGGER t1 ON pg_description RENAME TO t2;
--DROP TRIGGER t2 ON pg_description;
-- rules
CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
ALTER RULE r1 ON pg_description RENAME TO r2;
-- now make one to test dropping:
SET allow_system_table_mods TO on;
CREATE RULE r2 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
RESET allow_system_table_mods;
DROP RULE r2 ON pg_description;
-- cleanup:
SET allow_system_table_mods TO on;
DROP RULE r2 ON pg_description;
RESET allow_system_table_mods;
-- Reloptions on TOAST tables
ALTER TABLE pg_toast.pg_toast_2615 SET (fillfactor = '90');
SET allow_system_table_mods = on;
-- create new table in pg_catalog
BEGIN;
CREATE TABLE pg_catalog.test (a int);
ROLLBACK;
-- anyarray column
BEGIN;
CREATE TABLE t1 (a int, b anyarray);
ROLLBACK;
-- index on system catalog
BEGIN;
ALTER TABLE pg_namespace ADD CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index;
ROLLBACK;
-- write to system catalog table as superuser
BEGIN;
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 2, 'foo');
ROLLBACK;
-- write to system catalog table as normal user
-- (not allowed)
SET ROLE regress_user_ast;
INSERT INTO pg_description (objoid, classoid, objsubid, description) VALUES (0, 0, 3, 'foo');
RESET ROLE;
-- policy on system catalog
BEGIN;
CREATE POLICY foo ON pg_description FOR SELECT USING (description NOT LIKE 'secret%');
ROLLBACK;
-- reserved schema name
BEGIN;
CREATE SCHEMA pg_foo;
ROLLBACK;
-- drop system table
-- (This will fail anyway because it's pinned.)
BEGIN;
DROP TABLE pg_description;
ROLLBACK;
-- truncate of system table
BEGIN;
TRUNCATE pg_description;
ROLLBACK;
-- rename column of system table
BEGIN;
ALTER TABLE pg_description RENAME COLUMN description TO comment;
ROLLBACK;
-- ATSimplePermissions()
BEGIN;
ALTER TABLE pg_description ALTER COLUMN description SET NOT NULL;
ROLLBACK;
-- SET STATISTICS
BEGIN;
ALTER TABLE pg_description ALTER COLUMN description SET STATISTICS -1;
ROLLBACK;
-- foreign key referencing catalog
BEGIN;
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES pg_description);
ROLLBACK;
-- RangeVarCallbackOwnsRelation()
BEGIN;
CREATE INDEX pg_description_test_index ON pg_description (description);
ROLLBACK;
-- RangeVarCallbackForAlterRelation()
BEGIN;
ALTER TABLE pg_description RENAME TO pg_comment;
ROLLBACK;
BEGIN;
ALTER TABLE pg_description SET SCHEMA public;
ROLLBACK;
-- reserved tablespace name
SET client_min_messages = error; -- disable ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS warning
CREATE TABLESPACE pg_foo LOCATION '/no/such/location';
RESET client_min_messages;
-- triggers
CREATE TRIGGER t1 BEFORE INSERT ON pg_description EXECUTE FUNCTION tf1();
ALTER TRIGGER t1 ON pg_description RENAME TO t2;
DROP TRIGGER t2 ON pg_description;
-- rules
CREATE RULE r1 AS ON INSERT TO pg_description DO INSTEAD NOTHING;
ALTER RULE r1 ON pg_description RENAME TO r2;
DROP RULE r2 ON pg_description;
-- Reloptions on TOAST tables
ALTER TABLE pg_toast.pg_toast_2615 SET (fillfactor = '90');
-- cleanup
REVOKE ALL ON pg_description FROM regress_user_ast;
DROP USER regress_user_ast;
DROP FUNCTION tf1;
|
