src/test/regress/expected/privileges.out - master - githubmirror/postgres

src/test/regress/expected/privileges.out
3 779 строк131 KB
Nathan Bossart
Check for USAGE privilege on the composite type in ALTER TABLE OF.
10 авг 2026, 16:37
10 авг 2026, 16:3753ed5eb
--
-- 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 USER regress_priv_user8;
CREATE USER regress_priv_user9;
CREATE USER regress_priv_user10;
CREATE ROLE regress_priv_role;
-- circular ADMIN OPTION grants should be disallowed
GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION;
GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_user2;
GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_user3;
ERROR:  ADMIN option cannot be granted back to your own grantor
-- need CASCADE to revoke grant or admin option if dependent grants exist
REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
REVOKE regress_priv_user1 FROM regress_priv_user2; -- fail
ERROR:  dependent privileges exist
HINT:  Use CASCADE to revoke them too.
SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole;
       member       | admin_option 
--------------------+--------------
 regress_priv_user2 | t
 regress_priv_user3 | t
(2 rows)

BEGIN;
REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2 CASCADE;
SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole;
       member       | admin_option 
--------------------+--------------
 regress_priv_user2 | f
(1 row)

ROLLBACK;
REVOKE regress_priv_user1 FROM regress_priv_user2 CASCADE;
SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole;
 member | admin_option 
--------+--------------
(0 rows)

-- inferred grantor must be a role with ADMIN OPTION
GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION;
GRANT regress_priv_user2 TO regress_priv_user3;
SET ROLE regress_priv_user3;
GRANT regress_priv_user1 TO regress_priv_user4;
SELECT grantor::regrole FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole and member = 'regress_priv_user4'::regrole;
      grantor       
--------------------
 regress_priv_user2
(1 row)

RESET ROLE;
REVOKE regress_priv_user2 FROM regress_priv_user3;
REVOKE regress_priv_user1 FROM regress_priv_user2 CASCADE;
-- test GRANTED BY with DROP OWNED and REASSIGN OWNED
GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION;
GRANT regress_priv_user1 TO regress_priv_user3 GRANTED BY regress_priv_user2;
DROP ROLE regress_priv_user2; -- fail, dependency
ERROR:  role "regress_priv_user2" cannot be dropped because some objects depend on it
DETAIL:  privileges for membership of role regress_priv_user3 in role regress_priv_user1
REASSIGN OWNED BY regress_priv_user2 TO regress_priv_user4;
DROP ROLE regress_priv_user2; -- still fail, REASSIGN OWNED doesn't help
ERROR:  role "regress_priv_user2" cannot be dropped because some objects depend on it
DETAIL:  privileges for membership of role regress_priv_user3 in role regress_priv_user1
DROP OWNED BY regress_priv_user2;
DROP ROLE regress_priv_user2; -- ok now, DROP OWNED does the job
-- test that removing granted role or grantee role removes dependency
GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION;
GRANT regress_priv_user1 TO regress_priv_user4 GRANTED BY regress_priv_user3;
DROP ROLE regress_priv_user3; -- should fail, dependency
ERROR:  role "regress_priv_user3" cannot be dropped because some objects depend on it
DETAIL:  privileges for membership of role regress_priv_user4 in role regress_priv_user1
DROP ROLE regress_priv_user4; -- ok
DROP ROLE regress_priv_user3; -- ok now
GRANT regress_priv_user1 TO regress_priv_user5 WITH ADMIN OPTION;
GRANT regress_priv_user1 TO regress_priv_user6 GRANTED BY regress_priv_user5;
DROP ROLE regress_priv_user5; -- should fail, dependency
ERROR:  role "regress_priv_user5" cannot be dropped because some objects depend on it
DETAIL:  privileges for membership of role regress_priv_user6 in role regress_priv_user1
DROP ROLE regress_priv_user1, regress_priv_user5; -- ok, despite order
-- recreate the roles we just dropped
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;
GRANT pg_read_all_data TO regress_priv_user6;
GRANT pg_write_all_data TO regress_priv_user7;
GRANT pg_read_all_settings TO regress_priv_user8 WITH ADMIN OPTION;
GRANT regress_priv_user9 TO regress_priv_user8;
SET SESSION AUTHORIZATION regress_priv_user8;
GRANT pg_read_all_settings TO regress_priv_user9 WITH ADMIN OPTION;
SET SESSION AUTHORIZATION regress_priv_user9;
GRANT pg_read_all_settings TO regress_priv_user10;
SET SESSION AUTHORIZATION regress_priv_user8;
REVOKE pg_read_all_settings FROM regress_priv_user10 GRANTED BY regress_priv_user9;
REVOKE ADMIN OPTION FOR pg_read_all_settings FROM regress_priv_user9;
REVOKE pg_read_all_settings FROM regress_priv_user9;
RESET SESSION AUTHORIZATION;
REVOKE regress_priv_user9 FROM regress_priv_user8;
REVOKE ADMIN OPTION FOR pg_read_all_settings FROM regress_priv_user8;
SET SESSION AUTHORIZATION regress_priv_user8;
SET ROLE pg_read_all_settings;
RESET ROLE;
RESET SESSION AUTHORIZATION;
REVOKE SET OPTION FOR pg_read_all_settings FROM regress_priv_user8;
GRANT pg_read_all_stats TO regress_priv_user8 WITH SET FALSE;
SET SESSION AUTHORIZATION regress_priv_user8;
SET ROLE pg_read_all_settings;  -- fail, no SET option any more
ERROR:  permission denied to set role "pg_read_all_settings"
SET ROLE pg_read_all_stats;     -- fail, granted without SET option
ERROR:  permission denied to set role "pg_read_all_stats"
RESET ROLE;
RESET SESSION AUTHORIZATION;
-- test interaction of SET SESSION AUTHORIZATION and SET ROLE,
-- as well as propagation of these settings to parallel workers
GRANT regress_priv_user9 TO regress_priv_user8;
SET SESSION AUTHORIZATION regress_priv_user8;
SET ROLE regress_priv_user9;
SET debug_parallel_query = 0;
SELECT session_user, current_role, current_user, current_setting('role') as role;
    session_user    |    current_role    |    current_user    |        role        
