From: | Jason Matthew <Jason(dot)Matthew(at)sas(dot)com> |
---|---|
To: | "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | pg16 ALTER ROLE [NO]INHERIT has no effect on existing grants |
Date: | 2025-05-14 20:27:29 |
Message-ID: | MWHPR05MB3325D27D2AD3B47AD578764CF591A@MWHPR05MB3325.namprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Preface-
My apologies if choosing the wrong list. I was unsure if 'docs' was better suited but concluded the behavior witnessed could be seen as a regression which lands me here. I write this without expectation for a code change, but rather to support community awareness.
Context-
My team has a collection of services which leverage a single Postgres database. We are currently using pg15 and evaluating pg16 adoption.
Issue-
I notice ALTER ROLE [NO]INHERIT behaviors have changed which can lead to unexpected privileges. Giving an example, the following sequence produces different results when comparing pg15 and pg16. Specifically, "user3_create" has changed (FALSE -> TRUE) when comparing 15.13 (Debian 15.13-1.pgdg120+1) vs. 16.9 (Debian 16.9-1.pgdg120+1). Similar behavior is seen when managing other object types (for example- CREATE SCHEMA and has_schema_privilege).
---
SHOW server_version;
CREATE ROLE db_owner CREATEDB;
CREATE ROLE user1 IN ROLE db_owner;
CREATE ROLE user2 IN ROLE db_owner NOINHERIT;
CREATE ROLE user3 IN ROLE db_owner;
ALTER ROLE user3 NOINHERIT;
-- objects
CREATE DATABASE db1 OWNER db_owner;
-- inspect
SELECT has_database_privilege('user1', 'db1', 'CREATE') as user1_create,
has_database_privilege('user2', 'db1', 'CREATE') as user2_create,
has_database_privilege('user3', 'db1', 'CREATE') as user3_create;
---
Investigation-
Neither changelog or sql-alterrole.html documentation give hints to this change in behavior. Looking at code, I find the following commit. The commit message helps to explain why this is occurring.
"ALTER ROLE [NO]INHERIT now only changes the default behavior of future grants, and has no effect on existing ones."
https://github.com/postgres/postgres/commit/e3ce2de09d814f8770b2e3b3c152b7671bcdb83f
Conclusion-
1. REVOKE and/or ALTER ROLE documentation can be improved.
2. Pre-existing codebases which manage group roles should diligently assess privileges. REVOKE INHERIT (introduced in pg16) can be used to mimic pg15 ALTER ROLE NOINHERIT behaviors.
Thanks for reading,
Jason Matthew
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2025-05-14 21:16:59 | Re: pg16 ALTER ROLE [NO]INHERIT has no effect on existing grants |
Previous Message | Daniel Gustafsson | 2025-05-14 19:35:08 | Re: BUG #18925: Heap-buffer-overflow: pglz_compress with pglz_stategy_always |