On 6/13/23 04:17, Dominique Devienne wrote:
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.
This shows the path between roles taken which provides a particular
privilege for a particular object:
https://github.com/CrunchyData/crunchy_check_access
It might do for you as-is, or at least you can use it as an example.
HTH,
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
