Skip to main content
added 7 characters in body
Source Link
Craig Ringer
  • 57.3k
  • 6
  • 159
  • 192

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

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

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');
added 548 characters in body
Source Link
Craig Ringer
  • 57.3k
  • 6
  • 159
  • 192

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

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

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'
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');
added 548 characters in body
Source Link
Craig Ringer
  • 57.3k
  • 6
  • 159
  • 192

 

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.


 

Explanation:

This information is exposed for the current user in the information_schema. The view:

select * from information_schema.applicable_roles

only shows you information for the current user, but if you examine the definition of this view you can see how you can use it for roles other than the current role, and simplify it a bit. The definition:

postgres=> select pg_get_viewdef('information_schema.applicable_roles');
                           pg_get_viewdef                            
---------------------------------------------------------------------
  SELECT (a.rolname)::information_schema.sql_identifier AS grantee, +
     (b.rolname)::information_schema.sql_identifier AS role_name,   +
     (                                                              +
         CASE                                                       +
             WHEN m.admin_option THEN 'YES'::text                   +
             ELSE 'NO'::text                                        +
         END)::information_schema.yes_or_no AS is_grantable         +
    FROM ((pg_auth_members m                                        +
    JOIN pg_authid a ON ((m.member = a.oid)))                       +
    JOIN pg_authid b ON ((m.roleid = b.oid)))                       +
   WHERE pg_has_role(a.oid, 'USAGE'::text);
(1 row)

Since you only want oids, you can use:

SELECT member
FROM pg_auth_members m
WHERE pg_has_role('maxwell', m.member, 'member');

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.oid 
FROM pg_authid a 
WHERE pg_has_role('maxwell', a.oid, 'member');

The advantage of using pg_has_role is that it uses PostgreSQL's internal caches of role information to satisfy membership queries quickly.

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'
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...;
added 109 characters in body
Source Link
Craig Ringer
  • 57.3k
  • 6
  • 159
  • 192
Loading
added 1486 characters in body
Source Link
Craig Ringer
  • 57.3k
  • 6
  • 159
  • 192
Loading
added 1486 characters in body
Source Link
Craig Ringer
  • 57.3k
  • 6
  • 159
  • 192
Loading
Source Link
Craig Ringer
  • 57.3k
  • 6
  • 159
  • 192
Loading