Robert Haas <robertmh...@gmail.com> writes: > On Fri, May 24, 2024 at 11:59 AM Tom Lane <t...@sss.pgh.pa.us> wrote: >> So this goal seems to >> mean that neither ALTER OWNER nor REASSIGN OWNED should touch >> pg_init_privs at all, as that would break its function of recording >> a historical state. Only DROP OWNED should get rid of pg_init_privs >> grants, and that only because there's no choice -- if the role is >> about to go away, we can't hang on to a reference to its OID.
> But I would have thought that the right thing to do to pg_init_privs > here would be essentially s/$OLDOWNER/$NEWOWNER/g. Doesn't seem right to me. That will give pg_dump the wrong idea of what the initial privileges actually were, and I don't see how it can construct correct delta GRANT/REVOKE on the basis of false information. During the dump reload, the extension will be recreated with the original owner (I think), causing its objects' privileges to go back to the original pg_init_privs values. Applying a delta that starts from some other state seems pretty questionable in that case. It could be that if we expect pg_dump to issue an ALTER OWNER to move ownership of the altered extension object to its new owner, and only then apply its computed delta GRANT/REVOKEs, then indeed the right thing is for the original ALTER OWNER to apply s/$OLDOWNER/$NEWOWNER/g to pg_init_privs. I've not thought this through in complete detail, but it feels like that might work, because the reload-time ALTER OWNER would apply exactly that change to both the object's ACL and its pg_init_privs, and then the delta is starting from the right state. Of course, pg_dump can't do that right now because it lacks the information that such an ALTER is needed. Although ... this is tickling a recollection that pg_dump doesn't try very hard to run CREATE EXTENSION with the same owner that the extension had originally. That's a leftover from the times when basically all extensions required superuser to install, and of course one superuser is as good as the next. There might be some work we have to do on that side too if we want to up our game in this area. Another case that's likely not handled well is what if the extension really shouldn't have its original owner (e.g. you're using --no-owner). If it's restored under a new owner then the pg_init_privs data certainly doesn't apply, and it feels like it'd be mostly luck if the precomputed delta GRANT/REVOKEs lead to a state you like. regards, tom lane