--------------------+--------------------+--------------------+--------------------
 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
(1 row)

SET debug_parallel_query = 1;
SELECT session_user, current_role, current_user, current_setting('role') as role;
    session_user    |    current_role    |    current_user    |        role        
--------------------+--------------------+--------------------+--------------------
 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
(1 row)

BEGIN;
SET SESSION AUTHORIZATION regress_priv_user10;
SET debug_parallel_query = 0;
SELECT session_user, current_role, current_user, current_setting('role') as role;
    session_user     |    current_role     |    current_user     | role 
---------------------+---------------------+---------------------+------
 regress_priv_user10 | regress_priv_user10 | regress_priv_user10 | none
(1 row)

SET debug_parallel_query = 1;
SELECT session_user, current_role, current_user, current_setting('role') as role;
    session_user     |    current_role     |    current_user     | role 
---------------------+---------------------+---------------------+------
 regress_priv_user10 | regress_priv_user10 | regress_priv_user10 | none
(1 row)

ROLLBACK;
SET debug_parallel_query = 0;
SELECT session_user, current_role, current_user, current_setting('role') as role;
    session_user    |    current_role    |    current_user    |        role        
--------------------+--------------------+--------------------+--------------------
 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
(1 row)

SET debug_parallel_query = 1;
SELECT session_user, current_role, current_user, current_setting('role') as role;
    session_user    |    current_role    |    current_user    |        role        
--------------------+--------------------+--------------------+--------------------
 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
(1 row)

RESET SESSION AUTHORIZATION;
-- session_user at this point is installation-dependent
SET debug_parallel_query = 0;
SELECT session_user = current_role as c_r_ok, session_user = current_user as c_u_ok, current_setting('role') as role;
 c_r_ok | c_u_ok | role 
--------+--------+------
 t      | t      | none
(1 row)

SET debug_parallel_query = 1;
SELECT session_user = current_role as c_r_ok, session_user = current_user as c_u_ok, current_setting('role') as role;
 c_r_ok | c_u_ok | role 
--------+--------+------
 t      | t      | none
(1 row)

RESET debug_parallel_query;
REVOKE pg_read_all_settings FROM regress_priv_user8;
DROP USER regress_priv_user10;
DROP USER regress_priv_user9;
DROP USER regress_priv_user8;
CREATE GROUP regress_priv_group1;
CREATE GROUP regress_priv_group2 WITH ADMIN regress_priv_user1 USER regress_priv_user2;
ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
GRANT regress_priv_group2 TO regress_priv_user2 GRANTED BY regress_priv_user1;
SET SESSION AUTHORIZATION regress_priv_user3;
ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2;	-- fail
ERROR:  permission denied to alter role
DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may add or drop members.
ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;	-- fail
ERROR:  permission denied to alter role
DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may add or drop members.
SET SESSION AUTHORIZATION regress_priv_user1;
ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2;
NOTICE:  role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1"
ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2;	-- duplicate
NOTICE:  role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1"
ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted
ERROR:  permission denied to alter role
DETAIL:  To change another role's password, the current user must have the CREATEROLE attribute and the ADMIN option on the role.
RESET SESSION AUTHORIZATION;
ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1;
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 regress_priv_role; -- error, doesn't have ADMIN OPTION
ERROR:  permission denied to grant privileges as role "regress_priv_role"
DETAIL:  The grantor must have the ADMIN option on role "regress_priv_role".
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
ERROR:  role "foo" does not exist
REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY regress_priv_user2; -- warning, noop
WARNING:  role "regress_priv_user1" has not been granted membership in role "regress_priv_role" by role "regress_priv_user2"
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);
SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid, 0);
 pg_get_acl 
------------
 
(1 row)

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;
SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid, 0));
                     unnest                     
------------------------------------------------
 regress_priv_user1=arwdDxtm/regress_priv_user1
 regress_priv_user2=r/regress_priv_user1
 regress_priv_user3=w/regress_priv_user1
 regress_priv_user4=a/regress_priv_user1
 regress_priv_user5=D/regress_priv_user1
(5 rows)

-- Invalid inputs
SELECT pg_get_acl('pg_class'::regclass, 0, 0); -- null
 pg_get_acl 
------------
 
(1 row)

SELECT pg_get_acl(0, 0, 0); -- null
 pg_get_acl 
------------
 
(1 row)

GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5;  -- error
ERROR:  must inherit privileges of role "regress_priv_user5"
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;
-- 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);
-- These should not show any "leak" notices before failing.
EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0;
ERROR:  permission denied for table atest12
EXPLAIN (COSTS OFF) SELECT * FROM atest12v WHERE a >>> 0;
ERROR:  permission denied for view atest12v
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv WHERE a >>> 0;
ERROR:  permission denied for view atest12sbv
-- Now regress_priv_user1 grants access to regress_priv_user2 via the views.
SET SESSION AUTHORIZATION regress_priv_user1;
GRANT SELECT ON atest12v TO PUBLIC;
GRANT SELECT ON atest12sbv TO PUBLIC;
SET SESSION AUTHORIZATION regress_priv_user2;
-- 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;
SAVEPOINT s1;
DELETE FROM atest3; -- ok because grant-level option is unchanged
ROLLBACK TO s1;
RESET SESSION AUTHORIZATION;
GRANT regress_priv_group2 TO regress_priv_user1 WITH INHERIT FALSE;
SET SESSION AUTHORIZATION regress_priv_user1;
DELETE FROM atest3; -- fail
ERROR:  permission denied for table atest3
ROLLBACK TO s1;
RESET SESSION AUTHORIZATION;
REVOKE INHERIT OPTION FOR regress_priv_group2 FROM regress_priv_user1;
SET SESSION AUTHORIZATION regress_priv_user1;
DELETE FROM atest3; -- also fail
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;
SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 1));
                   unnest                   
--------------------------------------------
 regress_priv_user4=r/regress_priv_user1
 regress_priv_user3=arwx/regress_priv_user1
(2 rows)

SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 2));
                 unnest                  
-----------------------------------------
 regress_priv_user4=a/regress_priv_user1
(1 row)

SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 3));
                 unnest                  
-----------------------------------------
 regress_priv_user4=w/regress_priv_user1
(1 row)

SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 4));
 unnest 
--------
(0 rows)

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 column level privileges are enforced for ON CONFLICT ... WHERE
-- Ok. we may select one
INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.one = 1 RETURNING atest5.two;
 two 
-----
   2
(1 row)

-- Error. No select rights on three
INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT WHERE atest5.three = 1 RETURNING atest5.two;
ERROR:  permission denied for table atest5
-- Check that ON CONFLICT ... SELECT FOR UPDATE/SHARE requires an updatable column
SET SESSION AUTHORIZATION regress_priv_user1;
REVOKE UPDATE (three) ON atest5 FROM regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT FOR UPDATE RETURNING atest5.two; -- fails
ERROR:  permission denied for table atest5
SET SESSION AUTHORIZATION regress_priv_user1;
GRANT UPDATE (three) ON atest5 TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
INSERT INTO atest5(two) VALUES (2) ON CONFLICT (two) DO SELECT FOR UPDATE RETURNING atest5.two; -- ok
 two 
-----
   2
(1 row)

-- 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
-- test column privileges with MERGE
SET SESSION AUTHORIZATION regress_priv_user1;
CREATE TABLE mtarget (a int, b text);
CREATE TABLE msource (a int, b text);
INSERT INTO mtarget VALUES (1, 'init1'), (2, 'init2');
INSERT INTO msource VALUES (1, 'source1'), (2, 'source2'), (3, 'source3');
GRANT SELECT (a) ON msource TO regress_priv_user4;
GRANT SELECT (a) ON mtarget TO regress_priv_user4;
GRANT INSERT (a,b) ON mtarget TO regress_priv_user4;
GRANT UPDATE (b) ON mtarget TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
--
-- test source privileges
--
-- fail (no SELECT priv on s.b)
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED THEN
	UPDATE SET b = s.b
WHEN NOT MATCHED THEN
	INSERT VALUES (a, NULL);
ERROR:  permission denied for table msource
-- fail (s.b used in the INSERTed values)
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED THEN
	UPDATE SET b = 'x'
WHEN NOT MATCHED THEN
	INSERT VALUES (a, b);
ERROR:  permission denied for table msource
-- fail (s.b used in the WHEN quals)
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED AND s.b = 'x' THEN
	UPDATE SET b = 'x'
WHEN NOT MATCHED THEN
	INSERT VALUES (a, NULL);
ERROR:  permission denied for table msource
-- this should be ok since only s.a is accessed
BEGIN;
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED THEN
	UPDATE SET b = 'ok'
WHEN NOT MATCHED THEN
	INSERT VALUES (a, NULL);
ROLLBACK;
SET SESSION AUTHORIZATION regress_priv_user1;
GRANT SELECT (b) ON msource TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
-- should now be ok
BEGIN;
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED THEN
	UPDATE SET b = s.b
WHEN NOT MATCHED THEN
	INSERT VALUES (a, b);
ROLLBACK;
--
-- test target privileges
--
-- fail (no SELECT priv on t.b)
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED THEN
	UPDATE SET b = t.b
WHEN NOT MATCHED THEN
	INSERT VALUES (a, NULL);
ERROR:  permission denied for table mtarget
-- fail (no UPDATE on t.a)
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED THEN
	UPDATE SET b = s.b, a = t.a + 1
WHEN NOT MATCHED THEN
	INSERT VALUES (a, b);
ERROR:  permission denied for table mtarget
-- fail (no SELECT on t.b)
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED AND t.b IS NOT NULL THEN
	UPDATE SET b = s.b
WHEN NOT MATCHED THEN
	INSERT VALUES (a, b);
ERROR:  permission denied for table mtarget
-- ok
BEGIN;
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED THEN
	UPDATE SET b = s.b;
ROLLBACK;
-- fail (no DELETE)
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED AND t.b IS NOT NULL THEN
	DELETE;
ERROR:  permission denied for table mtarget
-- grant delete privileges
SET SESSION AUTHORIZATION regress_priv_user1;
GRANT DELETE ON mtarget TO regress_priv_user4;
-- should be ok now
BEGIN;
MERGE INTO mtarget t USING msource s ON t.a = s.a
WHEN MATCHED AND t.b IS NOT NULL THEN
	DELETE;
