Explanation: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.
This information is exposed for the current user in theThe advantage of using information_schemapg_has_role
is that it uses PostgreSQL's internal caches of role information to satisfy membership queries quickly. The view:
select * from information_schema.applicable_roles
only shows you information for the current user, but if you examine the definition ofYou might want to wrap this view you can see how you can use it for roles other than the current role, and simplify itin a bitSECURITY DEFINER
function, since pg_authid
has restricted access. The definitionSomething like:
postgres=> select pg_get_viewdef('information_schema.applicable_roles');
pg_get_viewdef
---------------------------------------------------------------------
SELECT (a.rolname)::information_schema.sql_identifier AS grantee, +
CREATE OR REPLACE FUNCTION user_role_memberships(b.rolnametext)::information_schema.sql_identifier AS role_name, +
( RETURNS SETOF +oid
CASE LANGUAGE +sql
WHEN m.admin_option THEN 'YES'::text SECURITY +DEFINER
ELSE 'NO'::text SET search_path = +'pg_catalog'
END)::information_schema.yes_or_no AS is_grantable +
FROM ((pg_auth_members m +$$
JOIN pg_authid a ON ((m.member =SELECT a.oid))) +
JOINFROM pg_authid b ON ((m.roleid = b.oid))) a +
WHERE pg_has_role($1, a.oid, 'USAGE'::text'member');
(1 row)$$;
Since you only want oids, you can use:
SELECT member
FROM pg_auth_membersREVOKE m
WHEREEXECUTE pg_has_role('maxwell',ON m.member,FUNCTION 'member'user_role_memberships(text);
using a different version of pg_has_role
that takes a role name as the subject and role oid to test for membership. It also passes member
instead of usage
so it finds roles that are available but not currently active.
However, as Erwin pointed out, this doesn't handle recursive memberships correctly, even though pg_has_role
in member
mode does. That's because not all the rows we need are present in pg_auth_members
at all. We can instead query pg_authid
directly for roles we're a member of:
SELECT a.oidFROM public;
FROM
GRANT pg_authidEXECUTE aON
WHEREFUNCTION pg_has_roleuser_role_memberships('maxwell',text) a.oid,TO 'member')...whoever...;
The advantage of using pg_has_role
is that it uses PostgreSQL's internal caches of role information to satisfy membership queries quickly.