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