how to list privileges on the database object itself via SQL?

These are the two I use:

SELECT  r.rolname

                              , ARRAY(SELECT b.rolname

        FROM pg_catalog.pg_auth_members m

        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)

        WHERE m.member = r.oid) as memberof

                              , r.rolinherit

                              , r.rolcanlogin

                              , j.privilege_type

                              , j.table_name

                              , j.grantor

FROM pg_catalog.pg_roles r

JOIN (SELECT * FROM information_schema.table_privileges

WHERE grantee ilike '%_app' or grantee ilike '%_ddl' or grantee ilike '%only') j ON j.grantee = r.rolname

WHERE r.rolname !~ '^pg_' AND r.rolname iLIKE '%_app' OR r.rolname ilike '%_ddl' or r.rolname ilike '%only'

ORDER BY 1,6,4,5,7 DESC;

-- This query will return all permissions (last two columns) for:

-- VIEW (v), MATERIALIZED_VIEW (m), and INDEX (i)

-- If you need other relkind types, just add to the IN CLAUSE

SELECT c.relname

, n.nspname

, c.relkind

, n.nspacl

, c.relacl

FROM pg_catalog.pg_class c

     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

--            AND n.nspname ilike 'foo'

ORDER BY 1,2,3

;

From: richard coleman <rcoleman.ascentgl@gmail.com>
Sent: Friday, April 28, 2023 7:19 AM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: how to list privileges on the database object itself via SQL?

Mathew, 

Thanks for the advice. I am aware of pgAdmin4, dBeaver, etc.  but I was looking for an SQL solution.

rik.

On Wed, Apr 26, 2023 at 5:28 PM Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com> wrote:

May I suggest PgAdmin GUI

From: richard coleman <rcoleman.ascentgl@gmail.com>
Sent: Wednesday, April 26, 2023 12:50 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: how to list privileges on the database object itself via SQL?

Tom, 

Thanks for that.  It still seems rather weird that there isn't a more straightforward way to get access to that information.

Also the SQL generated by psql -E doesn't seem to work on earlier versions of PostgreSQL:

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",

-- start this section works in pg15, but not in pg11
       d.daticulocale as "ICU Locale",
       CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS "Locale Provider",

-- end this section works in pg15, but not in pg11
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

Even then, the results are a potentially very long concatenated string, or originally an array, in the "Access privileges" column.

Are you sure there isn't a more straightforward way to access this information?  Are you saying that the only place this information is stored is in an array in the datacl column of the pg_catalog.pg_database table?

If that's the case then I am going to be forced to either write code to parse out that array, or write a looping union of multiple "has_database_privilege()" calls.

Either case seems like overkill to get such basic information out of PostgreSQL....

rik.

On Wed, Apr 26, 2023 at 1:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Thanks, but no.  I am looking for the SQL statement.
> I very rarely venture into psql, unless it's to run an SQL code block from
> the terminal.
> Is there an SQL way to do this?

psql is still a useful reference.  Run it with the -E option and
look at the SQL it issues when you say "\l".  Trim to fit your
requirements.

                        regards, tom lane

From: richard coleman <rcoleman.ascentgl@gmail.com>
Sent: Friday, April 28, 2023 7:19 AM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: how to list privileges on the database object itself via SQL?