roles management problem after upgrading in PG 17

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.

--
Robert Haas
EDB: http://www.enterprisedb.com

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
FROM pg_roles
where left(rolname,1) in('a','b')
and rolname not in(
'a_xx_administrators'
,'a_xx_standard_users'
,'a_xx_technical_users,
,'a_xx_owners',
...
)

Best regards,

Fabrice

On Thu, Jul 24, 2025 at 7:50 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Jul 25, 2025 at 10:07:38AM +0200, Fabrice Chapuis wrote:

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
FROM pg_roles
where left(rolname,1) in('a','b')
and rolname not in(
'a_xx_administrators'
,'a_xx_standard_users'
,'a_xx_technical_users,
,'a_xx_owners',
...
)

Echoing Tom's comments, I think the most we could do is to provide a
link to a Postgres wiki page that contains queries that show if people
are effected by the change. That way, no matter what method is used for
upgrading, people can run those queries. We can also improve the wiki
after the release.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.

On Mon, Jul 28, 2025 at 12:11:22PM -0400, Bruce Momjian wrote:

On Fri, Jul 25, 2025 at 10:07:38AM +0200, Fabrice Chapuis wrote:

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
FROM pg_roles
where left(rolname,1) in('a','b')
and rolname not in(
'a_xx_administrators'
,'a_xx_standard_users'
,'a_xx_technical_users,
,'a_xx_owners',
...
)

Echoing Tom's comments, I think the most we could do is to provide a
link to a Postgres wiki page that contains queries that show if people
are effected by the change. That way, no matter what method is used for
upgrading, people can run those queries. We can also improve the wiki
after the release.

Correction, Robert Haas's comments.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.