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

On Fri, Sep 20, 2024 at 12:18:00PM GMT, Tom Lane wrote:
> 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.

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