From: Tom Lane <tgl@sss.pgh.pa.us>
To: Evgeni Golov <evgeni@golov.de>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18625: user-created extensions change ownership to
 "postgres" after upgrade
In-reply-to: <gnjaw24lyttml4uhizflslowmqhtxmtl5mql5wegqs5fenuywb@ws2al3snz7fs>
References: <18625-77d1e2609972c616@postgresql.org>
 <2422972.1726841792@sss.pgh.pa.us>
 <wx6ndvw2ukpz2neoa2hkhldrrcfowlvyha4x7h746i4u73bjwp@l2vk2enhuwxf>
 <2443799.1726849080@sss.pgh.pa.us>
 <gnjaw24lyttml4uhizflslowmqhtxmtl5mql5wegqs5fenuywb@ws2al3snz7fs>
Comments: In-reply-to Evgeni Golov <evgeni@golov.de>
	message dated "Fri, 20 Sep 2024 19:05:06 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2464495.1726853783.1@sss.pgh.pa.us>
Date: Fri, 20 Sep 2024 13:36:23 -0400
Message-ID: <2464496.1726853783@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2464496.1726853783%40sss.pgh.pa.us>
Precedence: bulk

Evgeni Golov <evgeni@golov.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