| 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: | Whole Thread | Raw Message | 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 |