Re: DROP OWNED BY fails to clean out pg_init_privs grants

Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, May 24, 2024 at 11:59 AM Tom Lane <tgl@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