引言

PostgreSQL里面的权限对于老鸟来说还行，但对于刚入门的或者转型过来的可能不太好理解，今天正好有个需求，业务需要查看用户的权限列表，也折腾了一会，借此机会总结一下，你好我好大家好。

权限

PostgreSQL相对来说，与其说是更加严格，不如说是对SQL标准支持的更好。整体架构如下：

1.实例位于最顶层，也可以叫做"集簇"，你可以把一套完整运行的PostgreSQL称作为一个实例

2.实例下面就是database，database就好理解了，数据库

3.数据库下面就是schema了，schema就好比命名空间namespace，数据库是严格分开的，这意味着不能同时使用两个不同的数据库，而模式不是严格分开的，可以一起使用和访问同一数据库的两个或多个模式中的对象。按照官方说法，每个模式下允许创建多个对象，比如表、函数、视图、索引等，同时每个模式之间可以存在同名的表，井水不犯河水。A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.

4.每个表又可以依据行和列两个维度进行衡量

5.另外还有表空间，如果说模式是逻辑层面的划分，那么表空间就是物理层面的划分，表空间只能属于某一个实例，但是可以被多个数据库使用，通过使用表空间，我们可以控制磁盘的布局。表空间的最常用的作用是优化性能，比如一个最常用的索引可以建立在非常快的硬盘上，而不太常用的表可以建立在便宜的硬盘上，或用来存储用于进行归档、日志表之类。

所以，总结来说，就是你想要查看某个表的某一行数据，那么你得要能够登录数据库 (LOGIN) 这一系统权限，表所在的数据库的连接权限 (CONNECT) ，所在模式的使用权限 (USAGE) 和表本身的查看权限 (SELECT) ，同时还要满足对这一行数据的行级策略 (row level security)以及是否有相应列的查询权限，层层关卡，缺一不可。其中，最容易被忽略的，就是模式的Usage使用权限，当然针对列级安全和行级安全也挺容易被人忽视。

另外有一些需要注意的点：

1.在数据库中所有的权限都和角色（用户）挂钩，角色和用户的唯一区别在于，角色是nologin的，而用户允许login，仅此而已。而"public" 是一个特殊的角色，代表着所有人。

2.超级用户允许操作任何对象，所以超级用户请妥善保管，普通用户只能操作自己创建的对象，因为它是对象的owner。

3.有一些对象有赋予给public角色默认权限，所以建好之后，所有人都有这些默认权限

4.默认情况下，数据库在创建后，允许public角色连接，即允许任何人连接，需要revoke connect on database xxx from public之后，再显式执行grant connect on database xxx to xxx。

5.默认情况下，数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema。

6.默认情况下，数据库在创建后，会自动创建名为public的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象，所以为了方便安全，先revoke all吧，再按需授予权限。

7.schema级别的权限，包括允许查看schema中的对象，允许在schema中创建对象。

8.默认情况下新建的schema的权限不会赋予给public角色，因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

角色和用户

在PostgreSQL中，角色 (role) 和用户 (user) 没有本质区别，唯一区别在于角色默认是nologin的

并且由于历史原因，还有group语法的存在，从8.1开始，group和user都合并成了角色

Users, groups, and roles Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.

可以看到，create group其实也创建了一个role。

既然用户和角色如此像，该如何管理呢？这一点可以参照Amazon RDS 和 Aurora PostgreSQL的，包括我们内部，也是类似的机制：根据应用和访问需求，创建多个指定权限集的角色。然后把适当的角色分配给每个用户，这些角色只能被授予相应对象的最小权限。比如创建了readwrite和readonly两个角色，分别对应读写和只读，按需授予对应的用户。

另外，删除角色通常不仅仅是快速DROP ROLE的问题。角色拥有的任何对象都必须首先删除或重新分配给其他所有者；删除已用于拥有对象的角色的最一般方法是，先转移"资产"：
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
DROP ROLE doomed_role;
或者也可以使用如下SQL查询某个用户拥有的对象：
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 'SEQUNCE'
        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 = 'postgres'  
order by nsp.nspname, cls.relname;
在PostgreSQL中，也提供了一些默认的以pg_开头的角色，截止v14，我们可以按需授予

如下SQL查询被授予的角色：

postgres=# SELECT 
      r.rolname, 
      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
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;
          rolname          |                           memberof                           
