Обсуждение: 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)
disappearwhen 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
16concerning roles administration. 
>
> A message could be displayed when executing pg_upgrade with option --check  that the attribute "createrole"  for a
useris 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

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.