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 1rTWP5-003oeE-FP for pgsql-docs@arkaria.postgresql.org; Sat, 27 Jan 2024 00:18:36 +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 1rTWP1-003FI1-Py for pgsql-docs@arkaria.postgresql.org; Sat, 27 Jan 2024 00:18:31 +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 1rTWP1-003FFR-G2 for pgsql-docs@lists.postgresql.org; Sat, 27 Jan 2024 00:18:31 +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 1rTWOy-003uE5-2h for pgsql-docs@postgresql.org; Sat, 27 Jan 2024 00:18:30 +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=3eWm5X5js7nELP7Fcbh7DsxfZ5GfuwiDz8QG6R2Da9o=; b=UPDC5TBFom9Jm8w8zoDwJjdzPI /zJZDB/bw4nNPTlvr8kQaX19Xn9C1Xb3eN/yqlGqGfgBIVtnLkNM9OrNkbsLAhgasj9ac5IDt3VNF +v7lTzRsiIrftS7VEX+TKvEIPMHlJjZJaUg5VUu2SRQCAyXoTd1h82t8mfKhYCtaI/vGoe94WNh0a wAdekRNSvAcA502j5UWNzW2pMwtuSExG4fVlThkD7N9KPZNa0bAx/CtDUjuCcwPo+0pe5eWlfrdHA sbnnB/MCNb0jUyQITjUjxGsWpg+MVQ85jJFoyo4xwcI9bJlOnprgS3gkebVK/7msGTXnBM2v+97Va LtYNDMew==; Received: from bruce by momjian.us with local (Exim 4.96) (envelope-from ) id 1rTWOv-008ZHJ-2z; Fri, 26 Jan 2024 19:18:25 -0500 Date: Fri, 26 Jan 2024 19:18:25 -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="Pu6F0BkPT9hX91Ba" 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 --Pu6F0BkPT9hX91Ba Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit On Wed, Jan 17, 2024 at 02:32:47PM -0700, David G. Johnston wrote: > I had a go at this. > > I went with a more "bullet item" approach with my wording for INHERIT/ > NOINHERIT. I tried to address that. > The entire paragraph regarding how the INHERIT "option" works, as opposed to > the attribute, seems out of place where it was and the material is already > covered in the GRANT page.  We should either improve that page or extract this > level of detail somewhere else, not try to clutter up the CREATE ROLE page with > it. Because the CREATE ROLE is the way to create roles, and GRANT does much more than just grant membership, I ended up moving the details from the GRANT page to the CREATE ROLE page. > We consistently say what the default is for these attribute pairs, do so here > as well. I added that, I hope in all the right places. > Turn the parenthetical in the IN ROLE section into actual assertive > documentation of what the clause does.  Tweak ROLE and ADMIN as well to fit in > better. Yes, that needed help. > Reword the discussion regarding non-inheritance to be more direct. > > I added mention of the grantee aspect of privileges as a soft way of further > pointing out that the IN ROLE, ROLE, ADMIN clauses are limited in what they can > control in the resulting membership grants. > > I choose to use the phrasing "giving ... roles" for both parts of the sentence > instead of switching to "... roles are given" for the second half.  More of a > style choice but I didn't think switching really added much and just makes it a > bit wordier and possibly a bit more effort to mentally parse. I adjusted that working, but in a different way. Patch attached. I also found we didn't document that GRANT can be used to modify a membership's attributes even after it is created; I added that. Just a reminder, this is for PG 16 and master. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. --Pu6F0BkPT9hX91Ba 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..f2dc841b85 100644 --- a/doc/src/sgml/ref/create_role.sgml +++ b/doc/src/sgml/ref/create_role.sgml @@ -133,24 +133,33 @@ 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. + + + + 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. 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. @@ -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 command, which has additional attribute + control during membership creation and to modify these options + after the new role is created. @@ -297,8 +310,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 attributes will have + INHERIT enabled in the new membership. New + memberships will have the ADMIN option disabled. @@ -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. @@ -353,15 +370,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. @@ -459,9 +480,11 @@ 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. + The behavior specified by the SQL standard is most closely approximated + creating SQL-standard users as PostgreSQL + roles with the NOINHERIT attribute, and SQL-standard + roles as PostgreSQL roles with the + INHERIT attribute. diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 1ae5770fbb..fbf98b0cad 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -249,11 +249,14 @@ 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 attributes. 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 attributes of + an existing membership, simply specify the membership with updated + attribute values. @@ -275,15 +278,13 @@ 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 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. --Pu6F0BkPT9hX91Ba--