Content-Type: multipart/alternative;
 boundary="------------xCsc6DsU3Qh0aE2VamvgmYeb"
Message-ID: <7e16d145-acea-4f30-b5da-4e283989a957@postgrespro.ru>
Date: Tue, 9 Jul 2024 00:23:18 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christophe Pettus <xof@thebuild.com>,
 pgsql-general <pgsql-general@postgresql.org>
References: <69A2A7BD-F8CA-4067-B229-B5F9FC6A884F@thebuild.com>
 <2e3e4ddb-52b5-49b2-b363-00e3f12a83a0@postgrespro.ru>
 <1214992.1720473388@sss.pgh.pa.us>
Content-Language: en-US, ru-RU
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <1214992.1720473388@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/7e16d145-acea-4f30-b5da-4e283989a957%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------xCsc6DsU3Qh0aE2VamvgmYeb
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 09.07.2024 00:16, Tom Lane wrote:
> Pavel Luzanov<p.luzanov@postgrespro.ru>  writes:
>> On 08.07.2024 22:22, Christophe Pettus wrote:
>>> This is more curiosity than anything else.  In the v16 role system, is there actually any reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE?  Does the role granted membership gain any ability it didn't have before in that case?
>> Looks like there is one ability.
>> Authentication in pg_hba.conf "USER" field via +role syntax.
> Hmm, if that check doesn't require INHERIT TRUE I'd say it's
> a bug.
>
> 			regards, tom lane

My test scenario:

postgres@demo(16.3)=# select * from pg_hba_file_rules ;
  rule_number |              file_name              | line_number | type  | database | user_name  | address | netmask | auth_method | options | error
-------------+-------------------------------------+-------------+-------+----------+------------+---------+---------+-------------+---------+-------
            1 | /etc/postgresql/16/main/pg_hba.conf |         118 | local | {all}    | {postgres} |         |         | trust       |         |
            2 | /etc/postgresql/16/main/pg_hba.conf |         121 | local | {all}    | {+bob}     |         |         | trust       |         |
            3 | /etc/postgresql/16/main/pg_hba.conf |         122 | local | {all}    | {alice}    |         |         | reject      |         |
(3 rows)

postgres@demo(16.3)=# \drg
             List of role grants
  Role name | Member of | Options | Grantor
-----------+-----------+---------+----------
  alice     | bob       |         | postgres
(1 row)

postgres@demo(16.3)=# \c - alice
You are now connected to database "demo" as user "alice".
alice@demo(16.3)=>


-- 
Pavel Luzanov
Postgres Professional:https://postgrespro.com

--------------xCsc6DsU3Qh0aE2VamvgmYeb
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 09.07.2024 00:16, Tom Lane wrote:<br>
    <blockquote type="cite" cite="mid:1214992.1720473388@sss.pgh.pa.us">
      <pre class="moz-quote-pre" wrap="">Pavel Luzanov <a class="moz-txt-link-rfc2396E" href="mailto:p.luzanov@postgrespro.ru">&lt;p.luzanov@postgrespro.ru&gt;</a> writes:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">On 08.07.2024 22:22, Christophe Pettus wrote:
</pre>
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">This is more curiosity than anything else.  In the v16 role system, is there actually any reason to grant membership in a role to a different role, but with SET FALSE, INHERIT FALSE, and ADMIN FALSE?  Does the role granted membership gain any ability it didn't have before in that case?
</pre>
        </blockquote>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.

			regards, tom lane
</pre>
    </blockquote>
    <br>
    <pre>My test scenario:</pre>
    <pre>postgres@demo(16.3)=# select * from pg_hba_file_rules ;
 rule_number |              file_name              | line_number | type  | database | user_name  | address | netmask | auth_method | options | error 
-------------+-------------------------------------+-------------+-------+----------+------------+---------+---------+-------------+---------+-------
           1 | /etc/postgresql/16/main/pg_hba.conf |         118 | local | {all}    | {postgres} |         |         | trust       |         | 
           2 | /etc/postgresql/16/main/pg_hba.conf |         121 | local | {all}    | {+bob}     |         |         | trust       |         | 
           3 | /etc/postgresql/16/main/pg_hba.conf |         122 | local | {all}    | {alice}    |         |         | reject      |         | 
(3 rows)

postgres@demo(16.3)=# \drg
            List of role grants
 Role name | Member of | Options | Grantor  
-----------+-----------+---------+----------
 alice     | bob       |         | postgres
(1 row)

postgres@demo(16.3)=# \c - alice
You are now connected to database "demo" as user "alice".
alice@demo(16.3)=&gt; 
</pre>
    <br>
    <pre class="moz-signature" cols="72">-- 
Pavel Luzanov
Postgres Professional: <a class="moz-txt-link-freetext" href="https://postgrespro.com">https://postgrespro.com</a></pre>
  </body>
</html>

--------------xCsc6DsU3Qh0aE2VamvgmYeb--