How to get all roles that a user is a member of (including inherited roles)?

Question

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "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

I would like to write a performant function that returns a list of the roles (preferably their oid's) that maxwell belongs to, something like this:

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

It should return the oids for maxwell, authors, and editors (but not ernest).

But I am not sure how to do it when there is inheritance.

Answer 1

You can query the system catalog with a recursive query, in particular pg_auth_members:

WITH RECURSIVE cte AS (
   SELECT oid, 0 AS steps, true AS inherit_option
   FROM   pg_roles
   WHERE  rolname = 'maxwell'

   UNION ALL
   SELECT m.roleid, c.steps + 1, c.inherit_option AND m.inherit_option
   FROM   cte c
   JOIN   pg_auth_members m ON m.member = c.oid
   )
SELECT oid, oid::regrole::text AS rolename, steps, inherit_option
FROM   cte;

The manual about the cast to object identifier type regrole.

This only lists memberships with an explicit entry in pg_auth_members. Does not list pg_database_owner, where the owner of the DB is (the only) implicit member.

I added steps to indicate the number of steps to this membership.
And inherit_option to indicate whether privileges from this role are inherited automatically - only true with an unbroken chain of memberships with INHERITS.
Might be extended in a similar manner for set_option.
And for admin_option (not requiring an unbroken chain).

BTW 1: INHERIT is the default behavior of CREATE ROLE and doesn't have to be spelled out. GRANT inherits that setting unless specified explicitly.

BTW 2: circular dependencies are not possible. Postgres disallows that. So we don't have to check for that.

BTW 3: But membership in the same role can be established by multiple paths, so roles can be duplicated. Throw in DISTINCT or GROUP BY in the outer SELECT to get distinct roles.

Answer 2

This is a simplified version of Craig Ringer's answer that a non superuser can use directly:

 SELECT oid, rolname FROM pg_roles WHERE
   pg_has_role( 'maxwell', oid, 'member');

pg_roles is essentially a view on pg_authid accessible to public, as it doesn't reveal passwords, contrary to pg_authid. The base oid is even exported into the view. When not needing passwords, there's no point in creating the dedicated superuser-owned function.

Answer 3

Short version:

SELECT a.oid 
FROM pg_authid a 
WHERE pg_has_role('maxwell', a.oid, 'member');

Here we use a version of pg_has_role that takes a role name as the subject and role oid to test for membership, passing member mode so we test for inherited memberships.

The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role information to satisfy membership queries quickly.

You might want to wrap this in a SECURITY DEFINER function, since pg_authid has restricted access. Something like:

CREATE OR REPLACE FUNCTION user_role_memberships(text)
RETURNS SETOF oid
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
SELECT a.oid 
FROM pg_authid a 
WHERE pg_has_role($1, a.oid, 'member');
$$;

REVOKE EXECUTE ON FUNCTION user_role_memberships(text) FROM public;

GRANT EXECUTE ON FUNCTION user_role_memberships(text) TO ...whoever...;

You can use pg_get_userbyid(oid) to get the role name from the oid without the need to query pg_authid:

SELECT a.oid AS member_oid, pg_get_userbyid(oid) AS member_name
FROM pg_authid a 
WHERE pg_has_role('maxwell', a.oid, 'member');

Answer 4

Here is my take on it. It works for one specific user or all users.

select a.oid as user_role_id
, a.rolname as user_role_name
, b.roleid as other_role_id
, c.rolname as other_role_name
from pg_roles a
inner join pg_auth_members b on a.oid=b.member
inner join pg_roles c on b.roleid=c.oid 
where a.rolname = 'user_1'

Answer 5

I believe this will do it

SELECT 
    oid 
FROM 
    pg_roles 
WHERE 
    oid IN (SELECT 
                roleid 
            FROM 
                pg_auth_members 
            WHERE 
                member=(SELECT oid FROM pg_roles WHERE rolname='maxwell'));

If you prefer to get the role names then replace the first oid with rolname.

Answer 6

if you want to know all roles of your currently active role:

CREATE OR REPLACE VIEW public.my_roles
AS WITH RECURSIVE cte AS (
         SELECT pg_roles.oid,
            pg_roles.rolname
           FROM pg_roles
          WHERE pg_roles.rolname = CURRENT_USER
        UNION ALL
         SELECT m.roleid,
            pgr.rolname
           FROM cte cte_1
             JOIN pg_auth_members m ON m.member = cte_1.oid
             JOIN pg_roles pgr ON pgr.oid = m.roleid
        )
 SELECT array_agg(cte.rolname) AS my_roles
   FROM cte;

Answer 7

Strongly based on Erwin Brandstetter's answer, but also:

• 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;

As also mentioned in Erwin's answer, it does not check the INHERIT flag of each role. If you want to only include INHERITed roles you can use this query (for PostgreSQL pre-16):

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;

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 g.oid, g.rolname, g.path
FROM granted_roles g
WHERE array_length(g.path, 1) > 0;

which would output something like this:

  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)