全部产品
Search
文档中心

实时数仓Hologres:系统表

更新时间:Dec 04, 2023

本文将会为你介绍Hologres中的系统表以及每个表如何使用。

概述

Hologres系统表的组成如下表所示。

表名

使用场景

hologres.hg_table_properties

查看当前数据库下Hologres所有表以及表属性。

pg_catalog.pg_tables

查看表、视图等关系的信息。

pg_catalog.pg_locks

查看表的锁信息。

pg_catalog.pg_class

PostgreSQL原生元数据表,一般是结合其他PostgreSQL系统表一起使用,查看表关系等信息。

hologres_statistic.hg_table_statistic

Hologres的统计信息表,用于多节点共享统计信息存储。

pg_catalog.pg_stats

PostgreSQL原生统计信息表,在单节点本地直接供planner使用。

pg_catalog.pg_roles

查看实例内角色及其权限信息。

information_schema.role_table_grants

查看用户角色被授予对象(表、视图等)的权限信息。

使用限制

  • 表名称以hg开头的是Hologres系统表,以pg开头的是PostgreSQL系统表,简称PG系统表。PG系统表不能与业务创建的表进行关联查询,也不能将PG系统表的数据导入至业务创建的表中。

    说明

    Hologres 从V1.3.22版本开始,支持PG系统表与业务创建的表进行Join,以及将系统表数据导出到Hologres表,如果有需求请您升级实例。

  • 在Hologres中,系统表中的OID字段表示表、索引、视图等关系的唯一标志符。但Postgres是单机系统,Hologres是分布式系统,有多个FE节点,每个FE节点的OID序列通常不相同。所以当查询结果中有OID时,可能会出现OID结果不一致的情况。

hologres.hg_table_properties

hologres.hg_table_properties用于存放当前数据库下的所有表和表属性,包含如下字段。

字段

说明

table_namespace

Schema名称,Hologres会包含3个系统Schema:

  • hologres:用于Hologres的系统表。

  • hologres_statistic:用于存放统计信息表。

  • pg_catalog:用于PostgreSQL原生的元数据表。

table_name

表名称,Hologres包含多个系统表,如下。

  • hologres.hg_insert_progress_stats:insert进度信息。

  • hologres.hg_table_properties:表的索引、属性信息。

  • hologres.hg_table_group_properties:Table Group元信息。

  • hologres_statistic.hg_table_statistic:存放统计信息。

  • pg_catalog.pg_stat_activity:Query运行数据。

property_key

表属性,包含如下属性。

  • table_id:表的ID信息,后端会给每个表分配一个ID,方便身份识别。

  • clustering_index_id:Clustering索引的ID信息。

  • clustering_index_name:Clustering索引的名称。

  • lifecycle_in_days:表的生存时间TTL,值为-1表示永久生效,目前Hologres不支持修改。

  • storage_format:表数据的存储格式,行存表为sst,列存表从HologresV0.10版本开始默认为orc

  • table_group:表所在的Table Group名称。

  • schema_version:表的版本信息。

  • primary_key:表的主键信息。

  • orientation:表的存储模式,有以下三种模式。

    • row:行存。

    • column:列存。

    • row,column:行列混存(HologresV1.1版本开始支持)。

  • distribution_key:设置的分布列信息。

  • dictionary_encoding_columns:设置的字典编码列信息。

  • bitmap_columns:设置的Bitmap编码列。

  • clustering_key:设置的Clustering key(聚簇索引)信息。

  • create_time:表的创建时间。

  • last_ddl_time:最后执行DDL的时间。

  • storage_mode:表的分层存储属性。

    • hot:标准存储

    • cold:低频存储

property_value

表属性的值。

pg_catalog.pg_tables

pg_tables保存表的元信息,包含如下字段。

字段

描述

schemaname

表所在的Schema名称,除了业务创建的Schema外,还包含系统Schema名称如下。

  • hologres:保存Hologres的系统表。

  • pg_catalog:保存Potsgres的元数据信息。

  • information_schema:保存当前数据库对象信息的视图。

tablename

表名称。

tableowner

表的Owner,如下两种类型。

  • holo_admin:系统账号,是系统表的Owner,无法更改。

  • developer用户组:开启了简单权限模型(SPM)或者基于Schema级别的简单权限模型(简称SLPM)。

