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
On Wednesday, May 14, 2025, Jason Matthew <Jason.Matthew@sas.com> wrote:
Preface-
My apologies if choosing the wrong list.
Well, it’s not a bug and is documented so ultimately it is the wrong list.
Though if you fail to find the documentation as was the case here I suppose
it does look like a regression/bug…
Investigation-
Neither changelog
Quoting from said changelog:
-
Role inheritance now controls the default inheritance status of member
roles added during GRANT
<https://www.postgresql.org/docs/16/sql-grant.html> (Robert Haas) §
<https://postgr.es/c/e3ce2de09>
The role's default inheritance behavior can be overridden with the new GRANT
... WITH INHERIT clause. This allows inheritance of some roles and not
others because the members' inheritance status is set at GRANT time.
Previously the inheritance status of member roles was controlled only by
the role's inheritance status, and changes to a role's inheritance status
affected all previous and future member roles.
David J.
Thank you for the clarification. I overlooked that changelog message (docs/release/16.0/). Existing information reads clearly. Thank you for responding. I'm set.
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, May 14, 2025 5:17 PM
To: Jason Matthew <Jason.Matthew@sas.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: pg16 ALTER ROLE [NO]INHERIT has no effect on existing grants
EXTERNAL
On Wednesday, May 14, 2025, Jason Matthew <Jason.Matthew@sas.com<mailto:Jason.Matthew@sas.com>> wrote:
Preface-
My apologies if choosing the wrong list.
Well, it's not a bug and is documented so ultimately it is the wrong list. Though if you fail to find the documentation as was the case here I suppose it does look like a regression/bug...
Investigation-
Neither changelog
Quoting from said changelog:
* Role inheritance now controls the default inheritance status of member roles added during GRANT<https://protect.checkpoint.com/v2/r01/___https://www.postgresql.org/docs/16/sql-grant.html___.YzJ1OnNhc2luc3RpdHV0ZTpjOm86MTQ0MWUxODRlYzAyMzdmMTlmYjZjMWY1ODYxYjRkM2U6NzpmZjhhOmM2MDVjZjdiZGQ1Mjk5MzQ5NWRhMzRjMTQ1NzgxNDZkODdmOTBhMjA2NThiMjI0NjZjYmQ1M2Y0YjgyOWFhMzI6aDpUOk4> (Robert Haas) §<https://protect.checkpoint.com/v2/r01/___https://postgr.es/c/e3ce2de09___.YzJ1OnNhc2luc3RpdHV0ZTpjOm86MTQ0MWUxODRlYzAyMzdmMTlmYjZjMWY1ODYxYjRkM2U6Nzo5NmMzOjU3Mzc4Y2ViNDZmMDQzZDBhOWY3NTFmMmVjZTkyZjAwZTc3ZWVhYzNkZDM3ODNmZjM1MDZhZDk1NDNjMmRiYzM6aDpUOk4>
The role's default inheritance behavior can be overridden with the new GRANT ... WITH INHERIT clause. This allows inheritance of some roles and not others because the members' inheritance status is set at GRANT time. Previously the inheritance status of member roles was controlled only by the role's inheritance status, and changes to a role's inheritance status affected all previous and future member roles.
David J.