Re: roles management problem after upgrading in PG 17

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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

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