Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest".

create role authors;

create role editors;

create user maxwell;

create user ernest;

grant authors to editors; --editors can do what authors can do

grant editors to maxwell; --maxwell is an editor

grant authors to ernest; --ernest is an author

I would like to write a performant function that returns a list of the roles (preferably their oid's) that maxwell belongs to, something like this:

create or replace function get_all_roles() returns oid[] ...

It should return the oids for maxwell, authors, and editors (but not ernest).

But I am not sure how to do it when there is inheritance.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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 FROM cte;

BTW, INHERIT is the default behavior of CREATE ROLE and doesn't have to be spelled out.

BTW2: circular dependencies are not possible. Postgres disallows that. So we don't have to check for that.

share|improve this answer
add comment

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');
share|improve this answer
    
You may be overlooking the recursive nature of the question (including inherited roles). –  Erwin Brandstetter Jan 4 at 0:46
    
@ErwinBrandstetter Yes, I should've used member instead of usage. amended. Huh, except it's not finding nested roles. I could've sworn pg_has_role looked into inherited roles, that's kind of the point. Looking into it. –  Craig Ringer Jan 4 at 0:48
    
@ErwinBrandstetter Problem was that pg_auth_members its self wasn't the right thing to be querying, should've been looking at pg_authid directly. Corrected. –  Craig Ringer Jan 4 at 1:01
    
One more thing: a security definer function should set the search_path properly or it fails to deliver the suggested security: SET search_path = pg_catalog, pg_temp. –  Erwin Brandstetter Jan 4 at 1:09
1  
You got that backwards. SET search_path = pg_catalog, pg_temp is not supposed to include pg_temp, but to lower its priority to come after any other schema in the search_path. Read the chapter I have linked to. –  Erwin Brandstetter Jan 4 at 1:18
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.