直接切換到對應的庫下執行


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)