Re: How to ALTER EXTENSION name OWNER TO new_owner ?

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Wed, Aug 9, 2017 at 12:19 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:

On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Melvin Davidson <melvin6925@xxxxxxxxx> writes:
>> *UPDATE pg_extension    SET extowner = {oid_of_new_owner} WHERE extowner =
>> {oid_from_above_statement};*
>
> Note you'll also have to modify the rows in pg_shdepend that reflect
> this ownership property.

Seems like something that should be handled by alter doesn't it?

In keeping with what Tom advised, the SQL to do that would be"

UPDATE pg_shdepend
   SET refobjid = {oid_of_new_owner}
 WHERE refobjid = {oid_of old_owner}
   AND deptype = 'o';

However, as Scott suggested, there should definitely be an ALTER statement to change the owner of the extension

and that does the work required.

IE: ALTER EXTENSION name OWNER TO new_owner;

Perhaps in Version 10 or 11?

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.