Re: how to list privileges on the database object itself via SQL?
От | Inzamam Shafiq |
---|---|
Тема | Re: how to list privileges on the database object itself via SQL? |
Дата | |
Msg-id | AM9P251MB0330FD7CD9D100076F0879AD986B9@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM обсуждение исходный текст |
Ответ на | Re: how to list privileges on the database object itself via SQL? (richard coleman <rcoleman.ascentgl@gmail.com>) |
Ответы |
Re: how to list privileges on the database object itself via SQL?
(richard coleman <rcoleman.ascentgl@gmail.com>)
|
Список | pgsql-admin |
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='table_name';
Sent: Friday, April 28, 2023 5:18 PM
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: how to list privileges on the database object itself via SQL?
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
В списке pgsql-admin по дате отправления:
Предыдущее
От: richard colemanДата:
Сообщение: Re: how to list privileges on the database object itself via SQL?
Следующее
От: jian heДата:
Сообщение: Re: how to list privileges on the database object itself via SQL?