PostgreSQL物件許可權如何在後設資料中獲取-許可權解讀、定製化匯出許可權

德哥發表於2018-10-05

標籤

PostgreSQL , 許可權匯出 , 定製化許可權 , 後設資料


背景

在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  
  
                     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})  
  	 [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  
  	iming [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  


相關文章