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-
- REVOKE and/or ALTER ROLE documentation can be improved.
- 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
