Hello, hackers: I’ve been encountering issues with bogus pg_init_privs entries in pg_upgrade from v15 to v18. As previously discussed, it's caused by "REASSIGN OWNED BY" and "DROP ROLE": https://www.postgresql.org/message-id/flat/33E80863-5516-45A1-93D1-00F1A166EC7C%40yesql.se#5de834b4dcda3d9f3a9490344a0a7a4b <https://www.postgresql.org/message-id/flat/33E80863-5516-45A1-93D1-00F1A166EC7C%40yesql.se#5de834b4dcda3d9f3a9490344a0a7a4b > (As the thread has been inactive for some time, maybe we can continue the discussion here..) Although the issue was fixed in v17 by ReplaceRoleInInitPriv, I wonder if we can fix it in pg_dump (or other stages in pg_upgrade). This is because it seems very challenging to handle this issue even manually. And the only method I can think of is deleting these bogus entries through a superuser (too brute force)... > WITH q AS ( > SELECT objoid, classoid, objsubid, privtype, unnest(initprivs) AS > initpriv FROM saved_init_privs > ) > SELECT objoid, classoid, objsubid, privtype, array_agg(initpriv) as initprivs > FROM q > WHERE is_valid_value_for_type(initpriv::text, 'aclitem') > GROUP BY 1,2,3,4; This is described by Hannu in the thread above. In my opinion, it's a good approach. Besides, there seems no suggestions on how to handle the issue in logs of pg_upgrade or in docs. And it's a little bit difficult for users to search it in pgsql-hackers archives. Maybe we can add some logs/docs somewhere to point out the issue. > If we don't fix it in the code and we don't document it anywhere, the > next person who hits it is going to have to try to discover the fact > that there's a problem from the pgsql-hackers archives. +1 -- Boyu Yang
