Re: roles management problem after upgrading in PG 17

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.