/
githubmirror
/
postgres
Обзор
Документация
Войти
/
githubmirror
/
postgres
Код
Запросы
0
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/create_role.out
288 строк
15 KB
Fujii Masao
doc: Clarify that empty COMMENT string removes the comment.
03 мар 2026, 08:45
03 мар 2026, 08:45
bae42a5
Код
Авторство
О чём код?
-- 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; GRANT CREATE ON DATABASE regression TO regress_role_admin WITH GRANT OPTION; CREATE ROLE regress_role_limited_admin CREATEROLE; CREATE ROLE regress_role_normal; -- fail, CREATEROLE user can't give away role attributes without having them SET SESSION AUTHORIZATION regress_role_limited_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; ERROR: permission denied to create role DETAIL: Only roles with the SUPERUSER attribute may create roles with the SUPERUSER attribute. CREATE ROLE regress_nosuch_replication_bypassrls REPLICATION BYPASSRLS; ERROR: permission denied to create role DETAIL: Only roles with the REPLICATION attribute may create roles with the REPLICATION attribute. CREATE ROLE regress_nosuch_replication REPLICATION; ERROR: permission denied to create role DETAIL: Only roles with the REPLICATION attribute may create roles with the REPLICATION attribute. CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; ERROR: permission denied to create role DETAIL: Only roles with the BYPASSRLS attribute may create roles with the BYPASSRLS attribute. CREATE ROLE regress_nosuch_createdb CREATEDB; ERROR: permission denied to create role DETAIL: Only roles with the CREATEDB attribute may create roles with the CREATEDB attribute. -- ok, can create a role without any special attributes CREATE ROLE regress_role_limited; -- fail, can't give it in any of the restricted attributes ALTER ROLE regress_role_limited SUPERUSER; ERROR: permission denied to alter role DETAIL: Only roles with the SUPERUSER attribute may change the SUPERUSER attribute. ALTER ROLE regress_role_limited REPLICATION; ERROR: permission denied to alter role DETAIL: Only roles with the REPLICATION attribute may change the REPLICATION attribute. ALTER ROLE regress_role_limited CREATEDB; ERROR: permission denied to alter role DETAIL: Only roles with the CREATEDB attribute may change the CREATEDB attribute. ALTER ROLE regress_role_limited BYPASSRLS; ERROR: permission denied to alter role DETAIL: Only roles with the BYPASSRLS attribute may change the BYPASSRLS attribute. DROP ROLE regress_role_limited; -- ok, can give away these role attributes if you have them SET SESSION AUTHORIZATION regress_role_admin; CREATE ROLE regress_replication_bypassrls REPLICATION BYPASSRLS; CREATE ROLE regress_replication REPLICATION; CREATE ROLE regress_bypassrls BYPASSRLS; CREATE ROLE regress_createdb CREATEDB; -- ok, can toggle these role attributes off and on if you have them ALTER ROLE regress_replication NOREPLICATION; ALTER ROLE regress_replication REPLICATION; ALTER ROLE regress_bypassrls NOBYPASSRLS; ALTER ROLE regress_bypassrls BYPASSRLS; ALTER ROLE regress_createdb NOCREATEDB; ALTER ROLE regress_createdb CREATEDB; -- fail, can't toggle SUPERUSER ALTER ROLE regress_createdb SUPERUSER; ERROR: permission denied to alter role DETAIL: Only roles with the SUPERUSER attribute may change the SUPERUSER attribute. ALTER ROLE regress_createdb NOSUPERUSER; ERROR: permission denied to alter role DETAIL: Only roles with the SUPERUSER attribute may change the SUPERUSER attribute. -- ok, having CREATEROLE is enough to create users with these privileges CREATE ROLE regress_createrole CREATEROLE NOINHERIT; GRANT CREATE ON DATABASE regression TO regress_createrole WITH GRANT OPTION; 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: permission denied to grant role "regress_role_super" DETAIL: Only roles with the SUPERUSER attribute may grant roles with the SUPERUSER attribute. -- 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 different role -- attributes, including CREATEROLE CREATE ROLE regress_hasprivs CREATEROLE LOGIN INHERIT CONNECTION LIMIT 5; -- ok, we should be able to modify a role we created COMMENT ON ROLE regress_hasprivs IS 'some comment'; SELECT shobj_description('regress_hasprivs'::regrole, 'pg_authid') IS NOT NULL AS has_comment; has_comment ------------- t (1 row) COMMENT ON ROLE regress_hasprivs IS NULL; SELECT shobj_description('regress_hasprivs'::regrole, 'pg_authid') IS NULL AS no_comment; no_comment ------------ t (1 row) COMMENT ON ROLE regress_hasprivs IS 'add the comment back'; SELECT shobj_description('regress_hasprivs'::regrole, 'pg_authid') IS NOT NULL AS has_comment; has_comment ------------- t (1 row) COMMENT ON ROLE regress_hasprivs IS ''; -- empty string removes the comment, same as NULL SELECT shobj_description('regress_hasprivs'::regrole, 'pg_authid') IS NULL AS no_comment; no_comment ------------ t (1 row) ALTER ROLE regress_hasprivs RENAME TO regress_tenant; ALTER ROLE regress_tenant NOINHERIT NOLOGIN CONNECTION LIMIT 7; -- fail, we should be unable to modify a role we did not create COMMENT ON ROLE regress_role_normal IS 'some comment'; ERROR: permission denied DETAIL: The current user must have the ADMIN option on role "regress_role_normal". ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; ERROR: permission denied to rename role DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on role "regress_role_normal" may rename this role. ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; ERROR: permission denied to alter role DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on role "regress_role_normal" may alter this role. -- 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, can't create objects owned as regress_tenant CREATE SCHEMA regress_tenant_schema AUTHORIZATION regress_tenant; ERROR: must be able to SET ROLE "regress_tenant" -- fail, we don't inherit permissions from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; ERROR: permission denied to reassign objects DETAIL: Only roles with privileges of role "regress_tenant" may reassign objects owned by it. -- ok, create a role with a value for createrole_self_grant SET createrole_self_grant = 'set, inherit'; CREATE ROLE regress_tenant2; GRANT CREATE ON DATABASE regression TO regress_tenant2; -- ok, regress_tenant2 can create objects within the database SET SESSION AUTHORIZATION regress_tenant2; CREATE TABLE tenant2_table (i integer); REVOKE ALL PRIVILEGES ON tenant2_table FROM PUBLIC; -- ok, because we have SET and INHERIT on regress_tenant2 SET SESSION AUTHORIZATION regress_createrole; CREATE SCHEMA regress_tenant2_schema AUTHORIZATION regress_tenant2; ALTER SCHEMA regress_tenant2_schema OWNER TO regress_createrole; ALTER TABLE tenant2_table OWNER TO regress_createrole; ALTER TABLE tenant2_table OWNER TO regress_tenant2; -- with SET but not INHERIT, we can give away objects but not take them REVOKE INHERIT OPTION FOR regress_tenant2 FROM regress_createrole; ALTER SCHEMA regress_tenant2_schema OWNER TO regress_tenant2; ALTER TABLE tenant2_table OWNER TO regress_createrole; ERROR: must be owner of table tenant2_table -- with INHERIT but not SET, we can take objects but not give them away GRANT regress_tenant2 TO regress_createrole WITH INHERIT TRUE, SET FALSE; ALTER TABLE tenant2_table OWNER TO regress_createrole; ALTER TABLE tenant2_table OWNER TO regress_tenant2; ERROR: must be able to SET ROLE "regress_tenant2" DROP TABLE tenant2_table; -- fail, CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; ERROR: permission denied to grant role "pg_read_all_data" DETAIL: Only roles with the ADMIN option on role "pg_read_all_data" may grant this role. CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; ERROR: permission denied to grant role "pg_write_all_data" DETAIL: Only roles with the ADMIN option on role "pg_write_all_data" may grant this role. CREATE ROLE regress_monitor IN ROLE pg_monitor; ERROR: permission denied to grant role "pg_monitor" DETAIL: Only roles with the ADMIN option on role "pg_monitor" may grant this role. CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; ERROR: permission denied to grant role "pg_read_all_settings" DETAIL: Only roles with the ADMIN option on role "pg_read_all_settings" may grant this role. CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; ERROR: permission denied to grant role "pg_read_all_stats" DETAIL: Only roles with the ADMIN option on role "pg_read_all_stats" may grant this role. CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; ERROR: permission denied to grant role "pg_stat_scan_tables" DETAIL: Only roles with the ADMIN option on role "pg_stat_scan_tables" may grant this role. CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; ERROR: permission denied to grant role "pg_read_server_files" DETAIL: Only roles with the ADMIN option on role "pg_read_server_files" may grant this role. CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; ERROR: permission denied to grant role "pg_write_server_files" DETAIL: Only roles with the ADMIN option on role "pg_write_server_files" may grant this role. CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; ERROR: permission denied to grant role "pg_execute_server_program" DETAIL: Only roles with the ADMIN option on role "pg_execute_server_program" may grant this role. CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; ERROR: permission denied to grant role "pg_signal_backend" DETAIL: Only roles with the ADMIN option on role "pg_signal_backend" may grant this role. -- 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, 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; -- must revoke privileges before dropping role REVOKE CREATE ON DATABASE regression FROM regress_createrole CASCADE; -- ok, should be able to drop non-superuser roles we created DROP ROLE regress_replication_bypassrls; DROP ROLE regress_replication; DROP ROLE regress_bypassrls; 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; -- fail, cannot drop ourself, nor superusers or roles we lack ADMIN for DROP ROLE regress_role_super; ERROR: permission denied to drop role DETAIL: Only roles with the SUPERUSER attribute may drop roles with the SUPERUSER attribute. DROP ROLE regress_role_admin; ERROR: current user cannot be dropped DROP ROLE regress_rolecreator; ERROR: permission denied to drop role DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on role "regress_rolecreator" may drop this role. -- ok RESET SESSION AUTHORIZATION; REVOKE CREATE ON DATABASE regression FROM regress_role_admin CASCADE; DROP INDEX tenant_idx; DROP TABLE tenant_table; DROP VIEW tenant_view; DROP SCHEMA regress_tenant2_schema; -- check for duplicated drop DROP ROLE regress_tenant, regress_tenant; DROP ROLE regress_tenant2; DROP ROLE regress_rolecreator; DROP ROLE regress_role_admin; DROP ROLE regress_role_limited_admin; DROP ROLE regress_role_super; DROP ROLE regress_role_normal;