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

The manual about the cast to object identifier type regrole.

BTW 1: INHERIT is the default behavior of CREATE ROLE and doesn't have to be spelled out.

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

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;