From: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
To: richard coleman <rcoleman.ascentgl@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: how to list privileges on the database object itself via SQL?
Date: Fri, 28 Apr 2023 13:06:14 +0000
Message-ID: <7ef345ec89f346798596b05c796a6a6d@express-scripts.com> (raw)
In-Reply-To: <CAGA3vBsL1Pq8dH02=CQ+1GUpy+Q_aW8Co9Ds6FR2uGV0Kz4ong@mail.gmail.com>
References: <CAGA3vBvanu7UBtaaVv9PH25Ko==5F2wewhPWTTra8wqexdDAdQ@mail.gmail.com>
<870558.1682527243@sss.pgh.pa.us>
<CAGA3vBvjUiUb+O-UWYU8VNrrPrLrwz+KOg3zqwiWTAAkTChX_A@mail.gmail.com>
<875041.1682529779@sss.pgh.pa.us>
<CAGA3vBsPQ8672wf99EGGWDAG4GBTcOpiJeCrJOypQ1sGG02Bvw@mail.gmail.com>
<3e9ce35157e44b29bdf0a453236e947b@express-scripts.com>
<CAGA3vBsL1Pq8dH02=CQ+1GUpy+Q_aW8Co9Ds6FR2uGV0Kz4ong@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Matthew.Wetmore@express-scripts.com, rcoleman.ascentgl@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: how to list privileges on the database object itself via SQL?
In-Reply-To: <7ef345ec89f346798596b05c796a6a6d@express-scripts.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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