pg16 ALTER ROLE [NO]INHERIT has no effect on existing grants

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

Responses

Browse pgsql-bugs by date

  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