Re: How to ALTER EXTENSION name OWNER TO new_owner ?

On Wed, Aug 9, 2017 at 3:00 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>
>>>> Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
>>>> > Seems like something that should be handled by alter doesn't it?
>>>>
>>>> I have some vague memory that we intentionally didn't implement
>>>> ALTER EXTENSION OWNER because we were unsure what it ought to do
>>>> about ownership of objects belonging to the extension. If the answer
>>>> is "nothing" then it wouldn't be hard to add such a statement.
>>>>
>>>
>>> The documented contract of CREATE EXTENSION(1)/ALTER EXTENSION ADD
>>> MEMBER(2) requires that the extension owner and the owner of the member
>>> objects be one-and-the-same (I suppose the inclusion of DROP in (2) makes
>>> this debatable). I do not know what happens today if someone tries to
>>> ALTER OBJECT SET OWNER on a member object to a role other than the owner of
>>> the extension. From the docs I'd suggest that it should fail. Likewise,
>>> ALTER EXTENSION OWNER should cascade to all members - which (3), and normal
>>> dependency tracking, seems to make straight-forward.
>>>
>>> 1>The user who runs CREATE EXTENSION becomes the owner of the extension
>>> for purposes of later privilege checks, as well as the owner of any objects
>>> created by the extension's script.
>>>
>>> 2>You must own the extension to use ALTER EXTENSION. The ADD/DROP forms
>>> require ownership of the added/dropped object as well.
>>>
>>> 3>CREATE EXTENSION additionally records the identities of all the
>>> created objects, so that they can be dropped again if DROP EXTENSION is
>>> issued.
>>>
>>> David J.
>>>
>>>
>>
>>
>> *David,*
>>
>> *The problem is, The current owner of the extension needs to be dropped.
>> No one should have to jump through hoops*
>> *just to be able to do that. There is definitely a need for an*
>>
>> *ALTER EXTENSION name OWNER TO new_owner.*
>> *As Tom Lane has already pointed out, it would not be hard to add that.*
>>
>>
> ​I'm not sure what it is you think I'm missing here. My only point was
> I'm tending to think that "nothing", while workable, diverges from what I
> would expect - that an extension and all of its member objects should, at
> all times, share a common owner. I don't imagine that either definition
> would be abnormally difficult to implement for v11.
>
> I'm am wondering whether "REASSIGNED OWNED" needs fixing as well...since
> that command is specifically designed to handle this use case.
>
> https://www.postgresql.org/docs/9.6/static/sql-reassign-owned.html
> ​
> ​
>
> D
> ​avid J.
> ​
>

*>I'm am wondering whether "REASSIGNED OWNED" **needs fixing as well*

*Possibly, but as the op is on 9.3, it is not available to him.*
*I would also argue that since* *"OWNER TO new_owner" is available in all
other ALTER object statements, it is an omission and should be*
*included for extenstions as well..*

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