---------------------------+--------------------------------------------------------------
 g1                        | {}
 myrole                    | {}
 pg_execute_server_program | {}
 pg_monitor                | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables}
 pg_read_all_settings      | {}
 pg_read_all_stats         | {}
 pg_read_server_files      | {}
 pg_stat_scan_tables       | {}
 pg_write_server_files     | {}
 postgres                  | {}
 u1                        | {myrole}
(11 rows)

因为角色和用户可以嵌套，而嵌套可以无限深，使用如下SQL递归查询：

postgres=# grant u1 to g1;

GRANT ROLE

postgres=# WITH RECURSIVE x AS

(

  SELECT member::regrole,

         roleid::regrole AS role,

         member::regrole || ' -> ' || roleid::regrole AS path

  FROM pg_auth_members AS m

  WHERE roleid > 16384

  UNION ALL

  SELECT x.member::regrole,

         m.roleid::regrole,

         x.path || ' -> ' || m.roleid::regrole

 FROM pg_auth_members AS m

    JOIN x ON m.member = x.role

  )

  SELECT member, role, path

  FROM x

  ORDER BY member::text, role::text;

 member |  role  |        path        

--------+--------+--------------------

 g1     | myrole | g1 -> u1 -> myrole

 g1     | u1     | g1 -> u1

 u1     | myrole | u1 -> myrole

(3 rows)

AWS DEMO

参照一下AWS RDS是怎么管理角色和用户的：

1.A PostgreSQL database has been created with primary database named mydatabase.
2.A new schema has been created named myschema with multiple tables.
3.Two reporting users must be created with the permissions to read all tables in the schema myschema.
4.Two app users must be created with permissions to read and write to all tables in the schema myschema and also to create new tables.
5.The users should automatically get permissions on any new tables that are added in the future.
-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;
-- Read/write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;
-- Users creation
CREATE USER reporting_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER reporting_user2 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user1 WITH PASSWORD 'some_secret_passwd';
CREATE USER app_user2 WITH PASSWORD 'some_secret_passwd';
-- Grant privileges to users
GRANT readonly TO reporting_user1;
GRANT readonly TO reporting_user2;
GRANT readwrite TO app_user1;
GRANT readwrite TO app_user2;

BUG or just features

According to the SQL standard, the owner of a schema always owns all objects within it. PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on their schema to someone else, or a superuser chooses to create objects in it.

根据SQL标准，一个模式的所有者总是拥有其中的所有对象。PostgreSQL允许模式包含由模式所有者以外的用户拥有的对象。只有当模式拥有者将模式的"CREATE"权限授予其他人，或者超级用户选择在模式中创建对象时，这种情况才会发生。

所以，就存在这么一种情况，一个对象属于两个owner，schema的owner可以直接drop其他用户创建的对象，是不是感觉怪怪的？见如下例子：

postgres=# create user u1;

CREATE ROLE

postgres=# create user u2;

CREATE ROLE

postgres=# grant create on database postgres to u1;

GRANT

postgres=# \c postgres u1

You are now connected to database "postgres" as user "u1".

postgres=> create schema myschema;

CREATE SCHEMA

postgres=> \dn+

                           List of schemas

   Name   |  Owner   |  Access privileges   |      Description       

----------+----------+----------------------+------------------------

 myschema | u1       |                      | 

 public   | postgres | postgres=UC/postgres+| standard public schema

          |          | =C/postgres         +| 

          |          | myuser=UC/postgres   | 

 repack   | postgres |                      | 

(3 rows)

然后授予u2 在 myschema下面的创建权限

postgres=> grant create on schema myschema to u2;

GRANT

postgres=> \c postgres u2

You are now connected to database "postgres" as user "u2".

postgres=> create table myschema.t1(id int);

CREATE TABLE

postgres=> \dt+ myschema.t1 

                           List of relations

  Schema  | Name | Type  | Owner | Persistence |  Size   | Description 

----------+------+-------+-------+-------------+---------+-------------

 myschema | t1   | table | u2    | permanent   | 0 bytes | 

(1 row)

postgres=> \c postgres u1

You are now connected to database "postgres" as user "u1".

postgres=> drop table myschema.t1 ;        ---schema的owner u1删掉了u2创建的表

DROP TABLE

---当然也可以更加暴力使用cascade删除整个模式

postgres=> drop schema myschema cascade;    

NOTICE:  drop cascades to table myschema.t1

DROP SCHEMA

可以看到，myschema的owner u1删除掉了u2创建的表对象。