ROLLBACK;
-- 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 on the range used in FOR PORTION OF
SET SESSION AUTHORIZATION regress_priv_user1;
CREATE TABLE t1 (
  c1 int4range,
  valid_at tsrange,
	CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS)
);
-- UPDATE requires select and update permission on the valid_at column:
GRANT SELECT (c1) ON t1 TO regress_priv_user2;
GRANT UPDATE (c1) ON t1 TO regress_priv_user2;
GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user3;
GRANT UPDATE (c1) ON t1 TO regress_priv_user3;
GRANT SELECT (c1) ON t1 TO regress_priv_user4;
GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user4;
GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user5;
GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user5;
SET SESSION AUTHORIZATION regress_priv_user2;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
ERROR:  permission denied for table t1
SET SESSION AUTHORIZATION regress_priv_user3;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
ERROR:  permission denied for table t1
SET SESSION AUTHORIZATION regress_priv_user4;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
ERROR:  permission denied for table t1
SET SESSION AUTHORIZATION regress_priv_user5;
UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)';
SET SESSION AUTHORIZATION regress_priv_user1;
-- DELETE requires select permission on the valid_at column:
GRANT DELETE ON t1 TO regress_priv_user2;
GRANT DELETE ON t1 TO regress_priv_user3;
SET SESSION AUTHORIZATION regress_priv_user2;
DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01';
ERROR:  permission denied for table t1
SET SESSION AUTHORIZATION regress_priv_user3;
DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01';
SET SESSION AUTHORIZATION regress_priv_user1;
DROP TABLE t1;
-- 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 DOMAIN priv_testdomain4a AS int CHECK (VALUE > ('(0,1)'::priv_testtype1).a);
ERROR:  permission denied for type priv_testtype1
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 FUNCTION priv_testfunc7a(a int DEFAULT ('(0,1)'::priv_testtype1).a) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
ERROR:  permission denied for type priv_testtype1
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 test6a2 (a int, b text);
ALTER TABLE test6a2 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 INDEX ON test9a ((a::priv_testdomain1));
ERROR:  permission denied for type public.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 DOMAIN priv_testdomain5a AS test8a CHECK ((VALUE).a > 0 AND ('(0,1)'::priv_testtype1).a >= 0);
ERROR:  permission denied for type priv_testtype1
CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a);
ERROR:  permission denied for type priv_testdomain1
CREATE VIEW test16a AS SELECT ('(0,1)'::priv_testtype1).a;
ERROR:  permission denied for type priv_testtype1
CREATE TABLE test17a (a int) PARTITION BY RANGE ((a + ('(0,1)'::priv_testtype1).a));
ERROR:  permission denied for type priv_testtype1
CREATE POLICY priv_testpolicy1a ON test9a USING (('(0,1)'::priv_testtype1).a > 0);
ERROR:  permission denied for type priv_testtype1
REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
ERROR:  permission denied for type priv_testtype1
CREATE TABLE test12 (c int DEFAULT 0 CHECK (c > 0 AND '(0,1)'::priv_testtype1 IS NOT NULL));
ERROR:  permission denied for type priv_testtype1
CREATE TABLE test13 (c int DEFAULT ('(0,1)'::priv_testtype1).a);
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 DOMAIN priv_testdomain4b AS int CHECK (VALUE > ('(0,1)'::priv_testtype1).a);
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 FUNCTION priv_testfunc7b(a int DEFAULT ('(0,1)'::priv_testtype1).a) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1);
CREATE TABLE test5b (a int, b priv_testdomain1);
CREATE TABLE test6b OF priv_testtype1;
CREATE TABLE test6b2 (a int, b text);
ALTER TABLE test6b2 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 INDEX ON test9b ((a::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 DOMAIN priv_testdomain5b AS test8b CHECK ((VALUE).a > 0 AND ('(0,1)'::priv_testtype1).a >= 0);
CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a);
CREATE VIEW test16b AS SELECT ('(0,1)'::priv_testtype1).a;
CREATE TABLE test17b (a int) PARTITION BY RANGE ((a + ('(0,1)'::priv_testtype1).a));
CREATE POLICY priv_testpolicy1b ON test9b USING (('(0,1)'::priv_testtype1).a > 0);
REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
WARNING:  no privileges could be revoked for "priv_testtype1"
CREATE TABLE test12 (c int DEFAULT 0 CHECK (c > 0 AND '(0,1)'::priv_testtype1 IS NOT NULL));
CREATE TABLE test13 (c int DEFAULT ('(0,1)'::priv_testtype1).a);
-- new stored expressions require USAGE on types, rebuilds do not
\c -
REVOKE USAGE ON TYPE priv_testtype1 FROM regress_priv_user2;
SET SESSION AUTHORIZATION regress_priv_user2;
ALTER TABLE test12 ALTER COLUMN c TYPE bigint;
ALTER TYPE test8b ALTER ATTRIBUTE a TYPE bigint CASCADE;
ALTER TABLE test12 ALTER COLUMN c SET DEFAULT ('(0,1)'::priv_testtype1).a;
ERROR:  permission denied for type priv_testtype1
CREATE TABLE test14 (LIKE test12 INCLUDING CONSTRAINTS);
ERROR:  permission denied for type priv_testtype1
CREATE TABLE test15 (LIKE test13 INCLUDING DEFAULTS);
ERROR:  permission denied for type priv_testtype1
\c -
DROP AGGREGATE priv_testagg1b(priv_testdomain1);
DROP DOMAIN priv_testdomain2b;
DROP DOMAIN priv_testdomain4b;
DROP DOMAIN priv_testdomain5b;
DROP OPERATOR !! (NONE, priv_testdomain1);
DROP FUNCTION priv_testfunc5b(a priv_testdomain1);
DROP FUNCTION priv_testfunc6b(b int);
DROP FUNCTION priv_testfunc7b(a int);
DROP TABLE test5b;
DROP TABLE test6b;
DROP TABLE test6b2;
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 VIEW test16b;
DROP TABLE test11b;
DROP TABLE test17b;
DROP TABLE test12;
DROP TABLE test13;
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)

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 public.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 public.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 public.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:  permission denied to grant role "regress_priv_group2"
DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
CONTEXT:  SQL function "unwanted_grant" statement 1
SQL statement "SELECT public.unwanted_grant()"
PL/pgSQL function public.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 public.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:  permission denied to grant role "regress_priv_group2"
DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
SET SESSION AUTHORIZATION regress_priv_user1;
GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION
ERROR:  permission denied to grant role "regress_priv_group2"
DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
SELECT dogrant_ok();			-- ok: SECURITY DEFINER conveys ADMIN
NOTICE:  role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4"
 dogrant_ok 
------------
 
(1 row)

SET ROLE regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help
ERROR:  permission denied to grant role "regress_priv_group2"
DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
SET SESSION AUTHORIZATION regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin
ERROR:  permission denied to grant role "regress_priv_group2"
DETAIL:  Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
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)

\c -
-- confirm role with privileges of pg_read_all_data can read large objects
SET SESSION AUTHORIZATION regress_priv_user6;
SELECT loread(lo_open(1002, x'40000'::int), 32);
 loread 
--------
 \x
(1 row)

SELECT lo_get(1002);
 lo_get 
--------
 \x
(1 row)

SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');	-- 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_truncate(lo_open(1002, x'20000'::int), 0);	-- 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
\c -
-- confirm role with privileges of pg_write_all_data can write large objects
GRANT SELECT ON LARGE OBJECT 1002 TO regress_priv_user7;
SET SESSION AUTHORIZATION regress_priv_user7;
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
 lowrite 
---------
       4
(1 row)

SELECT lo_put(1002, 1, 'abcd');
 lo_put 
--------
 
(1 row)

SELECT lo_truncate(lo_open(1002, x'20000'::int), 0);
 lo_truncate 
-------------
           0
(1 row)

SELECT lo_unlink(1002);									-- to be denied
ERROR:  must be owner of large object 1002
-- has_largeobject_privilege function
-- superuser
\c -
SELECT has_largeobject_privilege(1001, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1002, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1003, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1004, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1001, 'UPDATE');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1002, 'UPDATE');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1003, 'UPDATE');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1004, 'UPDATE');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

