src/test/regress/expected/aoco_privileges.out

-- Create roles. --
CREATE ROLE new_aoco_table_owner;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE user_with_privileges_from_first_owner;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE user_with_privileges_from_second_owner;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE user_with_no_privileges;
NOTICE:  resource queue required -- using default resource queue "pg_default"
-- Create the table. --
CREATE TABLE aoco_privileges_table (a int) WITH (appendonly=true, orientation=column) DISTRIBUTED randomly;
-- Grant privileges to only one user
GRANT ALL ON aoco_privileges_table TO user_with_privileges_from_first_owner;
SELECT has_table_privilege('user_with_privileges_from_first_owner', 'aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 t
(1 row)

-- The original owner should have privileges
SELECT has_table_privilege('aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 t
(1 row)

-- The following users should not have privileges
SELECT has_table_privilege('new_aoco_table_owner', 'aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 f
(1 row)

SELECT has_table_privilege('user_with_privileges_from_second_owner', 'aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 f
(1 row)

SELECT has_table_privilege('user_with_no_privileges', 'aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 f
(1 row)

SET ROLE user_with_no_privileges;
SELECT * FROM aoco_privileges_table;
ERROR:  permission denied for table aoco_privileges_table
RESET ROLE;
-- Now change ownership to the new owner
ALTER TABLE aoco_privileges_table OWNER TO new_aoco_table_owner;
-- The original owner should still have privileges
SELECT has_table_privilege('aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 t
(1 row)

SELECT * FROM aoco_privileges_table;
 a 
---
(0 rows)

-- The people to whom the original owner granted privileges should still have privileges
SELECT has_table_privilege('user_with_privileges_from_first_owner', 'aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 t
(1 row)

SET ROLE user_with_privileges_from_first_owner;
SELECT * FROM aoco_privileges_table;
 a 
---
(0 rows)

-- The new owner of the table should have privileges
SET ROLE new_aoco_table_owner;
SELECT has_table_privilege('aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 t
(1 row)

SELECT * FROM aoco_privileges_table;
 a 
---
(0 rows)

-- The new owner should be able to grant privileges
GRANT ALL ON aoco_privileges_table TO user_with_privileges_from_second_owner;
SELECT has_table_privilege('user_with_privileges_from_second_owner', 'aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 t
(1 row)

SET ROLE user_with_privileges_from_second_owner;
SELECT * FROM aoco_privileges_table;
 a 
---
(0 rows)

-- The no privileges user should have no privileges still
SET ROLE user_with_no_privileges;
SELECT has_table_privilege('aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 f
(1 row)

SELECT * FROM aoco_privileges_table;
ERROR:  permission denied for table aoco_privileges_table
-- Revoke privileges
RESET ROLE;
REVOKE ALL ON aoco_privileges_table FROM user_with_privileges_from_first_owner;
SELECT has_table_privilege('user_with_privileges_from_first_owner', 'aoco_privileges_table', 'SELECT');
 has_table_privilege 
---------------------
 f
(1 row)

-- Clean up
DROP TABLE  aoco_privileges_table;
DROP ROLE new_aoco_table_owner;
DROP ROLE user_with_privileges_from_first_owner;
DROP ROLE user_with_privileges_from_second_owner;
DROP ROLE user_with_no_privileges;