Re: DROP OWNED BY fails to clean out pg_init_privs grants

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, May 24, 2024 at 2:57 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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?

Keep in mind that the whole point here is for the pg_dump script to
just say "CREATE EXTENSION foo", not to mess with the individual
objects therein. So the objects are (probably) going to be owned by
the user that issued CREATE EXTENSION.

In the original conception, that was the end of it: what you got for
the member objects was whatever state CREATE EXTENSION left behind.
The idea of pg_init_privs is to support dump/reload of subsequent
manual alterations of privileges for extension-created objects.
I'm not, at this point, 100% certain that that's a fully realizable
goal. But I definitely think it's insane to expect that to work
without also tracking changes in the ownership of said objects.

Maybe forbidding ALTER OWNER on extension-owned objects isn't
such a bad idea?

regards, tom lane