-- not-existing large object
SELECT has_largeobject_privilege(9999, 'SELECT');	-- NULL
 has_largeobject_privilege 
---------------------------
 
(1 row)

-- non-superuser
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT has_largeobject_privilege(1001, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1002, 'SELECT');	-- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege(1003, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1004, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1001, 'UPDATE');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1002, 'UPDATE');	-- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege(1003, 'UPDATE');	-- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege(1004, 'UPDATE');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege('regress_priv_user3', 1001, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege('regress_priv_user3', 1003, 'SELECT');	-- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege('regress_priv_user3', 1005, 'SELECT');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege('regress_priv_user3', 1005, 'UPDATE');	-- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege('regress_priv_user3', 2001, 'UPDATE');
 has_largeobject_privilege 
---------------------------
 t
(1 row)

-- compatibility mode in largeobject permission
\c -
SET lo_compat_privileges = false;	-- default setting
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT has_largeobject_privilege(1002, 'SELECT'); -- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege(1002, 'UPDATE'); -- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

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 has_largeobject_privilege(1002, 'SELECT'); -- true
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege(1002, 'UPDATE'); -- true
 has_largeobject_privilege 
---------------------------
 t
(1 row)

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
-- pg_signal_backend can't signal superusers
RESET SESSION AUTHORIZATION;
BEGIN;
CREATE OR REPLACE FUNCTION terminate_nothrow(pid int) RETURNS bool
	LANGUAGE plpgsql SECURITY DEFINER SET client_min_messages = error AS $$
BEGIN
	RETURN pg_terminate_backend($1);
EXCEPTION WHEN OTHERS THEN
	RETURN false;
END$$;
ALTER FUNCTION terminate_nothrow OWNER TO pg_signal_backend;
SELECT backend_type FROM pg_stat_activity
WHERE CASE WHEN COALESCE(usesysid, 10) = 10 THEN terminate_nothrow(pid) END;
 backend_type 
--------------
(0 rows)

ROLLBACK;
-- 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
-- Test makeaclitem()
SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole,
	'SELECT', TRUE);  -- single privilege
               makeaclitem                
------------------------------------------
 regress_priv_user1=r*/regress_priv_user2
(1 row)

SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole,
	'SELECT, INSERT,  UPDATE , DELETE  ', FALSE);  -- multiple privileges
                makeaclitem                 
--------------------------------------------
 regress_priv_user1=arwd/regress_priv_user2
(1 row)

SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole,
	'SELECT, fake_privilege', FALSE);  -- error
ERROR:  unrecognized privilege type: "fake_privilege"
-- Test quoting and dequoting of user names in ACLs
CREATE ROLE "regress_""quoted";
SELECT makeaclitem('regress_"quoted'::regrole, 'regress_"quoted'::regrole,
                   'SELECT', TRUE);
               makeaclitem                
------------------------------------------
 "regress_""quoted"=r*/"regress_""quoted"
(1 row)

SELECT '"regress_""quoted"=r*/"regress_""quoted"'::aclitem;
                 aclitem                  
------------------------------------------
 "regress_""quoted"=r*/"regress_""quoted"
(1 row)

