Content-Type: multipart/alternative;
 boundary="------------ZxH6ImbAJQ78FdxLaiMCrUye"
Message-ID: <20824093-d94f-e5d6-b611-8cec7fc6e95e@postgrespro.ru>
Date: Fri, 3 Mar 2023 14:01:59 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.7.1
Subject: Re: psql: Add role's membership options to the \du+ command
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: David Zhang <david.zhang@highgo.ca>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
References: <b9be2d0e-a9bc-0a30-492f-a4f68e4f7740@postgrespro.ru>
 <f388549c-4924-7142-72a5-e67c786d2a6f@highgo.ca>
 <CAKFQuwYt3ENZoYgY2RBh-U4id_=vntT9uJ83PvxPSsPQ0Cc_Jw@mail.gmail.com>
 <ee39eff0-3c0d-4367-4595-619fd1763a5d@highgo.ca>
 <CAKFQuwbmAFfDw9ScCiOxK4UGf8Z8ny1nDgAn41gOJCeJ76QC4Q@mail.gmail.com>
 <01620d6b-9efe-b199-b619-8450d865ec83@postgrespro.ru>
 <e587e2ee-7de0-88a2-10f8-c7cf001bab8c@postgrespro.ru>
 <CAKFQuwYZ3S_A1ZKdnmOv-LueO1683UZf7AocNFTDjwEMLVfMOA@mail.gmail.com>
 <94ce7392-fa90-bc1c-4dc2-677161bbff58@postgrespro.ru>
 <CAKFQuwbkywHVFPL6TrJiHFd0KgEvdH0LZTSFxAdK4wzP-u8m-A@mail.gmail.com>
Content-Language: en-US
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
In-Reply-To: <CAKFQuwbkywHVFPL6TrJiHFd0KgEvdH0LZTSFxAdK4wzP-u8m-A@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20824093-d94f-e5d6-b611-8cec7fc6e95e%40postgrespro.ru>
Precedence: bulk

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

Hello,

On 22.02.2023 00:34, David G. Johnston wrote:
> I didn't even know this function existed. But I see that it was 
> changed in 3d14e171 with updated documentation:
> https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS
> Maybe that's enough.
>
>
> I think that should probably have ADMIN as one of the options as well. 
> Also curious what it reports for an empty membership.

I've been experimenting for a few days and I want to admit that this is 
a very difficult and not obvious topic.
I'll try to summarize what I think.

1.
About ADMIN value for pg_has_role.
Implementation of ADMIN value will be different from USAGE and SET.
To be True, USAGE value requires the full chain of memberships to have 
INHERIT option.
Similar with SET: the full chain of memberships must have SET option.
But for ADMIN, only last member in the chain must have ADMIN option and 
all previous members
must have INHERIT (to administer directly) or SET option (to switch to 
role, last in the chain).
Therefore, it is not obvious to me that the function needs the ADMIN value.

2.
pg_has_role function description starts with: Does user have privilege 
for role?
     - This is not exact: function works not only with users, but with 
NOLOGIN roles too.
     - Term "privilege": this term used for ACL columns, such usage may 
be confusing,
       especially after adding INHERIT and SET in addition to ADMIN option.

3.
It is possible to grant membership with all three options turned off:
     grant a to b with admin false, inherit false, set false;
But such membership is completely useless (if i didn't miss something).
May be such grants must be prohibited. At least this may be documented 
in the GRANT command.

4.
Since v16 it is possible to grant membership from one role to another 
several times with different grantors.
And only grantor can revoke membership.
     - This is not documented anywhere.
     - Current behavior of \du command with duplicated roles in "Member 
of" column strongly confusing.
       This is one of the goals of the discussion patch.

I think to write about this to pgsql-docs additionally to this topic.

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

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

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <tt>Hello,</tt><tt><br>
    </tt><tt><br>
    </tt>
    <div class="moz-cite-prefix">On 22.02.2023 00:34, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwbkywHVFPL6TrJiHFd0KgEvdH0LZTSFxAdK4wzP-u8m-A@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <tt>I didn't even know this function existed. But I see that it
        was changed in 3d14e171 with updated documentation:</tt><br>
      <div dir="ltr">
        <div class="gmail_quote">
          <div><tt> </tt><tt><a
href="https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS"
                target="_blank" moz-do-not-send="true"
                class="moz-txt-link-freetext">https://www.postgresql.org/docs/devel/functions-info.html#FUNCTIONS-INFO-ACCESS</a></tt><tt><br>
            </tt><tt>Maybe that's enough.</tt><tt><br>
            </tt><br>
          </div>
          <div><br>
          </div>
          <div class="gmail_default"
            style="font-family:arial,helvetica,sans-serif">I think that
            should probably have ADMIN as one of the options as well. 
            Also curious what it reports for an empty membership.</div>
        </div>
      </div>
    </blockquote>
    <br>
    <tt>I've been experimenting for a few days and I want to admit that
      this is a very difficult and not obvious topic.</tt><tt><br>
    </tt><tt>I'll try to summarize what I think.</tt><tt><br>
    </tt><tt><br>
    </tt><tt>1.</tt><tt><br>
    </tt><tt>About ADMIN value for pg_has_role.</tt><tt><br>
    </tt><tt>Implementation of ADMIN value will be different from USAGE
      and SET.</tt><tt><br>
    </tt><tt>To be True, USAGE value requires the full chain of
      memberships to have INHERIT option.</tt><tt><br>
    </tt><tt>Similar with SET: the full chain of memberships must have
      SET option.</tt><tt><br>
    </tt><tt>But for ADMIN, only last member in the chain must have
      ADMIN option and all previous members</tt><tt><br>
    </tt><tt>must have INHERIT (to administer directly) or SET option
      (to switch to role, last in the chain).</tt><tt><br>
    </tt><tt>Therefore, it is not obvious to me that the function needs
      the ADMIN value.</tt><tt><br>
    </tt><tt><br>
    </tt><tt>2.</tt><tt><br>
    </tt><tt>pg_has_role function description starts with: Does user
      have privilege for role? </tt><tt><br>
    </tt><tt>    - This is not exact: function works not only with
      users, but with NOLOGIN roles too.</tt><tt><br>
    </tt><tt>    - Term "privilege": this term used for ACL columns,
      such usage may be confusing, <br>
            especially after adding INHERIT and SET in addition to ADMIN
      option.</tt><tt><br>
    </tt><tt>    </tt><tt><br>
    </tt><tt>3.</tt><tt><br>
    </tt><tt>It is possible to grant membership with all three options
      turned off:<br>
          grant a to b with admin false, inherit false, set false;</tt><tt><br>
    </tt><tt>But such membership is completely useless (if i didn't miss
      something).<br>
      May be such grants must be prohibited. At least this may be
      documented in the GRANT command.</tt><tt><br>
    </tt><tt><br>
    </tt><tt>4.</tt><tt><br>
    </tt><tt>Since v16 it is possible to grant membership from one role
      to another several times with different grantors.<br>
      And only grantor can revoke membership.</tt><tt><br>
    </tt><tt>    - This is not documented anywhere.</tt><tt><br>
    </tt><tt>    - Current behavior of \du command with duplicated roles
      in "Member of" column strongly confusing. <br>
            This is one of the goals of the discussion patch.</tt><tt><br>
    </tt><tt>    </tt><tt><br>
    </tt><tt>I think to write about this to pgsql-docs additionally to
      this topic.</tt><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>

--------------ZxH6ImbAJQ78FdxLaiMCrUye--