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

Evgeni Golov <evgeni(at)golov(dot)de> writes:
> On Fri, Sep 20, 2024 at 12:18:00PM GMT, Tom Lane wrote:
>> You could do that as superuser, but it's not really enough because
>> there are pg_shdepend entries that ought to be added/updated.

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

Stuff like DROP OWNED BY will misbehave, because it won't detect that
that extension belongs to that role.

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

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

Sure, \dx+ in psql, or look in pg_depend for 'e' dependencies.

regards, tom lane