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 1prjMs-0005B2-0O for pgsql-admin@arkaria.postgresql.org; Wed, 26 Apr 2023 17:55:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1prjMq-0007Uj-Mq for pgsql-admin@arkaria.postgresql.org; Wed, 26 Apr 2023 17:55:48 +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 1prjMq-0007UY-CH for pgsql-admin@lists.postgresql.org; Wed, 26 Apr 2023 17:55:48 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1prjMo-005DLc-52 for pgsql-admin@lists.postgresql.org; Wed, 26 Apr 2023 17:55:47 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id D1F4B380353; Wed, 26 Apr 2023 19:55:44 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id AaLGpP82MLKC; Wed, 26 Apr 2023 19:55:44 +0200 (CEST) Received: from [192.168.137.86] (dynamic-046-114-155-221.46.114.pool.telefonica.de [46.114.155.221]) by rs.plausibolo.de (Postfix) with ESMTPSA id 74F3038020B; Wed, 26 Apr 2023 19:55:44 +0200 (CEST) Message-ID: Date: Wed, 26 Apr 2023 19:55:43 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: how to list privileges on the database object itself via SQL? Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: <2058602074.1048315.1682529431551@office.mailbox.org> From: Holger Jakobs In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------KJZT171yhV4dEYvW7FJstEh3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------KJZT171yhV4dEYvW7FJstEh3 Content-Type: multipart/mixed; boundary="------------ahXELjKTzw06iCaiRWOJfY9e"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: how to list privileges on the database object itself via SQL? References: <2058602074.1048315.1682529431551@office.mailbox.org> In-Reply-To: --------------ahXELjKTzw06iCaiRWOJfY9e Content-Type: multipart/alternative; boundary="------------s6fcqzUq0GZrobUKanEkXsYo" --------------s6fcqzUq0GZrobUKanEkXsYo Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMjYuMDQuMjMgdW0gMTk6NTMgc2NocmllYiByaWNoYXJkIGNvbGVtYW46DQo+IEVyaWss DQo+DQo+IFRoYW5rcyBmb3IgdGhhdC7CoCBJJ2xsIGhhdmUgdG8gbG9vayBpbnRvIHRoZSBh Y2xleHBsb2RlKCkgZnVuY3Rpb24gDQo+IHNvbWUgbW9yZS4NCj4NCj4gV2hlbiBJIHRyeSBy dW5uaW5nIHlvdXIgU1FMIG9uIGEgcGcxMSBkYXRhYmFzZSBJIGdldDoNCj4NCj4gRVJST1I6 IMKgcm9sZSAicm9sZSIgZG9lcyBub3QgZXhpc3QNCj4gTElORSAzOiDCoCDCoCDCoCDCoCBX SEVSRSBncmFudGVlID0gJ3JvbGUnOjpyZWdyb2xlIEFORCBwcml2aWxlZ2VfdHlwZSBJLi4u DQo+IMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIF4N Cj4gU1FMIHN0YXRlOiA0MjcwNA0KPiBDaGFyYWN0ZXI6IDE1MQ0KPg0KPiByaWsuDQo+DQpJ ZiB5b3Ugd2FudCB0byB1c2UgdGhlIFNRTCBzdGF0ZW1lbnRzIHVzZWQgYnkgcHNxbCBvbiBv bGRlciB2ZXJzaW9ucyBvZiANClBvc3RncmVTUUwsIHlvdSBzaG91bGQgdXNlIHRoZSBvbGQg dmVyc2lvbiBvZiBwc3FsLg0KPg0KPg0KPg0KLS0gDQpIb2xnZXIgSmFrb2JzLCBCZXJnaXNj aCBHbGFkYmFjaCwgVGVsLiArNDktMTc4LTk3NTkwMTINCg0K --------------s6fcqzUq0GZrobUKanEkXsYo Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 26.04.23 um 19:53 schrieb richard coleman:
Erik,=C2=A0

Thanks for that.=C2=A0 I'll have to look into the aclexplode= () function some more.

When I try running your SQL on a pg11 database I get:

ERROR: =C2=A0role "role" does not exist
LINE 3: =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE grantee =3D 'role'::r= egrole AND privilege_type I...
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ^
SQL state: 42704
Character: 151

rik.

If you want to use the SQL statements used by psql on older versions of PostgreSQL, you should use the old version of psql.
= 

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------s6fcqzUq0GZrobUKanEkXsYo-- --------------ahXELjKTzw06iCaiRWOJfY9e-- --------------KJZT171yhV4dEYvW7FJstEh3 Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZEllnwUDAAAAAAAKCRCKV5yTsxlhuvM+ AKCmp5u5Dk4zgHUK/Sa7j2phGX9VkgCbBnp0bXHscCtJM8jFqI3fz6/g7mY= =ynd/ -----END PGP SIGNATURE----- --------------KJZT171yhV4dEYvW7FJstEh3--