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

Inzamam,

Thanks, but no. That would provide information on tables, I was looking
for information on the database itself.

fortunately, others have already given me plenty of suggestion (and SQL :)
) to get me well on my way there.

rik.

On Fri, Apr 28, 2023 at 8:27 AM Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)com>
wrote:

> are you looking for a solution like this?
>
> SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='table_name';
>
>
> Regards,
>
> *Inzamam Shafiq*
> *Sr. DBA*
> ------------------------------
> *From:* richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)com>
> *Sent:* Friday, April 28, 2023 5:18 PM
> *To:* Wetmore, Matthew (CTR) <Matthew(dot)Wetmore(at)express-scripts(dot)com>
> *Cc:* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-admin(at)lists(dot)postgresql(dot)org <
> pgsql-admin(at)lists(dot)postgresql(dot)org>
> *Subject:* 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(dot)Wetmore(at)express-scripts(dot)com> wrote:
>
> May I suggest PgAdmin GUI
>
>
>
> *From:* richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)com>
> *Sent:* Wednesday, April 26, 2023 12:50 PM
> *To:* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)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
>
>