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

博观而约取，厚积而薄发。这篇文章主要讲述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)