Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to list privileges on the database object itself via SQL?

how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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.

Re: how to list privileges on the database object itself via SQL?

From
Tom Lane
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
Erik Wienhold
Date:
> 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

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
Tom Lane
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
MichaelDBA
Date:
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 


Attachment

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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


Re: how to list privileges on the database object itself via SQL?

From
Holger Jakobs
Date:
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
Attachment

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

703-600-9343 


Attachment

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
Tom Lane
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
Holger Jakobs
Date:
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
Attachment

Re: how to list privileges on the database object itself via SQL?

From
Erik Wienhold
Date:
> 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

Re: how to list privileges on the database object itself via SQL?

From
Holger Jakobs
Date:
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
Attachment

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
Erik Wienhold
Date:
> 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

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

how to list privileges on the database object itself via SQL?

From
"Wetmore, Matthew (CTR)"
Date:

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

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
Inzamam Shafiq
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
jian he
Date:


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


how to list privileges on the database object itself via SQL?

From
"Wetmore, Matthew (CTR)"
Date:

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> 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

 

 

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> 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

Re: how to list privileges on the database object itself via SQL?

From
Joe Conway
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
richard coleman
Date:
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

Re: how to list privileges on the database object itself via SQL?

From
Laurenz Albe
Date:
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