aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ddl.sgml10
-rw-r--r--doc/src/sgml/ref/alter_role.sgml8
-rw-r--r--doc/src/sgml/ref/comment.sgml3
-rw-r--r--doc/src/sgml/ref/create_role.sgml4
-rw-r--r--doc/src/sgml/ref/createuser.sgml3
-rw-r--r--doc/src/sgml/ref/drop_role.sgml2
-rw-r--r--doc/src/sgml/ref/dropuser.sgml7
-rw-r--r--doc/src/sgml/ref/grant.sgml4
-rw-r--r--doc/src/sgml/user-manag.sgml44
-rw-r--r--src/backend/catalog/objectaddress.c10
-rw-r--r--src/backend/commands/user.c100
-rw-r--r--src/test/modules/dummy_seclabel/expected/dummy_seclabel.out17
-rw-r--r--src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql13
-rw-r--r--src/test/regress/expected/create_role.out53
-rw-r--r--src/test/regress/sql/create_role.sql38
15 files changed, 210 insertions, 106 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index d91a7814794..4b219435d45 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3216,13 +3216,11 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
name. Therefore, if each user has a separate schema, they access
their own schemas by default.) This pattern is a secure schema
usage pattern unless an untrusted user is the database owner or
- holds the <literal>CREATEROLE</literal> privilege, in which case no
- secure schema usage pattern exists.
+ has been granted <literal>ADMIN OPTION</literal> on a relevant role,
+ in which case no secure schema usage pattern exists.
</para>
<!-- A database owner can attack the database's users via "CREATE SCHEMA
- trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". A
- CREATEROLE user can issue "GRANT $dbowner TO $me" and then use the
- database owner attack. -->
+ trojan; ALTER DATABASE $mydb SET search_path = trojan, public;". -->
<para>
In <productname>PostgreSQL</productname> 15 and later, the default
@@ -3250,7 +3248,7 @@ REVOKE CREATE ON SCHEMA public FROM PUBLIC;
unreliable</link>. If you create functions or extensions in the public
schema, use the first pattern instead. Otherwise, like the first
pattern, this is secure unless an untrusted user is the database owner
- or holds the <literal>CREATEROLE</literal> privilege.
+ or has been granted <literal>ADMIN OPTION</literal> on a relevant role.
</para>
</listitem>
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index f5c1264942f..43067d3feca 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -73,7 +73,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
Roles having <literal>CREATEROLE</literal> privilege can change any of these
settings except <literal>SUPERUSER</literal>, <literal>REPLICATION</literal>,
and <literal>BYPASSRLS</literal>; but only for non-superuser and
- non-replication roles.
+ non-replication roles for which they have been
+ granted <literal>ADMIN OPTION</literal>.
Ordinary roles can only change their own password.
</para>
@@ -81,7 +82,7 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
The second variant changes the name of the role.
Database superusers can rename any role.
Roles having <literal>CREATEROLE</literal> privilege can rename non-superuser
- roles.
+ roles for which they have been granted <literal>ADMIN OPTION</literal>.
The current session user cannot be renamed.
(Connect as a different user if you need to do that.)
Because <literal>MD5</literal>-encrypted passwords use the role name as
@@ -116,7 +117,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
<para>
Superusers can change anyone's session defaults. Roles having
<literal>CREATEROLE</literal> privilege can change defaults for non-superuser
- roles. Ordinary roles can only set defaults for themselves.
+ roles for which they have been granted <literal>ADMIN OPTION</literal>.
+ Ordinary roles can only set defaults for themselves.
Certain configuration variables cannot be set this way, or can only be
set if a superuser issues the command. Only superusers can change a setting
for all roles in all databases.
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 23d9029af9c..7499da1d62a 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -99,7 +99,8 @@ COMMENT ON
For most kinds of object, only the object's owner can set the comment.
Roles don't have owners, so the rule for <literal>COMMENT ON ROLE</literal> is
that you must be superuser to comment on a superuser role, or have the
- <literal>CREATEROLE</literal> privilege to comment on non-superuser roles.
+ <literal>CREATEROLE</literal> privilege and have been granted
+ <literal>ADMIN OPTION</literal> on the target role.
Likewise, access methods don't have owners either; you must be superuser
to comment on an access method.
Of course, a superuser can comment on anything.
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 1ccc8325588..0863acbcac4 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -119,8 +119,8 @@ in sync when changing the above synopsis!
<listitem>
<para>
These clauses determine whether a role will be permitted to
- create, alter, drop, comment on, change the security label for,
- and grant or revoke membership in other roles.
+ create, alter, drop, comment on, and change the security label for
+ other roles.
See <xref linkend='role-creation' /> for more details about what
capabilities are conferred by this privilege.
If not specified, <literal>NOCREATEROLE</literal> is the default.
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index a41a2b24e6c..f91dc500a40 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -252,8 +252,7 @@ PostgreSQL documentation
<listitem>
<para>
The new user will be allowed to create, alter, drop, comment on,
- change the security label for, and grant or revoke membership in
- other roles; that is,
+ change the security label for other roles; that is,
this user will have <literal>CREATEROLE</literal> privilege.
See <xref linkend='role-creation' /> for more details about what
capabilities are conferred by this privilege.
diff --git a/doc/src/sgml/ref/drop_role.sgml b/doc/src/sgml/ref/drop_role.sgml
index 13dc1cc6499..cbcb3cd3d3e 100644
--- a/doc/src/sgml/ref/drop_role.sgml
+++ b/doc/src/sgml/ref/drop_role.sgml
@@ -32,7 +32,7 @@ DROP ROLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...
<command>DROP ROLE</command> removes the specified role(s).
To drop a superuser role, you must be a superuser yourself;
to drop non-superuser roles, you must have <literal>CREATEROLE</literal>
- privilege.
+ privilege and have been granted <literal>ADMIN OPTION</literal> on the role.
</para>
<para>
diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml
index 81580507e82..b6be26d5b0a 100644
--- a/doc/src/sgml/ref/dropuser.sgml
+++ b/doc/src/sgml/ref/dropuser.sgml
@@ -35,9 +35,10 @@ PostgreSQL documentation
<para>
<application>dropuser</application> removes an existing
<productname>PostgreSQL</productname> user.
- Only superusers and users with the <literal>CREATEROLE</literal> privilege can
- remove <productname>PostgreSQL</productname> users. (To remove a
- superuser, you must yourself be a superuser.)
+ Superusers can use this command to remove any role; otherwise, only
+ non-superuser roles can be removed, and only by a user who possesses
+ the <literal>CREATEROLE</literal> privilege and has been granted
+ <literal>ADMIN OPTION</literal> on the target role.
</para>
<para>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 518bdb32d82..85f5f42ea6e 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -271,9 +271,7 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
in the role as well. Without the admin option, ordinary users cannot
do that. A role is not considered to hold <literal>WITH ADMIN
OPTION</literal> on itself. Database superusers can grant or revoke
- membership in any role to anyone. Roles having
- <literal>CREATEROLE</literal> privilege can grant or revoke membership
- in any role that is not a superuser. This option defaults to
+ membership in any role to anyone. This option defaults to
<literal>FALSE</literal>.
</para>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 7aa6bdac163..71a2d8f2985 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -199,7 +199,12 @@ CREATE USER <replaceable>name</replaceable>;
checks). To create such a role, use <literal>CREATE ROLE
<replaceable>name</replaceable> CREATEROLE</literal>.
A role with <literal>CREATEROLE</literal> privilege can alter and drop
- other roles, too, as well as grant or revoke membership in them.
+ roles which have been granted to the <literal>CREATEROLE</literal>
+ user with the <literal>ADMIN</literal> option. Such a grant occurs
+ automatically when a <literal>CREATEROLE</literal> user that is not
+ a superuser creates a new role, so that by default, a
+ <literal>CREATEROLE</literal> user can alter and drop the roles
+ which they have created.
Altering a role includes most changes that can be made using
<literal>ALTER ROLE</literal>, including, for example, changing
passwords. It also includes modifications to a role that can
@@ -224,15 +229,6 @@ CREATE USER <replaceable>name</replaceable>;
confer the ability to grant or revoke the <literal>BYPASSRLS</literal>
privilege.
</para>
- <para>
- Because the <literal>CREATEROLE</literal> privilege allows a user
- to grant or revoke membership even in roles to which it does not (yet)
- have any access, a <literal>CREATEROLE</literal> user can obtain access
- to the capabilities of every predefined role in the system, including
- highly privileged roles such as
- <literal>pg_execute_server_program</literal> and
- <literal>pg_write_server_files</literal>.
- </para>
</listitem>
</varlistentry>
@@ -329,6 +325,34 @@ ALTER ROLE myname SET enable_indexscan TO off;
<literal>LOGIN</literal> privilege are fairly useless, since they will never
be invoked.
</para>
+
+ <para>
+ When a non-superuser creates a role using the <literal>CREATEROLE</literal>
+ privilege, the created role is automatically granted back to the creating
+ user, just as if the bootstrap superuser had executed the command
+ <literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
+ INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can
+ only exercise special privileges with regard to an existing role if they
+ have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient
+ to allow a <literal>CREATEROLE</literal> user to administer the roles they
+ created. However, because it is created with <literal>INHERIT FALSE, SET
+ FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the
+ privileges of the created role, nor can it access the privileges of that
+ role using <literal>SET ROLE</literal>. However, since any user who has
+ <literal>ADMIN OPTION</literal> on a role can grant membership in that
+ role to any other user, the <literal>CREATEROLE</literal> user can gain
+ access to the created role by simplying granting that role back to
+ themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal>
+ options. Thus, the fact that privileges are not inherited by default nor
+ is <literal>SET ROLE</literal> granted by default is a safeguard against
+ accidents, not a security feature. Also note that, because this automatic
+ grant is granted by the bootstrap user, it cannot be removed or changed by
+ the <literal>CREATEROLE</literal> user; however, any superuser could
+ revoke it, modify it, and/or issue additional such grants to other
+ <literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal>
+ users have <literal>ADMIN OPTION</literal> on a role at any given time
+ can administer it.
+ </para>
</sect1>
<sect1 id="role-membership">
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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-23 09:13:36 +0000