Thank you for your reply, Robert. My main goal was to report this observation, because I was surprised to see that in the dump the grantors were not exported. Thanks for drawing my attention to the "observes" section; it is indeed very useful.
For my part, I corrected the problem related to this perfectly justified restriction by using this query:
SELECT 'GRANT ' || rolname || ' TO group_of_administrators WITH ADMIN OPTION, INHERIT FALSE, SET TRUE;' as username
On Thu, Jul 24, 2025 at 7:03 AM Fabrice Chapuis <fabrice636861@gmail.com> wrote: > After upgrading from Postgres version 14 to Postgres version 17, I noticed that some "grantor" roles (admin_role) disappear when exporting all roles with grants > > /usr/pgsql-17/bin/pg_dumpall --globals-only --quote-all-identifiers --binary-upgrade --no-sync -f /var/lib/pgsql/roles_bin.sql > > Consequently, the admin_role could not administer some roles in version 17 related to modifications made in version 16 concerning roles administration. > > A message could be displayed when executing pg_upgrade with option --check that the attribute "createrole" for a user is more restrictive since version 16 and anomalies may appear after upgrading in the new version. > > What is your opinion
Well, we do have a section in the release notes that says "Observe the following incompatibilities" and I think that's the place that people should check when upgrading. In a perfect world, maybe tools like pg_dump and pg_upgrade could also warn you about incompatibilities that are likely to affect your specific installation, but that would be a lot of work to create and maintain and nobody's volunteered to do that much work yet. Of course, someone could argue that this particular incompatibility is so much worse than any of the others that it deserves to be called out specifically even if we don't do that in other cases, but I am a bit skeptical of that argument. I think it would lead to people being frightened of this particular change as compared with anything else, and I'm not sure that's a justified reaction. If anything, I think people who are using CREATEROLE with a non-SUPERUSER account should probably be very afraid of staying on OLD releases, where the behavior is extremely insecure and such users can just go take over the superuser account whenever they like.
Of course, you or others might see it differently.
