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](http://www.postgresql.org/docs/current/static/functions-info.html), 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');