对于database，也是类似的

postgres=# create user u3;

CREATE ROLE

postgres=# create user u4;

CREATE ROLE

postgres=# alter user u3 createdb;

ALTER ROLE

postgres=# \c postgres u3

You are now connected to database "postgres" as user "u3".

postgres=> create database db1;

CREATE DATABASE

postgres=> \l+ db1

                                              List of databases

 Name | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description 

------+-------+----------+-------------+-------------+-------------------+---------+------------+-------------

 db1  | u3    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7737 kB | pg_default | 

(1 row)

db1=> \c db1 u4

You are now connected to database "db1" as user "u4".

db1=> create table t1(id int);

CREATE TABLE

db1=> \dt+

                          List of relations

 Schema | Name | Type  | Owner | Persistence |  Size   | Description 

--------+------+-------+-------+-------------+---------+-------------

 public | t1   | table | u4    | permanent   | 0 bytes | 

(1 row)

db1=> \c db1 u3

You are now connected to database "db1" as user "u3".

db1=> drop table t1;               ---虽然无权删表

ERROR:  must be owner of table t1

postgres=# \c postgres u3

You are now connected to database "postgres" as user "u3".

postgres=> drop database db1 ;  ---那我直接删库

DROP DATABASE

可以看到，虽然我不能直接删除表，但我可以直接删除库，直接掀你的天灵盖

不过仅有删除的权限，要查询里面数据的话还是没有权限，仍然得按照之前说的那样层层赋权。

列级安全和行级安全

列级安全很好理解，如果在一些业务场景中，某些列存储敏感信息，需要对用户不可见，但其他列的数据又需要用户能够查看或操作，此时就需要针对数据表的特定列做访问控制，实现针对用户的列级别的访问控制。

postgres=# \h create policy 
Command:     CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
URL: https://www.postgresql.org/docs/13/sql-createpolicy.html

表级权限

针对表的权限，我们可以直接\z + 表名即可看到 Access privileges

系统视图
1.column_privileges：标识所有授予给一个用户或角色授予的特权。
2.routine_privileges：标识所有在函数上授予的特权。
3.table_privileges：标识在表或视图上所有被授予的特权。grantor表示授予该特权的角色名，grantee表示被授予该特权的角色名。
4.usage_privileges：标识所有在多种对象上授予的USAGE特权。
5.role_usage_grants：标识所有在多种对象上授予的USAGE特权。
6.role_column_grants：标识所有在列上授予的特权。
7.role_routine_grants：标识所有在函数上授予的特权。
8.role_table_grants：标识所有在表或视图上授予的特权。

系统函数

1.has_table_privilege() 判断一个用户是否可以用某种特定的方式访问一个表。

2.has_sequence_privilege() 检查一个用户是否能以某种特定方式访问一个序列。

3.has_any_column_privilege() 检查一个用户是否能以特定方式访问一个表的任意列。

4.has_column_privilege() 检查一个用户是否能以特定方式访问一个列。

5.has_database_privilege() 检查一个用户是否能以特定方式访问一个数据库。比如查看哪些库可以被连接：

6.has_function_privilege() 检查一个用户是否能以特定方式访问一个函数。

7.has_schema_privilege() 检查一个用户是否可以以某种特定的方式访问一个模式。

8.has_tablespace_privilege() 检查一个用户是否可以以某种特定的方式访问一个表空间。

与其他库的区别

MySQL 相对简单，它没有schema，或者准确地说schema是database。笔者就多次见到从MySQL转型过来的，在PostgreSQL下面建了多个database然后又吐槽居然不能跨库访问的，还得用什么dblink，因为PostgreSQL里面的schema的概念对应MySQL的database。

Oracle则不一样，schema严格绑定user，虽然也有create schema的语法，但是作用完全不同。每次为 Oracle 创建用户时，都会自动创建一个schema。

防止删库/删表/删列

删除跑路想必对于无数DBA来说，再熟悉不过了，那么又没有办法防止删库呢？有，默认是无法删除模板数据库的，所以改一下pg_database的datistemplate字段即可。

postgres=# update pg_database set datistemplate = 'true' where datname = 'mydb';

UPDATE 1

postgres=# drop database mydb;

ERROR:  cannot drop a template database

postgres=# update pg_database set datistemplate = 'false' where datname = 'mydb';

UPDATE 1

postgres=# drop database mydb;

