Hi , Found this interesting : "gm-dbo" can manage membership in "gm-doctors" (ADMIN TRUE), but does not inherit the role's privileges, nor can "gm-dbo" assume the identity of "gm-doctors".
INHERIT option should be used in caution to reduce the risk of privilege escalation, especially for sensitive roles: * Set NOINHERIT to TRUE on roles with elevated privileges (e.g., roles that have SUPERUSER, CREATEDB, CREATEROLE, or access to critical data or functions). * Tag such roles as sensitive, and prioritize restricting their inheritance to avoid unintended privilege propagation. * Enforce role separation by ensuring that users can manage sensitive roles without inheriting their privileges. Thanks & Regards Dinesh Nair ________________________________ From: Dominique Devienne <[email protected]> Sent: Monday, August 25, 2025 6:08 PM To: Pavel Luzanov <[email protected]> Cc: Laurenz Albe <[email protected]>; Karsten Hilbert <[email protected]>; [email protected] <[email protected]> Subject: Re: Q: GRANT ... WITH ADMIN on PG 17 Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments. On Mon, Aug 25, 2025 at 2:22 PM Pavel Luzanov <[email protected]> wrote: >> On 22.08.2025 11:40, Laurenz Albe wrote: >> Yes, that should work as follows: [...] > [...] A safer option is to use security definer function to grant membership FWIW, it's basically what I did. My primary "admin" application role lost CREATEROLE, and instead gained EXECUTE on security-definer procs from a new lower-level role (with CREATEROLE), in a new separate schema, which does all create/drop roles or grant/revoke DDLs. Which has the added benefits to enforce naming conventions for roles, to enforce grants are only between our "per-DB" roles, and made it easy to generate an audit-log for all those DDLs. So the v16 ROLE changes created a BIG MESS for us, slowing us down quite a bit, but we ended up with a much better "v2" architecture, so it was not all a loss... YMMV. So +1 to Pavel. --DD
