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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx> 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@xxxxxxxxxxxxx> wrote:
richard coleman <rcoleman.ascentgl@xxxxxxxxx> 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@xxxxxxxxxxx

703-600-9343

 




[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux