src/test/regress/expected/role.out - cloudberry - Git at Google

 --
 -- ROLE
 --
 --
 -- Test setting roles per-user and per user in a database.
 --
 -- This also covers an old bug with dispatching GUCs with units in ALTER ROLE
 -- statement (MPP-15479).
 --
 DROP ROLE IF EXISTS role_setting_test_1;
 DROP ROLE IF EXISTS role_setting_test_2;
 DROP ROLE IF EXISTS role_setting_test_3;
 DROP ROLE IF EXISTS role_setting_test_4;
 DROP ROLE IF EXISTS role_setting_test_5;
 DROP ROLE IF EXISTS role_setting_test_6;
 CREATE ROLE role_setting_test_1 NOLOGIN;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 CREATE ROLE role_setting_test_2 NOLOGIN;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 CREATE ROLE role_setting_test_3 NOLOGIN;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 CREATE ROLE role_setting_test_4 NOLOGIN;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 CREATE ROLE role_setting_test_5 NOLOGIN;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 CREATE ROLE role_setting_test_6 NOLOGIN;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 CREATE SCHEMA common_schema;
 /* Alter Role Set, with a GUC with units (statement_mem) and not (search_path) */
 ALTER ROLE role_setting_test_1 SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_1 SET search_path = common_schema;
 ALTER ROLE role_setting_test_2 IN DATABASE regression SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_2 IN DATABASE regression SET search_path = common_schema;
 /* Alter Role Reset */
 ALTER ROLE role_setting_test_3 SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_3 IN DATABASE regression SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_3 SET search_path = common_schema;
 ALTER ROLE role_setting_test_3 IN DATABASE regression SET search_path = common_schema;
 ALTER ROLE role_setting_test_3 RESET statement_mem;
 ALTER ROLE role_setting_test_3 IN DATABASE regression RESET search_path;
 /* Alter Role Reset All */
 ALTER ROLE role_setting_test_5 SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_5 IN DATABASE regression SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_6 SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_6 IN DATABASE regression SET statement_mem TO '150MB';
 ALTER ROLE role_setting_test_5 RESET ALL;
 ALTER ROLE role_setting_test_6 IN DATABASE regression RESET ALL;
 \drds role_setting_test_*
                        List of settings
         Role         |  Database  |         Settings
 ---------------------+------------+---------------------------
  role_setting_test_1 |            | statement_mem=150MB      +
                      |            | search_path=common_schema
  role_setting_test_2 | regression | statement_mem=150MB      +
                      |            | search_path=common_schema
  role_setting_test_3 | regression | statement_mem=150MB
  role_setting_test_3 |            | search_path=common_schema
  role_setting_test_5 | regression | statement_mem=150MB
  role_setting_test_6 |            | statement_mem=150MB
 (6 rows)

 -- Note: Don't drop the test roles, so that they get tested with pg_dump/restore, too,
 -- if you dump the regression database.
 create role superuser;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 create role u1;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 set role superuser;
 create table t1(a int, b int constraint c check (b>=100));
 NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
 HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
 create view t1_view as select * from t1;
 grant all privileges on t1, t1_view to u1;
 set role superuser;
 revoke all privileges on TABLE t1, t1_view FROM u1;
 set role u1;
 select * from t1_view order by 1;
 ERROR:  permission denied for view t1_view
 reset role;
 drop view t1_view;
 drop table t1;
 drop role u1;
 drop role superuser;
 -- Test creating user who has been renamed before.
 CREATE USER jonathan11 WITH PASSWORD 'abc1';
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 CREATE USER jonathan12 WITH PASSWORD 'abc2';
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 ALTER USER jonathan11 RENAME TO jona11;
 ALTER USER jonathan12 RENAME TO jona12;
 DROP USER jona11;
 DROP USER jona12;
 CREATE USER jonathan12 WITH PASSWORD 'abc2';
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 ALTER USER jonathan11 RENAME TO jona11;
 ERROR:  role "jonathan11" does not exist
 ALTER USER jonathan12 RENAME TO jona12;
 CREATE GROUP marketing WITH USER jona11,jona12;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 ERROR:  role "jona11" does not exist
 ALTER GROUP marketing RENAME TO market;
 ERROR:  role "marketing" does not exist
 DROP GROUP market;
 ERROR:  role "market" does not exist
 DROP USER jona11;
 ERROR:  role "jona11" does not exist
 DROP USER jona12;
 -- Test that a non-superuser cannot use ALTER USER RESET ALL to reset
 -- superuser-only GUCs. (A bug that was fixed in PostgreSQL commit
 -- e429448f33.)
 -- First, drop old user, if it was left over from previous run of this test.
 set client_min_messages='warning';
 drop role if exists guctestrole;
 reset client_min_messages;
 -- Create a user with two per-user settings. One is superuser-only,
 -- and another is not.
 create user guctestrole;
 NOTICE:  resource queue required -- using default resource queue "pg_default"
 alter user guctestrole set zero_damaged_pages=off; -- PGC_SUSET
 alter user guctestrole set application_name='test'; -- PGC_USERSET
 select rolconfig from pg_roles where rolname = 'guctestrole';
                    rolconfig
 ------------------------------------------------
  {zero_damaged_pages=off,application_name=test}
 (1 row)

 -- Switch to non-superuser role, and issue ALTER USER RESET ALL.
 -- It should clear the 'application_name' setting, but not the
 -- 'zero_damaged_pages' setting, because it's superuser-only.
 set role guctestrole;
 alter user guctestrole reset all;
 select rolconfig from pg_roles where rolname = 'guctestrole';
         rolconfig
 --------------------------
  {zero_damaged_pages=off}
 (1 row)

 reset role;
 -- Test ALTER USER ALL
 BEGIN;
 ALTER USER ALL SET application_name TO 'alter_user_all_test';
 ALTER USER ALL RESET ALL;
 ROLLBACK;
 -- Various syntax for GRANT/REVOKE with ONLY keywords.
 -- Only cover syntax correctness here, the actual grant behavior
 -- for ONLY is tested in partition.sql
 create table grant_only_syntax1 (a int);
 create table grant_only_syntax2 (a int);
 create role test_role;
 -- These should all work
 grant select on table grant_only_syntax1, grant_only_syntax2 to test_role;
 grant select on table only grant_only_syntax1, grant_only_syntax2 to test_role;
 grant select on table grant_only_syntax1, only grant_only_syntax2 to test_role;
 grant select on table only grant_only_syntax1, only grant_only_syntax2 to test_role;
 grant select on grant_only_syntax1, grant_only_syntax2 to test_role;
 grant select on only grant_only_syntax1, grant_only_syntax2 to test_role;
 grant select on grant_only_syntax1, only grant_only_syntax2 to test_role;
 grant select on only grant_only_syntax1, only grant_only_syntax2 to test_role;
 revoke select on table grant_only_syntax1, grant_only_syntax2 from test_role;
 revoke select on table only grant_only_syntax1, grant_only_syntax2 from test_role;
 NOTICE:  no privileges could be revoked
 revoke select on table grant_only_syntax1, only grant_only_syntax2 from test_role;
 NOTICE:  no privileges could be revoked
 revoke select on table only grant_only_syntax1, only grant_only_syntax2 from test_role;
 NOTICE:  no privileges could be revoked
 revoke select on grant_only_syntax1, grant_only_syntax2 from test_role;
 NOTICE:  no privileges could be revoked
 revoke select on only grant_only_syntax1, grant_only_syntax2 from test_role;
 NOTICE:  no privileges could be revoked
 revoke select on grant_only_syntax1, only grant_only_syntax2 from test_role;
 NOTICE:  no privileges could be revoked
 revoke select on only grant_only_syntax1, only grant_only_syntax2 from test_role;
 NOTICE:  no privileges could be revoked
 drop table grant_only_syntax1;
 drop table grant_only_syntax2;
 drop role test_role;
 -- should be able to create gp_ roles, but not pg_ ones
 drop role if exists gp_regresstestrole;
 create role gp_regresstestrole;
 create role pg_regresstestrole;
 DETAIL:  Role names starting with "pg_" are reserved.
 ERROR:  role name "pg_regresstestrole" is reserved
	--
	-- ROLE
	--
	--
	-- Test setting roles per-user and per user in a database.
	--
	-- This also covers an old bug with dispatching GUCs with units in ALTER ROLE
	-- statement (MPP-15479).
	--
	DROP ROLE IF EXISTS role_setting_test_1;
	DROP ROLE IF EXISTS role_setting_test_2;
	DROP ROLE IF EXISTS role_setting_test_3;
	DROP ROLE IF EXISTS role_setting_test_4;
	DROP ROLE IF EXISTS role_setting_test_5;
	DROP ROLE IF EXISTS role_setting_test_6;
	CREATE ROLE role_setting_test_1 NOLOGIN;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	CREATE ROLE role_setting_test_2 NOLOGIN;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	CREATE ROLE role_setting_test_3 NOLOGIN;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	CREATE ROLE role_setting_test_4 NOLOGIN;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	CREATE ROLE role_setting_test_5 NOLOGIN;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	CREATE ROLE role_setting_test_6 NOLOGIN;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	CREATE SCHEMA common_schema;
	/* Alter Role Set, with a GUC with units (statement_mem) and not (search_path) */
	ALTER ROLE role_setting_test_1 SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_1 SET search_path = common_schema;
	ALTER ROLE role_setting_test_2 IN DATABASE regression SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_2 IN DATABASE regression SET search_path = common_schema;
	/* Alter Role Reset */
	ALTER ROLE role_setting_test_3 SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_3 IN DATABASE regression SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_3 SET search_path = common_schema;
	ALTER ROLE role_setting_test_3 IN DATABASE regression SET search_path = common_schema;
	ALTER ROLE role_setting_test_3 RESET statement_mem;
	ALTER ROLE role_setting_test_3 IN DATABASE regression RESET search_path;
	/* Alter Role Reset All */
	ALTER ROLE role_setting_test_5 SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_5 IN DATABASE regression SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_6 SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_6 IN DATABASE regression SET statement_mem TO '150MB';
	ALTER ROLE role_setting_test_5 RESET ALL;
	ALTER ROLE role_setting_test_6 IN DATABASE regression RESET ALL;
	\drds role_setting_test_*
	List of settings
	Role \| Database \| Settings
	---------------------+------------+---------------------------
	role_setting_test_1 \| \| statement_mem=150MB +
	\| \| search_path=common_schema
	role_setting_test_2 \| regression \| statement_mem=150MB +
	\| \| search_path=common_schema
	role_setting_test_3 \| regression \| statement_mem=150MB
	role_setting_test_3 \| \| search_path=common_schema
	role_setting_test_5 \| regression \| statement_mem=150MB
	role_setting_test_6 \| \| statement_mem=150MB
	(6 rows)

	-- Note: Don't drop the test roles, so that they get tested with pg_dump/restore, too,
	-- if you dump the regression database.
	create role superuser;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	create role u1;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	set role superuser;
	create table t1(a int, b int constraint c check (b>=100));
	NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
	HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
	create view t1_view as select * from t1;
	grant all privileges on t1, t1_view to u1;
	set role superuser;
	revoke all privileges on TABLE t1, t1_view FROM u1;
	set role u1;
	select * from t1_view order by 1;
	ERROR: permission denied for view t1_view
	reset role;
	drop view t1_view;
	drop table t1;
	drop role u1;
	drop role superuser;
	-- Test creating user who has been renamed before.
	CREATE USER jonathan11 WITH PASSWORD 'abc1';
	NOTICE: resource queue required -- using default resource queue "pg_default"
	CREATE USER jonathan12 WITH PASSWORD 'abc2';
	NOTICE: resource queue required -- using default resource queue "pg_default"
	ALTER USER jonathan11 RENAME TO jona11;
	ALTER USER jonathan12 RENAME TO jona12;
	DROP USER jona11;
	DROP USER jona12;
	CREATE USER jonathan12 WITH PASSWORD 'abc2';
	NOTICE: resource queue required -- using default resource queue "pg_default"
	ALTER USER jonathan11 RENAME TO jona11;
	ERROR: role "jonathan11" does not exist
	ALTER USER jonathan12 RENAME TO jona12;
	CREATE GROUP marketing WITH USER jona11,jona12;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	ERROR: role "jona11" does not exist
	ALTER GROUP marketing RENAME TO market;
	ERROR: role "marketing" does not exist
	DROP GROUP market;
	ERROR: role "market" does not exist
	DROP USER jona11;
	ERROR: role "jona11" does not exist
	DROP USER jona12;
	-- Test that a non-superuser cannot use ALTER USER RESET ALL to reset
	-- superuser-only GUCs. (A bug that was fixed in PostgreSQL commit
	-- e429448f33.)
	-- First, drop old user, if it was left over from previous run of this test.
	set client_min_messages='warning';
	drop role if exists guctestrole;
	reset client_min_messages;
	-- Create a user with two per-user settings. One is superuser-only,
	-- and another is not.
	create user guctestrole;
	NOTICE: resource queue required -- using default resource queue "pg_default"
	alter user guctestrole set zero_damaged_pages=off; -- PGC_SUSET
	alter user guctestrole set application_name='test'; -- PGC_USERSET
	select rolconfig from pg_roles where rolname = 'guctestrole';
	rolconfig
	------------------------------------------------
	{zero_damaged_pages=off,application_name=test}
	(1 row)

	-- Switch to non-superuser role, and issue ALTER USER RESET ALL.
	-- It should clear the 'application_name' setting, but not the
	-- 'zero_damaged_pages' setting, because it's superuser-only.
	set role guctestrole;
	alter user guctestrole reset all;
	select rolconfig from pg_roles where rolname = 'guctestrole';
	rolconfig
	--------------------------
	{zero_damaged_pages=off}
	(1 row)

	reset role;
	-- Test ALTER USER ALL
	BEGIN;
	ALTER USER ALL SET application_name TO 'alter_user_all_test';
	ALTER USER ALL RESET ALL;
	ROLLBACK;
	-- Various syntax for GRANT/REVOKE with ONLY keywords.
	-- Only cover syntax correctness here, the actual grant behavior
	-- for ONLY is tested in partition.sql
	create table grant_only_syntax1 (a int);
	create table grant_only_syntax2 (a int);
	create role test_role;
	-- These should all work
	grant select on table grant_only_syntax1, grant_only_syntax2 to test_role;
	grant select on table only grant_only_syntax1, grant_only_syntax2 to test_role;
	grant select on table grant_only_syntax1, only grant_only_syntax2 to test_role;
	grant select on table only grant_only_syntax1, only grant_only_syntax2 to test_role;
	grant select on grant_only_syntax1, grant_only_syntax2 to test_role;
	grant select on only grant_only_syntax1, grant_only_syntax2 to test_role;
	grant select on grant_only_syntax1, only grant_only_syntax2 to test_role;
	grant select on only grant_only_syntax1, only grant_only_syntax2 to test_role;
	revoke select on table grant_only_syntax1, grant_only_syntax2 from test_role;
	revoke select on table only grant_only_syntax1, grant_only_syntax2 from test_role;
	NOTICE: no privileges could be revoked
	revoke select on table grant_only_syntax1, only grant_only_syntax2 from test_role;
	NOTICE: no privileges could be revoked
	revoke select on table only grant_only_syntax1, only grant_only_syntax2 from test_role;
	NOTICE: no privileges could be revoked
	revoke select on grant_only_syntax1, grant_only_syntax2 from test_role;
	NOTICE: no privileges could be revoked
	revoke select on only grant_only_syntax1, grant_only_syntax2 from test_role;
	NOTICE: no privileges could be revoked
	revoke select on grant_only_syntax1, only grant_only_syntax2 from test_role;
	NOTICE: no privileges could be revoked
	revoke select on only grant_only_syntax1, only grant_only_syntax2 from test_role;
	NOTICE: no privileges could be revoked
	drop table grant_only_syntax1;
	drop table grant_only_syntax2;
	drop role test_role;
	-- should be able to create gp_ roles, but not pg_ ones
	drop role if exists gp_regresstestrole;
	create role gp_regresstestrole;
	create role pg_regresstestrole;
	DETAIL: Role names starting with "pg_" are reserved.
	ERROR: role name "pg_regresstestrole" is reserved