Ygthkb

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.

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 FROM cte;

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

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

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');

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.

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.

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'