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

On Fri, Sep 20, 2024 at 12:18:00PM GMT, Tom Lane wrote:
> Evgeni Golov <evgeni(at)golov(dot)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.

At least in my trivial "create/upgrade" example, all entries in
pg_shdepend have refobjid of the user (16384).

What happens if those (pg_shdepend.refobjid, pg_extension.extowner)
are/get out of sync?

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

Is there a way to find/list those related objects?

In my case where I want to be able to drop things afterwards anyway,
updating them to be owned by the user seems reasonable?

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

At least in the specific case of pg_upgrade, that'd be OK as it happens
as superuser anyway, right?

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

Thanks for the explanation and background!

Evgeni