Hi,
While looking at Robert's work to improve our handling of roles I found it helpful to be able to see not only the directly recorded membership information, which now includes grantor, but also to see what was reachable via SET ROLE. The attached patch puts that information at our users' fingertips by creating new system views and psql meta-commands.
The patch presented is mostly content complete though not ready to be committed by my own standards. But before I start moving it closer to that state I wanted to get feedback and at least moral support for its completion.
I've decided not to touch \du and \dg at this time. The role graph concept I'm implementing complements their "show the catalog in user-friendly format" design.
The graph concept is doable even without the v16 changes but I'm wondering if that is a hard requirement here. In any case, the patch does not properly protect itself in that situation even though the meta-commands depend on a system view being present.
Not having pl/pgsql available while building out the system views (initdb) is an annoyance - one I've overcome by including the code I used to generate a mis-named normal view as part of the commit and doing the integration manually via pg_dump. The pg_role_relationship view probably could be pulled out of the dynamic code generator, or rolled back into it as a CTE, depending on how valuable it seems to provide the recursive CTE query to the user. I'm leaning toward CTE but figure my opinion is likely to change upon seeing feedback.
For the rest of my design choices and thinking please see the system view pg_role_graph documentation. There are also code comments in the pg_role_graph.plpgsql file.
I haven't looked at how to implement automated testing on this yet, I've just used the roles below and manually verified I got the expected results and that they didn't change during refactoring. A few of these are noted in the view documentation to explain the format I've implemented. If the view is designed well, reviewing the expected memberships should be reasonably easy, so checking the data also tests the user interface.
Thanks!
David J.
create group grp1;
create user usr1;
grant grp1 to usr1;
create group grp2;
create user usr2;
grant grp2 to usr1;
grant grp2 to usr2 with admin option;
grant grp2 to usr1 granted by usr2;
create user usr1a;
grant usr1 to usr1a;
create group "group 3";
grant "group 3" to usr1a;
create group grp4;
create group grp4adm;
create user usr4;
create user usr4a;
grant grp4 to grp4adm with admin option;
grant grp4adm to usr4;
grant grp4 to usr4a granted by grp4adm;
create role sup1 with superuser login;
create role usr5 with login;
create group grp5a;
create group grp5b;
create group grp5c;
create group grp5d;
grant grp5a to usr5;
grant grp5b to grp5a;
grant grp5c to grp5b with admin option;
grant grp5d to grp5c;
create group grp6a;
create group grp6b;
create group grp6c;
create group grp6d;
grant grp6b to grp6a;
grant grp6c to grp6b;
grant grp6d to grp6c;
-- grant grp6a to grp6d; // not possible, no cycles allowed
