PostgreSQL物件許可權如何在後設資料中獲取-許可權解讀、定製化匯出許可權
標籤
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
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
相關文章
- Oracle的物件許可權、角色許可權、系統許可權Oracle物件
- 選單許可權和按鈕許可權設定
- PostgreSQL：許可權管理SQL
- 許可權之選單許可權
- 許可權系統：一文搞懂功能許可權、資料許可權
- Atitit godaddy 檔案許可權 root許可權設定Go
- Oracle 使用者、物件許可權、系統許可權Oracle物件
- 如何用 Vue 實現前端許可權控制（路由許可權 + 檢視許可權 + 請求許可權）Vue前端路由
- 如何設定許可權？
- linux 檔案許可權 s 許可權和 t 許可權解析Linux
- Windows許可權設定詳解Windows
- Win8.1許可權獲取設定教程
- Win8.1許可權獲取設定技巧
- Linux-許可權管理（ACL許可權）Linux
- AIX 的許可許可權(轉)AI
- ubuntu 許可權管理設定Ubuntu
- 自定義許可權物件物件
- 物件許可權的回收物件
- .NET 程式許可權控制、獲得管理員許可權程式碼
- django開發之許可權管理(一)——許可權管理詳解(許可權管理原理以及方案)、不使用許可權框架的原始授權方式詳解Django框架
- 許可權系統：許可權應用服務設計
- 資料許可權
- Android系統許可權和root許可權Android
- 適配懸浮窗許可權與系統設定修改許可權
- mysql許可權MySql
- MySQL 許可權MySql
- ORACLE許可權Oracle
- 許可權系統：6個許可權概念模型設計模型
- 許可權系統：許可權應用服務設計Tu
- Linux 許可權管理之目錄許可權限制Linux
- 【LIUNX】目錄或檔案許可權，許可權授予
- Guest許可權突破——許可權提升方法總結(轉)
- 對定義者許可權和呼叫者許可權的理解
- 【自然框架】許可權的視訊演示（二）：許可權到欄位、許可權到記錄框架
- 小程式許可權設定（位置）
- PHP專案許可權設定PHP
- LightDB/PostgreSQL標準業務建立語句【賦予讀寫許可權和只讀許可權】SQL
- MySQL 許可權詳解MySql