DROP DATABASE

那么删表呢？很不幸，PostgreSQL目前还没有相关的直接权限控制。不过我们可以曲线救国，用事件触发器

CREATE OR REPLACE FUNCTION confirm_drop ()

    RETURNS event_trigger

    LANGUAGE plpgsql

    AS $$

DECLARE

    obj record;

BEGIN

    FOR obj IN

    SELECT

        *

    FROM

        pg_event_trigger_dropped_objects ()

        LOOP

            IF obj.object_name = 'users' AND obj.schema_name = 'public' THEN

                RAISE EXCEPTION 'command % IS disabled FOR this TABLE ', tg_tag;

            END IF;

        END LOOP;

END;

$$;

CREATE EVENT TRIGGER confirm_drop on sql_drop WHEN TAG IN ('DROP TABLE') EXECUTE PROCEDURE confirm_drop();

postgres=# create table users(id int);

CREATE TABLE

postgres=# drop table users ;

ERROR:  command DROP TABLE IS disabled FOR this TABLE 

CONTEXT: PL/pgSQL function confirm_drop() line 12 at RAISE

可以看到，超级用户也无法删除。自己改吧改吧，比如限制整个public模式下无法删除

postgres=# CREATE OR REPLACE FUNCTION confirm_drop ()

    RETURNS event_trigger

    LANGUAGE plpgsql

    AS $$

DECLARE

    obj record;

BEGIN

    FOR obj IN

    SELECT

        *

    FROM

        pg_event_trigger_dropped_objects ()

        LOOP

            IF  obj.schema_name = 'public' THEN

                RAISE EXCEPTION 'command % IS disabled FOR this TABLE ', tg_tag;

            END IF;

        END LOOP;

END;

$$;

CREATE FUNCTION

postgres=# create table t1(id int);

CREATE TABLE

postgres=# drop table t1;

ERROR:  command DROP TABLE IS disabled FOR this TABLE 

CONTEXT:  PL/pgSQL function confirm_drop() line 12 at RAISE

禁止删列也是类似

create function fetg() returns event_trigger language plpgsql as $$

begin

  if exists (

    select 1

    from pg_event_trigger_dropped_objects() as t

    where

      t.object_type = 'table column' and

      t.object_identity like any(array['%.t.y', 'public.tt.zz']))

  then

    raise exception 'Columns t.y and public.tt.zz are important!';

  end if;

end $$;

create event trigger etg on sql_drop execute procedure fetg();

postgres=# create table t(x int, y int);

CREATE TABLE

postgres=# alter table t drop column y;

ERROR:  Columns t.y and public.tt.zz are important!

CONTEXT:  PL/pgSQL function fetg() line 10 at RAISE

插件

当然遇事不决找插件，如下三个插件也都是权限相关。

set_user
•The current effective user becomes rolename.
•The role transition is logged, with a specific notation if rolename is a superuser.
•log_statement setting is set to "all", meaning every SQL statement executed while in this state will also get logged.
•If set_user.block_alter_system is set to "on", ALTER SYSTEM commands will be blocked.
•If set_user.block_copy_program is set to "on", COPY PROGRAM commands will be blocked.
•If set_user.block_log_statement is set to "on", SET log_statement and variations will be blocked.
•If set_user.block_log_statement is set to "on" and rolename is a database superuser, the current log_statement setting is changed to "all", meaning every SQL statement executed
•If set_user.superuser_audit_tag is set, the string value will be appended to log_line_prefix upon superuser escalation. All logs after superuser escalation will be tagged with the value of set_user.superuser_audit_tag. This value defaults to 'AUDIT'.
•If set_user.exit_on_error is set to "on", the backend process will exit on ERROR during calls to set_session_auth().
•Post-execution hook for set_user is called if it is set.
pg_permissions
•database_permissions: permissions granted on the current database
•schema_permissions: permissions granted on schemas
•table_permissions: permissions granted on tables
•view_permissions: permissions granted on views
•column_permissions: permissions granted on table and view columns
•function_permissions: permissions granted on functions
•sequence_permissions: permissions granted on sequences
•all_permissions: permissions on all objects (UNION of the above)
pg_restrict
pg_restrict is an extension to restrict some SQL commands on PostgreSQL. It introduces the master role concept that is similar to superuser. Even superusers can be forbid to drop databases and roles (if it is not a master role).
•pg_restrict.alter_system (boolean): restrict ALTER SYSTEM command to master roles (pg_restrict.master_roles parameter). Default is false.
•pg_restrict.copy_program (boolean): restrict COPY ... PROGRAM command to master roles (pg_restrict.master_roles parameter). Default is false.
•pg_restrict.master_roles (string): Roles that are allowed to execute the restricted commands. If there is more than one role, separate them with comma. Default is postgres.
•pg_restrict.nonremovable_databases (string): restrict DROP databases listed here to a master role (even if the current role is the database owner or superuser). Default is postgres, template1, template0.
•pg_restrict.nonremovable_roles (string): restrict DROP roles listed here to a master role (even if the current role has CREATEROLE privilege or is a superuser). Default is postgres.

实用SQL

前面说的那么一大坨，头都晕了，这么绕的权限，那有没有方便的SQL直接查出来呢？有！

查看表级权限

CREATE OR REPLACE FUNCTION table_privs(text) RETURNS table(username text, relname regclass, privs text[])

AS

$$

SELECT  $1,c.oid::regclass, array(select privs from unnest(ARRAY [ 

( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys')  and c.relkind='r' and

has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')

$$ language sql;

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table, 

    CASE 

        WHEN COUNT(privilege_type) = 7 THEN 'ALL'

        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')

    END AS grants

FROM information_schema.role_table_grants

GROUP BY table_name, table_schema, grantee;

查看库级权限

CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges  text[])

AS

$$

SELECT $1, datname, array(select privs from unnest(ARRAY[

( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),

(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),

(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),

(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE 

has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname not in ('template0');

$$ language sql;

查看表空间权限

CREATE OR REPLACE FUNCTION tablespace_privs(text) RETURNS table(username text,spcname name,privileges text[])

AS

$$

   SELECT $1, spcname, ARRAY[

(CASE WHEN has_tablespace_privilege($1,spcname,'CREATE') THEN 'CREATE' ELSE NULL END)] FROM pg_tablespace WHERE has_tablespace_privilege($1,spcname,'CREATE');

$$ language sql;

查看FDW权限

CREATE OR REPLACE FUNCTION fdw_wrapper_privs(text) RETURNS table(username text,fdwname name,privleges text[])

AS

$$

  SELECT $1, fdwname, ARRAY[

(CASE WHEN has_foreign_data_wrapper_privilege($1,fdwname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_foreign_data_wrapper WHERE has_foreign_data_wrapper_privilege($1,fdwname,'USAGE');

$$ language sql;

查看foreign server 权限

CREATE OR REPLACE FUNCTION foreign_server_privs(text) RETURNS table(username text, srvname name, privileges text[])

AS

$$

  SELECT $1, s.srvname ,  ARRAY[

(CASE WHEN has_server_privilege($1,srvname,'USAGE') THEN 'USAGE' ELSE NULL END)] from pg_catalog.pg_foreign_server s  WHERE has_server_privilege ($1,srvname,'USAGE');

$$

language sql;

查看语言权限

CREATE OR REPLACE FUNCTION language_privs(text) RETURNS table(username text,srvname name, privileges text[])

AS

$$

SELECT $1, l.lanname, ARRAY[(CASE WHEN has_language_privilege($1,lanname,'USAGE') THEN 'USAGE' ELSE NULL END)] FROM pg_catalog.pg_language l where has_language_privilege($1,lanname,'USAGE');

$$ language sql;

查看模式权限

CREATE OR REPLACE FUNCTION schema_privs(text) RETURNS table(username text, schemaname name, privileges text[])

AS

$$

  SELECT $1, c.nspname, array(select privs from unnest(ARRAY[

( CASE WHEN has_schema_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),

(CASE WHEN has_schema_privilege($1,c.oid,'USAGE') THEN 'USAGE' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL)

FROM pg_namespace c where has_schema_privilege($1,c.oid,'CREATE,USAGE');

$$ language sql;

postgres=# select  

  r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable

from pg_namespace

join lateral (

  SELECT

    *

  from

    aclexplode(nspacl) as x

) a on true

join pg_user e on a.grantee = e.usesysid

join pg_user r on a.grantor = r.usesysid 

 where e.usename = 'postgres';

 grantor  | grantee  |      nspname       | privilege_type | is_grantable 

----------+----------+--------------------+----------------+--------------

 postgres | postgres | pg_catalog         | USAGE          | f

 postgres | postgres | pg_catalog         | CREATE         | f

 postgres | postgres | public             | USAGE          | f

 postgres | postgres | public             | CREATE         | f

 postgres | postgres | information_schema | USAGE          | f

 postgres | postgres | information_schema | CREATE         | f

(6 rows)

查看视图权限

CREATE OR REPLACE FUNCTION view_privs(text) returns table(username text, viewname regclass, privileges text[])

AS

$$

SELECT  $1, c.oid::regclass, array(select privs from unnest(ARRAY [

( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'INSERT') THEN 'INSERT' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'DELETE') THEN 'DELETE' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'TRUNCATE') THEN 'TRUNCATE' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'REFERENCES') THEN 'REFERENCES' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'TRIGGER') THEN 'TRIGGER' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and  c.relkind='v' and has_table_privilege($1,c.oid,'SELECT, INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER') AND has_schema_privilege($1,c.relnamespace,'USAGE')

$$ language sql;

查看序列权限

CREATE OR REPLACE FUNCTION sequence_privs(text) RETURNS table(username text, sequence regclass, privileges text[])

AS

$$

  SELECT $1, c.oid::regclass, array(select privs from unnest(ARRAY [

( CASE WHEN has_table_privilege($1,c.oid,'SELECT') THEN 'SELECT' ELSE NULL END),

(CASE WHEN has_table_privilege($1,c.oid,'UPDATE') THEN 'UPDATE' ELSE NULL END)]) foo(privs) where privs is not null) FROM pg_class c JOIN pg_namespace n on c.relnamespace=n.oid where n.nspname not in ('information_schema','pg_catalog','sys') and  c.relkind='S' and

has_table_privilege($1,c.oid,'SELECT,UPDATE')  AND has_schema_privilege($1,c.relnamespace,'USAGE')

$$ language sql;

然后配合union all，看看效果，这样我就批次查出某个用户拥有的权限了，包括库级、模式、表、视图、序列等。

postgres=# select * from (

select username,'SCHEMA' as object_type,schemaname as object_name,privileges

    FROM schema_privs('xiongcc')

 UNION ALL

SELECT username,'TABLE' as object_type ,relname::name as object_name ,privs

 FROM table_privs('xiongcc') 

  UNION ALL

SELECT username,'DATABASE' as object_type ,dbname as object_name ,privileges

 FROM database_privs('xiongcc') 

 UNION ALL

SELECT username,'TABLESPACE' as object_type ,spcname as object_name ,privileges

 FROM tablespace_privs('xiongcc') 

 ) as text1 order by 2 ;

 username | object_type |    object_name     |                        privileges                         

----------+-------------+--------------------+-----------------------------------------------------------

 xiongcc  | DATABASE    | mydatabase1        | {CONNECT,CREATE,TEMPORARY,CONNECT}

 xiongcc  | DATABASE    | template1          | {CONNECT}

 xiongcc  | DATABASE    | benchmarksql       | {CONNECT,TEMPORARY,CONNECT}

 xiongcc  | DATABASE    | mydb               | {CONNECT,TEMPORARY,CONNECT}

 xiongcc  | DATABASE    | postgres           | {CONNECT,TEMPORARY,CONNECT}

 xiongcc  | SCHEMA      | pg_catalog         | {USAGE}

 xiongcc  | SCHEMA      | information_schema | {USAGE}

 xiongcc  | SCHEMA      | schema1            | {CREATE,USAGE}

 xiongcc  | SCHEMA      | public             | {USAGE}

 xiongcc  | TABLE       | test99             | {SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER}

(10 rows)

postgres=# select * from (

 SELECT username,'VIEW' as object_type ,viewname as object_name ,privileges

 FROM view_privs('xiongcc') 

  UNION ALL

SELECT username,'SEQUENCE' as object_type ,sequence as object_name ,privileges

 FROM sequence_privs('xiongcc') 

 ) as text1 order by 2 ;

 username | object_type |    object_name     |                        privileges                         

----------+-------------+--------------------+-----------------------------------------------------------

 xiongcc  | SEQUENCE    | myseq1             | {SELECT,UPDATE}

 xiongcc  | VIEW        | pg_stat_statements | {SELECT}

 xiongcc  | VIEW        | myview1            | {SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER}

 xiongcc  | VIEW        | pg_show_plans      | {SELECT}

(4 rows)

小结

PostgreSQL中的权限确实有点绕，不过搞清楚之后就不是大问题了。