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.
Best Answer
You can query the system catalog with a recursive query, in particular
pg_auth_members
:The manual about the cast to object identifier type
regrole
.BTW 1:
INHERIT
is the default behavior ofCREATE 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.