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

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