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 1pEuiH-0007hd-2q for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Jan 2023 16:09:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEuiE-0007fd-Qw for pgsql-hackers@arkaria.postgresql.org; Mon, 09 Jan 2023 16:09:26 +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 1pEuiD-0007fA-VI for pgsql-hackers@lists.postgresql.org; Mon, 09 Jan 2023 16:09:26 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEui9-0002u5-Oq for pgsql-hackers@lists.postgresql.org; Mon, 09 Jan 2023 16:09:24 +0000 Received: from [192.168.0.104] (unknown [62.217.188.29]) (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 BB72421C481C for ; Mon, 9 Jan 2023 19:09:19 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1673280559; bh=K7yI6hdGFMq9R+bxeJhs+Buz0YmNRHghKjG2tSp9Xz0=; h=Date:To:From:Subject; b=IYQ/7cwPOMHKgnVIoROW7Rm+3rq3SQacE+J0FoohnNj+n55ZLZyANqd55cQP615D3 jO6B+qKk8Eck6LxeOK2TdbITj4iYSp9h7VqxQH94ickuY4vUb3lFGHg+kwPHRSxdv0 S7c9N15WnjMmtmmLJi5IkU2wKVyw3Fb71XjNlySE= Content-Type: multipart/mixed; boundary="------------0jSG0C0jrrnjDx0bmzVzldtu" Message-ID: Date: Mon, 9 Jan 2023 19:09:19 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Content-Language: en-US To: "pgsql-hackers@lists.postgresql.org" From: Pavel Luzanov Subject: psql: Add role's membership options to the \du+ command 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. --------------0jSG0C0jrrnjDx0bmzVzldtu Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit When you include one role in another, you can specify three options: ADMIN, INHERIT (added in e3ce2de0) and SET (3d14e171). For example. CREATE ROLE alice LOGIN; GRANT pg_read_all_settings TO alice WITH ADMIN TRUE, INHERIT TRUE, SET TRUE; GRANT pg_stat_scan_tables TO alice WITH ADMIN FALSE, INHERIT FALSE, SET FALSE; GRANT pg_read_all_stats TO alice WITH ADMIN FALSE, INHERIT TRUE, SET FALSE; For information about the options, you need to look in the pg_auth_members: SELECT roleid::regrole, admin_option, inherit_option, set_option FROM pg_auth_members WHERE member = 'alice'::regrole;         roleid        | admin_option | inherit_option | set_option ----------------------+--------------+----------------+------------  pg_read_all_settings | t            | t              | t  pg_stat_scan_tables  | f            | f              | f  pg_read_all_stats    | f            | t              | f (3 rows) I think it would be useful to be able to get this information with a psql command like \du (and \dg). With proposed patch the \du command still only lists the roles of which alice is a member: \du alice                                      List of roles  Role name | Attributes |                          Member of -----------+------------+--------------------------------------------------------------  alice     |            | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} But the \du+ command adds information about the selected ADMIN, INHERIT and SET options: \du+ alice                                     List of roles  Role name | Attributes |                   Member of                   | Description -----------+------------+-----------------------------------------------+-------------  alice     |            | pg_read_all_settings WITH ADMIN, INHERIT, SET+|            |            | pg_read_all_stats WITH INHERIT               +|            |            | pg_stat_scan_tables                           | One more change. The roles in the "Member of" column are sorted for both \du+ and \du for consistent output. Any comments are welcome. -- Pavel Luzanov Postgres Professional: https://postgrespro.com --------------0jSG0C0jrrnjDx0bmzVzldtu Content-Type: text/x-patch; charset=UTF-8; name="add_role_membership_options_to_du.patch" Content-Disposition: attachment; filename="add_role_membership_options_to_du.patch" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9yZWYvcHNxbC1yZWYuc2dtbCBiL2RvYy9zcmMv c2dtbC9yZWYvcHNxbC1yZWYuc2dtbAppbmRleCA4YTUyODVkYTlhLi5lZjNlODdmYTMyIDEw MDY0NAotLS0gYS9kb2Mvc3JjL3NnbWwvcmVmL3BzcWwtcmVmLnNnbWwKKysrIGIvZG9jL3Ny Yy9zZ21sL3JlZi9wc3FsLXJlZi5zZ21sCkBAIC0xNzI0LDkgKzE3MjQsOCBAQCBJTlNFUlQg SU5UTyB0YmwxIFZBTFVFUyAoJDEsICQyKSBcYmluZCAnZmlyc3QgdmFsdWUnICdzZWNvbmQg dmFsdWUnIFxnCiAgICAgICAgIDxsaXRlcmFsPlM8L2xpdGVyYWw+IG1vZGlmaWVyIHRvIGlu Y2x1ZGUgc3lzdGVtIHJvbGVzLgogICAgICAgICBJZiA8cmVwbGFjZWFibGUgY2xhc3M9InBh cmFtZXRlciI+cGF0dGVybjwvcmVwbGFjZWFibGU+IGlzIHNwZWNpZmllZCwKICAgICAgICAg b25seSB0aG9zZSByb2xlcyB3aG9zZSBuYW1lcyBtYXRjaCB0aGUgcGF0dGVybiBhcmUgbGlz dGVkLgotICAgICAgICBJZiB0aGUgZm9ybSA8bGl0ZXJhbD5cZGcrPC9saXRlcmFsPiBpcyB1 c2VkLCBhZGRpdGlvbmFsIGluZm9ybWF0aW9uCi0gICAgICAgIGlzIHNob3duIGFib3V0IGVh Y2ggcm9sZTsgY3VycmVudGx5IHRoaXMgYWRkcyB0aGUgY29tbWVudCBmb3IgZWFjaAotICAg ICAgICByb2xlLgorICAgICAgICBJZiB0aGUgZm9ybSA8bGl0ZXJhbD5cZGcrPC9saXRlcmFs PiBpcyB1c2VkLCBlYWNoIHJvbGUgaXMgbGlzdGVkCisgICAgICAgIHdpdGggaXRzIGFzc29j aWF0ZWQgZGVzY3JpcHRpb24gYW5kIG9wdGlvbnMgZm9yIGVhY2ggcm9sZSdzIG1lbWJlcnNo aXAuCiAgICAgICAgIDwvcGFyYT4KICAgICAgICAgPC9saXN0aXRlbT4KICAgICAgIDwvdmFy bGlzdGVudHJ5PgpAQCAtMTk2NCw5ICsxOTYzLDggQEAgSU5TRVJUIElOVE8gdGJsMSBWQUxV RVMgKCQxLCAkMikgXGJpbmQgJ2ZpcnN0IHZhbHVlJyAnc2Vjb25kIHZhbHVlJyBcZwogICAg ICAgICA8bGl0ZXJhbD5TPC9saXRlcmFsPiBtb2RpZmllciB0byBpbmNsdWRlIHN5c3RlbSBy b2xlcy4KICAgICAgICAgSWYgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPnBhdHRl cm48L3JlcGxhY2VhYmxlPiBpcyBzcGVjaWZpZWQsCiAgICAgICAgIG9ubHkgdGhvc2Ugcm9s ZXMgd2hvc2UgbmFtZXMgbWF0Y2ggdGhlIHBhdHRlcm4gYXJlIGxpc3RlZC4KLSAgICAgICAg SWYgdGhlIGZvcm0gPGxpdGVyYWw+XGR1KzwvbGl0ZXJhbD4gaXMgdXNlZCwgYWRkaXRpb25h bCBpbmZvcm1hdGlvbgotICAgICAgICBpcyBzaG93biBhYm91dCBlYWNoIHJvbGU7IGN1cnJl bnRseSB0aGlzIGFkZHMgdGhlIGNvbW1lbnQgZm9yIGVhY2gKLSAgICAgICAgcm9sZS4KKyAg ICAgICAgSWYgdGhlIGZvcm0gPGxpdGVyYWw+XGR1KzwvbGl0ZXJhbD4gaXMgdXNlZCwgZWFj aCByb2xlIGlzIGxpc3RlZAorICAgICAgICB3aXRoIGl0cyBhc3NvY2lhdGVkIGRlc2NyaXB0 aW9uIGFuZCBvcHRpb25zIGZvciBlYWNoIHJvbGUncyBtZW1iZXJzaGlwLgogICAgICAgICA8 L3BhcmE+CiAgICAgICAgIDwvbGlzdGl0ZW0+CiAgICAgICA8L3Zhcmxpc3RlbnRyeT4KZGlm ZiAtLWdpdCBhL3NyYy9iaW4vcHNxbC9kZXNjcmliZS5jIGIvc3JjL2Jpbi9wc3FsL2Rlc2Ny aWJlLmMKaW5kZXggZGYxNjYzNjVlOC4uYzdiMTBmOTZmMyAxMDA2NDQKLS0tIGEvc3JjL2Jp bi9wc3FsL2Rlc2NyaWJlLmMKKysrIGIvc3JjL2Jpbi9wc3FsL2Rlc2NyaWJlLmMKQEAgLTM2 MjQsMTEgKzM2MjQsMjkgQEAgZGVzY3JpYmVSb2xlcyhjb25zdCBjaGFyICpwYXR0ZXJuLCBi b29sIHZlcmJvc2UsIGJvb2wgc2hvd1N5c3RlbSkKIAlwcmludGZQUUV4cEJ1ZmZlcigmYnVm LAogCQkJCQkgICJTRUxFQ1Qgci5yb2xuYW1lLCByLnJvbHN1cGVyLCByLnJvbGluaGVyaXQs XG4iCiAJCQkJCSAgIiAgci5yb2xjcmVhdGVyb2xlLCByLnJvbGNyZWF0ZWRiLCByLnJvbGNh bmxvZ2luLFxuIgotCQkJCQkgICIgIHIucm9sY29ubmxpbWl0LCByLnJvbHZhbGlkdW50aWws XG4iCi0JCQkJCSAgIiAgQVJSQVkoU0VMRUNUIGIucm9sbmFtZVxuIgotCQkJCQkgICIgICAg ICAgIEZST00gcGdfY2F0YWxvZy5wZ19hdXRoX21lbWJlcnMgbVxuIgotCQkJCQkgICIgICAg ICAgIEpPSU4gcGdfY2F0YWxvZy5wZ19yb2xlcyBiIE9OIChtLnJvbGVpZCA9IGIub2lkKVxu IgotCQkJCQkgICIgICAgICAgIFdIRVJFIG0ubWVtYmVyID0gci5vaWQpIGFzIG1lbWJlcm9m Iik7CisJCQkJCSAgIiAgci5yb2xjb25ubGltaXQsIHIucm9sdmFsaWR1bnRpbCxcbiIpOwor CisJaWYgKHZlcmJvc2UpCisJeworCQlhcHBlbmRQUUV4cEJ1ZmZlclN0cigmYnVmLAorCQkJ CQkJCSAiICAoU0VMRUNUIHN0cmluZ19hZ2cgKHF1b3RlX2lkZW50KGIucm9sbmFtZSkgfHwg cmVnZXhwX3JlcGxhY2UoXG4iCisJCQkJCQkJICIgICAgIENBU0UgV0hFTiBtLmFkbWluX29w dGlvbiBUSEVOICcsIEFETUlOJyBFTFNFICcnIEVORCB8fFxuIgorCQkJCQkJCSAiICAgICBD QVNFIFdIRU4gbS5pbmhlcml0X29wdGlvbiBUSEVOICcsIElOSEVSSVQnIEVMU0UgJycgRU5E IHx8XG4iCisJCQkJCQkJICIgICAgIENBU0UgV0hFTiBtLnNldF9vcHRpb24gVEhFTiAnLCBT RVQnIEVMU0UgJycgRU5ELCBcbiIKKwkJCQkJCQkgIiAgICAgJ14sJywgJyBXSVRIJywgMSks IEUnXFxuJyBPUkRFUiBCWSBiLnJvbG5hbWUpXG4iCisJCQkJCQkJICIgIEZST00gcGdfY2F0 YWxvZy5wZ19hdXRoX21lbWJlcnMgbVxuIgorCQkJCQkJCSAiICBKT0lOIHBnX2NhdGFsb2cu cGdfcm9sZXMgYiBPTiAobS5yb2xlaWQgPSBiLm9pZClcbiIKKwkJCQkJCQkgIiAgV0hFUkUg bS5tZW1iZXIgPSByLm9pZCkgYXMgbWVtYmVyb2YiKTsKKwl9CisJZWxzZQorCXsKKwkJYXBw ZW5kUFFFeHBCdWZmZXJTdHIoJmJ1ZiwKKwkJCQkJCQkgIiAgQVJSQVkoU0VMRUNUIGIucm9s bmFtZVxuIgorCQkJCQkJCSAiICAgICAgICBGUk9NIHBnX2NhdGFsb2cucGdfYXV0aF9tZW1i ZXJzIG1cbiIKKwkJCQkJCQkgIiAgICAgICAgSk9JTiBwZ19jYXRhbG9nLnBnX3JvbGVzIGIg T04gKG0ucm9sZWlkID0gYi5vaWQpXG4iCisJCQkJCQkJICIgICAgICAgIFdIRVJFIG0ubWVt YmVyID0gci5vaWRcbiIKKwkJCQkJCQkgIiAgICAgICAgT1JERVIgQlkgYi5yb2xuYW1lKSBh cyBtZW1iZXJvZiIpOworCX0KIAogCWlmICh2ZXJib3NlKQogCXsKZGlmZiAtLWdpdCBhL3Ny Yy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcHNxbC5vdXQgYi9zcmMvdGVzdC9yZWdyZXNzL2V4 cGVjdGVkL3BzcWwub3V0CmluZGV4IDhmYzYyY2ViZDIuLjNhMmViNjU4NTAgMTAwNjQ0Ci0t LSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvcHNxbC5vdXQKKysrIGIvc3JjL3Rlc3Qv cmVncmVzcy9leHBlY3RlZC9wc3FsLm91dApAQCAtNjU3OCwzICs2NTc4LDI0IEBAIGNyb3Nz LWRhdGFiYXNlIHJlZmVyZW5jZXMgYXJlIG5vdCBpbXBsZW1lbnRlZDogIm5vLnN1Y2guZGF0 YWJhc2UiLiJuby5zdWNoLnNjaGVtCiBjcm9zcy1kYXRhYmFzZSByZWZlcmVuY2VzIGFyZSBu b3QgaW1wbGVtZW50ZWQ6ICJuby5zdWNoLmRhdGFiYXNlIi4ibm8uc3VjaC5zY2hlbWEiLiJu by5zdWNoLmRhdGEudHlwZSIKIFxkWCAibm8uc3VjaC5kYXRhYmFzZSIuIm5vLnN1Y2guc2No ZW1hIi4ibm8uc3VjaC5leHRlbmRlZC5zdGF0aXN0aWNzIgogY3Jvc3MtZGF0YWJhc2UgcmVm ZXJlbmNlcyBhcmUgbm90IGltcGxlbWVudGVkOiAibm8uc3VjaC5kYXRhYmFzZSIuIm5vLnN1 Y2guc2NoZW1hIi4ibm8uc3VjaC5leHRlbmRlZC5zdGF0aXN0aWNzIgorLS0gY2hlY2sgXGR1 CitDUkVBVEUgUk9MRSByZWdyZXNzX2R1IExPR0lOOworQ09NTUVOVCBPTiBST0xFIHJlZ3Jl c3NfZHUgSVMgJ0Rlc2NyaXB0aW9uIGZvciByZWdyZXNzX2R1IHJvbGUnOworR1JBTlQgcGdf cmVhZF9hbGxfc2V0dGluZ3MgVE8gcmVncmVzc19kdSBXSVRIIEFETUlOIFRSVUUsIElOSEVS SVQgVFJVRSwgU0VUIFRSVUU7CitHUkFOVCBwZ19zdGF0X3NjYW5fdGFibGVzIFRPIHJlZ3Jl c3NfZHUgV0lUSCBBRE1JTiBGQUxTRSwgSU5IRVJJVCBGQUxTRSwgU0VUIEZBTFNFOworR1JB TlQgcGdfcmVhZF9hbGxfc3RhdHMgVE8gcmVncmVzc19kdSBXSVRIIEFETUlOIEZBTFNFLCBJ TkhFUklUIFRSVUUsIFNFVCBGQUxTRTsKK1xkdSByZWdyZXNzX2R1CisgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgTGlzdCBvZiByb2xlcworIFJvbGUgbmFtZSAgfCBB dHRyaWJ1dGVzIHwgICAgICAgICAgICAgICAgICAgICAgICAgIE1lbWJlciBvZiAgICAgICAg ICAgICAgICAgICAgICAgICAgIAorLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLSstLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LQorIHJlZ3Jlc3NfZHUgfCAgICAgICAgICAgIHwge3BnX3JlYWRfYWxsX3NldHRpbmdzLHBn X3JlYWRfYWxsX3N0YXRzLHBnX3N0YXRfc2Nhbl90YWJsZXN9CisKK1xkdSsgcmVncmVzc19k dQorICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBMaXN0 IG9mIHJvbGVzCisgUm9sZSBuYW1lICB8IEF0dHJpYnV0ZXMgfCAgICAgICAgICAgICAgICAg ICBNZW1iZXIgb2YgICAgICAgICAgICAgICAgICAgfCAgICAgICAgICAgRGVzY3JpcHRpb24g ICAgICAgICAgIAorLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0KKyByZWdyZXNzX2R1IHwgICAgICAgICAgICB8IHBnX3JlYWRfYWxsX3Nl dHRpbmdzIFdJVEggQURNSU4sIElOSEVSSVQsIFNFVCt8IERlc2NyaXB0aW9uIGZvciByZWdy ZXNzX2R1IHJvbGUKKyAgICAgICAgICAgIHwgICAgICAgICAgICB8IHBnX3JlYWRfYWxsX3N0 YXRzIFdJVEggSU5IRVJJVCAgICAgICAgICAgICAgICt8IAorICAgICAgICAgICAgfCAgICAg ICAgICAgIHwgcGdfc3RhdF9zY2FuX3RhYmxlcyAgICAgICAgICAgICAgICAgICAgICAgICAg IHwgCisKK0RST1AgUk9MRSByZWdyZXNzX2R1OwpkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVn cmVzcy9zcWwvcHNxbC5zcWwgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9wc3FsLnNxbAppbmRl eCAyZGE5NjY1YTE5Li42YTVhNTdhYjcyIDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNz L3NxbC9wc3FsLnNxbAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL3NxbC9wc3FsLnNxbApAQCAt MTc5MSwzICsxNzkxLDE0IEBAIERST1AgRlVOQ1RJT04gcHNxbF9lcnJvcjsKIFxkUCAibm8u c3VjaC5kYXRhYmFzZSIuIm5vLnN1Y2guc2NoZW1hIi4ibm8uc3VjaC5wYXJ0aXRpb25lZC5y ZWxhdGlvbiIKIFxkVCAibm8uc3VjaC5kYXRhYmFzZSIuIm5vLnN1Y2guc2NoZW1hIi4ibm8u c3VjaC5kYXRhLnR5cGUiCiBcZFggIm5vLnN1Y2guZGF0YWJhc2UiLiJuby5zdWNoLnNjaGVt YSIuIm5vLnN1Y2guZXh0ZW5kZWQuc3RhdGlzdGljcyIKKworLS0gY2hlY2sgXGR1CitDUkVB VEUgUk9MRSByZWdyZXNzX2R1IExPR0lOOworQ09NTUVOVCBPTiBST0xFIHJlZ3Jlc3NfZHUg SVMgJ0Rlc2NyaXB0aW9uIGZvciByZWdyZXNzX2R1IHJvbGUnOworR1JBTlQgcGdfcmVhZF9h bGxfc2V0dGluZ3MgVE8gcmVncmVzc19kdSBXSVRIIEFETUlOIFRSVUUsIElOSEVSSVQgVFJV RSwgU0VUIFRSVUU7CitHUkFOVCBwZ19zdGF0X3NjYW5fdGFibGVzIFRPIHJlZ3Jlc3NfZHUg V0lUSCBBRE1JTiBGQUxTRSwgSU5IRVJJVCBGQUxTRSwgU0VUIEZBTFNFOworR1JBTlQgcGdf cmVhZF9hbGxfc3RhdHMgVE8gcmVncmVzc19kdSBXSVRIIEFETUlOIEZBTFNFLCBJTkhFUklU IFRSVUUsIFNFVCBGQUxTRTsKKworXGR1IHJlZ3Jlc3NfZHUKK1xkdSsgcmVncmVzc19kdQor RFJPUCBST0xFIHJlZ3Jlc3NfZHU7Cg== --------------0jSG0C0jrrnjDx0bmzVzldtu--