SELECT '""=r*/""'::aclitem;  -- used to be misparsed as """"
ERROR:  a name must follow the "/" sign
LINE 1: SELECT '""=r*/""'::aclitem;
               ^
DROP ROLE "regress_""quoted";
-- Test non-throwing aclitem I/O
SELECT pg_input_is_valid('regress_priv_user1=r/regress_priv_user2', 'aclitem');
 pg_input_is_valid 
-------------------
 t
(1 row)

SELECT pg_input_is_valid('regress_priv_user1=r/', 'aclitem');
 pg_input_is_valid 
-------------------
 f
(1 row)

SELECT * FROM pg_input_error_info('regress_priv_user1=r/', 'aclitem');
             message             | detail | hint | sql_error_code 
---------------------------------+--------+------+----------------
 a name must follow the "/" sign |        |      | 22P02
(1 row)

SELECT pg_input_is_valid('regress_priv_user1=r/regress_no_such_user', 'aclitem');
 pg_input_is_valid 
-------------------
 f
(1 row)

SELECT * FROM pg_input_error_info('regress_priv_user1=r/regress_no_such_user', 'aclitem');
                  message                   | detail | hint | sql_error_code 
--------------------------------------------+--------+------+----------------
 role "regress_no_such_user" does not exist |        |      | 42704
(1 row)

SELECT pg_input_is_valid('regress_priv_user1=rY', 'aclitem');
 pg_input_is_valid 
-------------------
 f
(1 row)

SELECT * FROM pg_input_error_info('regress_priv_user1=rY', 'aclitem');
                         message                          | detail | hint | sql_error_code 
----------------------------------------------------------+--------+------+----------------
 invalid mode character: must be one of "arwdDxtXUCTcsAm" |        |      | 22P02
(1 row)

--
-- 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 default privileges on large objects. This is done in a
-- separate, rollbacked, transaction to avoid any trouble with other
-- regression sessions.
--
BEGIN;
SELECT lo_create(1007);
 lo_create 
-----------
      1007
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'SELECT'); -- no
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'UPDATE'); -- no
 has_largeobject_privilege 
---------------------------
 f
(1 row)

ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO regress_priv_user2;
SELECT lo_create(1008);
 lo_create 
-----------
      1008
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'SELECT'); -- yes
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege('regress_priv_user3', 1008, 'SELECT'); -- no
 has_largeobject_privilege 
---------------------------
 f
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'UPDATE'); -- no
 has_largeobject_privilege 
---------------------------
 f
(1 row)

ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2;
SELECT lo_create(1009);
 lo_create 
-----------
      1009
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'SELECT'); -- true
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'UPDATE'); -- true
 has_largeobject_privilege 
---------------------------
 t
(1 row)

ALTER DEFAULT PRIVILEGES REVOKE UPDATE ON LARGE OBJECTS FROM regress_priv_user2;
SELECT lo_create(1010);
 lo_create 
-----------
      1010
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'SELECT'); -- true
 has_largeobject_privilege 
---------------------------
 t
(1 row)

SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'UPDATE'); -- false
 has_largeobject_privilege 
---------------------------
 f
(1 row)

ROLLBACK;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON LARGE OBJECTS TO public; -- error
ERROR:  cannot use IN SCHEMA clause when using GRANT/REVOKE ON LARGE OBJECTS
\c -
-- 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 LARGE OBJECTS 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 
-------
     6
(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=arwdDxtm/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=arwdDxtm/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=arwdDxtm/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;
--
-- Property graphs
--
set session role regress_priv_user1;
create property graph ptg1
	vertex tables (
		atest5 key (four)
			default label properties (four)
			label lttc properties (three as lttck),
		atest1 key (a)
			default label
			label lttc properties (a as lttck),
		atest2 key (col1)
			default label
			label ltv properties (col1 as ltvk));
-- select privileges on property graph as well as table
select * from graph_table (ptg1 match (is atest5) COLUMNS (1 as value)) limit 0; -- ok
 value 
-------
(0 rows)

grant select on property graph ptg1 to regress_priv_user2;
set session role regress_priv_user2;
\dp ptg1
                                            Access privileges
 Schema | Name |      Type      |            Access privileges            | Column privileges | Policies 
--------+------+----------------+-----------------------------------------+-------------------+----------
 public | ptg1 | property graph | regress_priv_user1=r/regress_priv_user1+|                   | 
        |      |                | regress_priv_user2=r/regress_priv_user1 |                   | 
(1 row)

select * from graph_table (ptg1 match (is atest1) COLUMNS (1 as value)) limit 0; -- ok
 value 
-------
(0 rows)

-- select privileges on property graph but not table
select * from graph_table (ptg1 match (is atest5) COLUMNS (1 as value)) limit 0; -- fails
ERROR:  permission denied for table atest5
select * from graph_table (ptg1 match (is lttc) COLUMNS (1 as value)) limit 0; -- fails
ERROR:  permission denied for table atest5
set session role regress_priv_user3;
-- select privileges on table but not property graph
select * from graph_table (ptg1 match (is atest1) COLUMNS (1 as value)) limit 0; -- fails
ERROR:  permission denied for property graph ptg1
-- select privileges on neither
select * from graph_table (ptg1 match (is atest5) COLUMNS (1 as value)) limit 0; -- fails
ERROR:  permission denied for property graph ptg1
-- column privileges
set session role regress_priv_user1;
select * from graph_table (ptg1 match (v is lttc) COLUMNS (v.lttck)) limit 0; -- ok
 lttck 
-------
(0 rows)

grant select on property graph ptg1 to regress_priv_user4;
set session role regress_priv_user4;
select * from graph_table (ptg1 match (a is atest5) COLUMNS (a.four)) limit 0; -- ok
 four 
------
(0 rows)

select * from graph_table (ptg1 match (v is lttc) COLUMNS (v.lttck)) limit 0; -- fail
ERROR:  permission denied for table atest5
-- access property graph through security definer view
set session role regress_priv_user4;
create view atpgv1 as select * from graph_table (ptg1 match (is atest1) COLUMNS (1 as value)) limit 0;
grant select on atpgv1 to regress_priv_user3;
select * from atpgv1; -- ok
 value 
-------
(0 rows)

set session role regress_priv_user3;
select * from atpgv1; -- ok
 value 
-------
(0 rows)

set session role regress_priv_user4;
create view atpgv2 as select * from graph_table (ptg1 match (v is ltv) COLUMNS (v.ltvk)) limit 0;
-- though the session user is the owner of the view and also has access to the
-- property graph, it does not have access to a table referenced in the graph
-- pattern
select * from atpgv2; -- fail
ERROR:  permission denied for table atest2
grant select on atpgv2 to regress_priv_user2;
-- The user who otherwise does not have access to the property graph, gets
-- access to it through a security definer view and uses it successfully since
-- it has access to the tables referenced in the graph pattern.
set session role regress_priv_user2;
select * from atpgv2; -- ok
 ltvk 
------
(0 rows)

-- clean up
\c
drop sequence x_seq;
drop view atpgv1;
drop view atpgv2;
drop property graph ptg1;
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
-- leave some default ACLs for pg_upgrade's dump-restore test input.
ALTER DEFAULT PRIVILEGES FOR ROLE pg_signal_backend
	REVOKE USAGE ON TYPES FROM pg_signal_backend;
ALTER DEFAULT PRIVILEGES FOR ROLE pg_read_all_settings
	REVOKE USAGE ON TYPES FROM pg_read_all_settings;
-- 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 ACCESS SHARE MODE; -- should pass
COMMIT;
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 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 ACCESS SHARE MODE; -- should pass
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE 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 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 ACCESS SHARE MODE; -- should pass
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
COMMIT;
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 ACCESS SHARE MODE; -- should pass
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
COMMIT;
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 ACCESS SHARE MODE; -- should pass
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
COMMIT;
\c
REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
-- LOCK TABLE and MAINTAIN permission
GRANT MAINTAIN ON lock_table TO regress_locktable_user;
SET SESSION AUTHORIZATION regress_locktable_user;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
COMMIT;
\c
REVOKE MAINTAIN ON lock_table FROM regress_locktable_user;
-- clean up
DROP TABLE lock_table;
DROP USER regress_locktable_user;
-- test to check privileges of system views pg_shmem_allocations,
-- pg_shmem_allocations_numa, pg_dsm_registry_allocations, and
-- pg_backend_memory_contexts.
-- switch to superuser
\c -
CREATE ROLE regress_readallstats;
SELECT has_table_privilege('regress_readallstats','pg_aios','SELECT'); -- no
 has_table_privilege 
---------------------
 f
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- no
 has_table_privilege 
---------------------
 f
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- no
 has_table_privilege 
---------------------
 f
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations_numa','SELECT'); -- no
 has_table_privilege 
---------------------
 f
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_dsm_registry_allocations','SELECT'); -- no
 has_table_privilege 
---------------------
 f
(1 row)

GRANT pg_read_all_stats TO regress_readallstats;
SELECT has_table_privilege('regress_readallstats','pg_aios','SELECT'); -- yes
 has_table_privilege 
---------------------
 t
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- yes
 has_table_privilege 
---------------------
 t
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- yes
 has_table_privilege 
---------------------
 t
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations_numa','SELECT'); -- yes
 has_table_privilege 
---------------------
 t
(1 row)

SELECT has_table_privilege('regress_readallstats','pg_dsm_registry_allocations','SELECT'); -- yes
 has_table_privilege 
---------------------
 t
(1 row)

-- run query to ensure that functions within views can be executed
SET ROLE regress_readallstats;
SELECT COUNT(*) >= 0 AS ok FROM pg_aios;
 ok 
----
 t
(1 row)

SELECT COUNT(*) >= 0 AS ok FROM pg_backend_memory_contexts;
 ok 
----
 t
(1 row)

SELECT COUNT(*) >= 0 AS ok FROM pg_shmem_allocations;
 ok 
----
 t
(1 row)

RESET ROLE;
-- clean up
DROP ROLE regress_readallstats;
-- test role grantor machinery
CREATE ROLE regress_group;
CREATE ROLE regress_group_direct_manager;
CREATE ROLE regress_group_indirect_manager;
CREATE ROLE regress_group_member;
GRANT regress_group TO regress_group_direct_manager WITH INHERIT FALSE, ADMIN TRUE;
GRANT regress_group_direct_manager TO regress_group_indirect_manager;
SET SESSION AUTHORIZATION regress_group_direct_manager;
GRANT regress_group TO regress_group_member;
SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE grantor::regrole::text END FROM pg_auth_members WHERE roleid = 'regress_group'::regrole ORDER BY 1, 2;
            member            |           grantor            
------------------------------+------------------------------
 regress_group_direct_manager | BOOTSTRAP SUPERUSER
 regress_group_member         | regress_group_direct_manager
(2 rows)

REVOKE regress_group FROM regress_group_member;
SET SESSION AUTHORIZATION regress_group_indirect_manager;
GRANT regress_group TO regress_group_member;
SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE grantor::regrole::text END FROM pg_auth_members WHERE roleid = 'regress_group'::regrole ORDER BY 1, 2;
            member            |           grantor            
------------------------------+------------------------------
 regress_group_direct_manager | BOOTSTRAP SUPERUSER
 regress_group_member         | regress_group_direct_manager
(2 rows)

REVOKE regress_group FROM regress_group_member;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_group;
DROP ROLE regress_group_direct_manager;
DROP ROLE regress_group_indirect_manager;
DROP ROLE regress_group_member;
-- test SET and INHERIT options with object ownership changes
CREATE ROLE regress_roleoption_protagonist;
CREATE ROLE regress_roleoption_donor;
CREATE ROLE regress_roleoption_recipient;
CREATE SCHEMA regress_roleoption;
GRANT CREATE, USAGE ON SCHEMA regress_roleoption TO PUBLIC;
GRANT regress_roleoption_donor TO regress_roleoption_protagonist WITH INHERIT TRUE, SET FALSE;
GRANT regress_roleoption_recipient TO regress_roleoption_protagonist WITH INHERIT FALSE, SET TRUE;
SET SESSION AUTHORIZATION regress_roleoption_protagonist;
CREATE TABLE regress_roleoption.t1 (a int);
CREATE TABLE regress_roleoption.t2 (a int);
SET SESSION AUTHORIZATION regress_roleoption_donor;
CREATE TABLE regress_roleoption.t3 (a int);
SET SESSION AUTHORIZATION regress_roleoption_recipient;
CREATE TABLE regress_roleoption.t4 (a int);
SET SESSION AUTHORIZATION regress_roleoption_protagonist;
ALTER TABLE regress_roleoption.t1 OWNER TO regress_roleoption_donor; -- fails, can't be come donor
ERROR:  must be able to SET ROLE "regress_roleoption_donor"
ALTER TABLE regress_roleoption.t2 OWNER TO regress_roleoption_recipient; -- works
ALTER TABLE regress_roleoption.t3 OWNER TO regress_roleoption_protagonist; -- works
ALTER TABLE regress_roleoption.t4 OWNER TO regress_roleoption_protagonist; -- fails, we don't inherit from recipient
ERROR:  must be owner of table t4
RESET SESSION AUTHORIZATION;
DROP TABLE regress_roleoption.t1;
DROP TABLE regress_roleoption.t2;
DROP TABLE regress_roleoption.t3;
DROP TABLE regress_roleoption.t4;
DROP SCHEMA regress_roleoption;
DROP ROLE regress_roleoption_protagonist;
DROP ROLE regress_roleoption_donor;
DROP ROLE regress_roleoption_recipient;
-- MAINTAIN
CREATE ROLE regress_no_maintain;
CREATE ROLE regress_maintain;
CREATE ROLE regress_maintain_all IN ROLE pg_maintain;
CREATE TABLE maintain_test (a INT);
CREATE INDEX ON maintain_test (a);
GRANT MAINTAIN ON maintain_test TO regress_maintain;
CREATE MATERIALIZED VIEW refresh_test AS SELECT 1;
GRANT MAINTAIN ON refresh_test TO regress_maintain;
CREATE SCHEMA reindex_test;
-- negative tests; should fail
SET ROLE regress_no_maintain;
VACUUM maintain_test;
WARNING:  permission denied to vacuum "maintain_test", skipping it
ANALYZE maintain_test;
WARNING:  permission denied to analyze "maintain_test", skipping it
VACUUM (ANALYZE) maintain_test;
WARNING:  permission denied to vacuum "maintain_test", skipping it
CLUSTER maintain_test USING maintain_test_a_idx;
ERROR:  permission denied for table maintain_test
REFRESH MATERIALIZED VIEW refresh_test;
ERROR:  permission denied for materialized view refresh_test
REINDEX TABLE maintain_test;
ERROR:  permission denied for table maintain_test
REINDEX INDEX maintain_test_a_idx;
ERROR:  permission denied for index maintain_test_a_idx
REINDEX SCHEMA reindex_test;
ERROR:  must be owner of schema reindex_test
RESET ROLE;
SET ROLE regress_maintain;
VACUUM maintain_test;
ANALYZE maintain_test;
VACUUM (ANALYZE) maintain_test;
CLUSTER maintain_test USING maintain_test_a_idx;
REFRESH MATERIALIZED VIEW refresh_test;
REINDEX TABLE maintain_test;
REINDEX INDEX maintain_test_a_idx;
REINDEX SCHEMA reindex_test;
ERROR:  must be owner of schema reindex_test
RESET ROLE;
SET ROLE regress_maintain_all;
VACUUM maintain_test;
ANALYZE maintain_test;
VACUUM (ANALYZE) maintain_test;
CLUSTER maintain_test USING maintain_test_a_idx;
REFRESH MATERIALIZED VIEW refresh_test;
REINDEX TABLE maintain_test;
REINDEX INDEX maintain_test_a_idx;
REINDEX SCHEMA reindex_test;
RESET ROLE;
DROP TABLE maintain_test;
DROP MATERIALIZED VIEW refresh_test;
DROP SCHEMA reindex_test;
DROP ROLE regress_no_maintain;
DROP ROLE regress_maintain;
DROP ROLE regress_maintain_all;
-- grantor selection
CREATE ROLE regress_grantor1;
CREATE ROLE regress_grantor2 ROLE regress_grantor1;
CREATE ROLE regress_grantor3;
CREATE TABLE grantor_test1 ();
CREATE TABLE grantor_test2 ();
CREATE TABLE grantor_test3 ();
GRANT SELECT ON grantor_test2 TO regress_grantor1 WITH GRANT OPTION;
GRANT SELECT, UPDATE ON grantor_test3 TO regress_grantor2 WITH GRANT OPTION;
SET ROLE regress_grantor1;
GRANT SELECT, UPDATE ON grantor_test1 TO regress_grantor3;
ERROR:  permission denied for table grantor_test1
GRANT SELECT, UPDATE ON grantor_test2 TO regress_grantor3;
WARNING:  not all privileges were granted for "grantor_test2"
GRANT SELECT, UPDATE ON grantor_test3 TO regress_grantor3;
RESET ROLE;
SELECT * FROM information_schema.table_privileges t
	WHERE grantor LIKE 'regress_grantor%' ORDER BY ROW(t.*);
     grantor      |     grantee      | table_catalog | table_schema |  table_name   | privilege_type | is_grantable | with_hierarchy 
------------------+------------------+---------------+--------------+---------------+----------------+--------------+----------------
 regress_grantor1 | regress_grantor3 | regression    | public       | grantor_test2 | SELECT         | NO           | YES
 regress_grantor2 | regress_grantor3 | regression    | public       | grantor_test3 | SELECT         | NO           | YES
 regress_grantor2 | regress_grantor3 | regression    | public       | grantor_test3 | UPDATE         | NO           | NO
(3 rows)

DROP TABLE grantor_test1, grantor_test2, grantor_test3;
DROP ROLE regress_grantor1, regress_grantor2, regress_grantor3;
-- GRANTED BY
CREATE ROLE regress_grantor1;
CREATE ROLE regress_grantor2 ROLE regress_grantor1;
CREATE ROLE regress_grantor3 ROLE regress_grantor1;
CREATE ROLE regress_grantor4 ROLE regress_grantor1;
CREATE ROLE regress_grantor5;
CREATE TABLE grantor_test ();
GRANT SELECT ON grantor_test TO regress_grantor2 WITH GRANT OPTION;
GRANT UPDATE ON grantor_test TO regress_grantor3 WITH GRANT OPTION;
GRANT SELECT, UPDATE ON grantor_test TO regress_grantor4 WITH GRANT OPTION;
SET ROLE regress_grantor1;
GRANT SELECT, UPDATE ON grantor_test TO regress_grantor5;
SELECT * FROM information_schema.table_privileges t
    WHERE grantor LIKE 'regress_grantor%' ORDER BY ROW(t.*);
     grantor      |     grantee      | table_catalog | table_schema |  table_name  | privilege_type | is_grantable | with_hierarchy 
------------------+------------------+---------------+--------------+--------------+----------------+--------------+----------------
 regress_grantor4 | regress_grantor5 | regression    | public       | grantor_test | SELECT         | NO           | YES
 regress_grantor4 | regress_grantor5 | regression    | public       | grantor_test | UPDATE         | NO           | NO
(2 rows)

REVOKE SELECT, UPDATE ON grantor_test FROM regress_grantor5;
GRANT SELECT, UPDATE ON grantor_test TO regress_grantor5 GRANTED BY regress_grantor2;
WARNING:  not all privileges were granted for "grantor_test"
GRANT SELECT, UPDATE ON grantor_test TO regress_grantor5 GRANTED BY regress_grantor3;
WARNING:  not all privileges were granted for "grantor_test"
SELECT * FROM information_schema.table_privileges t
    WHERE grantor LIKE 'regress_grantor%' ORDER BY ROW(t.*);
     grantor      |     grantee      | table_catalog | table_schema |  table_name  | privilege_type | is_grantable | with_hierarchy 
------------------+------------------+---------------+--------------+--------------+----------------+--------------+----------------
 regress_grantor2 | regress_grantor5 | regression    | public       | grantor_test | SELECT         | NO           | YES
 regress_grantor3 | regress_grantor5 | regression    | public       | grantor_test | UPDATE         | NO           | NO
(2 rows)

REVOKE SELECT, UPDATE ON grantor_test FROM regress_grantor5 GRANTED BY regress_grantor2;
WARNING:  not all privileges could be revoked for "grantor_test"
WARNING:  not all privileges could be revoked for column "tableoid" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "cmax" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "xmax" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "cmin" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "xmin" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "ctid" of relation "grantor_test"
REVOKE SELECT, UPDATE ON grantor_test FROM regress_grantor5 GRANTED BY regress_grantor3;
WARNING:  not all privileges could be revoked for "grantor_test"
WARNING:  not all privileges could be revoked for column "tableoid" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "cmax" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "xmax" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "cmin" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "xmin" of relation "grantor_test"
WARNING:  not all privileges could be revoked for column "ctid" of relation "grantor_test"
SELECT * FROM information_schema.table_privileges t
    WHERE grantor LIKE 'regress_grantor%' ORDER BY ROW(t.*);
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

RESET ROLE;
DROP TABLE grantor_test;
DROP ROLE regress_grantor1, regress_grantor2, regress_grantor3, regress_grantor4, regress_grantor5;