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 1pSyVG-0000EQ-Ij for pgsql-hackers@arkaria.postgresql.org; Fri, 17 Feb 2023 11:02:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSyVF-0000Tg-Bi for pgsql-hackers@arkaria.postgresql.org; Fri, 17 Feb 2023 11:02:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pSyVE-0000TX-S8 for pgsql-hackers@lists.postgresql.org; Fri, 17 Feb 2023 11:02:09 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pSyVA-0001bU-Qw for pgsql-hackers@lists.postgresql.org; Fri, 17 Feb 2023 11:02:07 +0000 Received: from [192.168.28.30] (cyclops.postgrespro.ru [93.174.131.138]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id E8F5C21C2636; Fri, 17 Feb 2023 14:02:01 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1676631721; bh=5tKYZnwTL4aYbHCZbC5aWcCmO/5iHVke1TALrFbrlSc=; h=Date:Subject:From:To:Cc:References:In-Reply-To; b=IDcVQ8/b81Iea98WMZBpXvlu7062XtjJkMVVAqIu0+oNukzCDUXOov7DrvyOUXffl KziJgFO1e5yphVd6B9bWjFDRBl8s/OHeKjWNy2dCKNyvN6xsxP0ip5sNiTj/hU1pyc c/A/KZUwdkumMgepfiUuxLLJegvZ0xOGytfZSIGY= Content-Type: multipart/alternative; boundary="------------EBG3ohOK2ll1zG8Z3ssdNLx0" Message-ID: Date: Fri, 17 Feb 2023 14:02:01 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: psql: Add role's membership options to the \du+ command Content-Language: en-US, ru-RU From: Pavel Luzanov To: "David G. Johnston" , David Zhang Cc: "pgsql-hackers@lists.postgresql.org" References: <01620d6b-9efe-b199-b619-8450d865ec83@postgrespro.ru> In-Reply-To: <01620d6b-9efe-b199-b619-8450d865ec83@postgrespro.ru> 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. --------------EBG3ohOK2ll1zG8Z3ssdNLx0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hello, > On the one hand, it would be nice to see the membership options with > the psql command. After playing with cf5eb37c and e5b8a4c0 I think something must be made with \du command. postgres@demo(16.0)=# CREATE ROLE admin LOGIN CREATEROLE; CREATE ROLE postgres@demo(16.0)=# \c - admin You are now connected to database "demo" as user "admin". admin@demo(16.0)=> SET createrole_self_grant = 'SET, INHERIT'; SET admin@demo(16.0)=> CREATE ROLE bob LOGIN; CREATE ROLE admin@demo(16.0)=> \du                                    List of roles  Role name | Attributes                         | Member of -----------+------------------------------------------------------------+-----------  admin     | Create role                                                | {bob,bob}  bob |                                                            | {}  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {} We see two bob roles in the 'Member of' column.Strange? But this is correct. admin@demo(16.0)=> select roleid::regrole, member::regrole, * from pg_auth_members where roleid = 'bob'::regrole;  roleid | member |  oid  | roleid | member | grantor | admin_option | inherit_option | set_option --------+--------+-------+--------+--------+---------+--------------+----------------+------------  bob    | admin  | 16713 |  16712 |  16711 |      10 | t            | f              | f  bob    | admin  | 16714 |  16712 |  16711 |   16711 | f            | t              | t (2 rows) First 'grant bob to admin' command issued immediately after creating role bob by superuser(grantor=10). Second command issues by admin role and set membership options SET and INHERIT. If we don't ready to display membership options with \du+ may be at least we must group records in 'Member of' column for \du command? ----- Pavel Luzanov --------------EBG3ohOK2ll1zG8Z3ssdNLx0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Hello,
On the one hand, it would be nice to see the membership options with the psql command.

After playing with cf5eb37c and e5b8a4c0 I think something must be made with \du command.

postgres@demo(16.0)=# CREATE ROLE admin LOGIN CREATEROLE;
CREATE ROLE
postgres@demo(16.0)=# \c - admin
You are now connected to database "demo" as user "admin".
admin@demo(16.0)=> SET createrole_self_grant = 'SET, INHERIT';
SET
admin@demo(16.0)=> CREATE ROLE bob LOGIN;
CREATE ROLE
admin@demo(16.0)=> \du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 admin     | Create role                                                | {bob,bob}
 bob       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

We see two bob roles in the 'Member of' column. Strange? But this is correct.

admin@demo(16.0)=> select roleid::regrole, member::regrole, * from pg_auth_members where roleid = 'bob'::regrole;
 roleid | member |  oid  | roleid | member | grantor | admin_option | inherit_option | set_option
--------+--------+-------+--------+--------+---------+--------------+----------------+------------
 bob    | admin  | 16713 |  16712 |  16711 |      10 | t            | f              | f
 bob    | admin  | 16714 |  16712 |  16711 |   16711 | f            | t              | t
(2 rows)

First 'grant bob to admin' command issued immediately after creating role bob by superuser(grantor=10). Second command issues by admin role and set membership options SET and INHERIT.

If we don't ready to display membership options with \du+ may be at least we must group records in 'Member of' column for \du command?
 
-----
Pavel Luzanov
--------------EBG3ohOK2ll1zG8Z3ssdNLx0--