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 &amp; 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.