PostgreSQL / 認證與授權
在 PostgreSQL 中使用 grant 和 revoke 管理許可權
簡介
控制每個使用者在資料庫中被允許做什麼，是管理資料庫叢集的重要組成部分。PostgreSQL 提供了一套工具來控制認證和授權。
在認證方面，pg_hba.conf 檔案控制人們如何連線到資料庫。這包括允許訪問伺服器的確切使用者、資料庫、連線方法和認證方法組合。授權元件始於 PostgreSQL 的角色和角色屬性概念，它定義了系統中的使用者實體並控制其全域性許可權。
除了角色屬性提供的授權之外，還有更深層次的授權。管理特定資料庫物件的擁有權和授權是控制哪些角色可以管理、修改和檢視資料庫、表、序列等的主要方式。本指南將介紹如何使用 PostgreSQL 的授權和撤銷機制來精確地規定哪些角色可以訪問每個資料庫物件。
什麼是 PostgreSQL 物件許可權？
在關於角色和角色屬性的文章中，我們介紹了為角色定義系統級許可權的概念。透過使用角色屬性，管理員可以定義角色是否可以建立或修改資料庫、管理角色，甚至登入系統本身。這些型別的許可權在整個資料庫叢集中都有效，因此它們在控制對資料庫中單個物件的訪問時沒有粒度。
相反，PostgreSQL 使用一套互補的系統，即對特定資料庫實體進行單獨授權或賦予許可權。這允許資料庫物件的所有者確定哪些角色可以執行哪些型別的操作。這種額外的靈活性和粒度使得多使用者和多租戶部署既可能又實用。
資料庫授權或許可權定義了各種已認證角色可用的特定操作集或訪問級別。PostgreSQL 的授權系統遵循“允許列表”模型，這意味著角色除了被明確授予的許可權外，對資料庫物件沒有任何訪問許可權。
物件所有權和角色成員資格如何影響物件許可權？
該系統的基礎是物件所有權和角色成員資格的概念。在 PostgreSQL 中，每個資料庫物件都只有一個所有者，只有他（以及superuser角色）才擁有更改、刪除和管理物件本身的獨特能力。物件所有者透過授予許可權來管理物件對其他角色的許可權。資料庫物件上授予的每個許可權最終都可以由物件所有者控制。
角色成員資格是一種系統，它賦予角色其所屬於的角色的許可權。具有INHERIT屬性的角色，如果它們是其他角色的成員，將自動獲得其許可權，而無需使用SET ROLE更改當前角色。
例如，假設一個 salesadmin 角色具有修改 sales 資料庫中資料的能力。如果 sally 角色設定了 INHERIT 角色屬性，那麼將 sally 新增到 salesadmin 角色將自動使 sally 能夠修改 sales 資料庫中的資料。繼承特性的能力透過按“功能”角色分組屬性和訪問，然後根據需要將實際“使用者”角色新增到這些功能角色中，從而實現靈活的訪問管理風格。
系統上的每個角色預設都是 PUBLIC 角色的成員。這使得它在定義許可權時與“所有人”同義。
可用物件許可權概述
PostgreSQL 有許多許可權可以授予角色以啟用特定功能。
許可權僅適用於有意義的資料庫物件子集。例如，對資料庫執行“執行”操作是沒有意義的。有關哪些許可權對哪些資料庫物件有效，請參閱 PostgreSQL 文件中的ACL 許可權縮寫表和訪問許可權摘要表。
以下是每個許可權名稱及其功能的列表。您可以在 PostgreSQL 文件中找到每個許可權的完整解釋。
SELECT：SELECT許可權賦予角色從資料庫物件中選擇或讀取的能力。此許可權對於任何引用現有列值的UPDATE和DELETE操作也是必需的。INSERT：提供向表、檢視或列新增新行資料的能力。UPDATE：賦予更新資料庫物件中列儲存值的能力。大多數UPDATE操作也需要SELECT許可權。DELETE：允許角色從表或檢視中刪除行。與UPDATE類似，大多數DELETE操作也需要SELECT許可權才能定位到正確的行。TRUNCATE：TRUNCATE許可權允許角色清空表或檢視中的所有資料。REFERENCES：為角色提供建立引用表或表列的外部索引鍵的能力。TRIGGER：允許角色在表或檢視上定義觸發器。CREATE：允許角色建立資料庫、模式或表空間的子實體。例如，在資料庫上，CREATE許可權允許角色建立新的模式，而在模式上，它允許角色建立新的資料庫。CONNECT：允許角色連線到資料庫。這在連線時檢查。TEMPORARY：允許角色在資料庫中建立臨時表。EXECUTE：賦予角色呼叫函式或儲存過程的許可權。USAGE：允許角色對物件執行基本功能。例如，模式上的USAGE允許角色查詢其中的物件，而序列上的USAGE允許角色呼叫currval和nextval函式。ALL PRIVILEGES：一個簡寫，賦予指定角色對指定物件的所有許可權。
使用 GRANT 命令
GRANT 命令用於角色管理中的兩個獨立但相關的目的
- 向角色授予對資料庫物件的特定許可權
- 將角色新增為其他角色的成員
這兩個功能體現在命令語法的兩種結構中。
要將特定資料庫物件的許可權授予給定角色，請使用以下形式
可選的 WITH GRANT OPTION 子句還賦予接收角色將此能力傳遞給其他角色的能力。例如，如果 adam 獲得了使用 WITH GRANT OPTION 從 customers 中 DELETE 資料的能力，那麼他可以選擇將該能力授予 delores。實際上，這使您能夠讓角色管理特定物件上的某些能力。
另一種用於將一個角色新增到另一個角色的語法如下所示。在這種情況下，<role_member> 獲得了 <provider_role> 的許可權
如果 <role_member> 設定了 INHERIT 屬性，它將立即擁有 <provider_role> 的許可權。如果缺少此屬性，<role_member> 可以透過使用 SET ROLE 更改當前角色來訪問 <provider_role> 的許可權。
與另一種語法中的 WITH GRANT OPTION 子句類似，在授予角色成員資格時，您可以選擇新增 WITH ADMIN OPTION 子句。此子句還賦予角色成員新增新成員的能力。
示例
授予 sam 對 books 資料庫的 CRUD 許可權
允許 sam 委託對 books 資料庫的 CRUD 行為
讓 jasmine 對 customers 表擁有完全許可權
允許 keisha 執行 order_count 函式
將 calin 新增到 admin 角色
將 sofie 新增到 salesperson 角色，並允許她管理成員資格
使用 REVOKE 命令
REVOKE 命令從資料庫物件上的角色中撤銷許可權。在大多數情況下，它與 GRANT 命令的語法相似，有兩種格式用於涵蓋兩種用例。
要從給定角色撤銷資料庫物件上的特定許可權，請使用以下格式
在這種情況下，可以新增可選的 GRANT OPTION FOR 子句，以移除指定角色傳遞給定許可權的能力。當嘗試從已將許可權傳遞給另一個角色的角色撤銷 GRANT OPTION 時，可能會發生衝突。在這種情況下，從第一個角色移除 GRANT OPTION 將打破賦予輔助角色其許可權的授權鏈。
例如，如果 ada 使用 WITH GRANT OPTION 授予 pete 在 records 資料庫上更新內容的能力，那麼 pete 就可以再將更新內容的能力授予 simone。如果 ada 從 pete 撤銷 GRANT OPTION，那麼傳遞給 simone 的更新許可權會發生什麼就不清楚了。
可選的 CASCADE 或 RESTRICT 子句透過明確指定 REVOKE 在此場景中應如何操作來解決此衝突。預設行為是 RESTRICT，如果許可權已傳遞給其他角色，它將導致 REVOKE GRANT OPTION FOR 命令失敗。CASCADE 選項將此行為更改為除了指定角色之外，還從任何“下游”角色撤銷許可權，透過移除斷裂的鏈來解決衝突。
另一種用於撤銷角色成員資格的語法如下所示
在這種情況下，除非透過其他途徑獲得授權，否則 <member_role> 將不再擁有授予 <provider_role> 的許可權。
示例
撤銷 eddy 從 logs 表中刪除行的能力
移除 jerry 對 finances 的所有訪問許可權
撤銷 alice 向其他角色授予對 snacks 表的 DELETE 許可權的能力。請記住，執行此命令後，alice 仍將擁有 DELETE 許可權，但將不再能夠傳遞該許可權
將 natasha 從 moderators 角色中移除
撤銷 tony 管理 hr 角色成員資格的能力。請記住，tony 仍將是 hr 的成員
結論
PostgreSQL 的授權和許可權系統允許您為特定資料庫物件上的單個角色定義精細的許可權。授權系統將 PostgreSQL 的授權控制擴充套件到可以由其所有者管理的單個物件。
這種安排允許單個使用者控制他們自己的資料庫物件。他們可以授予和撤銷訪問許可權，並向其他角色委託某些管理功能。此外，相同的安排也用於實現角色成員資格，以簡化許可權管理。
