diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/catalog/objectaddress.c | 10 | ||||
| -rw-r--r-- | src/backend/commands/user.c | 100 | ||||
| -rw-r--r-- | src/test/modules/dummy_seclabel/expected/dummy_seclabel.out | 17 | ||||
| -rw-r--r-- | src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql | 13 | ||||
| -rw-r--r-- | src/test/regress/expected/create_role.out | 53 | ||||
| -rw-r--r-- | src/test/regress/sql/create_role.sql | 38 |
6 files changed, 156 insertions, 75 deletions
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 1367b5e7c56..25c50d66fdc 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -2538,7 +2538,9 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, /* * We treat roles as being "owned" by those with CREATEROLE priv, - * except that superusers are only owned by superusers. + * provided that they also have admin option on the role. + * + * However, superusers are only owned by superusers. */ if (superuser_arg(address.objectId)) { @@ -2553,6 +2555,12 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must have CREATEROLE privilege"))); + if (!is_admin_of_role(roleid, address.objectId)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin option on role \"%s\"", + GetUserNameFromId(address.objectId, + true)))); } break; case OBJECT_TSPARSER: diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index bc2b95ac814..1ae2d0a66fb 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -520,6 +520,42 @@ CreateRole(ParseState *pstate, CreateRoleStmt *stmt) } /* + * If the current user isn't a superuser, make them an admin of the new + * role so that they can administer the new object they just created. + * Superusers will be able to do that anyway. + * + * The grantor of record for this implicit grant is the bootstrap + * superuser, which means that the CREATEROLE user cannot revoke the + * grant. They can however grant the created role back to themselves + * with different options, since they enjoy ADMIN OPTION on it. + */ + if (!superuser()) + { + RoleSpec *current_role = makeNode(RoleSpec); + GrantRoleOptions poptself; + + current_role->roletype = ROLESPEC_CURRENT_ROLE; + current_role->location = -1; + + poptself.specified = GRANT_ROLE_SPECIFIED_ADMIN + | GRANT_ROLE_SPECIFIED_INHERIT + | GRANT_ROLE_SPECIFIED_SET; + poptself.admin = true; + poptself.inherit = false; + poptself.set = false; + + AddRoleMems(BOOTSTRAP_SUPERUSERID, stmt->role, roleid, + list_make1(current_role), list_make1_oid(GetUserId()), + BOOTSTRAP_SUPERUSERID, &poptself); + + /* + * We must make the implicit grant visible to the code below, else + * the additional grants will fail. + */ + CommandCounterIncrement(); + } + + /* * Add the specified members to this new role. adminmembers get the admin * option, rolemembers don't. * @@ -694,9 +730,7 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) /* * To mess with a superuser or replication role in any way you gotta be * superuser. We also insist on superuser to change the BYPASSRLS - * property. Otherwise, if you don't have createrole, you're only allowed - * to (1) change your own password or (2) add members to a role for which - * you have ADMIN OPTION. + * property. */ if (authform->rolsuper || dissuper) { @@ -719,29 +753,35 @@ AlterRole(ParseState *pstate, AlterRoleStmt *stmt) (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to change bypassrls attribute"))); } - else if (!have_createrole_privilege()) + + /* + * Most changes to a role require that you both have CREATEROLE privileges + * and also ADMIN OPTION on the role. + */ + if (!have_createrole_privilege() || + !is_admin_of_role(GetUserId(), roleid)) { - /* things you certainly can't do without CREATEROLE */ + /* things an unprivileged user certainly can't do */ if (dinherit || dcreaterole || dcreatedb || dcanlogin || dconnlimit || dvalidUntil) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied"))); - /* without CREATEROLE, can only change your own password */ + /* an unprivileged user can change their own password */ if (dpassword && roleid != currentUserId) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must have CREATEROLE privilege to change another user's password"))); - - /* without CREATEROLE, can only add members to roles you admin */ - if (drolemembers && !is_admin_of_role(currentUserId, roleid)) - ereport(ERROR, - (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), - errmsg("must have admin option on role \"%s\" to add members", - rolename))); } + /* To add members to a role, you need ADMIN OPTION. */ + if (drolemembers && !is_admin_of_role(currentUserId, roleid)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin option on role \"%s\" to add members", + rolename))); + /* Convert validuntil to internal form */ if (dvalidUntil) { @@ -935,8 +975,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) shdepLockAndCheckObject(AuthIdRelationId, roleid); /* - * To mess with a superuser you gotta be superuser; else you need - * createrole, or just want to change your own settings + * To mess with a superuser you gotta be superuser; otherwise you + * need CREATEROLE plus admin option on the target role; unless you're + * just trying to change your own settings */ if (roleform->rolsuper) { @@ -947,7 +988,9 @@ AlterRoleSet(AlterRoleSetStmt *stmt) } else { - if (!have_createrole_privilege() && roleid != GetUserId()) + if ((!have_createrole_privilege() || + !is_admin_of_role(GetUserId(), roleid)) + && roleid != GetUserId()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied"))); @@ -1067,13 +1110,18 @@ DropRole(DropRoleStmt *stmt) /* * For safety's sake, we allow createrole holders to drop ordinary - * roles but not superuser roles. This is mainly to avoid the - * scenario where you accidentally drop the last superuser. + * roles but not superuser roles, and only if they also have ADMIN + * OPTION. */ if (roleform->rolsuper && !superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to drop superusers"))); + if (!is_admin_of_role(GetUserId(), roleid)) + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("must have admin option on role \"%s\"", + role))); /* DROP hook for the role being removed */ InvokeObjectDropHook(AuthIdRelationId, roleid, 0); @@ -1312,7 +1360,8 @@ RenameRole(const char *oldname, const char *newname) errmsg("role \"%s\" already exists", newname))); /* - * createrole is enough privilege unless you want to mess with a superuser + * Only superusers can mess with superusers. Otherwise, a user with + * CREATEROLE can rename a role for which they have ADMIN OPTION. */ if (((Form_pg_authid) GETSTRUCT(oldtuple))->rolsuper) { @@ -1323,7 +1372,8 @@ RenameRole(const char *oldname, const char *newname) } else { - if (!have_createrole_privilege()) + if (!have_createrole_privilege() || + !is_admin_of_role(GetUserId(), roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("permission denied to rename role"))); @@ -2023,11 +2073,9 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, else { /* - * Otherwise, must have createrole or admin option on the role to be - * changed. + * Otherwise, must have admin option on the role to be changed. */ - if (!has_createrole_privilege(currentUserId) && - !is_admin_of_role(currentUserId, roleid)) + if (!is_admin_of_role(currentUserId, roleid)) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must have admin option on role \"%s\"", @@ -2049,7 +2097,7 @@ check_role_membership_authorization(Oid currentUserId, Oid roleid, * be passed as InvalidOid, and this function will infer the user to be * recorded as the grantor. In many cases, this will be the current user, but * things get more complicated when the current user doesn't possess ADMIN - * OPTION on the role but rather relies on having CREATEROLE privileges, or + * OPTION on the role but rather relies on having SUPERUSER privileges, or * on inheriting the privileges of a role which does have ADMIN OPTION. See * below for details. * @@ -2075,7 +2123,7 @@ check_role_grantor(Oid currentUserId, Oid roleid, Oid grantorId, bool is_grant) * not depend on any other existing grants, so always default to this * interpretation when possible. */ - if (has_createrole_privilege(currentUserId)) + if (superuser_arg(currentUserId)) return BOOTSTRAP_SUPERUSERID; /* diff --git a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out index b2d898a7d1a..c57d4fd2df0 100644 --- a/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out +++ b/src/test/modules/dummy_seclabel/expected/dummy_seclabel.out @@ -6,9 +6,11 @@ CREATE EXTENSION dummy_seclabel; SET client_min_messages TO 'warning'; DROP ROLE IF EXISTS regress_dummy_seclabel_user1; DROP ROLE IF EXISTS regress_dummy_seclabel_user2; +DROP ROLE IF EXISTS regress_dummy_seclabel_user3; RESET client_min_messages; CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE; CREATE USER regress_dummy_seclabel_user2; +CREATE USER regress_dummy_seclabel_user3; CREATE TABLE dummy_seclabel_tbl1 (a int, b text); CREATE TABLE dummy_seclabel_tbl2 (x int, y text); CREATE VIEW dummy_seclabel_view1 AS SELECT * FROM dummy_seclabel_tbl2; @@ -16,6 +18,8 @@ CREATE FUNCTION dummy_seclabel_four() RETURNS integer AS $$SELECT 4$$ language s CREATE DOMAIN dummy_seclabel_domain AS text; ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1; ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2; +GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3 + TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE; -- -- Test of SECURITY LABEL statement with a plugin -- @@ -43,16 +47,16 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified'; -- OK -- Test for shared database object -- SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; -SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified'; -- OK -SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...'; -- fail +SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified'; -- OK +SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...'; -- fail ERROR: '...invalid label...' is not a valid security label SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified'; -- OK SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified'; -- fail ERROR: security label provider "unknown_seclabel" is not loaded -SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret'; -- fail (not superuser) +SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret'; -- fail (not superuser) ERROR: only superuser can set 'secret' label -SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified'; -- fail (not found) -ERROR: role "regress_dummy_seclabel_user3" does not exist +SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified'; -- fail (not found) +ERROR: role "regress_dummy_seclabel_user4" does not exist SET SESSION AUTHORIZATION regress_dummy_seclabel_user2; SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified'; -- fail (not privileged) ERROR: must have CREATEROLE privilege @@ -81,8 +85,8 @@ SELECT objtype, objname, provider, label FROM pg_seclabels domain | dummy_seclabel_domain | dummy | classified function | dummy_seclabel_four() | dummy | classified publication | dummy_pub | dummy | classified - role | regress_dummy_seclabel_user1 | dummy | classified role | regress_dummy_seclabel_user2 | dummy | unclassified + role | regress_dummy_seclabel_user3 | dummy | classified schema | dummy_seclabel_test | dummy | unclassified subscription | dummy_sub | dummy | classified table | dummy_seclabel_tbl1 | dummy | top secret @@ -115,3 +119,4 @@ DROP SUBSCRIPTION dummy_sub; DROP PUBLICATION dummy_pub; DROP ROLE regress_dummy_seclabel_user1; DROP ROLE regress_dummy_seclabel_user2; +DROP ROLE regress_dummy_seclabel_user3; diff --git a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql index 8c347b6a68b..649409757e8 100644 --- a/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql +++ b/src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql @@ -8,11 +8,13 @@ SET client_min_messages TO 'warning'; DROP ROLE IF EXISTS regress_dummy_seclabel_user1; DROP ROLE IF EXISTS regress_dummy_seclabel_user2; +DROP ROLE IF EXISTS regress_dummy_seclabel_user3; RESET client_min_messages; CREATE USER regress_dummy_seclabel_user1 WITH CREATEROLE; CREATE USER regress_dummy_seclabel_user2; +CREATE USER regress_dummy_seclabel_user3; CREATE TABLE dummy_seclabel_tbl1 (a int, b text); CREATE TABLE dummy_seclabel_tbl2 (x int, y text); @@ -22,6 +24,8 @@ CREATE DOMAIN dummy_seclabel_domain AS text; ALTER TABLE dummy_seclabel_tbl1 OWNER TO regress_dummy_seclabel_user1; ALTER TABLE dummy_seclabel_tbl2 OWNER TO regress_dummy_seclabel_user2; +GRANT regress_dummy_seclabel_user2, regress_dummy_seclabel_user3 + TO regress_dummy_seclabel_user1 WITH ADMIN TRUE, INHERIT FALSE, SET FALSE; -- -- Test of SECURITY LABEL statement with a plugin @@ -47,12 +51,12 @@ SECURITY LABEL ON TABLE dummy_seclabel_tbl2 IS 'classified'; -- OK -- SET SESSION AUTHORIZATION regress_dummy_seclabel_user1; -SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'classified'; -- OK -SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS '...invalid label...'; -- fail +SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'classified'; -- OK +SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS '...invalid label...'; -- fail SECURITY LABEL FOR 'dummy' ON ROLE regress_dummy_seclabel_user2 IS 'unclassified'; -- OK SECURITY LABEL FOR 'unknown_seclabel' ON ROLE regress_dummy_seclabel_user1 IS 'unclassified'; -- fail -SECURITY LABEL ON ROLE regress_dummy_seclabel_user1 IS 'secret'; -- fail (not superuser) -SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'unclassified'; -- fail (not found) +SECURITY LABEL ON ROLE regress_dummy_seclabel_user3 IS 'secret'; -- fail (not superuser) +SECURITY LABEL ON ROLE regress_dummy_seclabel_user4 IS 'unclassified'; -- fail (not found) SET SESSION AUTHORIZATION regress_dummy_seclabel_user2; SECURITY LABEL ON ROLE regress_dummy_seclabel_user2 IS 'unclassified'; -- fail (not privileged) @@ -113,3 +117,4 @@ DROP PUBLICATION dummy_pub; DROP ROLE regress_dummy_seclabel_user1; DROP ROLE regress_dummy_seclabel_user2; +DROP ROLE regress_dummy_seclabel_user3; diff --git a/src/test/regress/expected/create_role.out b/src/test/regress/expected/create_role.out index 4e67d727603..f5f745504c2 100644 --- a/src/test/regress/expected/create_role.out +++ b/src/test/regress/expected/create_role.out @@ -1,6 +1,7 @@ -- 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; +CREATE ROLE regress_role_normal; -- fail, only superusers can create users with these privileges SET SESSION AUTHORIZATION regress_role_admin; CREATE ROLE regress_nosuch_superuser SUPERUSER; @@ -13,7 +14,7 @@ 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_createrole CREATEROLE NOINHERIT; CREATE ROLE regress_login LOGIN; CREATE ROLE regress_inherit INHERIT; CREATE ROLE regress_connection_limit CONNECTION LIMIT 5; @@ -51,7 +52,19 @@ 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; +CREATE ROLE regress_hasprivs CREATEDB 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'; +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: must have admin option on role "regress_role_normal" +ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; +ERROR: permission denied to rename role +ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; +ERROR: permission denied -- ok, regress_tenant can create objects within the database SET SESSION AUTHORIZATION regress_tenant; CREATE TABLE tenant_table (i integer); @@ -70,20 +83,35 @@ 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 +-- fail, we don't inherit permissions 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 +-- fail, CREATEROLE is not enough to create roles in privileged roles CREATE ROLE regress_read_all_data IN ROLE pg_read_all_data; +ERROR: must have admin option on role "pg_read_all_data" CREATE ROLE regress_write_all_data IN ROLE pg_write_all_data; +ERROR: must have admin option on role "pg_write_all_data" CREATE ROLE regress_monitor IN ROLE pg_monitor; +ERROR: must have admin option on role "pg_monitor" CREATE ROLE regress_read_all_settings IN ROLE pg_read_all_settings; +ERROR: must have admin option on role "pg_read_all_settings" CREATE ROLE regress_read_all_stats IN ROLE pg_read_all_stats; +ERROR: must have admin option on role "pg_read_all_stats" CREATE ROLE regress_stat_scan_tables IN ROLE pg_stat_scan_tables; +ERROR: must have admin option on role "pg_stat_scan_tables" CREATE ROLE regress_read_server_files IN ROLE pg_read_server_files; +ERROR: must have admin option on role "pg_read_server_files" CREATE ROLE regress_write_server_files IN ROLE pg_write_server_files; +ERROR: must have admin option on role "pg_write_server_files" CREATE ROLE regress_execute_server_program IN ROLE pg_execute_server_program; +ERROR: must have admin option on role "pg_execute_server_program" CREATE ROLE regress_signal_backend IN ROLE pg_signal_backend; +ERROR: must have admin option on role "pg_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, creation of these roles failed above so they do not now exist SET SESSION AUTHORIZATION regress_role_admin; DROP ROLE regress_nosuch_superuser; @@ -114,22 +142,6 @@ 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 @@ -143,3 +155,4 @@ DROP VIEW tenant_view; DROP ROLE regress_tenant; DROP ROLE regress_role_admin; DROP ROLE regress_role_super; +DROP ROLE regress_role_normal; diff --git a/src/test/regress/sql/create_role.sql b/src/test/regress/sql/create_role.sql index 292dc087975..ddc80578d90 100644 --- a/src/test/regress/sql/create_role.sql +++ b/src/test/regress/sql/create_role.sql @@ -1,6 +1,7 @@ -- 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; +CREATE ROLE regress_role_normal; -- fail, only superusers can create users with these privileges SET SESSION AUTHORIZATION regress_role_admin; @@ -11,7 +12,7 @@ CREATE ROLE regress_nosuch_bypassrls BYPASSRLS; -- ok, having CREATEROLE is enough to create users with these privileges CREATE ROLE regress_createdb CREATEDB; -CREATE ROLE regress_createrole CREATEROLE; +CREATE ROLE regress_createrole CREATEROLE NOINHERIT; CREATE ROLE regress_login LOGIN; CREATE ROLE regress_inherit INHERIT; CREATE ROLE regress_connection_limit CONNECTION LIMIT 5; @@ -54,7 +55,18 @@ CREATE ROLE regress_plainrole; 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; +CREATE ROLE regress_hasprivs CREATEDB 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'; +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'; +ALTER ROLE regress_role_normal RENAME TO regress_role_abnormal; +ALTER ROLE regress_role_normal NOINHERIT NOLOGIN CONNECTION LIMIT 7; -- ok, regress_tenant can create objects within the database SET SESSION AUTHORIZATION regress_tenant; @@ -71,10 +83,10 @@ DROP TABLE tenant_table; ALTER VIEW tenant_view OWNER TO regress_role_admin; DROP VIEW tenant_view; --- fail, cannot take ownership of these objects from regress_tenant +-- fail, we don't inherit permissions from regress_tenant REASSIGN OWNED BY regress_tenant TO regress_createrole; --- ok, having CREATEROLE is enough to create roles in privileged roles +-- fail, CREATEROLE is not 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; @@ -86,6 +98,9 @@ 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, role still owns database objects +DROP ROLE regress_tenant; + -- fail, creation of these roles failed above so they do not now exist SET SESSION AUTHORIZATION regress_role_admin; DROP ROLE regress_nosuch_superuser; @@ -109,20 +124,6 @@ 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; -- fail, cannot drop ourself nor superusers DROP ROLE regress_role_super; @@ -136,3 +137,4 @@ DROP VIEW tenant_view; DROP ROLE regress_tenant; DROP ROLE regress_role_admin; DROP ROLE regress_role_super; +DROP ROLE regress_role_normal; |