| blob | fcfa78aafc083bd2366063feec5d466c7fc76b68 |
1 CREATE ROLE regress_dump_test_role;
2 CREATE EXTENSION test_pg_dump;
3 ALTER EXTENSION test_pg_dump ADD DATABASE postgres; -- error
4 ERROR: cannot add an object of this type to an extension
5 CREATE TABLE test_pg_dump_t1 (c1 int, junk text);
6 ALTER TABLE test_pg_dump_t1 DROP COLUMN junk; -- to exercise dropped-col cases
7 CREATE VIEW test_pg_dump_v1 AS SELECT * FROM test_pg_dump_t1;
8 CREATE MATERIALIZED VIEW test_pg_dump_mv1 AS SELECT * FROM test_pg_dump_t1;
9 CREATE SCHEMA test_pg_dump_s1;
10 CREATE TYPE test_pg_dump_e1 AS ENUM ('abc', 'def');
11 CREATE AGGREGATE newavg (
12 sfunc = int4_avg_accum, basetype = int4, stype = _int8,
13 finalfunc = int8_avg,
14 initcond1 = '{0,0}'
15 );
16 CREATE FUNCTION test_pg_dump(int) RETURNS int AS $$
17 BEGIN
18 RETURN abs($1);
19 END
20 $$ LANGUAGE plpgsql IMMUTABLE;
21 CREATE OPERATOR ==== (
22 LEFTARG = int,
23 RIGHTARG = int,
24 PROCEDURE = int4eq,
25 COMMUTATOR = ====
26 );
27 CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
28 CREATE TYPE casttesttype;
29 CREATE FUNCTION casttesttype_in(cstring)
30 RETURNS casttesttype
31 AS 'textin'
32 LANGUAGE internal STRICT IMMUTABLE;
33 NOTICE: return type casttesttype is only a shell
34 CREATE FUNCTION casttesttype_out(casttesttype)
35 RETURNS cstring
36 AS 'textout'
37 LANGUAGE internal STRICT IMMUTABLE;
38 NOTICE: argument type casttesttype is only a shell
39 CREATE TYPE casttesttype (
40 internallength = variable,
41 input = casttesttype_in,
42 output = casttesttype_out,
43 alignment = int4
44 );
45 CREATE CAST (text AS casttesttype) WITHOUT FUNCTION;
46 CREATE FOREIGN DATA WRAPPER dummy;
47 CREATE SERVER s0 FOREIGN DATA WRAPPER dummy;
48 CREATE FOREIGN TABLE ft1 (
49 c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
50 c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''),
51 c3 date,
52 CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date)
53 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
54 REVOKE EXECUTE ON FUNCTION test_pg_dump(int) FROM PUBLIC;
55 GRANT EXECUTE ON FUNCTION test_pg_dump(int) TO regress_dump_test_role;
56 GRANT SELECT (c1) ON test_pg_dump_t1 TO regress_dump_test_role;
57 GRANT SELECT ON test_pg_dump_v1 TO regress_dump_test_role;
58 GRANT USAGE ON FOREIGN DATA WRAPPER dummy TO regress_dump_test_role;
59 GRANT USAGE ON FOREIGN SERVER s0 TO regress_dump_test_role;
60 GRANT SELECT (c1) ON ft1 TO regress_dump_test_role;
61 GRANT SELECT ON ft1 TO regress_dump_test_role;
62 GRANT UPDATE ON test_pg_dump_mv1 TO regress_dump_test_role;
63 GRANT USAGE ON SCHEMA test_pg_dump_s1 TO regress_dump_test_role;
64 GRANT USAGE ON TYPE test_pg_dump_e1 TO regress_dump_test_role;
65 -- Substitute for current user's name to keep test output consistent
66 SELECT s.obj,
67 CASE WHEN a.grantor::regrole::name = current_user THEN 'postgres'
68 ELSE a.grantor::regrole END,
69 CASE WHEN a.grantee::regrole::name = current_user THEN 'postgres'
70 ELSE a.grantee::regrole END,
71 a.privilege_type, a.is_grantable
72 FROM
73 (SELECT pg_describe_object(classoid,objoid,objsubid) AS obj, initprivs
74 FROM pg_init_privs WHERE privtype = 'e' ORDER BY 1) s,
75 aclexplode(s.initprivs) a;
76 obj | grantor | grantee | privilege_type | is_grantable
77 ----------------------------------------------------+----------+------------------------+----------------+--------------
78 column col1 of table regress_pg_dump_table | postgres | - | SELECT | f
79 function regress_pg_dump_schema.test_agg(smallint) | postgres | - | EXECUTE | f
80 function regress_pg_dump_schema.test_agg(smallint) | postgres | postgres | EXECUTE | f
81 function regress_pg_dump_schema.test_agg(smallint) | postgres | regress_dump_test_role | EXECUTE | f
82 function regress_pg_dump_schema.test_func() | postgres | - | EXECUTE | f
83 function regress_pg_dump_schema.test_func() | postgres | postgres | EXECUTE | f
84 function regress_pg_dump_schema.test_func() | postgres | regress_dump_test_role | EXECUTE | f
85 function wgo_then_no_access() | postgres | - | EXECUTE | f
86 function wgo_then_no_access() | postgres | postgres | EXECUTE | f
87 function wgo_then_no_access() | postgres | pg_signal_backend | EXECUTE | t
88 sequence regress_pg_dump_schema.test_seq | postgres | postgres | SELECT | f
89 sequence regress_pg_dump_schema.test_seq | postgres | postgres | UPDATE | f
90 sequence regress_pg_dump_schema.test_seq | postgres | postgres | USAGE | f
91 sequence regress_pg_dump_schema.test_seq | postgres | regress_dump_test_role | USAGE | f
92 sequence regress_pg_dump_seq | postgres | postgres | SELECT | f
93 sequence regress_pg_dump_seq | postgres | postgres | UPDATE | f
94 sequence regress_pg_dump_seq | postgres | postgres | USAGE | f
95 sequence regress_pg_dump_seq | postgres | regress_dump_test_role | USAGE | f
96 sequence regress_seq_dumpable | postgres | postgres | SELECT | f
97 sequence regress_seq_dumpable | postgres | postgres | UPDATE | f
98 sequence regress_seq_dumpable | postgres | postgres | USAGE | f
99 sequence regress_seq_dumpable | postgres | - | SELECT | f
100 sequence wgo_then_regular | postgres | postgres | SELECT | f
101 sequence wgo_then_regular | postgres | postgres | UPDATE | f
102 sequence wgo_then_regular | postgres | postgres | USAGE | f
103 sequence wgo_then_regular | postgres | pg_signal_backend | SELECT | f
104 sequence wgo_then_regular | postgres | pg_signal_backend | UPDATE | t
105 sequence wgo_then_regular | postgres | pg_signal_backend | USAGE | t
106 table regress_pg_dump_schema.test_table | postgres | postgres | INSERT | f
107 table regress_pg_dump_schema.test_table | postgres | postgres | SELECT | f
108 table regress_pg_dump_schema.test_table | postgres | postgres | UPDATE | f
109 table regress_pg_dump_schema.test_table | postgres | postgres | DELETE | f
110 table regress_pg_dump_schema.test_table | postgres | postgres | TRUNCATE | f
111 table regress_pg_dump_schema.test_table | postgres | postgres | REFERENCES | f
112 table regress_pg_dump_schema.test_table | postgres | postgres | TRIGGER | f
113 table regress_pg_dump_schema.test_table | postgres | postgres | MAINTAIN | f
114 table regress_pg_dump_schema.test_table | postgres | regress_dump_test_role | SELECT | f
115 table regress_pg_dump_table | postgres | postgres | INSERT | f
116 table regress_pg_dump_table | postgres | postgres | SELECT | f
117 table regress_pg_dump_table | postgres | postgres | UPDATE | f
118 table regress_pg_dump_table | postgres | postgres | DELETE | f
119 table regress_pg_dump_table | postgres | postgres | TRUNCATE | f
120 table regress_pg_dump_table | postgres | postgres | REFERENCES | f
121 table regress_pg_dump_table | postgres | postgres | TRIGGER | f
122 table regress_pg_dump_table | postgres | postgres | MAINTAIN | f
123 table regress_pg_dump_table | postgres | regress_dump_test_role | SELECT | f
124 table regress_table_dumpable | postgres | postgres | INSERT | f
125 table regress_table_dumpable | postgres | postgres | SELECT | f
126 table regress_table_dumpable | postgres | postgres | UPDATE | f
127 table regress_table_dumpable | postgres | postgres | DELETE | f
128 table regress_table_dumpable | postgres | postgres | TRUNCATE | f
129 table regress_table_dumpable | postgres | postgres | REFERENCES | f
130 table regress_table_dumpable | postgres | postgres | TRIGGER | f
131 table regress_table_dumpable | postgres | postgres | MAINTAIN | f
132 table regress_table_dumpable | postgres | - | SELECT | f
133 type regress_pg_dump_schema.test_type | postgres | - | USAGE | f
134 type regress_pg_dump_schema.test_type | postgres | postgres | USAGE | f
135 type regress_pg_dump_schema.test_type | postgres | regress_dump_test_role | USAGE | f
136 (58 rows)
138 SELECT pg_describe_object(classid,objid,objsubid) AS obj,
139 pg_describe_object(refclassid,refobjid,0) AS refobj,
140 deptype
141 FROM pg_shdepend JOIN pg_database d ON dbid = d.oid
142 WHERE d.datname = current_database()
143 ORDER BY 1, 3;
144 obj | refobj | deptype
145 ----------------------------------------------------+-----------------------------+---------
146 column c1 of foreign table ft1 | role regress_dump_test_role | a
147 column c1 of table test_pg_dump_t1 | role regress_dump_test_role | a
148 foreign table ft1 | role regress_dump_test_role | a
149 foreign-data wrapper dummy | role regress_dump_test_role | a
150 function regress_pg_dump_schema.test_agg(smallint) | role regress_dump_test_role | a
151 function regress_pg_dump_schema.test_agg(smallint) | role regress_dump_test_role | i
152 function regress_pg_dump_schema.test_func() | role regress_dump_test_role | a
153 function regress_pg_dump_schema.test_func() | role regress_dump_test_role | i
154 function test_pg_dump(integer) | role regress_dump_test_role | a
155 materialized view test_pg_dump_mv1 | role regress_dump_test_role | a
156 schema test_pg_dump_s1 | role regress_dump_test_role | a
157 sequence regress_pg_dump_schema.test_seq | role regress_dump_test_role | a
158 sequence regress_pg_dump_schema.test_seq | role regress_dump_test_role | i
159 sequence regress_pg_dump_seq | role regress_dump_test_role | a
160 sequence regress_pg_dump_seq | role regress_dump_test_role | i
161 server s0 | role regress_dump_test_role | a
162 table regress_pg_dump_schema.test_table | role regress_dump_test_role | a
163 table regress_pg_dump_schema.test_table | role regress_dump_test_role | i
164 table regress_pg_dump_table | role regress_dump_test_role | a
165 table regress_pg_dump_table | role regress_dump_test_role | i
166 type regress_pg_dump_schema.test_type | role regress_dump_test_role | a
167 type regress_pg_dump_schema.test_type | role regress_dump_test_role | i
168 type test_pg_dump_e1 | role regress_dump_test_role | a
169 view test_pg_dump_v1 | role regress_dump_test_role | a
170 (24 rows)
172 ALTER EXTENSION test_pg_dump ADD ACCESS METHOD gist2;
173 ALTER EXTENSION test_pg_dump ADD AGGREGATE newavg(int4);
174 ALTER EXTENSION test_pg_dump ADD CAST (text AS casttesttype);
175 ALTER EXTENSION test_pg_dump ADD FOREIGN DATA WRAPPER dummy;
176 ALTER EXTENSION test_pg_dump ADD FOREIGN TABLE ft1;
177 ALTER EXTENSION test_pg_dump ADD MATERIALIZED VIEW test_pg_dump_mv1;
178 ALTER EXTENSION test_pg_dump ADD OPERATOR ==== (int, int);
179 ALTER EXTENSION test_pg_dump ADD SCHEMA test_pg_dump_s1;
180 ALTER EXTENSION test_pg_dump ADD SERVER s0;
181 ALTER EXTENSION test_pg_dump ADD FUNCTION test_pg_dump(int);
182 ALTER EXTENSION test_pg_dump ADD TABLE test_pg_dump_t1;
183 ALTER EXTENSION test_pg_dump ADD TYPE test_pg_dump_e1;
184 ALTER EXTENSION test_pg_dump ADD VIEW test_pg_dump_v1;
185 REVOKE SELECT (c1) ON test_pg_dump_t1 FROM regress_dump_test_role;
186 REVOKE SELECT ON test_pg_dump_v1 FROM regress_dump_test_role;
187 REVOKE USAGE ON FOREIGN DATA WRAPPER dummy FROM regress_dump_test_role;
188 ALTER EXTENSION test_pg_dump DROP ACCESS METHOD gist2;
189 ALTER EXTENSION test_pg_dump DROP AGGREGATE newavg(int4);
190 ALTER EXTENSION test_pg_dump DROP CAST (text AS casttesttype);
191 ALTER EXTENSION test_pg_dump DROP FOREIGN DATA WRAPPER dummy;
192 ALTER EXTENSION test_pg_dump DROP FOREIGN TABLE ft1;
193 ALTER EXTENSION test_pg_dump DROP FUNCTION test_pg_dump(int);
194 ALTER EXTENSION test_pg_dump DROP MATERIALIZED VIEW test_pg_dump_mv1;
195 ALTER EXTENSION test_pg_dump DROP OPERATOR ==== (int, int);
196 ALTER EXTENSION test_pg_dump DROP SCHEMA test_pg_dump_s1;
197 ALTER EXTENSION test_pg_dump DROP SERVER s0;
198 ALTER EXTENSION test_pg_dump DROP TABLE test_pg_dump_t1;
199 ALTER EXTENSION test_pg_dump DROP TYPE test_pg_dump_e1;
200 ALTER EXTENSION test_pg_dump DROP VIEW test_pg_dump_v1;
201 DROP OWNED BY regress_dump_test_role RESTRICT;
202 -- Substitute for current user's name to keep test output consistent
203 SELECT s.obj,
204 CASE WHEN a.grantor::regrole::name = current_user THEN 'postgres'
205 ELSE a.grantor::regrole END,
206 CASE WHEN a.grantee::regrole::name = current_user THEN 'postgres'
207 ELSE a.grantee::regrole END,
208 a.privilege_type, a.is_grantable
209 FROM
210 (SELECT pg_describe_object(classoid,objoid,objsubid) AS obj, initprivs
211 FROM pg_init_privs WHERE privtype = 'e' ORDER BY 1) s,
212 aclexplode(s.initprivs) a;
213 obj | grantor | grantee | privilege_type | is_grantable
214 ----------------------------------------------------+----------+-------------------+----------------+--------------
215 column col1 of table regress_pg_dump_table | postgres | - | SELECT | f
216 function regress_pg_dump_schema.test_agg(smallint) | postgres | - | EXECUTE | f
217 function regress_pg_dump_schema.test_agg(smallint) | postgres | postgres | EXECUTE | f
218 function regress_pg_dump_schema.test_func() | postgres | - | EXECUTE | f
219 function regress_pg_dump_schema.test_func() | postgres | postgres | EXECUTE | f
220 function wgo_then_no_access() | postgres | - | EXECUTE | f
221 function wgo_then_no_access() | postgres | postgres | EXECUTE | f
222 function wgo_then_no_access() | postgres | pg_signal_backend | EXECUTE | t
223 sequence regress_pg_dump_schema.test_seq | postgres | postgres | SELECT | f
224 sequence regress_pg_dump_schema.test_seq | postgres | postgres | UPDATE | f
225 sequence regress_pg_dump_schema.test_seq | postgres | postgres | USAGE | f
226 sequence regress_pg_dump_seq | postgres | postgres | SELECT | f
227 sequence regress_pg_dump_seq | postgres | postgres | UPDATE | f
228 sequence regress_pg_dump_seq | postgres | postgres | USAGE | f
229 sequence regress_seq_dumpable | postgres | postgres | SELECT | f
230 sequence regress_seq_dumpable | postgres | postgres | UPDATE | f
231 sequence regress_seq_dumpable | postgres | postgres | USAGE | f
232 sequence regress_seq_dumpable | postgres | - | SELECT | f
233 sequence wgo_then_regular | postgres | postgres | SELECT | f
234 sequence wgo_then_regular | postgres | postgres | UPDATE | f
235 sequence wgo_then_regular | postgres | postgres | USAGE | f
236 sequence wgo_then_regular | postgres | pg_signal_backend | SELECT | f
237 sequence wgo_then_regular | postgres | pg_signal_backend | UPDATE | t
238 sequence wgo_then_regular | postgres | pg_signal_backend | USAGE | t
239 table regress_pg_dump_schema.test_table | postgres | postgres | INSERT | f
240 table regress_pg_dump_schema.test_table | postgres | postgres | SELECT | f
241 table regress_pg_dump_schema.test_table | postgres | postgres | UPDATE | f
242 table regress_pg_dump_schema.test_table | postgres | postgres | DELETE | f
243 table regress_pg_dump_schema.test_table | postgres | postgres | TRUNCATE | f
244 table regress_pg_dump_schema.test_table | postgres | postgres | REFERENCES | f
245 table regress_pg_dump_schema.test_table | postgres | postgres | TRIGGER | f
246 table regress_pg_dump_schema.test_table | postgres | postgres | MAINTAIN | f
247 table regress_pg_dump_table | postgres | postgres | INSERT | f
248 table regress_pg_dump_table | postgres | postgres | SELECT | f
249 table regress_pg_dump_table | postgres | postgres | UPDATE | f
250 table regress_pg_dump_table | postgres | postgres | DELETE | f
251 table regress_pg_dump_table | postgres | postgres | TRUNCATE | f
252 table regress_pg_dump_table | postgres | postgres | REFERENCES | f
253 table regress_pg_dump_table | postgres | postgres | TRIGGER | f
254 table regress_pg_dump_table | postgres | postgres | MAINTAIN | f
255 table regress_table_dumpable | postgres | postgres | INSERT | f
256 table regress_table_dumpable | postgres | postgres | SELECT | f
257 table regress_table_dumpable | postgres | postgres | UPDATE | f
258 table regress_table_dumpable | postgres | postgres | DELETE | f
259 table regress_table_dumpable | postgres | postgres | TRUNCATE | f
260 table regress_table_dumpable | postgres | postgres | REFERENCES | f
261 table regress_table_dumpable | postgres | postgres | TRIGGER | f
262 table regress_table_dumpable | postgres | postgres | MAINTAIN | f
263 table regress_table_dumpable | postgres | - | SELECT | f
264 type regress_pg_dump_schema.test_type | postgres | - | USAGE | f
265 type regress_pg_dump_schema.test_type | postgres | postgres | USAGE | f
266 (51 rows)
268 SELECT pg_describe_object(classid,objid,objsubid) AS obj,
269 pg_describe_object(refclassid,refobjid,0) AS refobj,
270 deptype
271 FROM pg_shdepend JOIN pg_database d ON dbid = d.oid
272 WHERE d.datname = current_database()
273 ORDER BY 1, 3;
274 obj | refobj | deptype
275 -----+--------+---------
276 (0 rows)
278 DROP ROLE regress_dump_test_role;