1--
2-- Test access privileges
3--
4
5-- Clean up in case a prior regression run failed
6
7-- Suppress NOTICE messages when users/groups don't exist
8SET client_min_messages TO 'warning';
9
10DROP ROLE IF EXISTS regress_priv_group1;
11DROP ROLE IF EXISTS regress_priv_group2;
12
13DROP ROLE IF EXISTS regress_priv_user1;
14DROP ROLE IF EXISTS regress_priv_user2;
15DROP ROLE IF EXISTS regress_priv_user3;
16DROP ROLE IF EXISTS regress_priv_user4;
17DROP ROLE IF EXISTS regress_priv_user5;
18DROP ROLE IF EXISTS regress_priv_user6;
19
20SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
21
22RESET client_min_messages;
23
24-- test proper begins here
25
26CREATE USER regress_priv_user1;
27CREATE USER regress_priv_user2;
28CREATE USER regress_priv_user3;
29CREATE USER regress_priv_user4;
30CREATE USER regress_priv_user5;
31CREATE USER regress_priv_user5;	-- duplicate
32
33CREATE GROUP regress_priv_group1;
34CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
35
36ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
37
38ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2;	-- duplicate
39ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
40GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION;
41
42-- test owner privileges
43
44SET SESSION AUTHORIZATION regress_priv_user1;
45SELECT session_user, current_user;
46
47CREATE TABLE atest1 ( a int, b text );
48SELECT * FROM atest1;
49INSERT INTO atest1 VALUES (1, 'one');
50DELETE FROM atest1;
51UPDATE atest1 SET a = 1 WHERE b = 'blech';
52TRUNCATE atest1;
53BEGIN;
54LOCK atest1 IN ACCESS EXCLUSIVE MODE;
55COMMIT;
56
57REVOKE ALL ON atest1 FROM PUBLIC;
58SELECT * FROM atest1;
59
60GRANT ALL ON atest1 TO regress_priv_user2;
61GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
62SELECT * FROM atest1;
63
64CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
65GRANT SELECT ON atest2 TO regress_priv_user2;
66GRANT UPDATE ON atest2 TO regress_priv_user3;
67GRANT INSERT ON atest2 TO regress_priv_user4;
68GRANT TRUNCATE ON atest2 TO regress_priv_user5;
69
70
71SET SESSION AUTHORIZATION regress_priv_user2;
72SELECT session_user, current_user;
73
74-- try various combinations of queries on atest1 and atest2
75
76SELECT * FROM atest1; -- ok
77SELECT * FROM atest2; -- ok
78INSERT INTO atest1 VALUES (2, 'two'); -- ok
79INSERT INTO atest2 VALUES ('foo', true); -- fail
80INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
81UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
82UPDATE atest2 SET col2 = NOT col2; -- fail
83SELECT * FROM atest1 FOR UPDATE; -- ok
84SELECT * FROM atest2 FOR UPDATE; -- fail
85DELETE FROM atest2; -- fail
86TRUNCATE atest2; -- fail
87BEGIN;
88LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
89COMMIT;
90GRANT ALL ON atest1 TO PUBLIC; -- fail
91
92-- checks in subquery, both ok
93SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
94SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
95
96
97SET SESSION AUTHORIZATION regress_priv_user3;
98SELECT session_user, current_user;
99
100SELECT * FROM atest1; -- ok
101SELECT * FROM atest2; -- fail
102INSERT INTO atest1 VALUES (2, 'two'); -- fail
103INSERT INTO atest2 VALUES ('foo', true); -- fail
104INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
105UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
106UPDATE atest2 SET col2 = NULL; -- ok
107UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
108UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok
109SELECT * FROM atest1 FOR UPDATE; -- fail
110SELECT * FROM atest2 FOR UPDATE; -- fail
111DELETE FROM atest2; -- fail
112TRUNCATE atest2; -- fail
113BEGIN;
114LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
115COMMIT;
116
117-- checks in subquery, both fail
118SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
119SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
120
121SET SESSION AUTHORIZATION regress_priv_user4;
122SELECT * FROM atest1; -- ok
123
124
125-- test leaky-function protections in selfuncs
126
127-- regress_priv_user1 will own a table and provide a view for it.
128SET SESSION AUTHORIZATION regress_priv_user1;
129
130CREATE TABLE atest12 as
131  SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x;
132CREATE INDEX ON atest12 (a);
133CREATE INDEX ON atest12 (abs(a));
134VACUUM ANALYZE atest12;
135
136CREATE FUNCTION leak(integer,integer) RETURNS boolean
137  AS $$begin return $1 < $2; end$$
138  LANGUAGE plpgsql immutable;
139CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer,
140                     restrict = scalarltsel);
141
142-- view with leaky operator
143CREATE VIEW atest12v AS
144  SELECT * FROM atest12 WHERE b <<< 5;
145GRANT SELECT ON atest12v TO PUBLIC;
146
147-- This plan should use nestloop, knowing that few rows will be selected.
148EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
149
150-- And this one.
151EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
152  WHERE x.a = y.b and abs(y.a) <<< 5;
153
154-- Check if regress_priv_user2 can break security.
155SET SESSION AUTHORIZATION regress_priv_user2;
156
157CREATE FUNCTION leak2(integer,integer) RETURNS boolean
158  AS $$begin raise notice 'leak % %', $1, $2; return $1 > $2; end$$
159  LANGUAGE plpgsql immutable;
160CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer,
161                     restrict = scalargtsel);
162
163-- This should not show any "leak" notices before failing.
164EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0;
165
166-- This plan should use hashjoin, as it will expect many rows to be selected.
167EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
168
169-- Now regress_priv_user1 grants sufficient access to regress_priv_user2.
170SET SESSION AUTHORIZATION regress_priv_user1;
171GRANT SELECT (a, b) ON atest12 TO PUBLIC;
172SET SESSION AUTHORIZATION regress_priv_user2;
173
174-- Now regress_priv_user2 will also get a good row estimate.
175EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
176
177-- But not for this, due to lack of table-wide permissions needed
178-- to make use of the expression index's statistics.
179EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
180  WHERE x.a = y.b and abs(y.a) <<< 5;
181
182-- clean up (regress_priv_user1's objects are all dropped later)
183DROP FUNCTION leak2(integer, integer) CASCADE;
184
185
186-- groups
187
188SET SESSION AUTHORIZATION regress_priv_user3;
189CREATE TABLE atest3 (one int, two int, three int);
190GRANT DELETE ON atest3 TO GROUP regress_priv_group2;
191
192SET SESSION AUTHORIZATION regress_priv_user1;
193
194SELECT * FROM atest3; -- fail
195DELETE FROM atest3; -- ok
196
197
198-- views
199
200SET SESSION AUTHORIZATION regress_priv_user3;
201
202CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
203/* The next *should* fail, but it's not implemented that way yet. */
204CREATE VIEW atestv2 AS SELECT * FROM atest2;
205CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
206/* Empty view is a corner case that failed in 9.2. */
207CREATE VIEW atestv0 AS SELECT 0 as x WHERE false; -- ok
208
209SELECT * FROM atestv1; -- ok
210SELECT * FROM atestv2; -- fail
211GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4;
212GRANT SELECT ON atestv2 TO regress_priv_user2;
213
214SET SESSION AUTHORIZATION regress_priv_user4;
215
216SELECT * FROM atestv1; -- ok
217SELECT * FROM atestv2; -- fail
218SELECT * FROM atestv3; -- ok
219SELECT * FROM atestv0; -- fail
220
221-- Appendrels excluded by constraints failed to check permissions in 8.4-9.2.
222select * from
223  ((select a.q1 as x from int8_tbl a offset 0)
224   union all
225   (select b.q2 as x from int8_tbl b offset 0)) ss
226where false;
227
228set constraint_exclusion = on;
229select * from
230  ((select a.q1 as x, random() from int8_tbl a where q1 > 0)
231   union all
232   (select b.q2 as x, random() from int8_tbl b where q2 > 0)) ss
233where x < 0;
234reset constraint_exclusion;
235
236CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
237SELECT * FROM atestv4; -- ok
238GRANT SELECT ON atestv4 TO regress_priv_user2;
239
240SET SESSION AUTHORIZATION regress_priv_user2;
241
242-- Two complex cases:
243
244SELECT * FROM atestv3; -- fail
245SELECT * FROM atestv4; -- ok (even though regress_priv_user2 cannot access underlying atestv3)
246
247SELECT * FROM atest2; -- ok
248SELECT * FROM atestv2; -- fail (even though regress_priv_user2 can access underlying atest2)
249
250-- Test column level permissions
251
252SET SESSION AUTHORIZATION regress_priv_user1;
253CREATE TABLE atest5 (one int, two int unique, three int, four int unique);
254CREATE TABLE atest6 (one int, two int, blue int);
255GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regress_priv_user4;
256GRANT ALL (one) ON atest5 TO regress_priv_user3;
257
258INSERT INTO atest5 VALUES (1,2,3);
259
260SET SESSION AUTHORIZATION regress_priv_user4;
261SELECT * FROM atest5; -- fail
262SELECT one FROM atest5; -- ok
263SELECT two FROM atest5; -- fail
264SELECT atest5 FROM atest5; -- fail
265SELECT 1 FROM atest5; -- ok
266SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok
267SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail
268SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail
269SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
270SELECT 1 FROM atest5 WHERE two = 2; -- fail
271SELECT * FROM atest1, atest5; -- fail
272SELECT atest1.* FROM atest1, atest5; -- ok
273SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok
274SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail
275SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok
276SELECT one, two FROM atest5; -- fail
277
278SET SESSION AUTHORIZATION regress_priv_user1;
279GRANT SELECT (one,two) ON atest6 TO regress_priv_user4;
280
281SET SESSION AUTHORIZATION regress_priv_user4;
282SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still
283
284SET SESSION AUTHORIZATION regress_priv_user1;
285GRANT SELECT (two) ON atest5 TO regress_priv_user4;
286
287SET SESSION AUTHORIZATION regress_priv_user4;
288SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now
289
290-- test column-level privileges for INSERT and UPDATE
291INSERT INTO atest5 (two) VALUES (3); -- ok
292INSERT INTO atest5 (three) VALUES (4); -- fail
293INSERT INTO atest5 VALUES (5,5,5); -- fail
294UPDATE atest5 SET three = 10; -- ok
295UPDATE atest5 SET one = 8; -- fail
296UPDATE atest5 SET three = 5, one = 2; -- fail
297-- Check that column level privs are enforced in RETURNING
298-- Ok.
299INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10;
300-- Error. No SELECT on column three.
301INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three;
302-- Ok.  May SELECT on column "one":
303INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one;
304-- Check that column level privileges are enforced for EXCLUDED
305-- Ok. we may select one
306INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one;
307-- Error. No select rights on three
308INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three;
309INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE)
310INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT)
311
312-- Check that the columns in the inference require select privileges
313INSERT INTO atest5(four) VALUES (4); -- fail
314
315SET SESSION AUTHORIZATION regress_priv_user1;
316GRANT INSERT (four) ON atest5 TO regress_priv_user4;
317SET SESSION AUTHORIZATION regress_priv_user4;
318
319INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT)
320INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- fails (due to SELECT)
321INSERT INTO atest5(four) VALUES (4); -- ok
322
323SET SESSION AUTHORIZATION regress_priv_user1;
324GRANT SELECT (four) ON atest5 TO regress_priv_user4;
325SET SESSION AUTHORIZATION regress_priv_user4;
326
327INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok
328INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- ok
329
330SET SESSION AUTHORIZATION regress_priv_user1;
331REVOKE ALL (one) ON atest5 FROM regress_priv_user4;
332GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4;
333
334SET SESSION AUTHORIZATION regress_priv_user4;
335SELECT one FROM atest5; -- fail
336UPDATE atest5 SET one = 1; -- fail
337SELECT atest6 FROM atest6; -- ok
338
339-- check error reporting with column privs
340SET SESSION AUTHORIZATION regress_priv_user1;
341CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2));
342GRANT SELECT (c1) ON t1 TO regress_priv_user2;
343GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2;
344GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_user2;
345
346-- seed data
347INSERT INTO t1 VALUES (1, 1, 1);
348INSERT INTO t1 VALUES (1, 2, 1);
349INSERT INTO t1 VALUES (2, 1, 2);
350INSERT INTO t1 VALUES (2, 2, 2);
351INSERT INTO t1 VALUES (3, 1, 3);
352
353SET SESSION AUTHORIZATION regress_priv_user2;
354INSERT INTO t1 (c1, c2) VALUES (1, 1); -- fail, but row not shown
355UPDATE t1 SET c2 = 1; -- fail, but row not shown
356INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted
357INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT
358INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT
359UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified
360
361SET SESSION AUTHORIZATION regress_priv_user1;
362DROP TABLE t1;
363
364-- test column-level privileges when involved with DELETE
365SET SESSION AUTHORIZATION regress_priv_user1;
366ALTER TABLE atest6 ADD COLUMN three integer;
367GRANT DELETE ON atest5 TO regress_priv_user3;
368GRANT SELECT (two) ON atest5 TO regress_priv_user3;
369REVOKE ALL (one) ON atest5 FROM regress_priv_user3;
370GRANT SELECT (one) ON atest5 TO regress_priv_user4;
371
372SET SESSION AUTHORIZATION regress_priv_user4;
373SELECT atest6 FROM atest6; -- fail
374SELECT one FROM atest5 NATURAL JOIN atest6; -- fail
375
376SET SESSION AUTHORIZATION regress_priv_user1;
377ALTER TABLE atest6 DROP COLUMN three;
378
379SET SESSION AUTHORIZATION regress_priv_user4;
380SELECT atest6 FROM atest6; -- ok
381SELECT one FROM atest5 NATURAL JOIN atest6; -- ok
382
383SET SESSION AUTHORIZATION regress_priv_user1;
384ALTER TABLE atest6 DROP COLUMN two;
385REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4;
386
387SET SESSION AUTHORIZATION regress_priv_user4;
388SELECT * FROM atest6; -- fail
389SELECT 1 FROM atest6; -- fail
390
391SET SESSION AUTHORIZATION regress_priv_user3;
392DELETE FROM atest5 WHERE one = 1; -- fail
393DELETE FROM atest5 WHERE two = 2; -- ok
394
395-- check inheritance cases
396SET SESSION AUTHORIZATION regress_priv_user1;
397CREATE TABLE atestp1 (f1 int, f2 int);
398CREATE TABLE atestp2 (fx int, fy int);
399CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2);
400GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2;
401GRANT SELECT(fx) ON atestc TO regress_priv_user2;
402
403SET SESSION AUTHORIZATION regress_priv_user2;
404SELECT fx FROM atestp2; -- ok
405SELECT fy FROM atestp2; -- ok
406SELECT atestp2 FROM atestp2; -- ok
407SELECT tableoid FROM atestp2; -- ok
408SELECT fy FROM atestc; -- fail
409
410SET SESSION AUTHORIZATION regress_priv_user1;
411GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2;
412
413SET SESSION AUTHORIZATION regress_priv_user2;
414SELECT fx FROM atestp2; -- still ok
415SELECT fy FROM atestp2; -- ok
416SELECT atestp2 FROM atestp2; -- ok
417SELECT tableoid FROM atestp2; -- ok
418
419-- privileges on functions, languages
420
421-- switch to superuser
422\c -
423
424REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
425GRANT USAGE ON LANGUAGE sql TO regress_priv_user1; -- ok
426GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
427
428SET SESSION AUTHORIZATION regress_priv_user1;
429GRANT USAGE ON LANGUAGE sql TO regress_priv_user2; -- fail
430CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
431CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
432CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4);
433CREATE PROCEDURE priv_testproc1(int) AS 'select $1;' LANGUAGE sql;
434
435REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FROM PUBLIC;
436GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) TO regress_priv_user2;
437REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC; -- fail, not a function
438REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC;
439GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2;
440GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3; -- semantic error
441GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3; -- semantic error
442GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3; -- semantic error
443GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4;
444GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4;
445GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4;
446GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
447
448CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
449  AS 'select col1 from atest2 where col2 = $1;'
450  LANGUAGE sql SECURITY DEFINER;
451GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
452
453SET SESSION AUTHORIZATION regress_priv_user2;
454SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
455CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
456SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
457CALL priv_testproc1(6); -- ok
458
459SET SESSION AUTHORIZATION regress_priv_user3;
460SELECT priv_testfunc1(5); -- fail
461SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail
462CALL priv_testproc1(6); -- fail
463SELECT col1 FROM atest2 WHERE col2 = true; -- fail
464SELECT priv_testfunc4(true); -- ok
465
466SET SESSION AUTHORIZATION regress_priv_user4;
467SELECT priv_testfunc1(5); -- ok
468SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
469CALL priv_testproc1(6); -- ok
470
471DROP FUNCTION priv_testfunc1(int); -- fail
472DROP AGGREGATE priv_testagg1(int); -- fail
473DROP PROCEDURE priv_testproc1(int); -- fail
474
475\c -
476
477DROP FUNCTION priv_testfunc1(int); -- ok
478-- restore to sanity
479GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
480
481-- verify privilege checks on array-element coercions
482BEGIN;
483SELECT '{1}'::int4[]::int8[];
484REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC;
485SELECT '{1}'::int4[]::int8[]; --superuser, succeed
486SET SESSION AUTHORIZATION regress_priv_user4;
487SELECT '{1}'::int4[]::int8[]; --other user, fail
488ROLLBACK;
489
490-- privileges on types
491
492-- switch to superuser
493\c -
494
495CREATE TYPE priv_testtype1 AS (a int, b text);
496REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC;
497GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2;
498GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2; -- fail
499GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2; -- fail
500
501CREATE DOMAIN priv_testdomain1 AS int;
502REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC;
503GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2;
504GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
505
506SET SESSION AUTHORIZATION regress_priv_user1;
507
508-- commands that should fail
509
510CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
511
512CREATE DOMAIN priv_testdomain2a AS priv_testdomain1;
513
514CREATE DOMAIN priv_testdomain3a AS int;
515CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS $$ SELECT $1::priv_testdomain3a $$ LANGUAGE SQL;
516CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int);
517DROP FUNCTION castfunc(int) CASCADE;
518DROP DOMAIN priv_testdomain3a;
519
520CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
521CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$;
522
523CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdomain1);
524
525CREATE TABLE test5a (a int, b priv_testdomain1);
526CREATE TABLE test6a OF priv_testtype1;
527CREATE TABLE test10a (a int[], b priv_testtype1[]);
528
529CREATE TABLE test9a (a int, b int);
530ALTER TABLE test9a ADD COLUMN c priv_testdomain1;
531ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1;
532
533CREATE TYPE test7a AS (a int, b priv_testdomain1);
534
535CREATE TYPE test8a AS (a int, b int);
536ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1;
537ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1;
538
539CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a);
540
541REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
542
543SET SESSION AUTHORIZATION regress_priv_user2;
544
545-- commands that should succeed
546
547CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
548
549CREATE DOMAIN priv_testdomain2b AS priv_testdomain1;
550
551CREATE DOMAIN priv_testdomain3b AS int;
552CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS $$ SELECT $1::priv_testdomain3b $$ LANGUAGE SQL;
553CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int);
554
555CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
556CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$;
557
558CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1);
559
560CREATE TABLE test5b (a int, b priv_testdomain1);
561CREATE TABLE test6b OF priv_testtype1;
562CREATE TABLE test10b (a int[], b priv_testtype1[]);
563
564CREATE TABLE test9b (a int, b int);
565ALTER TABLE test9b ADD COLUMN c priv_testdomain1;
566ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1;
567
568CREATE TYPE test7b AS (a int, b priv_testdomain1);
569
570CREATE TYPE test8b AS (a int, b int);
571ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1;
572ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1;
573
574CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a);
575
576REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
577
578\c -
579DROP AGGREGATE priv_testagg1b(priv_testdomain1);
580DROP DOMAIN priv_testdomain2b;
581DROP OPERATOR !! (NONE, priv_testdomain1);
582DROP FUNCTION priv_testfunc5b(a priv_testdomain1);
583DROP FUNCTION priv_testfunc6b(b int);
584DROP TABLE test5b;
585DROP TABLE test6b;
586DROP TABLE test9b;
587DROP TABLE test10b;
588DROP TYPE test7b;
589DROP TYPE test8b;
590DROP CAST (priv_testdomain1 AS priv_testdomain3b);
591DROP FUNCTION castfunc(int) CASCADE;
592DROP DOMAIN priv_testdomain3b;
593DROP TABLE test11b;
594
595DROP TYPE priv_testtype1; -- ok
596DROP DOMAIN priv_testdomain1; -- ok
597
598
599-- truncate
600SET SESSION AUTHORIZATION regress_priv_user5;
601TRUNCATE atest2; -- ok
602TRUNCATE atest3; -- fail
603
604-- has_table_privilege function
605
606-- bad-input checks
607select has_table_privilege(NULL,'pg_authid','select');
608select has_table_privilege('pg_shad','select');
609select has_table_privilege('nosuchuser','pg_authid','select');
610select has_table_privilege('pg_authid','sel');
611select has_table_privilege(-999999,'pg_authid','update');
612select has_table_privilege(1,'select');
613
614-- superuser
615\c -
616
617select has_table_privilege(current_user,'pg_authid','select');
618select has_table_privilege(current_user,'pg_authid','insert');
619
620select has_table_privilege(t2.oid,'pg_authid','update')
621from (select oid from pg_roles where rolname = current_user) as t2;
622select has_table_privilege(t2.oid,'pg_authid','delete')
623from (select oid from pg_roles where rolname = current_user) as t2;
624
625-- 'rule' privilege no longer exists, but for backwards compatibility
626-- has_table_privilege still recognizes the keyword and says FALSE
627select has_table_privilege(current_user,t1.oid,'rule')
628from (select oid from pg_class where relname = 'pg_authid') as t1;
629select has_table_privilege(current_user,t1.oid,'references')
630from (select oid from pg_class where relname = 'pg_authid') as t1;
631
632select has_table_privilege(t2.oid,t1.oid,'select')
633from (select oid from pg_class where relname = 'pg_authid') as t1,
634  (select oid from pg_roles where rolname = current_user) as t2;
635select has_table_privilege(t2.oid,t1.oid,'insert')
636from (select oid from pg_class where relname = 'pg_authid') as t1,
637  (select oid from pg_roles where rolname = current_user) as t2;
638
639select has_table_privilege('pg_authid','update');
640select has_table_privilege('pg_authid','delete');
641select has_table_privilege('pg_authid','truncate');
642
643select has_table_privilege(t1.oid,'select')
644from (select oid from pg_class where relname = 'pg_authid') as t1;
645select has_table_privilege(t1.oid,'trigger')
646from (select oid from pg_class where relname = 'pg_authid') as t1;
647
648-- non-superuser
649SET SESSION AUTHORIZATION regress_priv_user3;
650
651select has_table_privilege(current_user,'pg_class','select');
652select has_table_privilege(current_user,'pg_class','insert');
653
654select has_table_privilege(t2.oid,'pg_class','update')
655from (select oid from pg_roles where rolname = current_user) as t2;
656select has_table_privilege(t2.oid,'pg_class','delete')
657from (select oid from pg_roles where rolname = current_user) as t2;
658
659select has_table_privilege(current_user,t1.oid,'references')
660from (select oid from pg_class where relname = 'pg_class') as t1;
661
662select has_table_privilege(t2.oid,t1.oid,'select')
663from (select oid from pg_class where relname = 'pg_class') as t1,
664  (select oid from pg_roles where rolname = current_user) as t2;
665select has_table_privilege(t2.oid,t1.oid,'insert')
666from (select oid from pg_class where relname = 'pg_class') as t1,
667  (select oid from pg_roles where rolname = current_user) as t2;
668
669select has_table_privilege('pg_class','update');
670select has_table_privilege('pg_class','delete');
671select has_table_privilege('pg_class','truncate');
672
673select has_table_privilege(t1.oid,'select')
674from (select oid from pg_class where relname = 'pg_class') as t1;
675select has_table_privilege(t1.oid,'trigger')
676from (select oid from pg_class where relname = 'pg_class') as t1;
677
678select has_table_privilege(current_user,'atest1','select');
679select has_table_privilege(current_user,'atest1','insert');
680
681select has_table_privilege(t2.oid,'atest1','update')
682from (select oid from pg_roles where rolname = current_user) as t2;
683select has_table_privilege(t2.oid,'atest1','delete')
684from (select oid from pg_roles where rolname = current_user) as t2;
685
686select has_table_privilege(current_user,t1.oid,'references')
687from (select oid from pg_class where relname = 'atest1') as t1;
688
689select has_table_privilege(t2.oid,t1.oid,'select')
690from (select oid from pg_class where relname = 'atest1') as t1,
691  (select oid from pg_roles where rolname = current_user) as t2;
692select has_table_privilege(t2.oid,t1.oid,'insert')
693from (select oid from pg_class where relname = 'atest1') as t1,
694  (select oid from pg_roles where rolname = current_user) as t2;
695
696select has_table_privilege('atest1','update');
697select has_table_privilege('atest1','delete');
698select has_table_privilege('atest1','truncate');
699
700select has_table_privilege(t1.oid,'select')
701from (select oid from pg_class where relname = 'atest1') as t1;
702select has_table_privilege(t1.oid,'trigger')
703from (select oid from pg_class where relname = 'atest1') as t1;
704
705-- has_column_privilege function
706
707-- bad-input checks (as non-super-user)
708select has_column_privilege('pg_authid',NULL,'select');
709select has_column_privilege('pg_authid','nosuchcol','select');
710select has_column_privilege(9999,'nosuchcol','select');
711select has_column_privilege(9999,99::int2,'select');
712select has_column_privilege('pg_authid',99::int2,'select');
713select has_column_privilege(9999,99::int2,'select');
714
715create temp table mytable(f1 int, f2 int, f3 int);
716alter table mytable drop column f2;
717select has_column_privilege('mytable','f2','select');
718select has_column_privilege('mytable','........pg.dropped.2........','select');
719select has_column_privilege('mytable',2::int2,'select');
720revoke select on table mytable from regress_priv_user3;
721select has_column_privilege('mytable',2::int2,'select');
722drop table mytable;
723
724-- Grant options
725
726SET SESSION AUTHORIZATION regress_priv_user1;
727
728CREATE TABLE atest4 (a int);
729
730GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
731GRANT UPDATE ON atest4 TO regress_priv_user2;
732GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
733
734SET SESSION AUTHORIZATION regress_priv_user2;
735
736GRANT SELECT ON atest4 TO regress_priv_user3;
737GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail
738
739SET SESSION AUTHORIZATION regress_priv_user1;
740
741REVOKE SELECT ON atest4 FROM regress_priv_user3; -- does nothing
742SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- true
743REVOKE SELECT ON atest4 FROM regress_priv_user2; -- fail
744REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE; -- ok
745SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT'); -- true
746SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false
747
748SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
749
750
751-- Admin options
752
753SET SESSION AUTHORIZATION regress_priv_user4;
754CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS
755	'GRANT regress_priv_group2 TO regress_priv_user5';
756GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION
757SET ROLE regress_priv_group2;
758GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege
759
760SET SESSION AUTHORIZATION regress_priv_user1;
761GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION
762SELECT dogrant_ok();			-- ok: SECURITY DEFINER conveys ADMIN
763SET ROLE regress_priv_group2;
764GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help
765
766SET SESSION AUTHORIZATION regress_priv_group2;
767GRANT regress_priv_group2 TO regress_priv_user5; -- ok: a role can self-admin
768CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS
769	'GRANT regress_priv_group2 TO regress_priv_user5';
770SELECT dogrant_fails();			-- fails: no self-admin in SECURITY DEFINER
771DROP FUNCTION dogrant_fails();
772
773SET SESSION AUTHORIZATION regress_priv_user4;
774DROP FUNCTION dogrant_ok();
775REVOKE regress_priv_group2 FROM regress_priv_user5;
776
777
778-- has_sequence_privilege tests
779\c -
780
781CREATE SEQUENCE x_seq;
782
783GRANT USAGE on x_seq to regress_priv_user2;
784
785SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT');
786SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT');
787SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT');
788
789SET SESSION AUTHORIZATION regress_priv_user2;
790
791SELECT has_sequence_privilege('x_seq', 'USAGE');
792
793-- largeobject privilege tests
794\c -
795SET SESSION AUTHORIZATION regress_priv_user1;
796
797SELECT lo_create(1001);
798SELECT lo_create(1002);
799SELECT lo_create(1003);
800SELECT lo_create(1004);
801SELECT lo_create(1005);
802
803GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC;
804GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2;
805GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2;
806GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2;
807GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION;
808
809GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC;	-- to be failed
810GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser;	-- to be failed
811GRANT SELECT, UPDATE ON LARGE OBJECT  999 TO PUBLIC;	-- to be failed
812
813\c -
814SET SESSION AUTHORIZATION regress_priv_user2;
815
816SELECT lo_create(2001);
817SELECT lo_create(2002);
818
819SELECT loread(lo_open(1001, x'20000'::int), 32);	-- allowed, for now
820SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd');	-- fail, wrong mode
821
822SELECT loread(lo_open(1001, x'40000'::int), 32);
823SELECT loread(lo_open(1002, x'40000'::int), 32);	-- to be denied
824SELECT loread(lo_open(1003, x'40000'::int), 32);
825SELECT loread(lo_open(1004, x'40000'::int), 32);
826
827SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd');
828SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');	-- to be denied
829SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd');	-- to be denied
830SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd');
831
832GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3;
833GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3;	-- to be denied
834REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC;
835GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3;
836
837SELECT lo_unlink(1001);		-- to be denied
838SELECT lo_unlink(2002);
839
840\c -
841-- confirm ACL setting
842SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
843
844SET SESSION AUTHORIZATION regress_priv_user3;
845
846SELECT loread(lo_open(1001, x'40000'::int), 32);
847SELECT loread(lo_open(1003, x'40000'::int), 32);	-- to be denied
848SELECT loread(lo_open(1005, x'40000'::int), 32);
849
850SELECT lo_truncate(lo_open(1005, x'20000'::int), 10);	-- to be denied
851SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
852
853-- compatibility mode in largeobject permission
854\c -
855SET lo_compat_privileges = false;	-- default setting
856SET SESSION AUTHORIZATION regress_priv_user4;
857
858SELECT loread(lo_open(1002, x'40000'::int), 32);	-- to be denied
859SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');	-- to be denied
860SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);	-- to be denied
861SELECT lo_put(1002, 1, 'abcd');				-- to be denied
862SELECT lo_unlink(1002);					-- to be denied
863SELECT lo_export(1001, '/dev/null');			-- to be denied
864SELECT lo_import('/dev/null');				-- to be denied
865SELECT lo_import('/dev/null', 2003);			-- to be denied
866
867\c -
868SET lo_compat_privileges = true;	-- compatibility mode
869SET SESSION AUTHORIZATION regress_priv_user4;
870
871SELECT loread(lo_open(1002, x'40000'::int), 32);
872SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
873SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
874SELECT lo_unlink(1002);
875SELECT lo_export(1001, '/dev/null');			-- to be denied
876
877-- don't allow unpriv users to access pg_largeobject contents
878\c -
879SELECT * FROM pg_largeobject LIMIT 0;
880
881SET SESSION AUTHORIZATION regress_priv_user1;
882SELECT * FROM pg_largeobject LIMIT 0;			-- to be denied
883
884-- test default ACLs
885\c -
886
887CREATE SCHEMA testns;
888GRANT ALL ON SCHEMA testns TO regress_priv_user1;
889
890CREATE TABLE testns.acltest1 (x int);
891SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
892SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
893
894ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT SELECT ON TABLES TO public;
895
896SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
897SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
898
899DROP TABLE testns.acltest1;
900CREATE TABLE testns.acltest1 (x int);
901
902SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
903SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
904
905ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1;
906
907DROP TABLE testns.acltest1;
908CREATE TABLE testns.acltest1 (x int);
909
910SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
911SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes
912
913ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1;
914
915DROP TABLE testns.acltest1;
916CREATE TABLE testns.acltest1 (x int);
917
918SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
919SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
920
921ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
922
923ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- error
924
925--
926-- Testing blanket default grants is very hazardous since it might change
927-- the privileges attached to objects created by concurrent regression tests.
928-- To avoid that, be sure to revoke the privileges again before committing.
929--
930BEGIN;
931
932ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2;
933
934CREATE SCHEMA testns2;
935
936SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes
937SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no
938
939ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2;
940
941CREATE SCHEMA testns3;
942
943SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no
944SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no
945
946ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
947
948CREATE SCHEMA testns4;
949
950SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes
951SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes
952
953ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2;
954
955COMMIT;
956
957CREATE SCHEMA testns5;
958
959SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no
960SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no
961
962SET ROLE regress_priv_user1;
963
964CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
965CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
966CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
967
968SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no
969SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- no
970SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no
971
972ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public;
973
974DROP FUNCTION testns.foo();
975CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
976DROP AGGREGATE testns.agg1(int);
977CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
978DROP PROCEDURE testns.bar();
979CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
980
981SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- yes
982SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes
983SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here)
984
985DROP FUNCTION testns.foo();
986DROP AGGREGATE testns.agg1(int);
987DROP PROCEDURE testns.bar();
988
989ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public;
990
991CREATE DOMAIN testns.priv_testdomain1 AS int;
992
993SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- no
994
995ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public;
996
997DROP DOMAIN testns.priv_testdomain1;
998CREATE DOMAIN testns.priv_testdomain1 AS int;
999
1000SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- yes
1001
1002DROP DOMAIN testns.priv_testdomain1;
1003
1004RESET ROLE;
1005
1006SELECT count(*)
1007  FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
1008  WHERE nspname = 'testns';
1009
1010DROP SCHEMA testns CASCADE;
1011DROP SCHEMA testns2 CASCADE;
1012DROP SCHEMA testns3 CASCADE;
1013DROP SCHEMA testns4 CASCADE;
1014DROP SCHEMA testns5 CASCADE;
1015
1016SELECT d.*     -- check that entries went away
1017  FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
1018  WHERE nspname IS NULL AND defaclnamespace != 0;
1019
1020
1021-- Grant on all objects of given type in a schema
1022\c -
1023
1024CREATE SCHEMA testns;
1025CREATE TABLE testns.t1 (f1 int);
1026CREATE TABLE testns.t2 (f1 int);
1027
1028SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false
1029
1030GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1;
1031
1032SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true
1033SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true
1034
1035REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1;
1036
1037SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false
1038SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false
1039
1040CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
1041CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4);
1042CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql;
1043
1044SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true by default
1045SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true by default
1046SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true by default
1047
1048REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
1049
1050SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- false
1051SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- false
1052SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- still true, not a function
1053
1054REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC;
1055
1056SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- now false
1057
1058GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC;
1059
1060SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true
1061SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true
1062SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true
1063
1064DROP SCHEMA testns CASCADE;
1065
1066
1067-- Change owner of the schema & and rename of new schema owner
1068\c -
1069
1070CREATE ROLE regress_schemauser1 superuser login;
1071CREATE ROLE regress_schemauser2 superuser login;
1072
1073SET SESSION ROLE regress_schemauser1;
1074CREATE SCHEMA testns;
1075
1076SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
1077
1078ALTER SCHEMA testns OWNER TO regress_schemauser2;
1079ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed;
1080SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
1081
1082set session role regress_schemauser_renamed;
1083DROP SCHEMA testns CASCADE;
1084
1085-- clean up
1086\c -
1087
1088DROP ROLE regress_schemauser1;
1089DROP ROLE regress_schemauser_renamed;
1090
1091
1092-- test that dependent privileges are revoked (or not) properly
1093\c -
1094
1095set session role regress_priv_user1;
1096create table dep_priv_test (a int);
1097grant select on dep_priv_test to regress_priv_user2 with grant option;
1098grant select on dep_priv_test to regress_priv_user3 with grant option;
1099set session role regress_priv_user2;
1100grant select on dep_priv_test to regress_priv_user4 with grant option;
1101set session role regress_priv_user3;
1102grant select on dep_priv_test to regress_priv_user4 with grant option;
1103set session role regress_priv_user4;
1104grant select on dep_priv_test to regress_priv_user5;
1105\dp dep_priv_test
1106set session role regress_priv_user2;
1107revoke select on dep_priv_test from regress_priv_user4 cascade;
1108\dp dep_priv_test
1109set session role regress_priv_user3;
1110revoke select on dep_priv_test from regress_priv_user4 cascade;
1111\dp dep_priv_test
1112set session role regress_priv_user1;
1113drop table dep_priv_test;
1114
1115
1116-- clean up
1117
1118\c
1119
1120drop sequence x_seq;
1121
1122DROP AGGREGATE priv_testagg1(int);
1123DROP FUNCTION priv_testfunc2(int);
1124DROP FUNCTION priv_testfunc4(boolean);
1125DROP PROCEDURE priv_testproc1(int);
1126
1127DROP VIEW atestv0;
1128DROP VIEW atestv1;
1129DROP VIEW atestv2;
1130-- this should cascade to drop atestv4
1131DROP VIEW atestv3 CASCADE;
1132-- this should complain "does not exist"
1133DROP VIEW atestv4;
1134
1135DROP TABLE atest1;
1136DROP TABLE atest2;
1137DROP TABLE atest3;
1138DROP TABLE atest4;
1139DROP TABLE atest5;
1140DROP TABLE atest6;
1141DROP TABLE atestc;
1142DROP TABLE atestp1;
1143DROP TABLE atestp2;
1144
1145SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
1146
1147DROP GROUP regress_priv_group1;
1148DROP GROUP regress_priv_group2;
1149
1150-- these are needed to clean up permissions
1151REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1;
1152DROP OWNED BY regress_priv_user1;
1153
1154DROP USER regress_priv_user1;
1155DROP USER regress_priv_user2;
1156DROP USER regress_priv_user3;
1157DROP USER regress_priv_user4;
1158DROP USER regress_priv_user5;
1159DROP USER regress_priv_user6;
1160
1161
1162-- permissions with LOCK TABLE
1163CREATE USER regress_locktable_user;
1164CREATE TABLE lock_table (a int);
1165
1166-- LOCK TABLE and SELECT permission
1167GRANT SELECT ON lock_table TO regress_locktable_user;
1168SET SESSION AUTHORIZATION regress_locktable_user;
1169BEGIN;
1170LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail
1171ROLLBACK;
1172BEGIN;
1173LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
1174COMMIT;
1175BEGIN;
1176LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
1177ROLLBACK;
1178\c
1179REVOKE SELECT ON lock_table FROM regress_locktable_user;
1180
1181-- LOCK TABLE and INSERT permission
1182GRANT INSERT ON lock_table TO regress_locktable_user;
1183SET SESSION AUTHORIZATION regress_locktable_user;
1184BEGIN;
1185LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1186COMMIT;
1187BEGIN;
1188LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1189ROLLBACK;
1190BEGIN;
1191LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
1192ROLLBACK;
1193\c
1194REVOKE INSERT ON lock_table FROM regress_locktable_user;
1195
1196-- LOCK TABLE and UPDATE permission
1197GRANT UPDATE ON lock_table TO regress_locktable_user;
1198SET SESSION AUTHORIZATION regress_locktable_user;
1199BEGIN;
1200LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1201COMMIT;
1202BEGIN;
1203LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1204ROLLBACK;
1205BEGIN;
1206LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
1207COMMIT;
1208\c
1209REVOKE UPDATE ON lock_table FROM regress_locktable_user;
1210
1211-- LOCK TABLE and DELETE permission
1212GRANT DELETE ON lock_table TO regress_locktable_user;
1213SET SESSION AUTHORIZATION regress_locktable_user;
1214BEGIN;
1215LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1216COMMIT;
1217BEGIN;
1218LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1219ROLLBACK;
1220BEGIN;
1221LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
1222COMMIT;
1223\c
1224REVOKE DELETE ON lock_table FROM regress_locktable_user;
1225
1226-- LOCK TABLE and TRUNCATE permission
1227GRANT TRUNCATE ON lock_table TO regress_locktable_user;
1228SET SESSION AUTHORIZATION regress_locktable_user;
1229BEGIN;
1230LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
1231COMMIT;
1232BEGIN;
1233LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
1234ROLLBACK;
1235BEGIN;
1236LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
1237COMMIT;
1238\c
1239REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
1240
1241-- clean up
1242DROP TABLE lock_table;
1243DROP USER regress_locktable_user;
1244