PostgreSQL 对象权限如何在元数据中获取 - 权限解读、定制化导出权限
背景
在PostgreSQL中，如何查询出指定用户的：系统权限、角色权限，以及其他用户的对象权限？
实际上PostgreSQL中所有权限都以ACL的形式存储在元数据中，所以权限并不是在某一张与用户挂钩的元数据表里面，而是分散在不同类型的对象的元数据中，以一个ACL字段存在。
导出与某个用户相关的权限方法1
用pgdump，导出DDL，以及PRIVILETE， 然后在导出文本中根据关键字filter
导出与某个用户相关的权限方法2
根据数据库的逻辑结构与权限体系，直接从元数据中获取对象权限。
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