PostgreSQL技術大講堂 - Part 8：PG物件許可權管理

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

Part 8：PG物件管理

內容1：PostgreSQL 物件許可權概述

內容2：PostgreSQL 物件許可權授權

內容3：PostgreSQL 檢視物件許可權

內容4：PostgreSQL 物件許可權回收

8.1、物件許可權概述

1、每個資料庫物件都有一個所有者，預設情況下，所有者擁有該物件的所有許可權

2、在資料庫中所有的許可權都和角色掛鉤

3、對超級使用者postgres不做許可權檢查，其它使用者走ACL(Access ControL List)

4、對於資料庫物件，開始只有所有者和超級使用者可以做任何操作，其它走ACL

物件級別的許可權

表級物件許可權控制

列級別許可權控制

序列許可權控制

型別域的許可權控制(域簡單來說就是自定義的帶約束的資料型別)

FDW許可權控制

FS許可權控制

函式許可權控制

\h GRANT顯示所有可設定的訪問許可權

物件許可權列表

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

物件許可權含義

SELECT：允許從指定表，檢視或序列的任何列或列出的特定列進行SELECT。也允許使用COPY TO。在 UPDATE或DELETE中引用現有列值也需要此許可權。對於序列，此許可權還允許使用currval函式。對於大物件，此許可權允許讀取物件。

INSERT：允許將新行INSERT到指定的表中。如果列出了特定列，則只能在INSERT命令中為這些列分配(因 此其他列將接收預設值)。也允許COPY FROM。

UPDATE：允許更新指定表的任何列或列出的特定列，需要SELECT許可權。

DELETE：允許刪除指定表中的行，需要SELECT許可權。

TRUNCATE：允許在指定的表上截斷資料。

REFERENCES：允許建立引用指定表或表的指定列的外來鍵約束。

TRIGGER：允許在指定的表上建立觸發器。

CREATE：對於資料庫，允許在資料庫中建立新的schema、table、index。

CONNECT：允許使用者連線到指定的資料庫。在連線啟動時檢查此許可權。

TEMPORARY、TEMP：允許在使用指定資料庫時建立臨時表。

EXECUTE：允許使用指定的函式或過程以及在函式。

USAGE：對於schema，允許訪問指定模式中包含的物件;對於sequence，允許使用currval和nextval函式。對於型別和域，允許在建立表，函式和其他模式物件時使用型別或域。

ALL PRIVILEGES：一次授予所有可用許可權。

8.2、物件許可權授權

物件許可權授權 

每種型別的物件許可權都不一樣，詳細可參考：

基本語法參考(表物件)：

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 ON tab_name TO role_name;

授權多個/所有許可權給使用者

GRANT SELECT, UPDATE, INSERT ON tab_name TO role_name; GRANT ALL ON tab_name TO role_name;

授權某模式下所有表的查詢許可權給使用者

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

授權列許可權給使用者

GRANT SELECT (col1), UPDATE (col1) ON tab_name TO role_name;

授權所有許可權給所有使用者

GRANT ALL ON tab_name TO public;

8.3、檢視物件許可權

檢視物件許可權

檢視物件許可權資料字典表

information_schema.table_privileges

顯示物件的訪問許可權列表

\z或\dp [tablename]

檢視物件許可權示例

檢視物件許可權資料字典表

select grantor,grantee,privilege_type,is_grantable from information_schema.table_privileges where table_name='t1';

檢視物件許可權示例

 顯示物件的訪問許可權列表

\z或\dp [tablename]

8.4、物件許可權回收

回收示例

回收單個許可權

REVOKE SELECT ON tab_name FROM role_name;

回收多個/所有許可權

REVOKE SELECT, UPDATE, INSERT ON tab_name FROM role_name; 

REVOKE ALL ON tab_name FROM role_name;

回收某模式下所有表的查詢許可權

REVOKE SELECT ON ALL TABLES IN SCHEMA schema_name FROM role_name;

回收列許可權

REVOKE SELECT (col1), UPDATE (col1) ON tab_name FROM role_name;

回收所有許可權

REVOKE ALL ON tab_name FROM public;

回收示例(特例) 

任何使用者對public模式都有all的許可權，為了安全可以禁止使用者對public 模式的create許可權。

REVOKE CREATE ON SCHEMA public FROM public;

屬主可以取消自己在指定表上的某些許可權

REVOKE UPDAE ON tab_name FROM role_name;REVOKE ALL ON tab_name FROM role_name;

屬主可以授權自己在指定表上的某些許可權

GRANT ALL ON tab_name TO role_name;