Hi. We emulated a legacy security model (enforced in C/C++ code)
into "layers" of PostgreSQL ROLEs and GRANTs, thus enforced database-side.
To troubleshoot and validate that emulation, I'd like to introspect ROLE membership to:
1) Output the ROLE "path(s)" between any two ROLEs. Typically between the LOGIN USER and the ROLE that control access to a particular SCHEMA. In our model, there can be several ways the two end-roles are connected, involving a variable number of roles. So it has to be a recursive query.
2) target-end ROLEs (controlling access to SCHEMAs, again) follow a naming convention, so they can be identified using a LIKE pattern. Output all target ROLEs (aggregating each "paths" to the source-ROLE in an text[]) a given LOGIN USER has access to.
I'd appreciate either example SQL for the above; or hints to achieve the above.
My CTE "foo" is not great, thus reaching out to the community to avoid wasting too much time on this on my own.
Thanks, --DD