Обсуждение: "paths" between two ROLEs
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
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
On Tue, Jun 13, 2023 at 2:20 PM Joe Conway <mail@joeconway.com> wrote:
On 6/13/23 04:17, Dominique Devienne wrote:
> To troubleshoot and validate that emulation, I'd like to introspect ROLE
> membership to:
>
> 1) Output the ROLE "path(s)" between any two ROLEs.[...]
> 2) Output all target ROLEs [...] a given LOGIN USER has access to.
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.
Thanks. I'd never seem a nested-for-loop with recursive calls of a function, returning rows.
Interesting mix of SQL and procedural / imperative code to generate a view-like output.
Not quite what I was looking for though. But thank you for sharing. Very instructional. --DD
PS: I'll try to work out the SQL myself then, and if I get stuck, I'll ask a more actionable question with where I'm at so far.