tablespace

Hologres无此概念,无需关注

hasindexes

如果表有(或最近有过)任何一个索引,此列为true。

hasrules

如果表有(或曾经有过)规则,此列为true。

hastriggers

如果表有(或曾经有过)触发器,此列为true。

rowsecurity

如果表上启用了安全性规则,此列为true。在Hologres中无需关注。

pg_catalog.pg_locks

pg_locks记录运行时的锁信息,常用于当发现DDL卡住或者Query卡住时,定位是否有锁,其字段如下。

字段

说明

locktype

锁的类型,包含如下类型。

  • relation:表锁。

  • extend、page, tuple、transactionid、virtualxid、object、userlock:Postgres原生锁,在Hologres内无需关注。

  • advisory:DDL锁。

database

目标所在的数据库的对象标识符(OID)。

relation

表的对象标识符(OID),如果目标不是表,也不是表的一部分,则为null。

page

在Hologres内无需关注。

tuple

在Hologres内无需关注。

virtualxid

事务的虚拟ID,如果目标不是虚拟事务ID,就为null。

transactionid

事务的ID,如果目标不是事务ID,就为null。

classid

包含该目标的系统表的对象标识符(OID),如果目标不是普通数据库对象,则为null。在Hologres内无需关注。

objid

在Hologres内无需关注。

objsubid

在Hologres内无需关注。

virtualtransaction

持有或者等待此锁的事务ID。在Hologres内无需关注。

pid

持有或者等待这个锁的服务器进程的进程 ID,可以通过pg_stat_activity这个表查看进程信息。

mode

这个进程的锁模式,分为共享锁和排他锁等模式。

granted

  • 如果持有锁,为true。

  • 如果等待锁,为false。

fastpath

  • 如果锁通过快速路径获得为true。

  • 如果通过主锁表获得为false。

在Hologres内无需关注。

pg_catalog.pg_class

pg_class用于保存原生Postgres的所有系统信息,包含如下字段。

名字

说明

oid

表、索引、视图等关系的唯一标志符。

说明

Postgres是单机系统,Hologres是分布式系统,有多个FE节点,每个FE节点的OID序列通常不相同。所以当查询结果中有OID时,可能会出现OID结果不一致的情况。

relname

表、索引、视图等关系的名称。

relnamespace

包含这个关系的Schema的OID。

reltype

在Hologres内无需关注。

reloftype

在Hologres内无需关注。

relowner

关系的Owner。

relam

在Hologres内无需关注。

relfilenode

在Hologres内无需关注。

reltablespace

在Hologres内无需关注。

relpages

在Hologres内无需关注。

reltuples

表中行的数目。只是查询规划器使用的一个估计值,由VACUUM、ANALYZE和几个DDL命令更新。在Hologres中用作统计信息的行数。

relallvisible

在表的可见映射中标记所有可见的页的数目。只是查询规划器使用的一个估计值, 由VACUUM、ANALYZE 和几个 DDL 命令更新。在Hologres中用作统计信息的版本

reltoastrelid

在Hologres内无需关注。

relhasindex

如果它是一个表而且至少有(或者最近有过)一个索引,则为true。

relisshared

如果该表在整个集群中由所有数据库共享则为true。只有某些系统表(比如pg_database)是共享的,在Hologres内无需关注。

relpersistence

有如下值。

  • p:permanent table(永久表)。

  • u:unlogged table(未加载的表)。

  • t: temporary table (临时表)。

relkind

有如下值。

  • r:ordinary table(普通表)。

  • i: index(索引)。

  • S:sequence(序列)。

  • v:view(视图)。

  • m: materialized view(物化视图)。

  • c: composite type(复合类型)。

  • t: TOAST table(TOAST 表)。

  • f:foreign table(外部表)。

relnatts

表中列的数目(不包含系统字段)。

relchecks

表里的CHECK约束的数目,详情请参见pg_constraint,在Hologres内无需关注。

relhasoids

如果为关系中每行都生成一个OID则为true。在Hologres内无需关注。

relhaspkey

如果这个表有一个(或者曾经有一个)主键,则为true。

relhasrules

如果这个表有(或曾经有)规则就为true,详情请参见pg_rewrite。在Hologres内无需关注。

relhastriggers

如果表有(或者曾经有)触发器,则为true,详情见pg_trigger。在Hologres内无需关注。

