Role Graph Views for PostgreSQL
Summary
A PostgreSQL (v16+) pseudo-extension that provides the path of membership between all pairs of roles in the cluster.
Example Output
rolname | administration | memberof_groups | memberof_users | member_groups | member_users
---------+------------------------+-------------------------------+----------------+---------------------------+------------------------------
grp1 | by usr from superuser | grp2 from superuser (s,i) | | | usr from superuser (s,i)
grp2 | | | | grp1 from superuser (s,i) | usr via grp1/superuser (s,i)
usr | of grp1 from superuser | grp1 from superuser (s,i) +| | |
| | grp2 via grp1/superuser (s,i) | | |
The above output demonstrates a simple two group, one user, setup where the superuser is general responsible for administering the roles but has delegated adminstration of “grp1” to the role “usr”.
Location
Commentary
I wrote this pseudo-extension because of the changes instigated by Robert Haas to improve the viability of CREATE ROLE delegation as well as establishing the explicit tracking of SET and INHERIT membership permissions.
My initial version attempted to do this as a patch aginst psql but the lack of interest and difficulty is writing this to the standard that is expected of psql meta-command led me to turn it into a pseudo-extension instead.
That, and I’ve been working toward a more formal online presence in recent months and this seemed like a project that would lend itself well as content to be released as part of that endeavor.
I do welcome collaboration on this especially since at present it is basically a beta that only I have really worked on, more in theory than as someone who presently really needs such a tool. In particular I haven’t done much with respect to considering database permissions.
License
Covered officially in the repo itself, but the PostgreSQL license was chosen here with an eye toward making this project compatible with PGXN and thus an actual extension within the community.
Gratitude and Sustenance
