Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rUezh-00CGaZ-Cl for pgsql-docs@arkaria.postgresql.org; Tue, 30 Jan 2024 03:41:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rUeze-0028OH-KA for pgsql-docs@arkaria.postgresql.org; Tue, 30 Jan 2024 03:41:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rUeze-0028O9-4g for pgsql-docs@lists.postgresql.org; Tue, 30 Jan 2024 03:41:02 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rUeza-004S4x-Ms for pgsql-docs@postgresql.org; Tue, 30 Jan 2024 03:41:01 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=momjian.us; s=2024011501; h=In-Reply-To:Content-Transfer-Encoding:Content-Type: MIME-Version:References:Message-ID:Subject:Cc:To:From:Date:Sender:Reply-To: Content-ID:Content-Description; bh=OnR84FgFCAY0G5Zp4IxJVkiwlLhCJRaumk5e3k4mZiw=; b=NCyfeUL8Q+ZW79QQR4KA8MoTW9 kX5lx23gTg1DI4OTtGiaHsAblQTnf0ZmGJpzfi7i57/doIs/NlY4IR/klg03ztuCK6wb8RkZ1+14W MzrQWkffbIk/sVl+CdhqNpqZnlCgUSfRFNz49QC1VBem7n3iBLe0uaGbMmjQJBVLp/6ZofrOSXudD klcTPE2A3mdcPk9LgJ3q3a3XDqIwWfWIqYfFyJeLrs/HoXJ+zunuXgYTIBxYUd+ONjmu7HBv4A75z kxmtgAkVMdC3QEyQjzLnIJ3XgzaF3c4vi0MKorlrBqPfX8r45jmV/1GVqy5mFeQGT1e+znOZO3G4r c/CNkSnw==; Received: from bruce by momjian.us with local (Exim 4.96) (envelope-from ) id 1rUezX-004pWf-0D; Mon, 29 Jan 2024 22:40:55 -0500 Date: Mon, 29 Jan 2024 22:40:55 -0500 From: Bruce Momjian To: "David G. Johnston" Cc: Laurenz Albe , PostgreSQL-documentation , Robert Haas Subject: Re: CREATE ROLE inheritance details Message-ID: References: <8e1b78c8ad45febf2e530e1f7508ee01c68bf2ef.camel@cybertec.at>

MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="ii/+boU0tkvev3WB" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --ii/+boU0tkvev3WB Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit On Sun, Jan 28, 2024 at 10:11:18AM -0700, David G. Johnston wrote: > On Fri, Jan 26, 2024 at 5:18 PM Bruce Momjian wrote: > > + Role membership with the inherit attribute can automatically use > + whatever database privileges have been granted to all roles it > + is directly or indirectly a member of, though the chain stops > + at memberships lacking the inherit attribute. Without role > + inheritance, the only other value of membership is the use of > + SET ROLE, assuming the membership chain has > + SET attributes. > > > > I really think it is clearer if we consistently call the property attached to > the membership an "option" (the grant command calls them options) and restrict > the word attribute to only when talking about the role property. The following > refers to the option. I dislike burying this description about how the option > works within create role like this. It is already in chapter 22 and this > attribute description should point the reader there, not repeat the > information. Okay, I made these changes in the attached version of the patch. > > In PostgreSQL versions before 16, > - the GRANT statement did not support > - WITH INHERIT. Therefore, changing this role-level > - property would also change the behavior of already-existing grants. > - This is no longer the case. > + inheritance was a role-level attribute. It could not be specified > + during role addition with GRANT, and changing > + this role-level property would also change the inheritance behavior > + of all existing memberships. This is no longer the case. > > > My first reaction to the wording here is negative. > > I agree that the pre-v16 behavior dynamic should be documented but maybe l > eave a note with a bit more detail in chapter 22 and leave the following in > place here: > > Prior to version 16 this attribute directly controlled runtime privilege > inheritance instead of now only providing a default for when role membership is > established. Okay, I simplified the explaination. > @@ -285,9 +294,13 @@ in sync when changing the above synopsis! > > The IN ROLE clause causes the new role to > be automatically added as a member of the specified existing > - roles. (Note that there is no option to add the new role as an > - administrator; use a separate GRANT command > - to do that.) > + roles. The new membership will have the SET > + option enabled and the ADMIN option disabled. > + The INHERIT option will be enabled unless the > + NOINHERIT attribute is specified. See the + linkend="sql-grant"/> command, which has additional attribute > + control during membership creation and to modify these options > + after the new role is created. > > > additional attribute control s/b additional option control Done > @@ -307,10 +324,10 @@ in sync when changing the above synopsis! > ADMIN role_name > > > > - The ADMIN clause is like ROLE, > - but the named roles are added to the new role WITH ADMIN > - OPTION, giving them the right to grant membership in this > role > - to others. > + The ADMIN clause is similar to > + ROLE, but the named roles are added as members > + of the new role with ADMIN enabled, giving > + them the right to grant membership in this role to others. > > > > > I was trying to be explicitly clear that the ADMIN clause is effectively > additive to what ROLE does. "similar to + but" makes it easier to interpret as > something that only controls ADMIN, not SET or INHERIT. Okay, I used new wording. > The ADMIN clause behaves like ROLE but the ADMIN option is enabled. > > And modify ROLE as such: > > + INHERIT enabled in the new membership. New > + memberships will have the ADMIN option disabled. > + Use the ADMIN clause instead if you want the admin option enabled. > > This variant of the GRANT command grants membership > - in a role to one or more other roles. Membership in a role is significant > + in a role to one or more other roles, and the modification of > + membership attributes. Membership in a role is significant > > and (allows) the modification of three membership options, set, inherit, and > admin, described in chapter 22. Membership in a role is significant... Done. > To modify that attributes of > + an existing membership, simply specify the membership with updated > + attribute values. > > attribute s/b option Done. > > @@ -275,15 +278,13 @@ GRANT role_name replaceable> [, ...] TO > > > + The INHERIT option controls the inheritance status > + of the new membership; see for > + details on inheritance. If it is set to TRUE, > + it causes the new member to inherit from the granted role. If > + set to FALSE, the new member does not inherit. > + If unspecified, it defaults to the inheritance status of the role > + being added. > > > Suggest linking to chapter 22, not create role. K Done. > Unspecified has two outcomes: if the grant is establishing a new membership the > inheritance "attribute" value of the role being added is used, if the grant is > altering an existing membership the current value of the option is retained. Done. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. --ii/+boU0tkvev3WB Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="role.diff" diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml index 8dd2a6395c..d0d3d7ed64 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -133,24 +133,21 @@ in sync when changing the above synopsis! NOINHERIT - When the GRANT statement is used to confer - membership in one role to another role, the GRANT - may use the WITH INHERIT clause to specify whether - the privileges of the granted role should be inherited - by the new member. If the GRANT statement does not - specify either inheritance behavior, the new GRANT - will be created WITH INHERIT TRUE if the member - role is set to INHERIT and to - WITH INHERIT FALSE if it is set to - NOINHERIT. + This affects the membership inheritance status when this + role is added as a member of another role, both in this and + future commands. Specifically, it controls the inheritance + status of memberships added with this command using the + IN ROLE clause, and in later commands using + the ROLE clause. It is also used as the + default inheritance status when adding this role as a member + using the GRANT command. If not specified, + INHERIT is the default. In PostgreSQL versions before 16, - the GRANT statement did not support - WITH INHERIT. Therefore, changing this role-level - property would also change the behavior of already-existing grants. - This is no longer the case. + inheritance was a role-level attribute that controlled all runtime + membership checks for that role. @@ -285,9 +282,13 @@ in sync when changing the above synopsis! The IN ROLE clause causes the new role to be automatically added as a member of the specified existing - roles. (Note that there is no option to add the new role as an - administrator; use a separate GRANT command - to do that.) + roles. The new membership will have the SET + option enabled and the ADMIN option disabled. + The INHERIT option will be enabled unless the + NOINHERIT option is specified. See the command, which has additional option + control during membership creation and to modify these options + after the new role is created. @@ -297,8 +298,12 @@ in sync when changing the above synopsis! The ROLE clause causes one or more specified - existing roles to be automatically added as members of the new - role. This in effect makes the new role a group. + existing roles to be automatically added as members, with the + SET option enabled. This in effect makes the + new role a group. Roles named in this clause + with role-level INHERIT options will have + INHERIT enabled in the new membership. New + memberships will have the ADMIN option disabled. @@ -307,10 +312,10 @@ in sync when changing the above synopsis! ADMIN role_name - The ADMIN clause is like ROLE, - but the named roles are added to the new role WITH ADMIN - OPTION, giving them the right to grant membership in this role - to others. + The ADMIN clause has the same effect as + ROLE, but the named roles are added as members + of the new role with ADMIN enabled, giving + them the right to grant membership in the new role to others. @@ -353,15 +358,19 @@ in sync when changing the above synopsis! - The INHERIT attribute governs inheritance of grantable - privileges (that is, access privileges for database objects and role - memberships). It does not apply to the special role attributes set by - CREATE ROLE and ALTER ROLE. For example, being - a member of a role with CREATEDB privilege does not immediately - grant the ability to create databases, even if INHERIT is set; - it would be necessary to become that role via - SET ROLE before - creating a database. + The role attributes defined here are non-inheritable, i.e., being a + member of a role with, e.g., CREATEDB will not + allow the member to create new databases even if the membership grant + has the INHERIT option. Of course, if the membership + grant has the SET option the member role would be able to + SET ROLE to the + createdb role and then create a new database. + + + + The membership grants created by the + IN ROLE, ROLE, and ADMIN + clauses have the role executing this command as the grantee. @@ -460,8 +469,10 @@ CREATE ROLE name [ WITH ADMIN The behavior specified by the SQL standard is most closely approximated - by giving users the NOINHERIT attribute, while roles are - given the INHERIT attribute. + creating SQL-standard users as PostgreSQL + roles with the NOINHERIT option, and SQL-standard + roles as PostgreSQL roles with the + INHERIT option. diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 1ae5770fbb..ee53871713 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -249,11 +249,16 @@ GRANT role_name [, ...] TO This variant of the GRANT command grants membership - in a role to one or more other roles. Membership in a role is significant + in a role to one or more other roles, and the modification of + membership options SET, INHERIT, + and ADMIN; see + for details. Membership in a role is significant because it potentially allows access to the privileges granted to a role to each of its members, and potentially also the ability to make changes to the role itself. However, the actual permissions conferred depend on - the options associated with the grant. + the options associated with the grant. To modify that options of + an existing membership, simply specify the membership with updated + option values. @@ -275,15 +280,14 @@ GRANT role_name [, ...] TO - The INHERIT option, if it is set to - TRUE, causes the member to inherit the privileges of - the granted role. That is, it can automatically use whatever database - privileges have been granted to that role. If set to - FALSE, the member does not inherit the privileges - of the granted role. If this clause is not specified, it defaults to - true if the member role is set to INHERIT and to false - if the member role is set to NOINHERIT. - See CREATE ROLE. + The INHERIT attribute controls the inheritance status + of the new membership; see for + details on inheritance. If it is set to TRUE, + it causes the new member to inherit from the granted role. If + set to FALSE, the new member does not inherit. + If unspecified, new role membership defaults to the inheritance status + of the role being added. If an existing membership, the inheritance + option is unchanged. diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 1c011ac62b..e026a4e271 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -409,10 +409,10 @@ REVOKE group_role FROM role1INHERIT option automatically have use - of the privileges of those roles, including any - privileges inherited by those roles. - As an example, suppose we have done: + INHERIT option automatically have use of the + privileges of those directly or indirectly a member of, though the + chain stops at memberships lacking the inherit option. As an example, + suppose we have done: CREATE ROLE joe LOGIN; CREATE ROLE admin; --ii/+boU0tkvev3WB--