relhassubclass

如果表有(或者曾经有)任何继承的子表,则为true。

relispopulated

在Hologres内无需关注。

relreplident

在Hologres内无需关注。

relfrozenxid

在Hologres内无需关注。

relminmxid

在Hologres内无需关注。

relacl

访问权限。详情请参见GRANTREVOKE

reloptions

表的属性,例如autovacuum_enabled=false代表关闭此表的autovacuum/autoanalyze功能。

hologres_statistic.hg_table_statistic

Hologres的统计信息表,其字段如下。

字段

说明

unique_name

表的唯一标志。

schema_version

表的版本号。

statistic_version

统计信息版本。

statistics

统计信息内容,Base64编码。

schema_name

表所在Schema名称。

table_name

表名称。

total_rows

总行数。

sample_rows

本统计信息的采样行数。

nattr

表的字段个数。

used_attrs

Analyze用到的字段。

histogram_attrs

具备直方图统计信息的字段。

ndv_attrs

具备distinct value统计信息的字段。

user_name

Analyze或者Auto Analyze的执行者。

analyze_timestamp

Analyze或者Auto Analyze的执行开始时间。

analyze_cost

Analyze或者Auto Analyze的耗时。

analyze_count

Analyze或者Auto Analyze的次数。

pg_catalog.pg_stats

pg_stats用于保存Postgres原生的统计信息,字段如下。

字段

说明

schemaname

Schema名称。

tablename

表名称。

attname

列名(字段名)。

inherited

如果为true,表示此行包括继承子列。

null_frac

记录中字段为空的百分比。

avg_width

列的平均字节宽度。

n_distinct

  • 大于零,表示列中distinct值的估计个数。

  • 小于零,是distinct值个数除以行数的负值(当ANALYZE认为distinct值的数量会随着表增长而增加时采用负值的形式,而如果认为列具有固定数量的distinct值时采用正值的形式)。

例如,-1表示一个唯一列,即其中distinct值的个数等于行数。

most_common_vals

列中Most Common Values的一个列表(如果没有任何一个值看起来比其他值更常用,此列为空)。

most_common_freqs

Most Common Values值的频率列表,即每一个常用值的出现次数除以总行数(如果most_common_vals为空,则此列为空)。

histogram_bounds

将列值划分成大小接近的组的值列表,即直方图列表。如果存在most_common_vals,其中的值会被直方图计算所忽略。

correlation

在Hologres内无需关注。

most_common_elems

在列值中,Most Common Values出现的非空元素列表。

most_common_elem_freqs

Most Common Values值的频度列表,即含有至少一个给定值实例的行的分数。(如果most_common_elems为空,则此列为空)。

elem_count_histogram

在Hologres中无需关注。

pg_catalog.pg_roles

pg_roles用于存放实例内角色及其权限信息,字段如下。

字段

说明

rolname

角色名称。

rolsuper

角色是否具有超级用户权限,取值如下。

  • f:无超级用户权限。

  • t:有超级用户权限。

rolinherit

如果此角色是另一个角色的成员,角色是否能自动继承,取值如下。

  • f:不可以继承另一个角色的权限。

  • t:可以继承另一个角色的权限。

rolcreaterole

能否创建更多角色,取值如下。

  • f:不可以创建更多角色。

  • t:可以创建更多角色。

rolcreatedb

能否创建数据库,取值如下。

  • f:不可以创建数据库。

  • t:可以创建数据库。

rolcanlogin

角色是否能登录实例,取值如下。

  • f:不可以登录实例。

  • t:可以登录实例。

rolreplication

在Hologres内无需关注。

rolconnlimit

用户的连接数限制,-1表示无限制。

rolpassword

在Hologres内无需关注。

rolvaliduntil

在Hologres内无需关注。

rolbypassrls

在Hologres内无需关注。

rolconfig

在Hologres内无需关注。

oid

角色的ID,唯一标志符。

information_schema.role_table_grants

Hologres实例中用户角色被授予对象(表、视图等)的权限信息表,其字段如下。

字段

描述

grantor

授权方角色。

grantee

被授权方角色。

table_catalog

数据库名称。

table_schema

Schema名称。

table_name

表名称。

privilege_type

