指定したロールに属するロールを再帰クエリで検索する #PostgreSQL

指定したロール名に属する、全てのロール名を検索する例。

以下のSQLでは user_x というロール名が包含する全てのロール名を検索する。

WITH RECURSIVE t AS (
  SELECT usesysid as roleid FROM pg_user WHERE usename = 'user_x'
UNION ALL
  SELECT member as roleid FROM pg_auth_members pam JOIN t ON (pam.roleid= t.roleid)
) SELECT pu.usename FROM t JOIN pg_user pu ON (t.roleid = pu.usesysid);

指定したロールIDに属する、全てのロールIDを検索するSQLの例。

以下のSQLでは 16396 というロールidが包含する全てのロールidを検索する。

WITH RECURSIVE t AS (
  SELECT usesysid roleid FROM pg_user WHERE usesysid = 16396
UNION ALL
  SELECT member as roleid FROM pg_auth_members pam JOIN t ON (pam.roleid= t.roleid)
) SELECT roleid FROM t;