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：賦予更新資料庫物件中儲存的資料行值的能力。SELECT權限對於大多數UPDATE操作也是必要的。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 被授予 DELETE customers 中資料的能力，並帶有 WITH GRANT OPTION，他可以反過來選擇性地將該能力授予 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 將更新 records 資料庫內容的能力授予 pete，則 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 權限，但將不再能夠傳遞該權限
從 moderators 角色中移除 natasha
從 tony 撤銷管理 hr 角色的成員資格的能力。請記住，tony 仍然是 hr 的成員
結論
PostgreSQL 的授與與權限系統允許您為特定資料庫物件上的個別角色定義細緻的權限。授與系統將 PostgreSQL 的授權控制擴展到可以由其所有者管理的個別物件。
這種安排允許個別使用者對自己的資料庫物件行使控制權。他們可以授予和撤銷存取權，以及將某些管理功能委派給其他角色。此外，相同的安排也用於實作角色成員資格，以簡化權限管理。
