"paths" between two ROLEs

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

Responses

Browse pgsql-general by date

  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?