如何獲取使用者所屬的所有角色（包括繼承的角色）？

假設我有兩個 Postgresql 數據庫組，“authors”和“editors”，以及兩個使用者，“maxwell”和“ernest”。

create role authors;

create role editors;

create user maxwell;

create user ernest;

grant authors to editors; --editors can do what authors can do

grant editors to maxwell; --maxwell is an editor

grant authors to ernest; --ernest is an author

我想編寫一個高性能函式，它返回 maxwell 所屬的角色列表（最好是他們的 oid），如下所示：

create or replace function get_all_roles() returns oid[] ...

它應該返回 maxwell、authors 和 editors（但不是 ernest）的 oid。

但是我不確定當有繼承時該怎麼做。

您可以使用遞歸查詢來查詢系統目錄，特別是pg_auth_members：

WITH RECURSIVE cte AS (
  SELECT oid FROM pg_roles WHERE rolname = 'maxwell'

  UNION ALL
  SELECT m.roleid
  FROM   cte
  JOIN   pg_auth_members m ON m.member = cte.oid
  )
SELECT oid, oid::regrole::text AS rolename FROM cte;  -- oid & name

關於轉換為對象標識符類型的手冊regrole。

BTW 1:INHERIT是的預設行為，CREATE ROLE不必詳細說明。

BTW 2：循環依賴是不可能的。Postgres 不允許這樣做。所以我們不必檢查。

引用自：https://dba.stackexchange.com/questions/56096