PostgreSQL获取用户下所有对象的权限情况

直接切换到对应的库下执行

select 
nsp.nspname as SchemaName
,cls.relname as ObjectName
,rol.rolname as ObjectOwner
,case cls.relkind
when 'r' then 'TABLE'
when 'm' then 'MATERIALIZED_VIEW'
when 'i' then 'INDEX'
when 'S' then 'SEQUENCE'
when 'v' then 'VIEW'
when 'c' then 'TYPE'
else cls.relkind::text
end as ObjectType
from pg_class cls
join pg_roles rol
on rol.oid = cls.relowner
join pg_namespace nsp
on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
and nsp.nspname not like 'pg_toast%'
and rol.rolname = 'rw'
order by nsp.nspname, cls.relname;

注意,这个不能获取到schema和database的归属情况,只能系统运维包括哪些内容获取到表和序列的objectowner的情况

上面的这个sql,能查出下面的这种rolname=objectowner归属的
schemaname | objectname | objectowner | objecttype
------------+------------+-------------+------------
public | sq1l | rw | SEQUENCE
public | t1 | rw | TABLE


对于下面的这2种情况,都查不出来的。
1、表的owner是一个role,表里面access privilege又有其它的授权
2、非public的schema下

db2=# create schema myschema;
db1=# alter schema myschema owner to rw;
db1=# \dn
List of schemas
Name | Owner
----------+----------
myschema | rw
public | postgres
(2 rows)

db2=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tt | table | postgres
(1 row)

db2=# \z tt
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | tt | table | postgres=arwdDxt/postgres+| |
| | | dts=arwdDxt/postgres | |
(1 row)