Re: roles management problem after upgrading in PG 17

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(at)gmail(dot)com> wrote:

> 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
>