被授予权限的类型:

  • SELECT。

  • INSERT。

  • UPDATE。

  • DELETE。

  • TRUNCATE。

  • REFERENCES。

  • TRIGGER。

is_grantable

如果权限是可授予的,则此列值为YES,否则为NO

with_hierarchy

权限类型为SELECT时,此列值为YES,其他权限类型时值为NO

常用SQL

一些常见命令可以通过psql简写来实现,详情请参见psql。同时也可以根据业务情况选择使用如下提供的常见SQL。

查看表对应的Hologres索引和属性信息

SELECT * FROM hologres.hg_table_properties where table_name = '<tablename>';

tablename为表名称。

查看表/视图DDL

select hg_dump_script('<tablename>'); -- 表DDL
select hg_dump_script('<viewname>');  -- 视图DDL
说明

如果执行失败,需要使用如下命令在DB中创建extension。

create extension hg_toolkit;

查看实例的Endpoint

除了在Hologres管理控制台查看实例的Endpoint外,还可以通过以下命令查看实例的Endpoint。

show hg_frontend_endpoints;

查看当前实例下的所有数据库

SELECT
    d.datname AS "Name",
    pg_catalog.pg_get_userbyid(d.datdba) AS "Owner",
    pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
    d.datcollate AS "Collate",
    d.datctype AS "Ctype",
    pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM
    pg_catalog.pg_database d
WHERE
    d.datname != 'postgres'
    AND d.datname != 'template0'
    AND d.datname != 'template1'
ORDER BY
    1;

列出当前数据库下所有User Mapping

SELECT
    um.srvname AS "Server",
    um.usename AS "User name"
FROM
    pg_catalog.pg_user_mappings um
WHERE
    um.srvname != 'query_log_store_server'
ORDER BY
    1,
    2;

列出当前数据库下所有Schema

SELECT
    n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM
    pg_catalog.pg_namespace n
WHERE
    n.nspname !~ '^pg_'
    AND n.nspname <> 'information_schema'
    AND n.nspname != 'hologres'
    AND n.nspname != 'hologres_sample'
    AND n.nspname != 'hologres_statistic'
    AND n.nspname !~ '^hg_'
    AND n.nspname !~ '^holo_'
ORDER BY
    1;

列出当前数据库下所有表、外表和视图

SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r', 'p', 'v', 'm', 'S', 'f', '')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
    1,
    2;

查看当前Schema下所有表以及表Owner(不包含系统表)

--查看当前DB下所有表(包含系统表)
SELECT * FROM pg_tables


--查看当前schema下所有表以及表owner(不包含系统表)
SELECT  n.nspname as "Schema"
        ,c.relname as "Name"
        ,CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type"
        ,pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM    pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON      n.oid = c.relnamespace
WHERE   c.relkind IN ('r','p','v','m','S','f','')
and     n.nspname <> 'pg_catalog'
and     n.nspname <> 'information_schema'
and     n.nspname !~ '^pg_toast'
and     pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;
            

查看父表对应的所有子表

-- 含分区键值
SELECT  c.oid::pg_catalog.regclass
        ,c.relkind
        ,pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM    pg_catalog.pg_class c
        ,pg_catalog.pg_inherits i
WHERE   c.oid = i.inhrelid
AND     i.inhparent::pg_catalog.regclass = 'parent_table_name'::pg_catalog.regclass
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT'
;



-- 不含分区键值
SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';

查看所有子表的创建时间和所属父表

select
        cn.nspname as child_schema_name,c.relname as child_table_name,
        pn.nspname as parent_schema_name,p.relname as parent_table_name,
        to_timestamp(cp.property_value::bigint) as create_time
from pg_inherits i
left join pg_class p on p.oid=i.inhparent
left join pg_namespace pn on pn.oid = p.relnamespace
left join pg_class c on c.oid=i.inhrelid
left join pg_namespace cn on cn.oid = c.relnamespace
left join hologres.hg_table_properties cp on cp.property_key='create_time' and cp.table_namespace=pn.nspname and cp.table_name = c.relname;

查看所有外部表以及外部表对应的MaxCompute表

SELECT  n.nspname
        ,c.relname
        ,s.srvname
        ,pg_catalog.array_to_string(
            ARRAY(
                SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(ftoptions)
            )
            ,', '
        )
