Обсуждение: how to list privileges on the database object itself via SQL?

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

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

> 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

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

703-600-9343 

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

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

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

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

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