public inbox for [email protected]
help / color / mirror / Atom feedRe: Q: GRANT ... WITH ADMIN on PG 17
4+ messages / 4 participants
[nested] [flat]
* Re: Q: GRANT ... WITH ADMIN on PG 17
@ 2025-08-22 08:40 Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2025-08-22 08:40 UTC (permalink / raw)
To: Karsten Hilbert <[email protected]>; [email protected]
On Thu, 2025-08-21 at 17:36 +0200, Karsten Hilbert wrote:
> PG 17 documentation says that using "WITH ADMIN" allows the
> role being added to another group role to grant/revoke
> membership in said group to other roles.
>
> Does this imply that an ADMIN role _must_ itself be a member
> of the group role it is to maintain membership of ?
>
> The question arises from a scenario where a DBA role would
> not need to be a member of a clinical group role but would
> be intended to maintain membership of clinical user roles
> within that group role.
>
> From a security point of view the question might be moot
> because an ADMIN role could always grant itself membership
> in the group role -- but it feels wrong for reasons of
> theoretical "correctness".
>
> IOW:
>
> - gm-dbo: user role for a DBA admin (not! superuser)
> - gm-bones: user role for a LLAP doctor
> - gm-doctors: group role for doctors, upon which are resting
> access permissions for clinical data
> - gm-bones is to be a member of gm-doctors in order to access clinical data
> - gm-dbo is intended to manage membership of gm-bones in gm-doctors
> - however, gm-dbo need not itself be a member of gm-doctors
>
> Is that possible within the current (as of PG 17) framework ?
Yes, that should work as follows:
test=# CREATE ROLE "gm-dbo" LOGIN;
CREATE ROLE
test=# CREATE ROLE "gm-bones";
CREATE ROLE
test=# CREATE ROLE "gm-doctors";
CREATE ROLE
test=# GRANT "gm-doctors" to "gm-dbo" WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
GRANT ROLE
test=# SET SESSION AUTHORIZATION "gm-dbo";
SET
test=> GRANT "gm-doctors" TO "gm-bones";
GRANT ROLE
test=> SET ROLE "gm-doctors";
ERROR: permission denied to set role "gm-doctors"
"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".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Q: GRANT ... WITH ADMIN on PG 17
@ 2025-08-25 12:21 Pavel Luzanov <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Pavel Luzanov @ 2025-08-25 12:21 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Karsten Hilbert <[email protected]>; [email protected]
On 22.08.2025 11:40, Laurenz Albe wrote:
>> - gm-dbo: user role for a DBA admin (not! superuser)
>> - gm-bones: user role for a LLAP doctor
>> - gm-doctors: group role for doctors, upon which are resting
>> access permissions for clinical data
>> - gm-bones is to be a member of gm-doctors in order to access clinical data
>> - gm-dbo is intended to manage membership of gm-bones in gm-doctors
>> - however, gm-dbo need not itself be a member of gm-doctors
>>
>> Is that possible within the current (as of PG 17) framework ?
> Yes, that should work as follows:
>
> test=# CREATE ROLE "gm-dbo" LOGIN;
> CREATE ROLE
> test=# CREATE ROLE "gm-bones";
> CREATE ROLE
> test=# CREATE ROLE "gm-doctors";
> CREATE ROLE
> test=# GRANT "gm-doctors" to "gm-dbo" WITH ADMIN TRUE, INHERIT FALSE, SET FALSE;
> GRANT ROLE
> test=# SET SESSION AUTHORIZATION "gm-dbo";
> SET
> test=> GRANT "gm-doctors" TO "gm-bones";
> GRANT ROLE
> test=> SET ROLE "gm-doctors";
> ERROR: permission denied to set role "gm-doctors"
>
> "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".
Such a scheme will protect against accidental (unintended) use of the gm-dbo
role of its capabilities. But gm-dbo can grant itself SET and INHERIT
options
in gm-doctors:
GRANT "gm-doctors" to "gm-dbo" WITH INHERIT TRUE, SET TRUE;
A safer option is to use security definer function to grant membership
in the gm-doctors group. Something like this:
\connect - postgres
CREATE ROLE dbo LOGIN;
CREATE ROLE bones LOGIN;
CREATE ROLE doctors;
CREATE OR REPLACE PROCEDURE grant_doctors_to (member_role text)
AS $$
BEGIN
IF member_role != 'dbo' THEN
EXECUTE format('GRANT doctors TO %I WITH INHERIT TRUE, SET
TRUE, ADMIN FALSE', member_role);
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
REVOKE EXECUTE ON PROCEDURE grant_doctors_to FROM public;
GRANT EXECUTE ON PROCEDURE grant_doctors_to TO dbo;
\connect - dbo
CALL grant_doctors_to('bones');
CALL
GRANT doctors to dbo WITH INHERIT TRUE, SET TRUE;
ERROR: permission denied to grant role "doctors"
DETAIL: Only roles with the ADMIN option on role "doctors" may grant
this role.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Q: GRANT ... WITH ADMIN on PG 17
@ 2025-08-25 12:38 Dominique Devienne <[email protected]>
parent: Pavel Luzanov <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Dominique Devienne @ 2025-08-25 12:38 UTC (permalink / raw)
To: Pavel Luzanov <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Karsten Hilbert <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Q: GRANT ... WITH ADMIN on PG 17
@ 2025-08-25 18:42 DINESH NAIR <[email protected]>
parent: Dominique Devienne <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: DINESH NAIR @ 2025-08-25 18:42 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; Pavel Luzanov <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Karsten Hilbert <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-08-25 18:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-22 08:40 Re: Q: GRANT ... WITH ADMIN on PG 17 Laurenz Albe <[email protected]>
2025-08-25 12:21 ` Pavel Luzanov <[email protected]>
2025-08-25 12:38 ` Dominique Devienne <[email protected]>
2025-08-25 18:42 ` DINESH NAIR <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox