pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to list privileges on the database object itself via SQL?
26+ messages / 10 participants
[nested] [flat]
* how to list privileges on the database object itself via SQL?
@ 2023-04-26 16:34 richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:17 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
2023-04-28 12:36 ` Re: how to list privileges on the database object itself via SQL? jian he <jian.universality@gmail.com>
0 siblings, 3 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 16:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
All,
This might sound like a silly question, but how would I list the privileges
the various roles have on the database objects themselves?
There appear to be a million ways to list the privileges on various
database objects; tables, views, foreign tables, etc. but for the life of
me there doesn't appear to an analogous way to get permissions on the
database objects themselves.
At the moment all that I've found is:
select has_database_privilege('role','db_name', 'CREATE') as can_create;
select has_database_privilege('role','db_name', 'CONNECT') as can_connect;
select has_database_privilege('role','db_name', 'TEMP') as can_temp;
Am I missing something?
Thanks,
rik.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2023-04-26 16:40 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> This might sound like a silly question, but how would I list the privileges
> the various roles have on the database objects themselves?
Perhaps you are looking for psql's \l command? It's a bit
nonstandardly named (one would expect \d something)
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-26 17:20 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:35 ` Re: how to list privileges on the database object itself via SQL? MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 2 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 17:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
Tom,
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?
Thanks again,
rik.
On Wed, Apr 26, 2023 at 12:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> richard coleman <rcoleman.ascentgl@gmail.com> writes:
> > This might sound like a silly question, but how would I list the
> privileges
> > the various roles have on the database objects themselves?
>
> Perhaps you are looking for psql's \l command? It's a bit
> nonstandardly named (one would expect \d something)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 17:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Tom Lane @ 2023-04-26 17:22 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-26 17:50 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:05 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 18:22 ` Re: how to list privileges on the database object itself via SQL? Holger Jakobs <holger@jakobs.com>
2023-04-26 21:28 ` how to list privileges on the database object itself via SQL? Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-04-28 13:25 ` Re: how to list privileges on the database object itself via SQL? Joe Conway <mail@joeconway.com>
0 siblings, 4 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 17:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 18:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 18:07 ` Re: how to list privileges on the database object itself via SQL? Holger Jakobs <holger@jakobs.com>
2023-04-26 18:28 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
3 siblings, 2 replies; 26+ messages in thread
From: Tom Lane @ 2023-04-26 18:05 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Thanks for that. It still seems rather weird that there isn't a more
> straightforward way to get access to that information.
You could just read the system catalog documentation:
https://www.postgresql.org/docs/current/catalogs.html
psql's queries are mostly useful as a shortcut to finding out
where to look.
> Also the SQL generated by psql -E doesn't seem to work on earlier versions
> of PostgreSQL:
Yeah, you'd need to try it against a server of the vintage you
care about. (Also read the documentation of the correct version.)
> 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?
Precisely.
> 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.
You were already pointed at aclexplode(), which might help.
select datname, a.* from pg_database, aclexplode(datacl) a;
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:05 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-26 18:07 ` Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 26+ messages in thread
From: Holger Jakobs @ 2023-04-26 18:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 26.04.23 um 20:05 schrieb Tom Lane:
> richard coleman <rcoleman.ascentgl@gmail.com> writes:
>> Thanks for that. It still seems rather weird that there isn't a more
>> straightforward way to get access to that information.
> You could just read the system catalog documentation:
>
> https://www.postgresql.org/docs/current/catalogs.html
>
> psql's queries are mostly useful as a shortcut to finding out
> where to look.
>
>> Also the SQL generated by psql -E doesn't seem to work on earlier versions
>> of PostgreSQL:
> Yeah, you'd need to try it against a server of the vintage you
> care about. (Also read the documentation of the correct version.)
>
>> 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?
> Precisely.
>
>> 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.
> You were already pointed at aclexplode(), which might help.
>
> select datname, a.* from pg_database, aclexplode(datacl) a;
>
> regards, tom lane
>
\df+ aclexplode
in psql will show you more information about the function, which might
help in make good use of it.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../df6be05b-60ca-bb2c-4122-48dc03846837@jakobs.com/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:05 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-04-26 18:28 ` richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 18:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
Tom,
Thanks for your, and everyone else that's contributed's help.
It's good to know where to look, even though I wish it was as simple as
getting the list of role permissions on a table.
rik.
On Wed, Apr 26, 2023 at 2:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> richard coleman <rcoleman.ascentgl@gmail.com> writes:
> > Thanks for that. It still seems rather weird that there isn't a more
> > straightforward way to get access to that information.
>
> You could just read the system catalog documentation:
>
> https://www.postgresql.org/docs/current/catalogs.html
>
> psql's queries are mostly useful as a shortcut to finding out
> where to look.
>
> > Also the SQL generated by psql -E doesn't seem to work on earlier
> versions
> > of PostgreSQL:
>
> Yeah, you'd need to try it against a server of the vintage you
> care about. (Also read the documentation of the correct version.)
>
> > 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?
>
> Precisely.
>
> > 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.
>
> You were already pointed at aclexplode(), which might help.
>
> select datname, a.* from pg_database, aclexplode(datacl) a;
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 18:22 ` Holger Jakobs <holger@jakobs.com>
2023-04-26 18:30 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
3 siblings, 1 reply; 26+ messages in thread
From: Holger Jakobs @ 2023-04-26 18:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Try this command:
with cte as (select oid, datname, (aclexplode(datacl)).* from
pg_database where oid>1)
select cte.oid, datname, grantor.rolname as grantorname,
coalesce(grantee.rolname, 'public') as granteename, privilege_type,
is_grantable
from cte
join pg_authid grantor on cte.grantor = grantor.oid
left join pg_authid grantee on cte.grantee = grantee.oid
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../5d4cc93e-cea0-6b99-53d3-2f0c7a3d6a59@jakobs.com/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:22 ` Re: how to list privileges on the database object itself via SQL? Holger Jakobs <holger@jakobs.com>
@ 2023-04-26 18:30 ` richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 18:30 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
Holger,
Thanks, that looks very good.
rik.
On Wed, Apr 26, 2023 at 2:23 PM Holger Jakobs <holger@jakobs.com> wrote:
> Try this command:
>
> with cte as (select oid, datname, (aclexplode(datacl)).* from
> pg_database where oid>1)
> select cte.oid, datname, grantor.rolname as grantorname,
> coalesce(grantee.rolname, 'public') as granteename, privilege_type,
> is_grantable
> from cte
> join pg_authid grantor on cte.grantor = grantor.oid
> left join pg_authid grantee on cte.grantee = grantee.oid
>
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 21:28 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-04-28 12:18 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
3 siblings, 1 reply; 26+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-04-26 21:28 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:tgl@sss.pgh.pa.us>> wrote:
richard coleman <rcoleman.ascentgl@gmail.com<mailto: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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 21:28 ` how to list privileges on the database object itself via SQL? Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2023-04-28 12:18 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-28 12:27 ` Re: how to list privileges on the database object itself via SQL? Inzamam Shafiq <inzamam.shafiq@hotmail.com>
2023-04-28 13:06 ` how to list privileges on the database object itself via SQL? Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
0 siblings, 2 replies; 26+ messages in thread
From: richard coleman @ 2023-04-28 12:18 UTC (permalink / raw)
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>
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
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 21:28 ` how to list privileges on the database object itself via SQL? Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-04-28 12:18 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-28 12:27 ` Inzamam Shafiq <inzamam.shafiq@hotmail.com>
2023-04-28 14:44 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Inzamam Shafiq @ 2023-04-28 12:27 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; 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>
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.ascentgl@gmail.com>
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?
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<mailto:Matthew.Wetmore@express-scripts.com>> wrote:
May I suggest PgAdmin GUI
From: richard coleman <rcoleman.ascentgl@gmail.com<mailto:rcoleman.ascentgl@gmail.com>>
Sent: Wednesday, April 26, 2023 12:50 PM
To: Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>>
Cc: pgsql-admin@lists.postgresql.org<mailto: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<mailto:tgl@sss.pgh.pa.us>> wrote:
richard coleman <rcoleman.ascentgl@gmail.com<mailto: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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 21:28 ` how to list privileges on the database object itself via SQL? Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-04-28 12:18 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-28 12:27 ` Re: how to list privileges on the database object itself via SQL? Inzamam Shafiq <inzamam.shafiq@hotmail.com>
@ 2023-04-28 14:44 ` richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: richard coleman @ 2023-04-28 14:44 UTC (permalink / raw)
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>; +Cc: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.shafiq@hotmail.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.ascentgl@gmail.com>
> *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?
>
> 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
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 21:28 ` how to list privileges on the database object itself via SQL? Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-04-28 12:18 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-28 13:06 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
1 sibling, 0 replies; 26+ messages in thread
From: Wetmore, Matthew (CTR) @ 2023-04-28 13:06 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:Matthew.Wetmore@express-scripts.com>> wrote:
May I suggest PgAdmin GUI
From: richard coleman <rcoleman.ascentgl@gmail.com<mailto:rcoleman.ascentgl@gmail.com>>
Sent: Wednesday, April 26, 2023 12:50 PM
To: Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>>
Cc: pgsql-admin@lists.postgresql.org<mailto: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<mailto:tgl@sss.pgh.pa.us>> wrote:
richard coleman <rcoleman.ascentgl@gmail.com<mailto: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?
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<mailto:Matthew.Wetmore@express-scripts.com>> wrote:
May I suggest PgAdmin GUI
From: richard coleman <rcoleman.ascentgl@gmail.com<mailto:rcoleman.ascentgl@gmail.com>>
Sent: Wednesday, April 26, 2023 12:50 PM
To: Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>>
Cc: pgsql-admin@lists.postgresql.org<mailto: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<mailto:tgl@sss.pgh.pa.us>> wrote:
richard coleman <rcoleman.ascentgl@gmail.com<mailto: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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-28 13:25 ` Joe Conway <mail@joeconway.com>
2023-05-01 04:09 ` Re: how to list privileges on the database object itself via SQL? Laurenz Albe <laurenz.albe@cybertec.at>
3 siblings, 1 reply; 26+ messages in thread
From: Joe Conway @ 2023-04-28 13:25 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
On 4/26/23 13:50, richard coleman wrote:
> Thanks for that. It still seems rather weird that there isn't a more
> straightforward way to get access to that information.
Does this help?
https://github.com/CrunchyData/crunchy_check_access
For some examples of use:
https://www.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-28 13:25 ` Re: how to list privileges on the database object itself via SQL? Joe Conway <mail@joeconway.com>
@ 2023-05-01 04:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 26+ messages in thread
From: Laurenz Albe @ 2023-05-01 04:09 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; richard coleman <rcoleman.ascentgl@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
On Fri, 2023-04-28 at 09:25 -0400, Joe Conway wrote:
> On 4/26/23 13:50, richard coleman wrote:
> > Thanks for that. It still seems rather weird that there isn't a more
> > straightforward way to get access to that information.
>
> Does this help?
>
> https://github.com/CrunchyData/crunchy_check_access
>
> For some examples of use:
> https://www.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1
There is also pg_permissions:
https://github.com/cybertec-postgresql/pg_permissions
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 17:35 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-04-26 17:57 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: MichaelDBA @ 2023-04-26 17:35 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
Here's a teaser:
select c.relnamespace::regnamespace as schema, c.relname as tablename ,
pg_catalog.array_to_string(c.relacl, E'\n') AS privileges,
CASE WHEN c.relkind = 'r' THEN 'TABLE' WHEN c.relkind = 'S' THEN
'SEQUENCE' WHEN c.relkind = 'v' THEN 'VIEW' WHEN c.relkind = 'm' THEN
'MATVIEW' WHEN c.relkind = 'p' THEN 'PARTITIONED TABLE' END as object_type
FROM pg_class c WHERE c.relnamespace::regnamespace::text not in
('pg_catalog','information_schema') AND c.relkind IN
('r','v','m','S','p') ORDER BY 1,2;
Regards,
Michael Vitale
richard coleman wrote on 4/26/2023 1:20 PM:
> Tom,
>
> 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?
>
> Thanks again,
> rik.
>
> On Wed, Apr 26, 2023 at 12:40 PM Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
> richard coleman <rcoleman.ascentgl@gmail.com
> <mailto:rcoleman.ascentgl@gmail.com>> writes:
> > This might sound like a silly question, but how would I list the
> privileges
> > the various roles have on the database objects themselves?
>
> Perhaps you are looking for psql's \l command? It's a bit
> nonstandardly named (one would expect \d something)
>
> regards, tom lane
>
Regards,
Michael Vitale
Michaeldba@sqlexec.com <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../e11c31b9-858c-a69d-7f20-f68dc5be6a6e@sqlexec.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Re: how to list privileges on the database object itself via SQL? Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:35 ` Re: how to list privileges on the database object itself via SQL? MichaelDBA <MichaelDBA@sqlexec.com>
@ 2023-04-26 17:57 ` richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 17:57 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
Michael,
Thanks, but that lists privs in tables. There are lots and lots of ways to
get table privs.
What I am looking for is a simple way to get the privs on the database
object itself.
rik.
On Wed, Apr 26, 2023 at 1:35 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> Here's a teaser:
>
> select c.relnamespace::regnamespace as schema, c.relname as tablename ,
> pg_catalog.array_to_string(c.relacl, E'\n') AS privileges,
> CASE WHEN c.relkind = 'r' THEN 'TABLE' WHEN c.relkind = 'S' THEN
> 'SEQUENCE' WHEN c.relkind = 'v' THEN 'VIEW' WHEN c.relkind = 'm' THEN
> 'MATVIEW' WHEN c.relkind = 'p' THEN 'PARTITIONED TABLE' END as object_type
> FROM pg_class c WHERE c.relnamespace::regnamespace::text not in
> ('pg_catalog','information_schema') AND c.relkind IN ('r','v','m','S','p')
> ORDER BY 1,2;
>
> Regards,
> Michael Vitale
>
> richard coleman wrote on 4/26/2023 1:20 PM:
>
> Tom,
>
> 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?
>
> Thanks again,
> rik.
>
> On Wed, Apr 26, 2023 at 12:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> richard coleman <rcoleman.ascentgl@gmail.com> writes:
>> > This might sound like a silly question, but how would I list the
>> privileges
>> > the various roles have on the database objects themselves?
>>
>> Perhaps you are looking for psql's \l command? It's a bit
>> nonstandardly named (one would expect \d something)
>>
>> regards, tom lane
>>
>
>
> Regards,
>
> Michael Vitale
>
> Michaeldba@sqlexec.com <michaelvitale@sqlexec.com>
>
> 703-600-9343
>
>
>
>
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../../CAGA3vBuRQgz0RK-OC0f0o+n-n24oWrUNhCU_yx_xJrsEyKj0zw@mail.gmail.com/3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 17:17 ` Erik Wienhold <ewie@ewie.name>
2023-04-26 17:53 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2 siblings, 1 reply; 26+ messages in thread
From: Erik Wienhold @ 2023-04-26 17:17 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; pgsql-admin@lists.postgresql.org
> On 26/04/2023 18:34 CEST richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> This might sound like a silly question, but how would I list the privileges
> the various roles have on the database objects themselves?
>
> There appear to be a million ways to list the privileges on various database
> objects; tables, views, foreign tables, etc. but for the life of me there
> doesn't appear to an analogous way to get permissions on the database objects
> themselves.
>
> At the moment all that I've found is:
>
> select has_database_privilege('role','db_name', 'CREATE') as can_create;
> select has_database_privilege('role','db_name', 'CONNECT') as can_connect;
> select has_database_privilege('role','db_name', 'TEMP') as can_temp;
>
> Am I missing something?
If you're interested in specific roles or privileges, then aclexplode is your
friend.
SELECT
datname,
grantor::regrole,
grantee::regrole,
privilege_type,
is_grantable
FROM
pg_database,
aclexplode(datacl)
WHERE
grantee = 'role'::regrole
AND privilege_type IN ('CREATE', 'CONNECT', 'TEMPORARY');
--
Erik
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:17 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
@ 2023-04-26 17:53 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:55 ` Re: how to list privileges on the database object itself via SQL? Holger Jakobs <holger@jakobs.com>
2023-04-26 18:21 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 17:53 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin@lists.postgresql.org
Erik,
Thanks for that. I'll have to look into the aclexplode() function some
more.
When I try running your SQL on a pg11 database I get:
ERROR: role "role" does not exist
LINE 3: WHERE grantee = 'role'::regrole AND privilege_type I...
^
SQL state: 42704
Character: 151
rik.
On Wed, Apr 26, 2023 at 1:17 PM Erik Wienhold <ewie@ewie.name> wrote:
> > On 26/04/2023 18:34 CEST richard coleman <rcoleman.ascentgl@gmail.com>
> wrote:
> >
> > This might sound like a silly question, but how would I list the
> privileges
> > the various roles have on the database objects themselves?
> >
> > There appear to be a million ways to list the privileges on various
> database
> > objects; tables, views, foreign tables, etc. but for the life of me there
> > doesn't appear to an analogous way to get permissions on the database
> objects
> > themselves.
> >
> > At the moment all that I've found is:
> >
> > select has_database_privilege('role','db_name', 'CREATE') as can_create;
> > select has_database_privilege('role','db_name', 'CONNECT') as
> can_connect;
> > select has_database_privilege('role','db_name', 'TEMP') as can_temp;
> >
> > Am I missing something?
>
> If you're interested in specific roles or privileges, then aclexplode is
> your
> friend.
>
> SELECT
> datname,
> grantor::regrole,
> grantee::regrole,
> privilege_type,
> is_grantable
> FROM
> pg_database,
> aclexplode(datacl)
> WHERE
> grantee = 'role'::regrole
> AND privilege_type IN ('CREATE', 'CONNECT', 'TEMPORARY');
>
> --
> Erik
>
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:17 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
2023-04-26 17:53 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 17:55 ` Holger Jakobs <holger@jakobs.com>
2023-04-26 18:00 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:29 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
1 sibling, 2 replies; 26+ messages in thread
From: Holger Jakobs @ 2023-04-26 17:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 26.04.23 um 19:53 schrieb richard coleman:
> Erik,
>
> Thanks for that. I'll have to look into the aclexplode() function
> some more.
>
> When I try running your SQL on a pg11 database I get:
>
> ERROR: role "role" does not exist
> LINE 3: WHERE grantee = 'role'::regrole AND privilege_type I...
> ^
> 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.
>
>
>
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../fd81361e-fa84-1d75-d944-1c590de0e424@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:17 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
2023-04-26 17:53 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:55 ` Re: how to list privileges on the database object itself via SQL? Holger Jakobs <holger@jakobs.com>
@ 2023-04-26 18:00 ` richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: richard coleman @ 2023-04-26 18:00 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
Holger,
Thanks, I managed to get the code to work across versions myself.
Unfortunately that doesn't rectify the situation which is that the only
place I've seen so far to get access to that information is in an array of
values in the datacl column in the pg_catalog.pg_database table by database.
rik.
On Wed, Apr 26, 2023 at 1:55 PM Holger Jakobs <holger@jakobs.com> wrote:
> Am 26.04.23 um 19:53 schrieb richard coleman:
>
> Erik,
>
> Thanks for that. I'll have to look into the aclexplode() function some
> more.
>
> When I try running your SQL on a pg11 database I get:
>
> ERROR: role "role" does not exist
> LINE 3: WHERE grantee = 'role'::regrole AND privilege_type I...
> ^
> 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.
>
>
>>
>> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:17 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
2023-04-26 17:53 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:55 ` Re: how to list privileges on the database object itself via SQL? Holger Jakobs <holger@jakobs.com>
@ 2023-04-26 18:29 ` Erik Wienhold <ewie@ewie.name>
1 sibling, 0 replies; 26+ messages in thread
From: Erik Wienhold @ 2023-04-26 18:29 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org, richard coleman <rcoleman.ascentgl@gmail.com>
> On 26/04/2023 19:55 CEST Holger Jakobs <holger@jakobs.com> wrote:
>
> Am 26.04.23 um 19:53 schrieb richard coleman:
>
> > ERROR: role "role" does not exist
> > LINE 3: WHERE grantee = 'role'::regrole AND privilege_type I...
> > ^
> > SQL state: 42704
> > Character: 151
>
> If you want to use the SQL statements used by psql on older versions of
> PostgreSQL, you should use the old version of psql.
'role'::regrole works perfectly fine since pg9.5 if the role exists.
Use grantee::regrole::text = 'role' to be safe.
--
Erik
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:17 ` Re: how to list privileges on the database object itself via SQL? Erik Wienhold <ewie@ewie.name>
2023-04-26 17:53 ` Re: how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-26 18:21 ` Erik Wienhold <ewie@ewie.name>
1 sibling, 0 replies; 26+ messages in thread
From: Erik Wienhold @ 2023-04-26 18:21 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On 26/04/2023 19:53 CEST richard coleman <rcoleman.ascentgl@gmail.com> wrote:
>
> Erik,
>
> Thanks for that. I'll have to look into the aclexplode() function some more.
>
> When I try running your SQL on a pg11 database I get:
>
> ERROR: role "role" does not exist
> LINE 3: WHERE grantee = 'role'::regrole AND privilege_type I...
> ^
> SQL state: 42704
> Character: 151
I used 'role'::regrole as example because your has_table_privilege sample uses
that role name. Weird name for a role but I went along with it ;)
--
Erik
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: how to list privileges on the database object itself via SQL?
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-04-28 12:36 ` jian he <jian.universality@gmail.com>
2 siblings, 0 replies; 26+ messages in thread
From: jian he @ 2023-04-28 12:36 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Apr 27, 2023 at 12:34 AM richard coleman <
rcoleman.ascentgl@gmail.com> wrote:
> All,
>
> This might sound like a silly question, but how would I list the
> privileges the various roles have on the database objects themselves?
>
> There appear to be a million ways to list the privileges on various
> database objects; tables, views, foreign tables, etc. but for the life of
> me there doesn't appear to an analogous way to get permissions on the
> database objects themselves.
>
> At the moment all that I've found is:
>
> select has_database_privilege('role','db_name', 'CREATE') as can_create;
> select has_database_privilege('role','db_name', 'CONNECT') as can_connect;
> select has_database_privilege('role','db_name', 'TEMP') as can_temp;
>
> Am I missing something?
>
> Thanks,
> rik.
>
I found this is more right than the aclexplode function.
to make it one query, then you need to construct a values table like CROSS
JOIN (values('CREATE'), ('CONNECT'), ('TEMPORARY')) s(priv)
also join view pg_roles.
I don't think aclexplode can handle grants on roles. like grant alice to bob
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2023-05-01 04:09 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-26 16:34 how to list privileges on the database object itself via SQL? richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 16:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:20 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 17:50 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-26 18:07 ` Holger Jakobs <holger@jakobs.com>
2023-04-26 18:28 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:22 ` Holger Jakobs <holger@jakobs.com>
2023-04-26 18:30 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 21:28 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-04-28 12:18 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-28 12:27 ` Inzamam Shafiq <inzamam.shafiq@hotmail.com>
2023-04-28 14:44 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-28 13:06 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2023-04-28 13:25 ` Joe Conway <mail@joeconway.com>
2023-05-01 04:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-04-26 17:35 ` MichaelDBA <MichaelDBA@sqlexec.com>
2023-04-26 17:57 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:17 ` Erik Wienhold <ewie@ewie.name>
2023-04-26 17:53 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 17:55 ` Holger Jakobs <holger@jakobs.com>
2023-04-26 18:00 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-04-26 18:29 ` Erik Wienhold <ewie@ewie.name>
2023-04-26 18:21 ` Erik Wienhold <ewie@ewie.name>
2023-04-28 12:36 ` jian he <jian.universality@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox