PostgreSQL 对象权限如何在元数据中获取 - 权限解读、定制化导出权限

10 minute read

背景

在PostgreSQL中,如何查询出指定用户的:系统权限、角色权限,以及其他用户的对象权限?

实际上PostgreSQL中所有权限都以ACL的形式存储在元数据中,所以权限并不是在某一张与用户挂钩的元数据表里面,而是分散在不同类型的对象的元数据中,以一个ACL字段存在。

导出与某个用户相关的权限方法1

用pgdump,导出DDL,以及PRIVILETE, 然后在导出文本中根据关键字filter

导出与某个用户相关的权限方法2

《PostgreSQL 逻辑结构 和 权限体系 介绍》

根据数据库的逻辑结构与权限体系,直接从元数据中获取对象权限。

1 数据库中有哪些对象可以赋权

通过grant命令可以看到,可以赋权的对象包括:

表、视图、物化视图、序列、外部表、数据库、域、类型、fdw、FS、函数、存储过程、routine、函数语言、大对象、SCHEMA、表空间、成员关系。

postgres=# \h grant  
Command:     GRANT  
Description: define access privileges  
Syntax:  
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON { [ TABLE ] table_name [, ...]  
         | ALL TABLES IN SCHEMA schema_name [, ...] }  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )  
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }  
    ON [ TABLE ] table_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { USAGE | SELECT | UPDATE }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON { SEQUENCE sequence_name [, ...]  
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }  
    ON DATABASE database_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON DOMAIN domain_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN DATA WRAPPER fdw_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN SERVER server_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]  
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON LANGUAGE lang_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }  
    ON LARGE OBJECT loid [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }  
    ON SCHEMA schema_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { CREATE | ALL [ PRIVILEGES ] }  
    ON TABLESPACE tablespace_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPE type_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
where role_specification can be:  
  
    [ GROUP ] role_name  
  | PUBLIC  
  | CURRENT_USER  
  | SESSION_USER  
  
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]  

2 获取对象OWNER

owner 拥有对象的所有权限.

不同的对象类型,元数据在不同的元数据表里面,元数据里面包括了OWNER,OID,ACL等

1、表、视图、物化视图、序列、外部表、

pg_class  

2、数据库、

pg_database  

3、域、类型、

pg_type  

4、fdw、

pg_foreign_data_wrapper  

5、FS、

pg_foreign_server  

6、函数、存储过程、routine、

pg_proc  

7、函数语言、

pg_language  

8、大对象、

pg_largeobject_metadata  

9、SCHEMA、

pg_namespace  

10、表空间、

pg_tablespace  

11、成员关系。

pg_auth_members  
  
角色,角色中有哪些成员.   

从以上所有元数据中,可以获取到所有对象对应的OWNER。

3 获取对象的,已赋予给指定角色的权限

方法与2相同,从不同对象的元数据中,获取对象对应的ACL的信息。

pg_class 权限 (SEQUENCE, TABLE, 视图, 物化视图)  
  
pg_database 权限 (DATABASE)  
  
pg_type 权限 (DOMAIN, TYPE)  
  
pg_foreign_data_wrapper 权限 (FOREIGN DATA WRAPPER)  
  
pg_foreign_server 权限 (FOREIGN SERVER)  
  
pg_proc 权限 (FUNCTION | PROCEDURE | ROUTINE)  
  
pg_language 权限 (LANGUAGE)  
  
pg_largeobject_metadata 权限 (LARGE OBJECT)  
  
pg_namespace 权限 (SCHEMA)  
  
pg_tablespace 权限 (TABLESPACE)  
  
pg_auth_members 权限 (MEMBER SHIP)  

ACL解读含义如下

https://www.postgresql.org/docs/current/static/sql-grant.html

rolename=xxxx -- privileges granted to a role  
        =xxxx -- privileges granted to PUBLIC  
  
            r -- SELECT ("read")  
            w -- UPDATE ("write")  
            a -- INSERT ("append")  
            d -- DELETE  
            D -- TRUNCATE  
            x -- REFERENCES  
            t -- TRIGGER  
            X -- EXECUTE  
            U -- USAGE  
            C -- CREATE  
            c -- CONNECT  
            T -- TEMPORARY  
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)  
            * -- grant option for preceding privilege  
  
        /yyyy -- role that granted this privilege  

4 获取对象的,已赋予给PUBLIC角色的权限

实际上方法还是与2相同,只是解读ACL时,需要注意:

PUBLIC角色代表所有用户,在ACL中显示为等号前面没有角色名。

rolename=xxxx -- privileges granted to a role  
        =xxxx -- privileges granted to PUBLIC  
  
            r -- SELECT ("read")  
            w -- UPDATE ("write")  
            a -- INSERT ("append")  
            d -- DELETE  
            D -- TRUNCATE  
            x -- REFERENCES  
            t -- TRIGGER  
            X -- EXECUTE  
            U -- USAGE  
            C -- CREATE  
            c -- CONNECT  
            T -- TEMPORARY  
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)  
            * -- grant option for preceding privilege  
  
        /yyyy -- role that granted this privilege  

5 获取系统的默认权限

1、首先如何赋予将来创建的对象的默认权限

postgres=# \h alter default  
Command:     ALTER DEFAULT PRIVILEGES  
Description: define default access privileges  
Syntax:  
ALTER DEFAULT PRIVILEGES  
    [ FOR { ROLE | USER } target_role [, ...] ]  
    [ IN SCHEMA schema_name [, ...] ]  
    abbreviated_grant_or_revoke  
  
where abbreviated_grant_or_revoke is one of:  
  
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON TABLES  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { USAGE | SELECT | UPDATE }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON SEQUENCES  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { FUNCTIONS | ROUTINES }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPES  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }  
    ON SCHEMAS  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON TABLES  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { { USAGE | SELECT | UPDATE }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON SEQUENCES  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { FUNCTIONS | ROUTINES }  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPES  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { USAGE | CREATE | ALL [ PRIVILEGES ] }  
    ON SCHEMAS  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  

2、获取默认权限

postgres=# select * from pg_default_acl ;  
 defaclrole | defaclnamespace | defaclobjtype |   defaclacl     
------------+-----------------+---------------+---------------  
         10 |            2200 | r             | {=r/postgres}  
(1 row)  

6 获取用户的成员关系

1、角色 member ship

postgres=# select oid,* from pg_roles ;  
  oid  |          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid    
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------  
  4200 | pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200  
  4569 | pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4569  
    10 | postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10  
  4570 | pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4570  
  4571 | pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4571  
  3375 | pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375  
 16487 | test                      | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16487  
  3373 | pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373  
  3374 | pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374  
  3377 | pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377  
(10 rows)  

2、

postgres=# select * from pg_auth_members ;  
 roleid | member | grantor | admin_option   
--------+--------+---------+--------------  
  
角色,成员,赋权者,成员是否可以二次赋权  
  
   3374 |   3373 |      10 | f  
   3375 |   3373 |      10 | f  
   3377 |   3373 |      10 | f  
     10 |  16487 |      10 | f  
(4 rows)  

小结

以上5个部分包含了所有的对象权限,用户可以根据需要自行组合导出。

除了以上提到的对象,还有索引、操作符等其他对象,他们没有单独的权限体系,但是他们依附于其他对象比如索引依附于表,操作符依附于函数。

https://www.postgresql.org/docs/10/static/catalogs.html

其他辅助工具

1、psql客户端

psql -E  
  
  
\?  
  
postgres=# \?  
General  
  \copyright             show PostgreSQL usage and distribution terms  
  \crosstabview [COLUMNS] execute query and display results in crosstab  
  \errverbose            show most recent error message at maximum verbosity  
  \g [FILE] or ;         execute query (and send results to file or |pipe)  
  \gdesc                 describe result of query, without executing it  
  \gexec                 execute query, then execute each value in its result  
  \gset [PREFIX]         execute query and store results in psql variables  
  \gx [FILE]             as \g, but forces expanded output mode  
  \q                     quit psql  
  \watch [SEC]           execute query every SEC seconds  
  
Help  
  \? [commands]          show help on backslash commands  
  \? options             show help on psql command-line options  
  \? variables           show help on special variables  
  \h [NAME]              help on syntax of SQL commands, * for all commands  
  
Query Buffer  
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor  
  \ef [FUNCNAME [LINE]]  edit function definition with external editor  
  \ev [VIEWNAME [LINE]]  edit view definition with external editor  
  \p                     show the contents of the query buffer  
  \r                     reset (clear) the query buffer  
  \s [FILE]              display history or save it to file  
  \w FILE                write query buffer to file  
  
Input/Output  
  \copy ...              perform SQL COPY with data stream to the client host  
  \echo [STRING]         write string to standard output  
  \i FILE                execute commands from file  
  \ir FILE               as \i, but relative to location of current script  
  \o [FILE]              send all query results to file or |pipe  
  \qecho [STRING]        write string to query output stream (see \o)  
  
Conditional  
  \if EXPR               begin conditional block  
  \elif EXPR             alternative within current conditional block  
  \else                  final alternative within current conditional block  
  \endif                 end conditional block  
  
Informational  
  (options: S = show system objects, + = additional detail)  
  \d[S+]                 list tables, views, and sequences  
  \d[S+]  NAME           describe table, view, sequence, or index  
  \da[S]  [PATTERN]      list aggregates  
  \dA[+]  [PATTERN]      list access methods  
  \db[+]  [PATTERN]      list tablespaces  
  \dc[S+] [PATTERN]      list conversions  
  \dC[+]  [PATTERN]      list casts  
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere  
  \dD[S+] [PATTERN]      list domains  
  \ddp    [PATTERN]      list default privileges  
  \dE[S+] [PATTERN]      list foreign tables  
  \det[+] [PATTERN]      list foreign tables  
  \des[+] [PATTERN]      list foreign servers  
  \deu[+] [PATTERN]      list user mappings  
  \dew[+] [PATTERN]      list foreign-data wrappers  
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions  
  \dF[+]  [PATTERN]      list text search configurations  
  \dFd[+] [PATTERN]      list text search dictionaries  
  \dFp[+] [PATTERN]      list text search parsers  
  \dFt[+] [PATTERN]      list text search templates  
  \dg[S+] [PATTERN]      list roles  
  \di[S+] [PATTERN]      list indexes  
  \dl                    list large objects, same as \lo_list  
  \dL[S+] [PATTERN]      list procedural languages  
  \dm[S+] [PATTERN]      list materialized views  
  \dn[S+] [PATTERN]      list schemas  
  \do[S]  [PATTERN]      list operators  
  \dO[S+] [PATTERN]      list collations  
  \dp     [PATTERN]      list table, view, and sequence access privileges  
  \drds [PATRN1 [PATRN2]] list per-database role settings  
  \dRp[+] [PATTERN]      list replication publications  
  \dRs[+] [PATTERN]      list replication subscriptions  
  \ds[S+] [PATTERN]      list sequences  
  \dt[S+] [PATTERN]      list tables  
  \dT[S+] [PATTERN]      list data types  
  \du[S+] [PATTERN]      list roles  
  \dv[S+] [PATTERN]      list views  
  \dx[+]  [PATTERN]      list extensions  
  \dy     [PATTERN]      list event triggers  
  \l[+]   [PATTERN]      list databases  
  \sf[+]  FUNCNAME       show a function's definition  
  \sv[+]  VIEWNAME       show a view's definition  
  \z      [PATTERN]      same as \dp  
  
Formatting  
  \a                     toggle between unaligned and aligned output mode  
  \C [STRING]            set table title, or unset if none  
  \f [STRING]            show or set field separator for unaligned query output  
  \H                     toggle HTML output mode (currently off)  
  \pset [NAME [VALUE]]   set table output option  
                         (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|  
                         footer|format|linestyle|null|numericlocale|pager|  
                         pager_min_lines|recordsep|recordsep_zero|tableattr|title|  
                         tuples_only|unicode_border_linestyle|  
                         unicode_column_linestyle|unicode_header_linestyle})  
  \t [on|off]            show only rows (currently off)  
  \T [STRING]            set HTML <table> tag attributes, or unset if none  
  \x [on|off|auto]       toggle expanded output (currently off)  
  
Connection  
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}  
                         connect to new database (currently "postgres")  
  \conninfo              display information about current connection  
  \encoding [ENCODING]   show or set client encoding  
  \password [USERNAME]   securely change the password for a user  
  
Operating System  
  \cd [DIR]              change the current working directory  
  \setenv NAME [VALUE]   set or unset environment variable  
  \timing [on|off]       toggle timing of commands (currently off)  
  \! [COMMAND]           execute command in shell or start interactive shell  
  
Variables  
  \prompt [TEXT] NAME    prompt user to set internal variable  
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters  
  \unset NAME            unset (delete) internal variable  
  
Large Objects  
  \lo_export LOBOID FILE  
  \lo_import FILE [COMMENT]  
  \lo_list  
  \lo_unlink LOBOID      large object operations  

Flag Counter

digoal’s 大量PostgreSQL文章入口