FROM    pg_catalog.pg_foreign_table f
        ,pg_catalog.pg_foreign_server s
        ,pg_catalog.pg_class c
        ,pg_catalog.pg_namespace n
WHERE   s.oid = f.ftserver
and     c.oid = f.ftrelid
and     c.relnamespace = n.oid
and     n.nspname not in ('hologres', 'hologres_statistic', 'pg_catalog', 'pg_toast')
;
            

列出当前数据库下所有视图

SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('v', '')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
    AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
    1,
    2;

查看表被哪些view依赖

select * from information_schema.view_table_usage where table_name = '<table_name>';

查看表或者表字段的注释

  • 查看指定表中字段的注释:

    SELECT a.attname as Column,
      pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
      a.attnotnull as "Nullable",
      pg_catalog.col_description(a.attrelid, a.attnum) as "Description"
    FROM pg_catalog.pg_attribute a
    WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = '<schema.tablename>'::regclass::oid
    ORDER BY a.attnum;

    其中schema.tablename{Schema名称}.{表名称}

  • 查看表的注释并且包含表的Owner等相关信息。

    SELECT n.nspname as "Schema",
      c.relname as "Name",
      CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
      pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
      pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
    FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','p','v','m','S','f','')
          AND n.nspname <> 'pg_catalog'
          AND n.nspname <> 'information_schema'
          AND n.nspname !~ '^pg_toast'
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 1,2;
    
    
    --返回结果示例
                            List of relations
     Schema | Name | Type  |      Owner       |  Size   | Description
    --------+------+-------+------------------+---------+-------------
     public | a    | table | 1365937xxxx | xxxx bytes | abcdef
    (1 row)
  • 只查看指定表的注释信息:

    select pg_catalog.obj_description('<tablename>'::regclass::oid, 'pg_class') as "Description";
    
    --返回结果示例
    Description
    ------------
    abcdef

    其中tablename为指定表名称。

列出当前数据库下所有用户和角色

SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY (
        SELECT
            b.rolname
        FROM
            pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE
            m.member = r.oid) AS memberof,
    r.rolreplication,
    r.rolbypassrls
FROM
    pg_catalog.pg_roles r
WHERE
    r.rolname !~ '^pg_'
    AND r.rolname != 'holo_admin'
ORDER BY
    1;

列出当前数据库下所有EXTENSIONS

SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
        AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
    e.extname != 'hg_admin_cmd'
    AND e.extname != 'holo_dump_stat'
    AND e.extname != 'holo_funcs'
    AND e.extname != 'holo_link'
    AND e.extname != 'holo_system_admin'
    AND e.extname != 'holo_dump_stat'
    AND e.extname != 'query_log'
    AND e.extname != 'plpgsql'
ORDER BY
    1;

查看某个账号的对应权限

SELECT * FROM pg_roles where rolname='<uid>'

查看当前实例下所有的用户以及权限示例

SELECT  r.rolname
        ,r.rolsuper
        ,r.rolinherit
        ,r.rolcreaterole
        ,r.rolcreatedb
        ,r.rolcanlogin
        ,r.rolconnlimit
        ,r.rolvaliduntil
        ,ARRAY(
            SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid
        ) as memberof
        ,r.rolreplication
        ,r.rolbypassrls
FROM    pg_catalog.pg_roles r
WHERE   r.rolname !~ '^pg_'
ORDER BY 1;

查看一个用户有权限的所有表

SELECT current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,
    c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"]) THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            WHEN c.relkind = 'f'::"char" THEN 'FOREIGN'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type,
        CASE
            WHEN (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) OR (c.relkind = ANY 
(ARRAY['v'::"char", 'f'::"char"])) AND (pg_relation_is_updatable(c.oid::regclass, false) 
& 8) = 8 THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_insertable_into,
        CASE
            WHEN t.typname IS NOT NULL THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_typed,
    NULL::character varying::information_schema.character_data AS commit_action
   FROM pg_namespace nc
     JOIN pg_class c ON nc.oid = c.relnamespace
     LEFT JOIN (pg_type t
     JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])) AND 
NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role('<USERID>', c.relowner, 'USAGE'::text)
 OR has_table_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
 OR has_any_column_privilege('<USERID>', c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

查看一张表有权限的所有用户

select rolname from pg_roles where has_table_privilege(rolname, '<schemaname>.<tablename>',
 'SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER');