Return to Answer

• Removing duplicates (the same role can appear in multiple places in the "tree" of parent roles)
• Does not use the magic ::regrole to get the name. This wraps the role name in double quotes in some cases, which can make it more awkward to use (at least for my cases)

WITH RECURSIVE granted_roles AS (
    SELECT r.oid, r.rolname
    FROM pg_roles r
    WHERE r.rolname = 'maxwell'
  UNION
    SELECT r.oid, r.rolname
    FROM granted_roles g
    INNER JOIN pg_auth_members m ON m.member = g.oid
    INNER JOIN pg_roles r ON r.oid = m.roleid
)
SELECT *
FROM granted_roles;

WITH RECURSIVE granted_roles AS (
    SELECT r.oid, r.rolname, r.rolinherit 
    FROM pg_roles r
    WHERE rolname = 'maxwell'
  UNION
    SELECT r.oid, r.rolname, r.rolinherit
    FROM granted_roles g
    INNER JOIN pg_auth_members m ON m.member = g.oid
    INNER JOIN pg_roles r ON r.oid = m.roleid
    WHERE g.rolinherit = TRUE -- Do not walk up tree beyond NOINHERIT
)
SELECT oid, rolname
FROM granted_roles;

WITH RECURSIVE granted_roles AS (
    SELECT r.oid, r.rolname, r.rolinherit, ARRAY[]::name[] AS path
    FROM pg_roles r
    WHERE rolname = 'maxwell'
  UNION ALL
    SELECT r.oid, r.rolname, r.rolinherit, g.path || ARRAY[r.rolname]
    FROM granted_roles g
    INNER JOIN pg_auth_members m ON m.member = g.oid
    INNER JOIN pg_roles r ON r.oid = m.roleid
    WHERE g.rolinherit = TRUE -- Do not walk up tree beyond NOINHERIT
)
SELECT oid, rolname, path
FROM granted_roles;

  oid  | rolname |       path        
-------+---------+-------------------
 16401 | maxwell | {}
 16406 | editors | {editors}
 16405 | authors | {editors,authors}

(In this query, duplicates are not removed because their paths would be different)

• Removing duplicates (the same role can appear in multiple places in the "tree" of parent roles)
• Does not use the magic ::regrole to get the name. This wraps the role name in double quotes in some cases, which can make it more awkward to use (at least for my cases)
• Does not include the role you're querying for in the output

WITH RECURSIVE granted_roles AS (
    SELECT r.oid, r.rolname, 1 AS depth
    FROM pg_roles r
    WHERE r.rolname = 'maxwell'
  UNION
    SELECT r.oid, r.rolname, g.depth + 1 AS depth
    FROM granted_roles g
    INNER JOIN pg_auth_members m ON m.member = g.oid
    INNER JOIN pg_roles r ON r.oid = m.roleid
)
SELECT g.oid, g.rolname
FROM granted_roles g
WHERE g.depth > 1;

WITH RECURSIVE granted_roles AS (
    SELECT r.oid, r.rolname, r.rolinherit, 1 AS depth
    FROM pg_roles r
    WHERE rolname = 'maxwell'
  UNION
    SELECT r.oid, r.rolname, r.rolinherit, g.depth + 1 AS depth
    FROM granted_roles g
    INNER JOIN pg_auth_members m ON m.member = g.oid
    INNER JOIN pg_roles r ON r.oid = m.roleid
    WHERE g.rolinherit = TRUE -- Do not walk up tree beyond NOINHERIT
)
SELECT g.oid, g.rolname
FROM granted_roles g
WHERE g.depth > 1;

WITH RECURSIVE granted_roles AS (
    SELECT r.oid, r.rolname, r.rolinherit, ARRAY[]::name[] AS path
    FROM pg_roles r
    WHERE rolname = 'maxwell'
  UNION ALL
    SELECT r.oid, r.rolname, r.rolinherit, g.path || ARRAY[r.rolname]
    FROM granted_roles g
    INNER JOIN pg_auth_members m ON m.member = g.oid
    INNER JOIN pg_roles r ON r.oid = m.roleid
    WHERE g.rolinherit = TRUE -- Do not walk up tree beyond NOINHERIT
)
SELECT g.oid, g.rolname, g.path
FROM granted_roles g
WHERE array_length(g.path, 1) > 0;

  oid  | rolname |       path        
-------+---------+-------------------
 16406 | editors | {editors}
 16405 | authors | {editors,authors}

(In this query, duplicates are not removed because their paths would be different)

(Some of the above could maybe be simpler if you allowed the role name you're searching for, 'maxwell' in this case, in multiple places in the SQL, but for me it's convenient to just be in the one)

But also say if you want the "path" of grants leading to a particular role:

WITH RECURSIVE granted_roles AS (
    SELECT r.oid, r.rolname, r.rolinherit, ARRAY[]::name[] AS path
    FROM pg_roles r
    WHERE rolname = 'maxwell'
  UNION ALL
    SELECT r.oid, r.rolname, r.rolinherit, g.path || ARRAY[r.rolname]
    FROM granted_roles g
    INNER JOIN pg_auth_members m ON m.member = g.oid
    INNER JOIN pg_roles r ON r.oid = m.roleid
    WHERE g.rolinherit = TRUE -- Do not walk up tree beyond NOINHERIT
)
SELECT oid, rolname, path
FROM granted_roles;

which would output something like this:

  oid  | rolname |       path        
-------+---------+-------------------
 16401 | maxwell | {}
 16406 | editors | {editors}
 16405 | authors | {editors,authors}

(In this query, duplicates are not removed because their paths would be different)