Date: Tue, 16 Jan 2024 17:03:06 -0500
From: Bruce Momjian <bruce@momjian.us>
To: PostgreSQL-documentation <pgsql-docs@postgresql.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Subject: CREATE ROLE inheritance details
Message-ID: <Zab9GiV63EENDcWG@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="mkRRXG4AMhl7evcO"
Content-Disposition: inline
Archived-At: 
 <https://www.postgresql.org/message-id/Zab9GiV63EENDcWG%40momjian.us>
Precedence: bulk


--mkRRXG4AMhl7evcO
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

I am unhappy with the documentation adjustments made to CREATE ROLE in
Postgres 16 by this commit:

	commit e3ce2de09d
	Author: Robert Haas <rhaas@postgresql.org>
	Date:   Thu Aug 25 10:06:02 2022 -0400
	
	    Allow grant-level control of role inheritance behavior.
	
	    The GRANT statement can now specify WITH INHERIT TRUE or WITH
	    INHERIT FALSE to control whether the member inherits the granted
	    role's permissions. For symmetry, you can now likewise write
	    WITH ADMIN TRUE or WITH ADMIN FALSE to turn ADMIN OPTION on or off.
	
	    If a GRANT does not specify WITH INHERIT, the behavior based on
	    whether the member role is marked INHERIT or NOINHERIT. This means
	    that if all roles are marked INHERIT or NOINHERIT before any role
	    grants are performed, the behavior is identical to what we had before;
	    otherwise, it's different, because ALTER ROLE [NO]INHERIT now only
	    changes the default behavior of future grants, and has no effect on
	    existing ones.
	
	    Patch by me. Reviewed and testing by Nathan Bossart and Tushar Ahuja,
	    with design-level comments from various others.
	
	    Discussion: http://postgr.es/m/CA+Tgmoa5Sf4PiWrfxA=sGzDKg0Ojo3dADw=wAHOhR9dggV=RmQ@mail.gmail.com

It seems to have removed important details about how inherit works
beyond GRANT.

Using the attached script, test_inh.sh, I generated SQL queries
contained in attached file test_inh.sql, and this generated this output
showing that the inheritance setting of the role being added as a member
controls the inheritance status of the membership:

	     rolname      | Is member of role | inherit_option
	------------------+-------------------+----------------
	 init_1_noinherit | user_1_inherit    | f
	 init_1_noinherit | user_2_noinherit  | f
	 init_1_noinherit | user_3_inherit    | f
	 init_1_noinherit | user_4_noinherit  | f
	 init_2_inherit   | user_5_inherit    | t
	 init_2_inherit   | user_6_noinherit  | t
	 init_2_inherit   | user_7_inherit    | t
	 init_2_inherit   | user_8_noinherit  | t
	 user_1_inherit   | init_3_noinherit  | t
	 user_2_noinherit | init_3_noinherit  | f
	 user_3_inherit   | init_4_inherit    | t
	 user_4_noinherit | init_4_inherit    | f
	 user_5_inherit   | init_3_noinherit  | t
	 user_6_noinherit | init_3_noinherit  | f
	 user_7_inherit   | init_4_inherit    | t
	 user_8_noinherit | init_4_inherit    | f

I have attached a patch to re-add this information, and clarify it.  I
would like to apply this to PG 16 and master.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

--mkRRXG4AMhl7evcO
Content-Type: application/x-sh
Content-Disposition: attachment; filename="test_inh.sh"
Content-Transfer-Encoding: quoted-printable

X=3D1=0Acat <<END=0ACREATE ROLE init_1_noinherit NOINHERIT;=0ACREATE ROLE i=
nit_2_inherit INHERIT;=0ACREATE ROLE init_3_noinherit NOINHERIT;=0ACREATE R=
OLE init_4_inherit INHERIT;=0AEND=0Afor ROLE1 in init_1_noinherit init_2_in=
herit=0Ado	for ROLE2 in init_3_noinherit init_4_inherit=0A	do 	for INH in I=
NHERIT NOINHERIT=0A		do	echo "CREATE ROLE user_${X}_${INH} ROLE $ROLE1 IN R=
OLE $ROLE2 $INH;"=0A			X=3D$((X + 1))=0A		done=0A	done=0Adone=0A=0Acat <<EN=
D=0ASELECT rolname, roleid::regrole AS "Is member of role", inherit_option=
=0AFROM  pg_roles, pg_auth_members=0AWHERE member =3D pg_roles.oid AND=0A  =
    rolname NOT LIKE 'pg_%'=0AORDER BY 1, 2;=0AEND=0A
