How to get all roles that a user is a member of (including inherited roles)?
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.
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.
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');