diff options
Diffstat (limited to 'src/test/regress/expected/create_role.out')
-rw-r--r-- | src/test/regress/expected/create_role.out | 145 |
1 files changed, 145 insertions, 0 deletions
diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out new file mode 100644 index 0000000..4e67d72 --- /dev/null +++ b/src/test/regress/expected/create_role.out @@ -0,0 +1,145 @@ +-- ok, superuser can create users with any set of privileges +CREATE ROLE regress_role_super SUPERUSER; +CREATE ROLE regress_role_admin CREATEDB CREATEROLE REPLICATION BYPASSRLS; +-- fail, only superusers can create users with these privileges +SET SESSION AUTHORIZATION regress_role_admin; +CREATE ROLE regress_nosuch_superuser SUPERUSER; +ERROR: must be superuser to create superusers +CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; +ERROR: must be superuser to create replication users +CREATE ROLE regress_nosuch_replication REPLICATION; +ERROR: must be superuser to create replication users +CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; +ERROR: must be superuser to create bypassrls users +-- ok, having CREATEROLE is enough to create users with these privileges +CREATE ROLE regress_createdb CREATEDB; +CREATE ROLE regress_createrole CREATEROLE; +CREATE ROLE regress_login LOGIN; +CREATE ROLE regress_inherit INHERIT; +CREATE ROLE regress_connection_limit CONNECTION LIMIT 5; +CREATE ROLE regress_encrypted_password ENCRYPTED PASSWORD 'foo'; +CREATE ROLE regress_password_null PASSWORD NULL; +-- ok, backwards compatible noise words should be ignored +CREATE ROLE regress_noiseword SYSID 12345; +NOTICE: SYSID can no longer be specified +-- fail, cannot grant membership in superuser role +CREATE ROLE regress_nosuch_super IN ROLE regress_role_super; +ERROR: must be superuser to alter superusers +-- fail, database owner cannot have members +CREATE ROLE regress_nosuch_dbowner IN ROLE pg_database_owner; +ERROR: role "pg_database_owner" cannot have explicit members +-- ok, can grant other users into a role +CREATE ROLE regress_inroles ROLE + regress_role_super, regress_createdb, regress_createrole, regress_login, + regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null; +-- fail, cannot grant a role into itself +CREATE ROLE regress_nosuch_recursive ROLE regress_nosuch_recursive; +ERROR: role "regress_nosuch_recursive" is a member of role "regress_nosuch_recursive" +-- ok, can grant other users into a role with admin option +CREATE ROLE regress_adminroles ADMIN + regress_role_super, regress_createdb, regress_createrole, regress_login, + regress_inherit, regress_connection_limit, regress_encrypted_password, regress_password_null; +-- fail, cannot grant a role into itself with admin option +CREATE ROLE regress_nosuch_admin_recursive ADMIN regress_nosuch_admin_recursive; +ERROR: role "regress_nosuch_admin_recursive" is a member of role "regress_nosuch_admin_recursive" +-- fail, regress_createrole does not have CREATEDB privilege +SET SESSION AUTHORIZATION regress_createrole; +CREATE DATABASE regress_nosuch_db; +ERROR: permission denied to create database +-- ok, regress_createrole can create new roles +CREATE ROLE regress_plainrole; +-- ok, roles with CREATEROLE can create new roles with it +CREATE ROLE regress_rolecreator CREATEROLE; +-- ok, roles with CREATEROLE can create new roles with privilege they lack +CREATE ROLE regress_tenant CREATEDB CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5; +-- ok, regress_tenant can create objects within the database +SET SESSION AUTHORIZATION regress_tenant; +CREATE TABLE tenant_table (i integer); +CREATE INDEX tenant_idx ON tenant_table(i); +CREATE VIEW tenant_view AS SELECT * FROM pg_catalog.pg_class; +REVOKE ALL PRIVILEGES ON tenant_table FROM PUBLIC; +-- fail, these objects belonging to regress_tenant +SET SESSION AUTHORIZATION regress_createrole; +DROP INDEX tenant_idx; +ERROR: must be owner of index tenant_idx +ALTER TABLE tenant_table ADD COLUMN t text; +ERROR: must be owner of table tenant_table +DROP TABLE tenant_table; +ERROR: must be owner of table tenant_table +ALTER VIEW tenant_view OWNER TO regress_role_admin; +ERROR: must be owner of view tenant_view +DROP VIEW tenant_view; +ERROR: must be owner of view tenant_view +-- fail, cannot take ownership of these objects from regress_tenant +REASSIGN OWNED BY regress_tenant TO regress_createrole; +ERROR: permission denied to reassign objects +-- ok, having CREATEROLE is enough to create roles in privileged roles +CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; +CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; +CREATE ROLE regress_monitor IN ROLE pg_monitor; +CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; +CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; +CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; +CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; +CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; +CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; +CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; +-- fail, creation of these roles failed above so they do not now exist +SET SESSION AUTHORIZATION regress_role_admin; +DROP ROLE regress_nosuch_superuser; +ERROR: role "regress_nosuch_superuser" does not exist +DROP ROLE regress_nosuch_replication_bypassrls; +ERROR: role "regress_nosuch_replication_bypassrls" does not exist +DROP ROLE regress_nosuch_replication; +ERROR: role "regress_nosuch_replication" does not exist +DROP ROLE regress_nosuch_bypassrls; +ERROR: role "regress_nosuch_bypassrls" does not exist +DROP ROLE regress_nosuch_super; +ERROR: role "regress_nosuch_super" does not exist +DROP ROLE regress_nosuch_dbowner; +ERROR: role "regress_nosuch_dbowner" does not exist +DROP ROLE regress_nosuch_recursive; +ERROR: role "regress_nosuch_recursive" does not exist +DROP ROLE regress_nosuch_admin_recursive; +ERROR: role "regress_nosuch_admin_recursive" does not exist +DROP ROLE regress_plainrole; +-- ok, should be able to drop non-superuser roles we created +DROP ROLE regress_createdb; +DROP ROLE regress_createrole; +DROP ROLE regress_login; +DROP ROLE regress_inherit; +DROP ROLE regress_connection_limit; +DROP ROLE regress_encrypted_password; +DROP ROLE regress_password_null; +DROP ROLE regress_noiseword; +DROP ROLE regress_inroles; +DROP ROLE regress_adminroles; +DROP ROLE regress_rolecreator; +DROP ROLE regress_read_all_data; +DROP ROLE regress_write_all_data; +DROP ROLE regress_monitor; +DROP ROLE regress_read_all_settings; +DROP ROLE regress_read_all_stats; +DROP ROLE regress_stat_scan_tables; +DROP ROLE regress_read_server_files; +DROP ROLE regress_write_server_files; +DROP ROLE regress_execute_server_program; +DROP ROLE regress_signal_backend; +-- fail, role still owns database objects +DROP ROLE regress_tenant; +ERROR: role "regress_tenant" cannot be dropped because some objects depend on it +DETAIL: owner of table tenant_table +owner of view tenant_view +-- fail, cannot drop ourself nor superusers +DROP ROLE regress_role_super; +ERROR: must be superuser to drop superusers +DROP ROLE regress_role_admin; +ERROR: current user cannot be dropped +-- ok +RESET SESSION AUTHORIZATION; +DROP INDEX tenant_idx; +DROP TABLE tenant_table; +DROP VIEW tenant_view; +DROP ROLE regress_tenant; +DROP ROLE regress_role_admin; +DROP ROLE regress_role_super; |