Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pzdLs-0006tu-AS for pgsql-hackers@arkaria.postgresql.org; Thu, 18 May 2023 13:07:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pzdLr-0001I6-4v for pgsql-hackers@arkaria.postgresql.org; Thu, 18 May 2023 13:07:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pzdLq-0001Hs-LN; Thu, 18 May 2023 13:07:26 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1pzdLn-000XFR-CX; Thu, 18 May 2023 13:07:26 +0000 Received: from [192.168.28.30] (unknown [192.168.28.30]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 5DB69E2100D; Thu, 18 May 2023 16:08:06 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1684415286; bh=4vqdwZalUEK/yKxyu6URRQyncrMX5oQ700RlRSX31j4=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=GjSGrCU6Hoo/Oah1e2EySdjOfguguRrNpN/9UwEI8CB02okUF3RgyQi9RcWsTT2ml CULW6N3IrwyrAzGMf4w8aiBKJFX7QE8QJ4i6mBToYze6oGrM6X1teStqJcW2rSMQTY Gr7mpkltdoYjzMFEqKQZDbmO4nCtgl8b4ev83Wqd8KTO7Y5iFmygfnz1OrO/rUwofA L8BAqToQTWRTN2h1QTvunZWs8KbqEGtLCwssA5iAv5dP1NrCSeWlx/voDj4ztua/S2 QOIYnVKEVzmHXo6xeVFvoevifwqFvfROfdubEFx7OV/3qWrCKbej8BkfkAl6E5a1DW ACCWiy/5o86pQ== Content-Type: multipart/alternative; boundary="------------FdKBWtdEn0wnkx5Q5JsKmq0S" Message-ID: <7aa3d663-1ea9-de0d-c13a-cbce83902214@postgrespro.ru> Date: Thu, 18 May 2023 16:07:22 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: psql: Add role's membership options to the \du+ command Content-Language: en-US, ru-RU To: "Jonathan S. Katz" , "David G. Johnston" Cc: Tom Lane , Robert Haas , David Zhang , "pgsql-hackers@lists.postgresql.org" , rmt@lists.postgresql.org, horikyota.ntt@gmail.com References: <1525117.1680628353@sss.pgh.pa.us> <1575395.1680629866@sss.pgh.pa.us> <1831161.1680703111@sss.pgh.pa.us> <2977612.1683131109@sss.pgh.pa.us> <39e7431c-1224-ab05-c67c-68f4cf625442@postgrespro.ru> <6b64c59a-fe41-bb07-d0cd-856896412b00@postgresql.org> From: Pavel Luzanov In-Reply-To: <6b64c59a-fe41-bb07-d0cd-856896412b00@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------FdKBWtdEn0wnkx5Q5JsKmq0S Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 18.05.2023 05:42, Jonathan S. Katz wrote: > That said, from a readability standpoint, it was easier for me to > follow the tabular form vs. the sentence form. May be possible to reach a agreement on the sentence form. Similar descriptions used for referential constraints in the \d command: create table t1 (id int primary key);create table t2 (id int references t1(id));\d t2                 Table "public.t2" Column |  Type   | Collation | Nullable | Default --------+---------+-----------+----------+--------- id     | integer |           |          | Foreign-key constraints:    "t2_id_fkey" FOREIGN KEY (id) REFERENCES t1(id)As for tabular form it looks more natural to have a separate psql command for pg_auth_members system catalog. Something based on this query:SELECT r.rolname role, m.rolname member,       admin_option admin, inherit_option inherit, set_option set,       g.rolname grantorFROM pg_catalog.pg_auth_members pam     JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)     JOIN pg_catalog.pg_roles m ON (pam.member = m.oid)     JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)WHERE r.rolname !~ '^pg_'ORDER BY role, member, grantor;       role       |      member      | admin | inherit | set |     grantor ------------------+------------------+-------+---------+-----+------------------ regress_du_role0 | regress_du_admin | t     | t       | t   | postgres regress_du_role0 | regress_du_role1 | t     | t       | t   | regress_du_admin regress_du_role0 | regress_du_role1 | f     | t       | f   | regress_du_role1 regress_du_role0 | regress_du_role1 | f     | f       | t   | regress_du_role2 regress_du_role0 | regress_du_role2 | t     | f       | f   | regress_du_admin regress_du_role0 | regress_du_role2 | f     | t       | t   | regress_du_role1 regress_du_role0 | regress_du_role2 | f     | f       | f   | regress_du_role2 regress_du_role1 | regress_du_admin | t     | t       | t   | postgres regress_du_role1 | regress_du_role2 | t     | f       | t   | regress_du_admin regress_du_role2 | regress_du_admin | t     | t       | t   | postgres(10 rows)But is it worth inventing a new psql command for this? ----- Pavel Luzanov --------------FdKBWtdEn0wnkx5Q5JsKmq0S Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 
On 18.05.2023 05:42, Jonathan S. Katz wrote:

That said, from a readability standpoint, it was easier for me to follow 
the tabular form vs. the sentence form.

May be possible to reach a agreement on the sentence form. Similar descriptions used
for referential constraints in the \d command:

create table t1 (id int primary key);
create table t2 (id int references t1(id));
\d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
Foreign-key constraints:
    "t2_id_fkey" FOREIGN KEY (id) REFERENCES t1(id)


As for tabular form it looks more natural to have a separate psql command
for pg_auth_members system catalog. Something based on this query:

SELECT r.rolname role, m.rolname member,
       admin_option admin, inherit_option inherit, set_option set,
       g.rolname grantor
FROM pg_catalog.pg_auth_members pam
     JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)
     JOIN pg_catalog.pg_roles m ON (pam.member = m.oid)
     JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)
WHERE r.rolname !~ '^pg_'
ORDER BY role, member, grantor;
       role       |      member      | admin | inherit | set |     grantor      
------------------+------------------+-------+---------+-----+------------------
 regress_du_role0 | regress_du_admin | t     | t       | t   | postgres
 regress_du_role0 | regress_du_role1 | t     | t       | t   | regress_du_admin
 regress_du_role0 | regress_du_role1 | f     | t       | f   | regress_du_role1
 regress_du_role0 | regress_du_role1 | f     | f       | t   | regress_du_role2
 regress_du_role0 | regress_du_role2 | t     | f       | f   | regress_du_admin
 regress_du_role0 | regress_du_role2 | f     | t       | t   | regress_du_role1
 regress_du_role0 | regress_du_role2 | f     | f       | f   | regress_du_role2
 regress_du_role1 | regress_du_admin | t     | t       | t   | postgres
 regress_du_role1 | regress_du_role2 | t     | f       | t   | regress_du_admin
 regress_du_role2 | regress_du_admin | t     | t       | t   | postgres
(10 rows)

But is it worth inventing a new psql command for this?

-----
Pavel Luzanov
--------------FdKBWtdEn0wnkx5Q5JsKmq0S--