Re: roles management problem after upgrading in PG 17

On Thu, Jul 24, 2025 at 7:03 AM Fabrice Chapuis <fabrice636861(at)gmail(dot)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