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

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(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> richard coleman <rcoleman(dot)ascentgl(at)gmail(dot)com
> <mailto:rcoleman(dot)ascentgl(at)gmail(dot)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(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>

703-600-9343