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