From: Bruce Momjian <bruce@momjian.us>
To: PostgreSQL-documentation <pgsql-docs@postgresql.org>
Cc: Robert Haas <robertmhaas@gmail.com>
Subject: CREATE ROLE inheritance details
Date: Tue, 16 Jan 2024 17:03:06 -0500
Message-ID: <Zab9GiV63EENDcWG@momjian.us> (raw)
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.
Attachments:
[application/x-sh] test_inh.sh (600B, ../Zab9GiV63EENDcWG@momjian.us/2-test_inh.sh)
download
[application/sql] test_inh.sql (996B, ../Zab9GiV63EENDcWG@momjian.us/3-test_inh.sql)
download
[text/x-diff] role.diff (3.2K, ../Zab9GiV63EENDcWG@momjian.us/4-role.diff)
download | inline diff:diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, robertmhaas@gmail.com
Subject: Re: CREATE ROLE inheritance details
In-Reply-To: <Zab9GiV63EENDcWG@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox