From: | richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: how to list privileges on the database object itself via SQL? |
Date: | 2023-04-26 17:50:18 |
Message-ID: | CAGA3vBsPQ8672wf99EGGWDAG4GBTcOpiJeCrJOypQ1sGG02Bvw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | MichaelDBA | 2023-04-26 17:53:06 | Am I being blacklisted? |
Previous Message | MichaelDBA | 2023-04-26 17:35:26 | Re: how to list privileges on the database object itself via SQL? |