Re: DROP OWNED BY fails to clean out pg_init_privs grants

On Fri, May 24, 2024 at 2:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

Oh! That does seem like it would make what I said wrong, but how would
it even know who the original owner was? Shouldn't we be recreating
the object with the owner it had at dump time?

> 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.

Hmm, yeah.

> 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.

I'm not sure exactly how this computation works, but if tgl granted
nmisch privileges on an object and the extension is now owned by
rhaas, it would seem like the right thing to do would be for rhaas to
grant nmisch those same privileges. Conversely if tgl started with
privileges to do X and Y and later was granted privileges to do Z and
we dump and restore such that the extension is owned by rhaas, I'd
presume rhaas would end up with those same privileges. I'm probably
too far from the code to give terribly useful advice here, but I think
the expected behavior is that the new owner replaces the old one for
all purposes relating to the owned object(s). At least, I can't
currently see what else makes any sense.

-- 
Robert Haas
EDB: http://www.enterprisedb.com