--mkRRXG4AMhl7evcO
Content-Type: application/sql
Content-Disposition: attachment; filename="test_inh.sql"
Content-Transfer-Encoding: quoted-printable

CREATE ROLE init_1_noinherit NOINHERIT;=0ACREATE ROLE init_2_inherit INHERI=
T;=0ACREATE ROLE init_3_noinherit NOINHERIT;=0ACREATE ROLE init_4_inherit I=
NHERIT;=0ACREATE ROLE user_1_INHERIT ROLE init_1_noinherit IN ROLE init_3_n=
oinherit INHERIT;=0ACREATE ROLE user_2_NOINHERIT ROLE init_1_noinherit IN R=
OLE init_3_noinherit NOINHERIT;=0ACREATE ROLE user_3_INHERIT ROLE init_1_no=
inherit IN ROLE init_4_inherit INHERIT;=0ACREATE ROLE user_4_NOINHERIT ROLE=
 init_1_noinherit IN ROLE init_4_inherit NOINHERIT;=0ACREATE ROLE user_5_IN=
HERIT ROLE init_2_inherit IN ROLE init_3_noinherit INHERIT;=0ACREATE ROLE u=
ser_6_NOINHERIT ROLE init_2_inherit IN ROLE init_3_noinherit NOINHERIT;=0AC=
REATE ROLE user_7_INHERIT ROLE init_2_inherit IN ROLE init_4_inherit INHERI=
T;=0ACREATE ROLE user_8_NOINHERIT ROLE init_2_inherit IN ROLE init_4_inheri=
t NOINHERIT;=0ASELECT rolname, roleid::regrole AS "Is member of role", inhe=
rit_option=0AFROM  pg_roles, pg_auth_members=0AWHERE member =3D pg_roles.oi=
d AND=0A      rolname NOT LIKE 'pg_%'=0AORDER BY 1, 2;=0A
--mkRRXG4AMhl7evcO
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..5c2b71bf69 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -133,16 +133,24 @@ in sync when changing the above synopsis!
       <term><literal>NOINHERIT</literal></term>
       <listitem>
        <para>
-        When the <literal>GRANT</literal> statement is used to confer
-        membership in one role to another role, the <literal>GRANT</literal>
-        may use the <literal>WITH INHERIT</literal> clause to specify whether
-        the privileges of the granted role should be <quote>inherited</quote>
-        by the new member. If the <literal>GRANT</literal> statement does not
-        specify either inheritance behavior, the new <literal>GRANT</literal>
-        will be created <literal>WITH INHERIT TRUE</literal> if the member
-        role is set to <literal>INHERIT</literal> and to
-        <literal>WITH INHERIT FALSE</literal> if it is set to
-        <literal>NOINHERIT</literal>.
+        This controls the membership inheritance status when a new role
+        is added as a member using the <literal>IN ROLE</literal> clause,
+        when this role is later added as a member of a new role with the
+        <literal>ROLE</literal> clause, and the default inheritance when
+        adding this role as a member using the <literal>GRANT</literal>
+        statement.  In such <literal>GRANT</literal> statements, the
+        role's inheritance status will be used unless overridden by the
+        <literal>GRANT</literal> <literal>WITH INHERIT</literal> clause.
+       </para>
+
+       <para>
+        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 without the inherit attribute.
+        Without role inheritance, the only other value of membership
+        is via <command>SET ROLE</command>, assuming the membership has
+        the <literal>SET</literal> attribute.
        </para>
 
        <para>
@@ -286,8 +294,8 @@ in sync when changing the above synopsis!
         The <literal>IN ROLE</literal> 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 <command>GRANT</command> command
-        to do that.)
+        administrator or to disallow <literal>SET ROLE</literal>; use a
+        separate <command>GRANT</command> command to do that.)
        </para>
       </listitem>
      </varlistentry>
@@ -460,8 +468,10 @@ CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <repl
 
   <para>
    The behavior specified by the SQL standard is most closely approximated
-   by giving users the <literal>NOINHERIT</literal> attribute, while roles are
-   given the <literal>INHERIT</literal> attribute.
+   by giving <literal>LOGIN</literal> roles
+   the <literal>NOINHERIT</literal> attribute, while
+   <literal>NOLOGIN</literal>roles are given the <literal>INHERIT</literal>
+   attribute.
   </para>
 
   <para>

--mkRRXG4AMhl7evcO--