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

On Fri, Sep 20, 2024 at 10:16:32AM GMT, Tom Lane wrote:
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > when upgrading 15 to 16 (but also observed the same on 12 to 13), an
> > extension that was previously created by a "normal" user is now owned by
> > "postgres":
>
> 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

Background: my app uses "DROP OWNED BY CURRENT_USER CASCADE;" to clean
the DB (and then start fresh), which now (obviously) fails as the
extension is not owned and thus not dropped. But a type defined by the
extenion is still owned and is tried to be dropped, which leads to
errors.

I guess the same would also apply to any "ALTER EXTENSION … UPDATE" etc
calls by the user, which would now be denied.