Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 2493 insertions, 0 deletions

diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
new file mode 100644
index 0000000..b3c3b25
--- /dev/null
+++ b/src/test/regress/expected/privileges.out
@@ -0,0 +1,2493 @@
+--
+-- Test access privileges
+--
+-- Clean up in case a prior regression run failed
+-- Suppress NOTICE messages when users/groups don't exist
+SET client_min_messages TO 'warning';
+DROP ROLE IF EXISTS regress_priv_group1;
+DROP ROLE IF EXISTS regress_priv_group2;
+DROP ROLE IF EXISTS regress_priv_user1;
+DROP ROLE IF EXISTS regress_priv_user2;
+DROP ROLE IF EXISTS regress_priv_user3;
+DROP ROLE IF EXISTS regress_priv_user4;
+DROP ROLE IF EXISTS regress_priv_user5;
+DROP ROLE IF EXISTS regress_priv_user6;
+DROP ROLE IF EXISTS regress_priv_user7;
+SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
+ lo_unlink 
+-----------
+(0 rows)
+
+RESET client_min_messages;
+-- test proper begins here
+CREATE USER regress_priv_user1;
+CREATE USER regress_priv_user2;
+CREATE USER regress_priv_user3;
+CREATE USER regress_priv_user4;
+CREATE USER regress_priv_user5;
+CREATE USER regress_priv_user5;	-- duplicate
+ERROR:  role "regress_priv_user5" already exists
+CREATE USER regress_priv_user6;
+CREATE USER regress_priv_user7;
+CREATE ROLE regress_priv_role;
+GRANT pg_read_all_data TO regress_priv_user6;
+GRANT pg_write_all_data TO regress_priv_user7;
+CREATE GROUP regress_priv_group1;
+CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
+ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
+ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2;	-- duplicate
+NOTICE:  role "regress_priv_user2" is already a member of role "regress_priv_group2"
+ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
+GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION;
+-- prepare non-leakproof function for later
+CREATE FUNCTION leak(integer,integer) RETURNS boolean
+  AS 'int4lt'
+  LANGUAGE internal IMMUTABLE STRICT;  -- but deliberately not LEAKPROOF
+ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1;
+-- test owner privileges
+GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE;
+REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error
+REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY regress_priv_user2; -- error
+REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_USER;
+REVOKE regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_ROLE;
+DROP ROLE regress_priv_role;
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT session_user, current_user;
+    session_user    |    current_user    
+--------------------+--------------------
+ regress_priv_user1 | regress_priv_user1
+(1 row)
+
+CREATE TABLE atest1 ( a int, b text );
+SELECT * FROM atest1;
+ a | b 
+---+---
+(0 rows)
+
+INSERT INTO atest1 VALUES (1, 'one');
+DELETE FROM atest1;
+UPDATE atest1 SET a = 1 WHERE b = 'blech';
+TRUNCATE atest1;
+BEGIN;
+LOCK atest1 IN ACCESS EXCLUSIVE MODE;
+COMMIT;
+REVOKE ALL ON atest1 FROM PUBLIC;
+SELECT * FROM atest1;
+ a | b 
+---+---
+(0 rows)
+
+GRANT ALL ON atest1 TO regress_priv_user2;
+GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
+SELECT * FROM atest1;
+ a | b 
+---+---
+(0 rows)
+
+CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+GRANT SELECT ON atest2 TO regress_priv_user2;
+GRANT UPDATE ON atest2 TO regress_priv_user3;
+GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
+GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
+GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
+ERROR:  grantor must be current user
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT session_user, current_user;
+    session_user    |    current_user    
+--------------------+--------------------
+ regress_priv_user2 | regress_priv_user2
+(1 row)
+
+-- try various combinations of queries on atest1 and atest2
+SELECT * FROM atest1; -- ok
+ a | b 
+---+---
+(0 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2 
+------+------
+(0 rows)
+
+INSERT INTO atest1 VALUES (2, 'two'); -- ok
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR:  permission denied for table atest2
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
+UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fail
+ERROR:  permission denied for table atest2
+SELECT * FROM atest1 FOR UPDATE; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atest2 FOR UPDATE; -- fail
+ERROR:  permission denied for table atest2
+DELETE FROM atest2; -- fail
+ERROR:  permission denied for table atest2
+TRUNCATE atest2; -- fail
+ERROR:  permission denied for table atest2
+BEGIN;
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
+ERROR:  permission denied for table atest2
+COMMIT;
+COPY atest2 FROM stdin; -- fail
+ERROR:  permission denied for table atest2
+GRANT ALL ON atest1 TO PUBLIC; -- fail
+WARNING:  no privileges were granted for "atest1"
+-- checks in subquery, both ok
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+ a | b 
+---+---
+(0 rows)
+
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+ col1 | col2 
+------+------
+(0 rows)
+
+SET SESSION AUTHORIZATION regress_priv_user6;
+SELECT * FROM atest1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2 
+------+------
+(0 rows)
+
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR:  permission denied for table atest2
+SET SESSION AUTHORIZATION regress_priv_user7;
+SELECT * FROM atest1; -- fail
+ERROR:  permission denied for table atest1
+SELECT * FROM atest2; -- fail
+ERROR:  permission denied for table atest2
+INSERT INTO atest2 VALUES ('foo', true); -- ok
+UPDATE atest2 SET col2 = true; -- ok
+DELETE FROM atest2; -- ok
+-- Make sure we are not able to modify system catalogs
+UPDATE pg_catalog.pg_class SET relname = '123'; -- fail
+ERROR:  permission denied for table pg_class
+DELETE FROM pg_catalog.pg_class; -- fail
+ERROR:  permission denied for table pg_class
+UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail
+ERROR:  permission denied for table pg_toast_1213
+SET SESSION AUTHORIZATION regress_priv_user3;
+SELECT session_user, current_user;
+    session_user    |    current_user    
+--------------------+--------------------
+ regress_priv_user3 | regress_priv_user3
+(1 row)
+
+SELECT * FROM atest1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atest2; -- fail
+ERROR:  permission denied for table atest2
+INSERT INTO atest1 VALUES (2, 'two'); -- fail
+ERROR:  permission denied for table atest1
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR:  permission denied for table atest2
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
+ERROR:  permission denied for table atest1
+UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
+ERROR:  permission denied for table atest1
+UPDATE atest2 SET col2 = NULL; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
+ERROR:  permission denied for table atest2
+UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok
+SELECT * FROM atest1 FOR UPDATE; -- fail
+ERROR:  permission denied for table atest1
+SELECT * FROM atest2 FOR UPDATE; -- fail
+ERROR:  permission denied for table atest2
+DELETE FROM atest2; -- fail
+ERROR:  permission denied for table atest2
+TRUNCATE atest2; -- fail
+ERROR:  permission denied for table atest2
+BEGIN;
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
+COMMIT;
+COPY atest2 FROM stdin; -- fail
+ERROR:  permission denied for table atest2
+-- checks in subquery, both fail
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+ERROR:  permission denied for table atest2
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+ERROR:  permission denied for table atest2
+SET SESSION AUTHORIZATION regress_priv_user4;
+COPY atest2 FROM stdin; -- ok
+SELECT * FROM atest1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+-- test leaky-function protections in selfuncs
+-- regress_priv_user1 will own a table and provide views for it.
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE atest12 as
+  SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x;
+CREATE INDEX ON atest12 (a);
+CREATE INDEX ON atest12 (abs(a));
+-- results below depend on having quite accurate stats for atest12, so...
+ALTER TABLE atest12 SET (autovacuum_enabled = off);
+SET default_statistics_target = 10000;
+VACUUM ANALYZE atest12;
+RESET default_statistics_target;
+CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer,
+                     restrict = scalarltsel);
+-- views with leaky operator
+CREATE VIEW atest12v AS
+  SELECT * FROM atest12 WHERE b <<< 5;
+CREATE VIEW atest12sbv WITH (security_barrier=true) AS
+  SELECT * FROM atest12 WHERE b <<< 5;
+GRANT SELECT ON atest12v TO PUBLIC;
+GRANT SELECT ON atest12sbv TO PUBLIC;
+-- This plan should use nestloop, knowing that few rows will be selected.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on atest12 atest12_1
+         Filter: (b <<< 5)
+   ->  Index Scan using atest12_a_idx on atest12
+         Index Cond: (a = atest12_1.b)
+         Filter: (b <<< 5)
+(6 rows)
+
+-- And this one.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
+  WHERE x.a = y.b and abs(y.a) <<< 5;
+                    QUERY PLAN                     
+---------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on atest12 y
+         Filter: (abs(a) <<< 5)
+   ->  Index Scan using atest12_a_idx on atest12 x
+         Index Cond: (a = y.b)
+(5 rows)
+
+-- This should also be a nestloop, but the security barrier forces the inner
+-- scan to be materialized
+EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
+                QUERY PLAN                 
+-------------------------------------------
+ Nested Loop
+   Join Filter: (atest12.a = atest12_1.b)
+   ->  Seq Scan on atest12
+         Filter: (b <<< 5)
+   ->  Materialize
+         ->  Seq Scan on atest12 atest12_1
+               Filter: (b <<< 5)
+(7 rows)
+
+-- Check if regress_priv_user2 can break security.
+SET SESSION AUTHORIZATION regress_priv_user2;
+CREATE FUNCTION leak2(integer,integer) RETURNS boolean
+  AS $$begin raise notice 'leak % %', $1, $2; return $1 > $2; end$$
+  LANGUAGE plpgsql immutable;
+CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer,
+                     restrict = scalargtsel);
+-- This should not show any "leak" notices before failing.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0;
+ERROR:  permission denied for table atest12
+-- These plans should continue to use a nestloop, since they execute with the
+-- privileges of the view owner.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on atest12 atest12_1
+         Filter: (b <<< 5)
+   ->  Index Scan using atest12_a_idx on atest12
+         Index Cond: (a = atest12_1.b)
+         Filter: (b <<< 5)
+(6 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
+                QUERY PLAN                 
+-------------------------------------------
+ Nested Loop
+   Join Filter: (atest12.a = atest12_1.b)
+   ->  Seq Scan on atest12
+         Filter: (b <<< 5)
+   ->  Materialize
+         ->  Seq Scan on atest12 atest12_1
+               Filter: (b <<< 5)
+(7 rows)
+
+-- A non-security barrier view does not guard against information leakage.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y
+  WHERE x.a = y.b and abs(y.a) <<< 5;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on atest12 atest12_1
+         Filter: ((b <<< 5) AND (abs(a) <<< 5))
+   ->  Index Scan using atest12_a_idx on atest12
+         Index Cond: (a = atest12_1.b)
+         Filter: (b <<< 5)
+(6 rows)
+
+-- But a security barrier view isolates the leaky operator.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y
+  WHERE x.a = y.b and abs(y.a) <<< 5;
+             QUERY PLAN              
+-------------------------------------
+ Nested Loop
+   Join Filter: (atest12_1.a = y.b)
+   ->  Subquery Scan on y
+         Filter: (abs(y.a) <<< 5)
+         ->  Seq Scan on atest12
+               Filter: (b <<< 5)
+   ->  Seq Scan on atest12 atest12_1
+         Filter: (b <<< 5)
+(8 rows)
+
+-- Now regress_priv_user1 grants sufficient access to regress_priv_user2.
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT SELECT (a, b) ON atest12 TO PUBLIC;
+SET SESSION AUTHORIZATION regress_priv_user2;
+-- regress_priv_user2 should continue to get a good row estimate.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
+                   QUERY PLAN                    
+-------------------------------------------------
+ Nested Loop
+   ->  Seq Scan on atest12 atest12_1
+         Filter: (b <<< 5)
+   ->  Index Scan using atest12_a_idx on atest12
+         Index Cond: (a = atest12_1.b)
+         Filter: (b <<< 5)
+(6 rows)
+
+-- But not for this, due to lack of table-wide permissions needed
+-- to make use of the expression index's statistics.
+EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
+  WHERE x.a = y.b and abs(y.a) <<< 5;
+              QUERY PLAN              
+--------------------------------------
+ Hash Join
+   Hash Cond: (x.a = y.b)
+   ->  Seq Scan on atest12 x
+   ->  Hash
+         ->  Seq Scan on atest12 y
+               Filter: (abs(a) <<< 5)
+(6 rows)
+
+-- clean up (regress_priv_user1's objects are all dropped later)
+DROP FUNCTION leak2(integer, integer) CASCADE;
+NOTICE:  drop cascades to operator >>>(integer,integer)
+-- groups
+SET SESSION AUTHORIZATION regress_priv_user3;
+CREATE TABLE atest3 (one int, two int, three int);
+GRANT DELETE ON atest3 TO GROUP regress_priv_group2;
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT * FROM atest3; -- fail
+ERROR:  permission denied for table atest3
+DELETE FROM atest3; -- ok
+BEGIN;
+RESET SESSION AUTHORIZATION;
+ALTER ROLE regress_priv_user1 NOINHERIT;
+SET SESSION AUTHORIZATION regress_priv_user1;
+DELETE FROM atest3;
+ERROR:  permission denied for table atest3
+ROLLBACK;
+-- views
+SET SESSION AUTHORIZATION regress_priv_user3;
+CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
+/* The next *should* fail, but it's not implemented that way yet. */
+CREATE VIEW atestv2 AS SELECT * FROM atest2;
+CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
+/* Empty view is a corner case that failed in 9.2. */
+CREATE VIEW atestv0 AS SELECT 0 as x WHERE false; -- ok
+SELECT * FROM atestv1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atestv2; -- fail
+ERROR:  permission denied for table atest2
+GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4;
+GRANT SELECT ON atestv2 TO regress_priv_user2;
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT * FROM atestv1; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT * FROM atestv2; -- fail
+ERROR:  permission denied for view atestv2
+SELECT * FROM atestv3; -- ok
+ one | two | three 
+-----+-----+-------
+(0 rows)
+
+SELECT * FROM atestv0; -- fail
+ERROR:  permission denied for view atestv0
+-- Appendrels excluded by constraints failed to check permissions in 8.4-9.2.
+select * from
+  ((select a.q1 as x from int8_tbl a offset 0)
+   union all
+   (select b.q2 as x from int8_tbl b offset 0)) ss
+where false;
+ERROR:  permission denied for table int8_tbl
+set constraint_exclusion = on;
+select * from
+  ((select a.q1 as x, random() from int8_tbl a where q1 > 0)
+   union all
+   (select b.q2 as x, random() from int8_tbl b where q2 > 0)) ss
+where x < 0;
+ERROR:  permission denied for table int8_tbl
+reset constraint_exclusion;
+CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
+SELECT * FROM atestv4; -- ok
+ one | two | three 
+-----+-----+-------
+(0 rows)
+
+GRANT SELECT ON atestv4 TO regress_priv_user2;
+SET SESSION AUTHORIZATION regress_priv_user2;
+-- Two complex cases:
+SELECT * FROM atestv3; -- fail
+ERROR:  permission denied for view atestv3
+SELECT * FROM atestv4; -- ok (even though regress_priv_user2 cannot access underlying atestv3)
+ one | two | three 
+-----+-----+-------
+(0 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2 
+------+------
+ bar  | t
+(1 row)
+
+SELECT * FROM atestv2; -- fail (even though regress_priv_user2 can access underlying atest2)
+ERROR:  permission denied for table atest2
+-- Test column level permissions
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE atest5 (one int, two int unique, three int, four int unique);
+CREATE TABLE atest6 (one int, two int, blue int);
+GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regress_priv_user4;
+GRANT ALL (one) ON atest5 TO regress_priv_user3;
+INSERT INTO atest5 VALUES (1,2,3);
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT * FROM atest5; -- fail
+ERROR:  permission denied for table atest5
+SELECT one FROM atest5; -- ok
+ one 
+-----
+   1
+(1 row)
+
+COPY atest5 (one) TO stdout; -- ok
+1
+SELECT two FROM atest5; -- fail
+ERROR:  permission denied for table atest5
+COPY atest5 (two) TO stdout; -- fail
+ERROR:  permission denied for table atest5
+SELECT atest5 FROM atest5; -- fail
+ERROR:  permission denied for table atest5
+COPY atest5 (one,two) TO stdout; -- fail
+ERROR:  permission denied for table atest5
+SELECT 1 FROM atest5; -- ok
+ ?column? 
+----------
+        1
+(1 row)
+
+SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok
+ ?column? 
+----------
+        1
+(1 row)
+
+SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail
+ERROR:  permission denied for table atest5
+SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail
+ERROR:  permission denied for table atest5
+SELECT * FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
+ERROR:  permission denied for table atest5
+SELECT j.* FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
+ERROR:  permission denied for table atest5
+SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
+ERROR:  permission denied for table atest5
+SELECT one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok
+ one 
+-----
+   1
+(1 row)
+
+SELECT j.one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok
+ one 
+-----
+   1
+(1 row)
+
+SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
+ERROR:  permission denied for table atest5
+SELECT j.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
+ERROR:  permission denied for table atest5
+SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
+ERROR:  permission denied for table atest5
+SELECT j.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
+ERROR:  permission denied for table atest5
+SELECT * FROM (atest5 a JOIN atest5 b USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT a.* FROM (atest5 a JOIN atest5 b USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT (a.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT a.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT b.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT b.y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT b.y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail
+ERROR:  permission denied for table atest5
+SELECT 1 FROM atest5 WHERE two = 2; -- fail
+ERROR:  permission denied for table atest5
+SELECT * FROM atest1, atest5; -- fail
+ERROR:  permission denied for table atest5
+SELECT atest1.* FROM atest1, atest5; -- ok
+ a |  b  
+---+-----
+ 1 | two
+ 1 | two
+(2 rows)
+
+SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok
+ a |  b  | one 
+---+-----+-----
+ 1 | two |   1
+ 1 | two |   1
+(2 rows)
+
+SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail
+ERROR:  permission denied for table atest5
+SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok
+ a |  b  | one 
+---+-----+-----
+ 1 | two |   1
+ 1 | two |   1
+(2 rows)
+
+SELECT one, two FROM atest5; -- fail
+ERROR:  permission denied for table atest5
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT SELECT (one,two) ON atest6 TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still
+ERROR:  permission denied for table atest5
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT SELECT (two) ON atest5 TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now
+ one | two 
+-----+-----
+(0 rows)
+
+-- test column-level privileges for INSERT and UPDATE
+INSERT INTO atest5 (two) VALUES (3); -- ok
+COPY atest5 FROM stdin; -- fail
+ERROR:  permission denied for table atest5
+COPY atest5 (two) FROM stdin; -- ok
+INSERT INTO atest5 (three) VALUES (4); -- fail
+ERROR:  permission denied for table atest5
+INSERT INTO atest5 VALUES (5,5,5); -- fail
+ERROR:  permission denied for table atest5
+UPDATE atest5 SET three = 10; -- ok
+UPDATE atest5 SET one = 8; -- fail
+ERROR:  permission denied for table atest5
+UPDATE atest5 SET three = 5, one = 2; -- fail
+ERROR:  permission denied for table atest5
+-- Check that column level privs are enforced in RETURNING
+-- Ok.
+INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10;
+-- Error. No SELECT on column three.
+INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three;
+ERROR:  permission denied for table atest5
+-- Ok.  May SELECT on column "one":
+INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one;
+ one 
+-----
+    
+(1 row)
+
+-- Check that column level privileges are enforced for EXCLUDED
+-- Ok. we may select one
+INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one;
+-- Error. No select rights on three
+INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three;
+ERROR:  permission denied for table atest5
+INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE)
+ERROR:  permission denied for table atest5
+INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT)
+ERROR:  permission denied for table atest5
+-- Check that the columns in the inference require select privileges
+INSERT INTO atest5(four) VALUES (4); -- fail
+ERROR:  permission denied for table atest5
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT INSERT (four) ON atest5 TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT)
+ERROR:  permission denied for table atest5
+INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- fails (due to SELECT)
+ERROR:  permission denied for table atest5
+INSERT INTO atest5(four) VALUES (4); -- ok
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT SELECT (four) ON atest5 TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok
+INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- ok
+SET SESSION AUTHORIZATION regress_priv_user1;
+REVOKE ALL (one) ON atest5 FROM regress_priv_user4;
+GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT one FROM atest5; -- fail
+ERROR:  permission denied for table atest5
+UPDATE atest5 SET one = 1; -- fail
+ERROR:  permission denied for table atest5
+SELECT atest6 FROM atest6; -- ok
+ atest6 
+--------
+(0 rows)
+
+COPY atest6 TO stdout; -- ok
+-- check error reporting with column privs
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2));
+GRANT SELECT (c1) ON t1 TO regress_priv_user2;
+GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2;
+GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_user2;
+-- seed data
+INSERT INTO t1 VALUES (1, 1, 1);
+INSERT INTO t1 VALUES (1, 2, 1);
+INSERT INTO t1 VALUES (2, 1, 2);
+INSERT INTO t1 VALUES (2, 2, 2);
+INSERT INTO t1 VALUES (3, 1, 3);
+SET SESSION AUTHORIZATION regress_priv_user2;
+INSERT INTO t1 (c1, c2) VALUES (1, 1); -- fail, but row not shown
+ERROR:  duplicate key value violates unique constraint "t1_pkey"
+UPDATE t1 SET c2 = 1; -- fail, but row not shown
+ERROR:  duplicate key value violates unique constraint "t1_pkey"
+INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted
+ERROR:  null value in column "c1" of relation "t1" violates not-null constraint
+DETAIL:  Failing row contains (c1, c2) = (null, null).
+INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT
+ERROR:  null value in column "c1" of relation "t1" violates not-null constraint
+DETAIL:  Failing row contains (c1, c3) = (null, null).
+INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT
+ERROR:  null value in column "c2" of relation "t1" violates not-null constraint
+DETAIL:  Failing row contains (c1) = (5).
+UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified
+ERROR:  new row for relation "t1" violates check constraint "t1_c3_check"
+DETAIL:  Failing row contains (c1, c3) = (1, 10).
+SET SESSION AUTHORIZATION regress_priv_user1;
+DROP TABLE t1;
+-- check error reporting with column privs on a partitioned table
+CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a);
+CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text);
+CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL);
+ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa');
+ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa');
+GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2;
+GRANT UPDATE (a, b, c) ON TABLE errtst TO regress_priv_user2;
+GRANT INSERT (a, b, c) ON TABLE errtst TO regress_priv_user2;
+INSERT INTO errtst_part_1 (a, b, c, secret1, secret2)
+VALUES ('aaa', 'bbb', 'ccc', 'the body', 'is in the attic');
+SET SESSION AUTHORIZATION regress_priv_user2;
+-- Perform a few updates that violate the NOT NULL constraint. Make sure
+-- the error messages don't leak the secret fields.
+-- simple insert.
+INSERT INTO errtst (a, b) VALUES ('aaa', NULL);
+ERROR:  null value in column "b" of relation "errtst_part_1" violates not-null constraint
+DETAIL:  Failing row contains (a, b, c) = (aaa, null, null).
+-- simple update.
+UPDATE errtst SET b = NULL;
+ERROR:  null value in column "b" of relation "errtst_part_1" violates not-null constraint
+DETAIL:  Failing row contains (a, b, c) = (aaa, null, ccc).
+-- partitioning key is updated, doesn't move the row.
+UPDATE errtst SET a = 'aaa', b = NULL;
+ERROR:  null value in column "b" of relation "errtst_part_1" violates not-null constraint
+DETAIL:  Failing row contains (a, b, c) = (aaa, null, ccc).
+-- row is moved to another partition.
+UPDATE errtst SET a = 'aaaa', b = NULL;
+ERROR:  null value in column "b" of relation "errtst_part_2" violates not-null constraint
+DETAIL:  Failing row contains (a, b, c) = (aaaa, null, ccc).
+-- row is moved to another partition. This differs from the previous case in
+-- that the new partition is excluded by constraint exclusion, so its
+-- ResultRelInfo is not created at ExecInitModifyTable, but needs to be
+-- constructed on the fly when the updated tuple is routed to it.
+UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa';
+ERROR:  null value in column "b" of relation "errtst_part_2" violates not-null constraint
+DETAIL:  Failing row contains (a, b, c) = (aaaa, null, ccc).
+SET SESSION AUTHORIZATION regress_priv_user1;
+DROP TABLE errtst;
+-- test column-level privileges when involved with DELETE
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER TABLE atest6 ADD COLUMN three integer;
+GRANT DELETE ON atest5 TO regress_priv_user3;
+GRANT SELECT (two) ON atest5 TO regress_priv_user3;
+REVOKE ALL (one) ON atest5 FROM regress_priv_user3;
+GRANT SELECT (one) ON atest5 TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT atest6 FROM atest6; -- fail
+ERROR:  permission denied for table atest6
+SELECT one FROM atest5 NATURAL JOIN atest6; -- fail
+ERROR:  permission denied for table atest5
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER TABLE atest6 DROP COLUMN three;
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT atest6 FROM atest6; -- ok
+ atest6 
+--------
+(0 rows)
+
+SELECT one FROM atest5 NATURAL JOIN atest6; -- ok
+ one 
+-----
+(0 rows)
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+ALTER TABLE atest6 DROP COLUMN two;
+REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT * FROM atest6; -- fail
+ERROR:  permission denied for table atest6
+SELECT 1 FROM atest6; -- fail
+ERROR:  permission denied for table atest6
+SET SESSION AUTHORIZATION regress_priv_user3;
+DELETE FROM atest5 WHERE one = 1; -- fail
+ERROR:  permission denied for table atest5
+DELETE FROM atest5 WHERE two = 2; -- ok
+-- check inheritance cases
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE atestp1 (f1 int, f2 int);
+CREATE TABLE atestp2 (fx int, fy int);
+CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2);
+GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2;
+GRANT SELECT(fx) ON atestc TO regress_priv_user2;
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT fx FROM atestp2; -- ok
+ fx 
+----
+(0 rows)
+
+SELECT fy FROM atestp2; -- ok
+ fy 
+----
+(0 rows)
+
+SELECT atestp2 FROM atestp2; -- ok
+ atestp2 
+---------
+(0 rows)
+
+SELECT tableoid FROM atestp2; -- ok
+ tableoid 
+----------
+(0 rows)
+
+SELECT fy FROM atestc; -- fail
+ERROR:  permission denied for table atestc
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2;
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT fx FROM atestp2; -- still ok
+ fx 
+----
+(0 rows)
+
+SELECT fy FROM atestp2; -- ok
+ fy 
+----
+(0 rows)
+
+SELECT atestp2 FROM atestp2; -- ok
+ atestp2 
+---------
+(0 rows)
+
+SELECT tableoid FROM atestp2; -- ok
+ tableoid 
+----------
+(0 rows)
+
+-- child's permissions do not apply when operating on parent
+SET SESSION AUTHORIZATION regress_priv_user1;
+REVOKE ALL ON atestc FROM regress_priv_user2;
+GRANT ALL ON atestp1 TO regress_priv_user2;
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT f2 FROM atestp1; -- ok
+ f2 
+----
+(0 rows)
+
+SELECT f2 FROM atestc; -- fail
+ERROR:  permission denied for table atestc
+DELETE FROM atestp1; -- ok
+DELETE FROM atestc; -- fail
+ERROR:  permission denied for table atestc
+UPDATE atestp1 SET f1 = 1; -- ok
+UPDATE atestc SET f1 = 1; -- fail
+ERROR:  permission denied for table atestc
+TRUNCATE atestp1; -- ok
+TRUNCATE atestc; -- fail
+ERROR:  permission denied for table atestc
+BEGIN;
+LOCK atestp1;
+END;
+BEGIN;
+LOCK atestc;
+ERROR:  permission denied for table atestc
+END;
+-- privileges on functions, languages
+-- switch to superuser
+\c -
+REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
+GRANT USAGE ON LANGUAGE sql TO regress_priv_user1; -- ok
+GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
+ERROR:  language "c" is not trusted
+DETAIL:  GRANT and REVOKE are not allowed on untrusted languages, because only superusers can use untrusted languages.
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT USAGE ON LANGUAGE sql TO regress_priv_user2; -- fail
+WARNING:  no privileges were granted for "sql"
+CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
+CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
+CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE priv_testproc1(int) AS 'select $1;' LANGUAGE sql;
+REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) TO regress_priv_user2;
+REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC; -- fail, not a function
+ERROR:  priv_testproc1(integer) is not a function
+REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC;
+GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2;
+GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3; -- semantic error
+ERROR:  invalid privilege type USAGE for function
+GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3; -- semantic error
+ERROR:  invalid privilege type USAGE for function
+GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3; -- semantic error
+ERROR:  invalid privilege type USAGE for procedure
+GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4;
+GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4;
+ERROR:  function priv_testfunc_nosuch(integer) does not exist
+GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4;
+GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
+CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
+  AS 'select col1 from atest2 where col2 = $1;'
+  LANGUAGE sql SECURITY DEFINER;
+GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
+ priv_testfunc1 | priv_testfunc2 
+----------------+----------------
+             10 |             15
+(1 row)
+
+CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
+ERROR:  permission denied for language sql
+SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
+ priv_testagg1 
+---------------
+             6
+(1 row)
+
+CALL priv_testproc1(6); -- ok
+SET SESSION AUTHORIZATION regress_priv_user3;
+SELECT priv_testfunc1(5); -- fail
+ERROR:  permission denied for function priv_testfunc1
+SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail
+ERROR:  permission denied for aggregate priv_testagg1
+CALL priv_testproc1(6); -- fail
+ERROR:  permission denied for procedure priv_testproc1
+SELECT col1 FROM atest2 WHERE col2 = true; -- fail
+ERROR:  permission denied for table atest2
+SELECT priv_testfunc4(true); -- ok
+ priv_testfunc4 
+----------------
+ bar
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT priv_testfunc1(5); -- ok
+ priv_testfunc1 
+----------------
+             10
+(1 row)
+
+SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
+ priv_testagg1 
+---------------
+             6
+(1 row)
+
+CALL priv_testproc1(6); -- ok
+DROP FUNCTION priv_testfunc1(int); -- fail
+ERROR:  must be owner of function priv_testfunc1
+DROP AGGREGATE priv_testagg1(int); -- fail
+ERROR:  must be owner of aggregate priv_testagg1
+DROP PROCEDURE priv_testproc1(int); -- fail
+ERROR:  must be owner of procedure priv_testproc1
+\c -
+DROP FUNCTION priv_testfunc1(int); -- ok
+-- restore to sanity
+GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
+-- verify privilege checks on array-element coercions
+BEGIN;
+SELECT '{1}'::int4[]::int8[];
+ int8 
+------
+ {1}
+(1 row)
+
+REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC;
+SELECT '{1}'::int4[]::int8[]; --superuser, succeed
+ int8 
+------
+ {1}
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT '{1}'::int4[]::int8[]; --other user, fail
+ERROR:  permission denied for function int8
+ROLLBACK;
+-- privileges on types
+-- switch to superuser
+\c -
+CREATE TYPE priv_testtype1 AS (a int, b text);
+REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC;
+GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2;
+GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2; -- fail
+ERROR:  cannot set privileges of array types
+HINT:  Set the privileges of the element type instead.
+GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2; -- fail
+ERROR:  "priv_testtype1" is not a domain
+CREATE DOMAIN priv_testdomain1 AS int;
+REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC;
+GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2;
+GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
+SET SESSION AUTHORIZATION regress_priv_user1;
+-- commands that should fail
+CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
+ERROR:  permission denied for type priv_testdomain1
+CREATE DOMAIN priv_testdomain2a AS priv_testdomain1;
+ERROR:  permission denied for type priv_testdomain1
+CREATE DOMAIN priv_testdomain3a AS int;
+CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS $$ SELECT $1::priv_testdomain3a $$ LANGUAGE SQL;
+CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int);
+ERROR:  permission denied for type priv_testdomain1
+DROP FUNCTION castfunc(int) CASCADE;
+DROP DOMAIN priv_testdomain3a;
+CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
+ERROR:  permission denied for type priv_testdomain1
+CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$;
+ERROR:  permission denied for type priv_testdomain1
+CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdomain1);
+ERROR:  permission denied for type priv_testdomain1
+CREATE TABLE test5a (a int, b priv_testdomain1);
+ERROR:  permission denied for type priv_testdomain1
+CREATE TABLE test6a OF priv_testtype1;
+ERROR:  permission denied for type priv_testtype1
+CREATE TABLE test10a (a int[], b priv_testtype1[]);
+ERROR:  permission denied for type priv_testtype1
+CREATE TABLE test9a (a int, b int);
+ALTER TABLE test9a ADD COLUMN c priv_testdomain1;
+ERROR:  permission denied for type priv_testdomain1
+ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1;
+ERROR:  permission denied for type priv_testdomain1
+CREATE TYPE test7a AS (a int, b priv_testdomain1);
+ERROR:  permission denied for type priv_testdomain1
+CREATE TYPE test8a AS (a int, b int);
+ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1;
+ERROR:  permission denied for type priv_testdomain1
+ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1;
+ERROR:  permission denied for type priv_testdomain1
+CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a);
+ERROR:  permission denied for type priv_testdomain1
+REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
+ERROR:  permission denied for type priv_testtype1
+SET SESSION AUTHORIZATION regress_priv_user2;
+-- commands that should succeed
+CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
+CREATE DOMAIN priv_testdomain2b AS priv_testdomain1;
+CREATE DOMAIN priv_testdomain3b AS int;
+CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS $$ SELECT $1::priv_testdomain3b $$ LANGUAGE SQL;
+CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int);
+WARNING:  cast will be ignored because the source data type is a domain
+CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
+CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$;
+CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1);
+CREATE TABLE test5b (a int, b priv_testdomain1);
+CREATE TABLE test6b OF priv_testtype1;
+CREATE TABLE test10b (a int[], b priv_testtype1[]);
+CREATE TABLE test9b (a int, b int);
+ALTER TABLE test9b ADD COLUMN c priv_testdomain1;
+ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1;
+CREATE TYPE test7b AS (a int, b priv_testdomain1);
+CREATE TYPE test8b AS (a int, b int);
+ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1;
+ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1;
+CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a);
+REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
+WARNING:  no privileges could be revoked for "priv_testtype1"
+\c -
+DROP AGGREGATE priv_testagg1b(priv_testdomain1);
+DROP DOMAIN priv_testdomain2b;
+DROP OPERATOR !! (NONE, priv_testdomain1);
+DROP FUNCTION priv_testfunc5b(a priv_testdomain1);
+DROP FUNCTION priv_testfunc6b(b int);
+DROP TABLE test5b;
+DROP TABLE test6b;
+DROP TABLE test9b;
+DROP TABLE test10b;
+DROP TYPE test7b;
+DROP TYPE test8b;
+DROP CAST (priv_testdomain1 AS priv_testdomain3b);
+DROP FUNCTION castfunc(int) CASCADE;
+DROP DOMAIN priv_testdomain3b;
+DROP TABLE test11b;
+DROP TYPE priv_testtype1; -- ok
+DROP DOMAIN priv_testdomain1; -- ok
+-- truncate
+SET SESSION AUTHORIZATION regress_priv_user5;
+TRUNCATE atest2; -- ok
+TRUNCATE atest3; -- fail
+ERROR:  permission denied for table atest3
+-- has_table_privilege function
+-- bad-input checks
+select has_table_privilege(NULL,'pg_authid','select');
+ has_table_privilege 
+---------------------
+ 
+(1 row)
+
+select has_table_privilege('pg_shad','select');
+ERROR:  relation "pg_shad" does not exist
+select has_table_privilege('nosuchuser','pg_authid','select');
+ERROR:  role "nosuchuser" does not exist
+select has_table_privilege('pg_authid','sel');
+ERROR:  unrecognized privilege type: "sel"
+select has_table_privilege(-999999,'pg_authid','update');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(1,'select');
+ has_table_privilege 
+---------------------
+ 
+(1 row)
+
+-- superuser
+\c -
+select has_table_privilege(current_user,'pg_authid','select');
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(current_user,'pg_authid','insert');
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_authid','update')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_authid','delete')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+-- 'rule' privilege no longer exists, but for backwards compatibility
+-- has_table_privilege still recognizes the keyword and says FALSE
+select has_table_privilege(current_user,t1.oid,'rule')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,t1.oid,'references')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_authid') as t1,
+  (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'insert')
+from (select oid from pg_class where relname = 'pg_authid') as t1,
+  (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege('pg_authid','update');
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege('pg_authid','delete');
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege('pg_authid','truncate');
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'trigger')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+-- non-superuser
+SET SESSION AUTHORIZATION regress_priv_user3;
+select has_table_privilege(current_user,'pg_class','select');
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(current_user,'pg_class','insert');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_class','update')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_class','delete')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,t1.oid,'references')
+from (select oid from pg_class where relname = 'pg_class') as t1;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_class') as t1,
+  (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'insert')
+from (select oid from pg_class where relname = 'pg_class') as t1,
+  (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('pg_class','update');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('pg_class','delete');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('pg_class','truncate');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_class') as t1;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'trigger')
+from (select oid from pg_class where relname = 'pg_class') as t1;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,'atest1','select');
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(current_user,'atest1','insert');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'atest1','update')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'atest1','delete')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,t1.oid,'references')
+from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'select')
+from (select oid from pg_class where relname = 'atest1') as t1,
+  (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'insert')
+from (select oid from pg_class where relname = 'atest1') as t1,
+  (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('atest1','update');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('atest1','delete');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('atest1','truncate');
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t1.oid,'select')
+from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'trigger')
+from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+-- has_column_privilege function
+-- bad-input checks (as non-super-user)
+select has_column_privilege('pg_authid',NULL,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+select has_column_privilege('pg_authid','nosuchcol','select');
+ERROR:  column "nosuchcol" of relation "pg_authid" does not exist
+select has_column_privilege(9999,'nosuchcol','select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+select has_column_privilege(9999,99::int2,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+select has_column_privilege('pg_authid',99::int2,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+select has_column_privilege(9999,99::int2,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+create temp table mytable(f1 int, f2 int, f3 int);
+alter table mytable drop column f2;
+select has_column_privilege('mytable','f2','select');
+ERROR:  column "f2" of relation "mytable" does not exist
+select has_column_privilege('mytable','........pg.dropped.2........','select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+select has_column_privilege('mytable',2::int2,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+select has_column_privilege('mytable',99::int2,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+revoke select on table mytable from regress_priv_user3;
+select has_column_privilege('mytable',2::int2,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+select has_column_privilege('mytable',99::int2,'select');
+ has_column_privilege 
+----------------------
+ 
+(1 row)
+
+drop table mytable;
+-- Grant options
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE atest4 (a int);
+GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
+GRANT UPDATE ON atest4 TO regress_priv_user2;
+GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
+SET SESSION AUTHORIZATION regress_priv_user2;
+GRANT SELECT ON atest4 TO regress_priv_user3;
+GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail
+WARNING:  no privileges were granted for "atest4"
+SET SESSION AUTHORIZATION regress_priv_user1;
+REVOKE SELECT ON atest4 FROM regress_priv_user3; -- does nothing
+SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- true
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+REVOKE SELECT ON atest4 FROM regress_priv_user2; -- fail
+ERROR:  dependent privileges exist
+HINT:  Use CASCADE to revoke them too.
+REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE; -- ok
+SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT'); -- true
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+-- security-restricted operations
+\c -
+CREATE ROLE regress_sro_user;
+-- Check that index expressions and predicates are run as the table's owner
+-- A dummy index function checking current_user
+CREATE FUNCTION sro_ifun(int) RETURNS int AS $$
+BEGIN
+	-- Below we set the table's owner to regress_sro_user
+	ASSERT current_user = 'regress_sro_user',
+		format('sro_ifun(%s) called by %s', $1, current_user);
+	RETURN $1;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+-- Create a table owned by regress_sro_user
+CREATE TABLE sro_tab (a int);
+ALTER TABLE sro_tab OWNER TO regress_sro_user;
+INSERT INTO sro_tab VALUES (1), (2), (3);
+-- Create an expression index with a predicate
+CREATE INDEX sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0)))
+	WHERE sro_ifun(a + 10) > sro_ifun(10);
+DROP INDEX sro_idx;
+-- Do the same concurrently
+CREATE INDEX CONCURRENTLY sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0)))
+	WHERE sro_ifun(a + 10) > sro_ifun(10);
+-- REINDEX
+REINDEX TABLE sro_tab;
+REINDEX INDEX sro_idx;
+REINDEX TABLE CONCURRENTLY sro_tab;
+DROP INDEX sro_idx;
+-- CLUSTER
+CREATE INDEX sro_cluster_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0)));
+CLUSTER sro_tab USING sro_cluster_idx;
+DROP INDEX sro_cluster_idx;
+-- BRIN index
+CREATE INDEX sro_brin ON sro_tab USING brin ((sro_ifun(a) + sro_ifun(0)));
+SELECT brin_desummarize_range('sro_brin', 0);
+ brin_desummarize_range 
+------------------------
+ 
+(1 row)
+
+SELECT brin_summarize_range('sro_brin', 0);
+ brin_summarize_range 
+----------------------
+                    1
+(1 row)
+
+DROP TABLE sro_tab;
+-- Check with a partitioned table
+CREATE TABLE sro_ptab (a int) PARTITION BY RANGE (a);
+ALTER TABLE sro_ptab OWNER TO regress_sro_user;
+CREATE TABLE sro_part PARTITION OF sro_ptab FOR VALUES FROM (1) TO (10);
+ALTER TABLE sro_part OWNER TO regress_sro_user;
+INSERT INTO sro_ptab VALUES (1), (2), (3);
+CREATE INDEX sro_pidx ON sro_ptab ((sro_ifun(a) + sro_ifun(0)))
+	WHERE sro_ifun(a + 10) > sro_ifun(10);
+REINDEX TABLE sro_ptab;
+REINDEX INDEX CONCURRENTLY sro_pidx;
+SET SESSION AUTHORIZATION regress_sro_user;
+CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS
+	'GRANT regress_priv_group2 TO regress_sro_user';
+CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
+	'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true';
+-- REFRESH of this MV will queue a GRANT at end of transaction
+CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA;
+REFRESH MATERIALIZED VIEW sro_mv;
+ERROR:  cannot create a cursor WITH HOLD within security-restricted operation
+CONTEXT:  SQL function "mv_action" statement 1
+\c -
+REFRESH MATERIALIZED VIEW sro_mv;
+ERROR:  cannot create a cursor WITH HOLD within security-restricted operation
+CONTEXT:  SQL function "mv_action" statement 1
+SET SESSION AUTHORIZATION regress_sro_user;
+-- INSERT to this table will queue a GRANT at end of transaction
+CREATE TABLE sro_trojan_table ();
+CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS
+	'BEGIN PERFORM unwanted_grant(); RETURN NULL; END';
+CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table
+    INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan();
+-- Now, REFRESH will issue such an INSERT, queueing the GRANT
+CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
+	'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true';
+REFRESH MATERIALIZED VIEW sro_mv;
+ERROR:  cannot fire deferred trigger within security-restricted operation
+CONTEXT:  SQL function "mv_action" statement 1
+\c -
+REFRESH MATERIALIZED VIEW sro_mv;
+ERROR:  cannot fire deferred trigger within security-restricted operation
+CONTEXT:  SQL function "mv_action" statement 1
+BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT;
+ERROR:  must have admin option on role "regress_priv_group2"
+CONTEXT:  SQL function "unwanted_grant" statement 1
+SQL statement "SELECT unwanted_grant()"
+PL/pgSQL function sro_trojan() line 1 at PERFORM
+SQL function "mv_action" statement 1
+-- REFRESH MATERIALIZED VIEW CONCURRENTLY use of eval_const_expressions()
+SET SESSION AUTHORIZATION regress_sro_user;
+CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int
+	IMMUTABLE LANGUAGE plpgsql AS $$
+BEGIN
+	PERFORM unwanted_grant();
+	RAISE WARNING 'owned';
+	RETURN 1;
+EXCEPTION WHEN OTHERS THEN
+	RETURN 2;
+END$$;
+CREATE MATERIALIZED VIEW sro_index_mv AS SELECT 1 AS c;
+CREATE UNIQUE INDEX ON sro_index_mv (c) WHERE unwanted_grant_nofail(1) > 0;
+\c -
+REFRESH MATERIALIZED VIEW CONCURRENTLY sro_index_mv;
+REFRESH MATERIALIZED VIEW sro_index_mv;
+DROP OWNED BY regress_sro_user;
+DROP ROLE regress_sro_user;
+-- Admin options
+SET SESSION AUTHORIZATION regress_priv_user4;
+CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS
+	'GRANT regress_priv_group2 TO regress_priv_user5';
+GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION
+SET ROLE regress_priv_group2;
+GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege
+ERROR:  must have admin option on role "regress_priv_group2"
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION
+ERROR:  must have admin option on role "regress_priv_group2"
+SELECT dogrant_ok();			-- ok: SECURITY DEFINER conveys ADMIN
+NOTICE:  role "regress_priv_user5" is already a member of role "regress_priv_group2"
+ dogrant_ok 
+------------
+ 
+(1 row)
+
+SET ROLE regress_priv_group2;
+GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help
+ERROR:  must have admin option on role "regress_priv_group2"
+SET SESSION AUTHORIZATION regress_priv_group2;
+GRANT regress_priv_group2 TO regress_priv_user5; -- ok: a role can self-admin
+NOTICE:  role "regress_priv_user5" is already a member of role "regress_priv_group2"
+CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS
+	'GRANT regress_priv_group2 TO regress_priv_user5';
+SELECT dogrant_fails();			-- fails: no self-admin in SECURITY DEFINER
+ERROR:  must have admin option on role "regress_priv_group2"
+CONTEXT:  SQL function "dogrant_fails" statement 1
+DROP FUNCTION dogrant_fails();
+SET SESSION AUTHORIZATION regress_priv_user4;
+DROP FUNCTION dogrant_ok();
+REVOKE regress_priv_group2 FROM regress_priv_user5;
+-- has_sequence_privilege tests
+\c -
+CREATE SEQUENCE x_seq;
+GRANT USAGE on x_seq to regress_priv_user2;
+SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT');
+ERROR:  "atest1" is not a sequence
+SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT');
+ERROR:  unrecognized privilege type: "INSERT"
+SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT');
+ has_sequence_privilege 
+------------------------
+ f
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT has_sequence_privilege('x_seq', 'USAGE');
+ has_sequence_privilege 
+------------------------
+ t
+(1 row)
+
+-- largeobject privilege tests
+\c -
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT lo_create(1001);
+ lo_create 
+-----------
+      1001
+(1 row)
+
+SELECT lo_create(1002);
+ lo_create 
+-----------
+      1002
+(1 row)
+
+SELECT lo_create(1003);
+ lo_create 
+-----------
+      1003
+(1 row)
+
+SELECT lo_create(1004);
+ lo_create 
+-----------
+      1004
+(1 row)
+
+SELECT lo_create(1005);
+ lo_create 
+-----------
+      1005
+(1 row)
+
+GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC;
+GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2;
+GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2;
+GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2;
+GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION;
+GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC;	-- to be failed
+ERROR:  invalid privilege type INSERT for large object
+GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser;	-- to be failed
+ERROR:  role "nosuchuser" does not exist
+GRANT SELECT, UPDATE ON LARGE OBJECT  999 TO PUBLIC;	-- to be failed
+ERROR:  large object 999 does not exist
+\c -
+SET SESSION AUTHORIZATION regress_priv_user2;
+SELECT lo_create(2001);
+ lo_create 
+-----------
+      2001
+(1 row)
+
+SELECT lo_create(2002);
+ lo_create 
+-----------
+      2002
+(1 row)
+
+SELECT loread(lo_open(1001, x'20000'::int), 32);	-- allowed, for now
+ loread 
+--------
+ \x
+(1 row)
+
+SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd');	-- fail, wrong mode
+ERROR:  large object descriptor 0 was not opened for writing
+SELECT loread(lo_open(1001, x'40000'::int), 32);
+ loread 
+--------
+ \x
+(1 row)
+
+SELECT loread(lo_open(1002, x'40000'::int), 32);	-- to be denied
+ERROR:  permission denied for large object 1002
+SELECT loread(lo_open(1003, x'40000'::int), 32);
+ loread 
+--------
+ \x
+(1 row)
+
+SELECT loread(lo_open(1004, x'40000'::int), 32);
+ loread 
+--------
+ \x
+(1 row)
+
+SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd');
+ lowrite 
+---------
+       4
+(1 row)
+
+SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');	-- to be denied
+ERROR:  permission denied for large object 1002
+SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd');	-- to be denied
+ERROR:  permission denied for large object 1003
+SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd');
+ lowrite 
+---------
+       4
+(1 row)
+
+GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3;
+GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3;	-- to be denied
+ERROR:  large object 1006 does not exist
+REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC;
+GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3;
+SELECT lo_unlink(1001);		-- to be denied
+ERROR:  must be owner of large object 1001
+SELECT lo_unlink(2002);
+ lo_unlink 
+-----------
+         1
+(1 row)
+
+\c -
+-- confirm ACL setting
+SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
+ oid  |     ownername      |                                                            lomacl                                                            
+------+--------------------+------------------------------------------------------------------------------------------------------------------------------
+ 1001 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,=rw/regress_priv_user1}
+ 1002 | regress_priv_user1 | 
+ 1003 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r/regress_priv_user1}
+ 1004 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=rw/regress_priv_user1}
+ 1005 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r*w/regress_priv_user1,regress_priv_user3=r/regress_priv_user2}
+ 2001 | regress_priv_user2 | {regress_priv_user2=rw/regress_priv_user2,regress_priv_user3=rw/regress_priv_user2}
+(6 rows)
+
+SET SESSION AUTHORIZATION regress_priv_user3;
+SELECT loread(lo_open(1001, x'40000'::int), 32);
+   loread   
+------------
+ \x61626364
+(1 row)
+
+SELECT loread(lo_open(1003, x'40000'::int), 32);	-- to be denied
+ERROR:  permission denied for large object 1003
+SELECT loread(lo_open(1005, x'40000'::int), 32);
+ loread 
+--------
+ \x
+(1 row)
+
+SELECT lo_truncate(lo_open(1005, x'20000'::int), 10);	-- to be denied
+ERROR:  permission denied for large object 1005
+SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
+ lo_truncate 
+-------------
+           0
+(1 row)
+
+-- compatibility mode in largeobject permission
+\c -
+SET lo_compat_privileges = false;	-- default setting
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT loread(lo_open(1002, x'40000'::int), 32);	-- to be denied
+ERROR:  permission denied for large object 1002
+SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');	-- to be denied
+ERROR:  permission denied for large object 1002
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);	-- to be denied
+ERROR:  permission denied for large object 1002
+SELECT lo_put(1002, 1, 'abcd');				-- to be denied
+ERROR:  permission denied for large object 1002
+SELECT lo_unlink(1002);					-- to be denied
+ERROR:  must be owner of large object 1002
+SELECT lo_export(1001, '/dev/null');			-- to be denied
+ERROR:  permission denied for function lo_export
+SELECT lo_import('/dev/null');				-- to be denied
+ERROR:  permission denied for function lo_import
+SELECT lo_import('/dev/null', 2003);			-- to be denied
+ERROR:  permission denied for function lo_import
+\c -
+SET lo_compat_privileges = true;	-- compatibility mode
+SET SESSION AUTHORIZATION regress_priv_user4;
+SELECT loread(lo_open(1002, x'40000'::int), 32);
+ loread 
+--------
+ \x
+(1 row)
+
+SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
+ lowrite 
+---------
+       4
+(1 row)
+
+SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
+ lo_truncate 
+-------------
+           0
+(1 row)
+
+SELECT lo_unlink(1002);
+ lo_unlink 
+-----------
+         1
+(1 row)
+
+SELECT lo_export(1001, '/dev/null');			-- to be denied
+ERROR:  permission denied for function lo_export
+-- don't allow unpriv users to access pg_largeobject contents
+\c -
+SELECT * FROM pg_largeobject LIMIT 0;
+ loid | pageno | data 
+------+--------+------
+(0 rows)
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+SELECT * FROM pg_largeobject LIMIT 0;			-- to be denied
+ERROR:  permission denied for table pg_largeobject
+-- test pg_database_owner
+RESET SESSION AUTHORIZATION;
+GRANT pg_database_owner TO regress_priv_user1;
+ERROR:  role "pg_database_owner" cannot have explicit members
+GRANT regress_priv_user1 TO pg_database_owner;
+ERROR:  role "pg_database_owner" cannot be a member of any role
+CREATE TABLE datdba_only ();
+ALTER TABLE datdba_only OWNER TO pg_database_owner;
+REVOKE DELETE ON datdba_only FROM pg_database_owner;
+SELECT
+	pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+	pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+	pg_has_role('regress_priv_user1', 'pg_database_owner',
+				'MEMBER WITH ADMIN OPTION') as admin;
+ priv | mem | admin 
+------+-----+-------
+ f    | f   | f
+(1 row)
+
+BEGIN;
+DO $$BEGIN EXECUTE format(
+	'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$;
+SELECT
+	pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
+	pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
+	pg_has_role('regress_priv_user1', 'pg_database_owner',
+				'MEMBER WITH ADMIN OPTION') as admin;
+ priv | mem | admin 
+------+-----+-------
+ t    | t   | f
+(1 row)
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+TABLE information_schema.enabled_roles ORDER BY role_name COLLATE "C";
+      role_name      
+---------------------
+ pg_database_owner
+ regress_priv_group2
+ regress_priv_user1
+(3 rows)
+
+TABLE information_schema.applicable_roles ORDER BY role_name COLLATE "C";
+       grantee       |      role_name      | is_grantable 
+---------------------+---------------------+--------------
+ regress_priv_group2 | pg_database_owner   | NO
+ regress_priv_user1  | regress_priv_group2 | NO
+(2 rows)
+
+INSERT INTO datdba_only DEFAULT VALUES;
+SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q;
+ERROR:  permission denied for table datdba_only
+SET SESSION AUTHORIZATION regress_priv_user2;
+TABLE information_schema.enabled_roles;
+     role_name      
+--------------------
+ regress_priv_user2
+(1 row)
+
+INSERT INTO datdba_only DEFAULT VALUES;
+ERROR:  permission denied for table datdba_only
+ROLLBACK;
+-- test default ACLs
+\c -
+CREATE SCHEMA testns;
+GRANT ALL ON SCHEMA testns TO regress_priv_user1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+-- placeholder for test with duplicated schema and role names
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public;
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1;
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1;
+DROP TABLE testns.acltest1;
+CREATE TABLE testns.acltest1 (x int);
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- error
+ERROR:  cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS
+--
+-- Testing blanket default grants is very hazardous since it might change
+-- the privileges attached to objects created by concurrent regression tests.
+-- To avoid that, be sure to revoke the privileges again before committing.
+--
+BEGIN;
+ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2;
+CREATE SCHEMA testns2;
+SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes
+ has_schema_privilege 
+----------------------
+ t
+(1 row)
+
+SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes
+ has_schema_privilege 
+----------------------
+ t
+(1 row)
+
+SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2;
+CREATE SCHEMA testns3;
+SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
+CREATE SCHEMA testns4;
+SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes
+ has_schema_privilege 
+----------------------
+ t
+(1 row)
+
+SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes
+ has_schema_privilege 
+----------------------
+ t
+(1 row)
+
+ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2;
+COMMIT;
+-- Test for DROP OWNED BY with shared dependencies.  This is done in a
+-- separate, rollbacked, transaction to avoid any trouble with other
+-- regression sessions.
+BEGIN;
+ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO regress_priv_user2;
+ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
+ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO regress_priv_user2;
+ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO regress_priv_user2;
+ALTER DEFAULT PRIVILEGES GRANT ALL ON TYPES TO regress_priv_user2;
+SELECT count(*) FROM pg_shdepend
+  WHERE deptype = 'a' AND
+        refobjid = 'regress_priv_user2'::regrole AND
+	classid = 'pg_default_acl'::regclass;
+ count 
+-------
+     5
+(1 row)
+
+DROP OWNED BY regress_priv_user2, regress_priv_user2;
+SELECT count(*) FROM pg_shdepend
+  WHERE deptype = 'a' AND
+        refobjid = 'regress_priv_user2'::regrole AND
+	classid = 'pg_default_acl'::regclass;
+ count 
+-------
+     0
+(1 row)
+
+ROLLBACK;
+CREATE SCHEMA testns5;
+SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no
+ has_schema_privilege 
+----------------------
+ f
+(1 row)
+
+SET ROLE regress_priv_user1;
+CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
+SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public;
+DROP FUNCTION testns.foo();
+CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
+DROP AGGREGATE testns.agg1(int);
+CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
+DROP PROCEDURE testns.bar();
+CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
+SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here)
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+DROP FUNCTION testns.foo();
+DROP AGGREGATE testns.agg1(int);
+DROP PROCEDURE testns.bar();
+ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public;
+CREATE DOMAIN testns.priv_testdomain1 AS int;
+SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- no
+ has_type_privilege 
+--------------------
+ f
+(1 row)
+
+ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public;
+DROP DOMAIN testns.priv_testdomain1;
+CREATE DOMAIN testns.priv_testdomain1 AS int;
+SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- yes
+ has_type_privilege 
+--------------------
+ t
+(1 row)
+
+DROP DOMAIN testns.priv_testdomain1;
+RESET ROLE;
+SELECT count(*)
+  FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
+  WHERE nspname = 'testns';
+ count 
+-------
+     3
+(1 row)
+
+DROP SCHEMA testns CASCADE;
+NOTICE:  drop cascades to table testns.acltest1
+DROP SCHEMA testns2 CASCADE;
+DROP SCHEMA testns3 CASCADE;
+DROP SCHEMA testns4 CASCADE;
+DROP SCHEMA testns5 CASCADE;
+SELECT d.*     -- check that entries went away
+  FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
+  WHERE nspname IS NULL AND defaclnamespace != 0;
+ oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl 
+-----+------------+-----------------+---------------+-----------
+(0 rows)
+
+-- Grant on all objects of given type in a schema
+\c -
+CREATE SCHEMA testns;
+CREATE TABLE testns.t1 (f1 int);
+CREATE TABLE testns.t2 (f1 int);
+SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1;
+SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true
+ has_table_privilege 
+---------------------
+ t
+(1 row)
+
+REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1;
+SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false
+ has_table_privilege 
+---------------------
+ f
+(1 row)
+
+CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
+CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4);
+CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql;
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true by default
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true by default
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true by default
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- false
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- false
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- still true, not a function
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC;
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- now false
+ has_function_privilege 
+------------------------
+ f
+(1 row)
+
+GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC;
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true
+ has_function_privilege 
+------------------------
+ t
+(1 row)
+
+DROP SCHEMA testns CASCADE;
+NOTICE:  drop cascades to 5 other objects
+DETAIL:  drop cascades to table testns.t1
+drop cascades to table testns.t2
+drop cascades to function testns.priv_testfunc(integer)
+drop cascades to function testns.priv_testagg(integer)
+drop cascades to function testns.priv_testproc(integer)
+-- Change owner of the schema & and rename of new schema owner
+\c -
+CREATE ROLE regress_schemauser1 superuser login;
+CREATE ROLE regress_schemauser2 superuser login;
+SET SESSION ROLE regress_schemauser1;
+CREATE SCHEMA testns;
+SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
+ nspname |       rolname       
+---------+---------------------
+ testns  | regress_schemauser1
+(1 row)
+
+ALTER SCHEMA testns OWNER TO regress_schemauser2;
+ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed;
+SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
+ nspname |          rolname           
+---------+----------------------------
+ testns  | regress_schemauser_renamed
+(1 row)
+
+set session role regress_schemauser_renamed;
+DROP SCHEMA testns CASCADE;
+-- clean up
+\c -
+DROP ROLE regress_schemauser1;
+DROP ROLE regress_schemauser_renamed;
+-- test that dependent privileges are revoked (or not) properly
+\c -
+set session role regress_priv_user1;
+create table dep_priv_test (a int);
+grant select on dep_priv_test to regress_priv_user2 with grant option;
+grant select on dep_priv_test to regress_priv_user3 with grant option;
+set session role regress_priv_user2;
+grant select on dep_priv_test to regress_priv_user4 with grant option;
+set session role regress_priv_user3;
+grant select on dep_priv_test to regress_priv_user4 with grant option;
+set session role regress_priv_user4;
+grant select on dep_priv_test to regress_priv_user5;
+\dp dep_priv_test
+                                               Access privileges
+ Schema |     Name      | Type  |               Access privileges               | Column privileges | Policies 
+--------+---------------+-------+-----------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+|                   | 
+        |               |       | regress_priv_user2=r*/regress_priv_user1     +|                   | 
+        |               |       | regress_priv_user3=r*/regress_priv_user1     +|                   | 
+        |               |       | regress_priv_user4=r*/regress_priv_user2     +|                   | 
+        |               |       | regress_priv_user4=r*/regress_priv_user3     +|                   | 
+        |               |       | regress_priv_user5=r/regress_priv_user4       |                   | 
+(1 row)
+
+set session role regress_priv_user2;
+revoke select on dep_priv_test from regress_priv_user4 cascade;
+\dp dep_priv_test
+                                               Access privileges
+ Schema |     Name      | Type  |               Access privileges               | Column privileges | Policies 
+--------+---------------+-------+-----------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+|                   | 
+        |               |       | regress_priv_user2=r*/regress_priv_user1     +|                   | 
+        |               |       | regress_priv_user3=r*/regress_priv_user1     +|                   | 
+        |               |       | regress_priv_user4=r*/regress_priv_user3     +|                   | 
+        |               |       | regress_priv_user5=r/regress_priv_user4       |                   | 
+(1 row)
+
+set session role regress_priv_user3;
+revoke select on dep_priv_test from regress_priv_user4 cascade;
+\dp dep_priv_test
+                                               Access privileges
+ Schema |     Name      | Type  |               Access privileges               | Column privileges | Policies 
+--------+---------------+-------+-----------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+|                   | 
+        |               |       | regress_priv_user2=r*/regress_priv_user1     +|                   | 
+        |               |       | regress_priv_user3=r*/regress_priv_user1      |                   | 
+(1 row)
+
+set session role regress_priv_user1;
+drop table dep_priv_test;
+-- clean up
+\c
+drop sequence x_seq;
+DROP AGGREGATE priv_testagg1(int);
+DROP FUNCTION priv_testfunc2(int);
+DROP FUNCTION priv_testfunc4(boolean);
+DROP PROCEDURE priv_testproc1(int);
+DROP VIEW atestv0;
+DROP VIEW atestv1;
+DROP VIEW atestv2;
+-- this should cascade to drop atestv4
+DROP VIEW atestv3 CASCADE;
+NOTICE:  drop cascades to view atestv4
+-- this should complain "does not exist"
+DROP VIEW atestv4;
+ERROR:  view "atestv4" does not exist
+DROP TABLE atest1;
+DROP TABLE atest2;
+DROP TABLE atest3;
+DROP TABLE atest4;
+DROP TABLE atest5;
+DROP TABLE atest6;
+DROP TABLE atestc;
+DROP TABLE atestp1;
+DROP TABLE atestp2;
+SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
+ lo_unlink 
+-----------
+         1
+         1
+         1
+         1
+         1
+(5 rows)
+
+DROP GROUP regress_priv_group1;
+DROP GROUP regress_priv_group2;
+-- these are needed to clean up permissions
+REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1;
+DROP OWNED BY regress_priv_user1;
+DROP USER regress_priv_user1;
+DROP USER regress_priv_user2;
+DROP USER regress_priv_user3;
+DROP USER regress_priv_user4;
+DROP USER regress_priv_user5;
+DROP USER regress_priv_user6;
+DROP USER regress_priv_user7;
+DROP USER regress_priv_user8; -- does not exist
+ERROR:  role "regress_priv_user8" does not exist
+-- permissions with LOCK TABLE
+CREATE USER regress_locktable_user;
+CREATE TABLE lock_table (a int);
+-- LOCK TABLE and SELECT permission
+GRANT SELECT ON lock_table TO regress_locktable_user;
+SET SESSION AUTHORIZATION regress_locktable_user;
+BEGIN;
+LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail
+ERROR:  permission denied for table lock_table
+ROLLBACK;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
+COMMIT;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
+ERROR:  permission denied for table lock_table
+ROLLBACK;
+\c
+REVOKE SELECT ON lock_table FROM regress_locktable_user;
+-- LOCK TABLE and INSERT permission
+GRANT INSERT ON lock_table TO regress_locktable_user;
+SET SESSION AUTHORIZATION regress_locktable_user;
+BEGIN;
+LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
+COMMIT;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
+ERROR:  permission denied for table lock_table
+ROLLBACK;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
+ERROR:  permission denied for table lock_table
+ROLLBACK;
+\c
+REVOKE INSERT ON lock_table FROM regress_locktable_user;
+-- LOCK TABLE and UPDATE permission
+GRANT UPDATE ON lock_table TO regress_locktable_user;
+SET SESSION AUTHORIZATION regress_locktable_user;
+BEGIN;
+LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
+COMMIT;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
+ERROR:  permission denied for table lock_table
+ROLLBACK;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
+COMMIT;
+\c
+REVOKE UPDATE ON lock_table FROM regress_locktable_user;
+-- LOCK TABLE and DELETE permission
+GRANT DELETE ON lock_table TO regress_locktable_user;
+SET SESSION AUTHORIZATION regress_locktable_user;
+BEGIN;
+LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
+COMMIT;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
+ERROR:  permission denied for table lock_table
+ROLLBACK;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
+COMMIT;
+\c
+REVOKE DELETE ON lock_table FROM regress_locktable_user;
+-- LOCK TABLE and TRUNCATE permission
+GRANT TRUNCATE ON lock_table TO regress_locktable_user;
+SET SESSION AUTHORIZATION regress_locktable_user;
+BEGIN;
+LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
+COMMIT;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail
+ERROR:  permission denied for table lock_table
+ROLLBACK;
+BEGIN;
+LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
+COMMIT;
+\c
+REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
+-- clean up
+DROP TABLE lock_table;
+DROP USER regress_locktable_user;