Re: BUG #18625: user-created extensions change ownership to "postgres" after upgrade

Evgeni Golov <evgeni@golov.de> writes:
> On Fri, Sep 20, 2024 at 10:16:32AM GMT, Tom Lane wrote:
>> Yeah, this is a known shortcoming --- pg_dump doesn't make any effort
>> to preserve ownership of extensions.  Nobody's really been motivated
>> to do something about that.

> There is also no way for the user to update that ownership on their own,
> after that happened, right?
> foreman=> update pg_extension set extowner=16384 where extname='cube';
> ERROR:  permission denied for table pg_extension

You could do that as superuser, but it's not really enough because
there are pg_shdepend entries that ought to be added/updated.

The bigger picture here is that it's not just the pg_extension object
whose ownership is at stake.  There are going to be objects belonging
to the extension that probably --- but not certainly --- should have
the same owner as the extension.  And it's not too clear what should
happen to them.  That's why we've not implemented ALTER EXTENSION
OWNER: it's not 100% clear what it should do to the contained objects.

And that's a problem for pg_dump, because its standard strategy for
dealing with object ownership is to issue ALTER <object> OWNER
commands.  It does have the ability to use SET SESSION AUTHORIZATION
commands instead, but as the fine manual says,

  --use-set-session-authorization

    Output SQL-standard SET SESSION AUTHORIZATION commands instead of
    ALTER OWNER commands to determine object ownership. This makes the
    dump more standards-compatible, but depending on the history of
    the objects in the dump, might not restore properly. Also, a dump
    using SET SESSION AUTHORIZATION will certainly require superuser
    privileges to restore correctly, whereas ALTER OWNER requires
    lesser privileges.

(I'm not sure offhand what cases there are that "might not restore
properly".  But the point about requiring superuser is surely valid.)

So the only simple fix is for pg_dump to issue SET SESSION
AUTHORIZATION before the CREATE EXTENSION command.  But we have not
really wanted to do that because of the requires-superuser angle.
Maybe we should just bite the bullet and do it, though.

Another idea might be to use SET ROLE instead of SET SESSION
AUTHORIZATION, because the backend will let you do that if you've been
granted appropriate privileges; it's not a hard "must be superuser"
check.  I do not recall why pg_dump doesn't do that already, but
perhaps there's a reason beyond historical accident.  It'd require
some research before we could consider changing that.

            regards, tom lane