From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | "paths" between two ROLEs |
Date: | 2023-06-13 08:17:27 |
Message-ID: | CAFCRh-_NvCgXLSQ5w8XhXqyTo1ts5jG_WEYqwuvRdBVEyMpySQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | rihad | 2023-06-13 08:35:07 | pg_upgrade v15 not generating analyze_new_cluster.sh |
Previous Message | Wen Yi | 2023-06-13 08:00:37 | [Beginner Question] Will the backup wal file take too much storage space? |