I am unhappy with the documentation adjustments made to CREATE ROLE in Postgres 16 by this commit:
commit e3ce2de09d
Author: Robert Haas <rh...@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=r...@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 <br...@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
test_inh.sh
Description: Bourne shell script
test_inh.sql
Description: application/sql
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>
