Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Colin 't Hart]

Would this be a relatively easy patch for a beginning contributor?

If so, I wouldn't mind having a go.

/Colin

On 9 August 2017 at 21:37, Melvin Davidson  wrote:

>
>
> On Wed, Aug 9, 2017 at 3:32 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson 
>> wrote:
>>
>>> *>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.*
>>>
>>
>> ​You should check the docs again...​
>>
>>
>>> *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..*
>>>
>>
>> ​As am I, but omission or not I don't recall that we've ever back-patched
>> new SQL grammar.
>>
>> David J.
>> ​
>>
> *>You should check the docs again...​*
>
> *Yes, you are correct, it is in 9.3*
>
> *>I don't recall that we've ever back-patched new SQL grammar.*
>
> *I am not saying back patch, I am saying an enhancement for 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.
>

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Melvin Davidson]

On Wed, Aug 9, 2017 at 3:32 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson 
> wrote:
>
>> *>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.*
>>
>
> ​You should check the docs again...​
>
>
>> *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..*
>>
>
> ​As am I, but omission or not I don't recall that we've ever back-patched
> new SQL grammar.
>
> David J.
> ​
>
*>You should check the docs again...​*

*Yes, you are correct, it is in 9.3*

*>I don't recall that we've ever back-patched new SQL grammar.*

*I am not saying back patch, I am saying an enhancement for 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.

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[David G. Johnston]

On Wed, Aug 9, 2017 at 12:26 PM, Melvin Davidson 
wrote:

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

​You should check the docs again...​


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

​As am I, but omission or not I don't recall that we've ever back-patched
new SQL grammar.

David J.
​

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Melvin Davidson]

On Wed, Aug 9, 2017 at 3:00 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson 
> wrote:
>
>>
>>
>> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane  wrote:
>>>
 Scott Marlowe  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.

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[David G. Johnston]

On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson 
wrote:

>
>
> On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane  wrote:
>>
>>> Scott Marlowe  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.
​

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Melvin Davidson]

On Wed, Aug 9, 2017 at 1:56 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane  wrote:
>
>> Scott Marlowe  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.*

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

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[David G. Johnston]

On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane  wrote:

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

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Melvin Davidson]

On Wed, Aug 9, 2017 at 12:19 PM, Scott Marlowe 
wrote:

> On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane  wrote:
> > Melvin Davidson  writes:
> >> *UPDATE pg_extensionSET 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 DavidsonI reserve the right to fantasize.  Whether or not you wish
to share my fantasy is entirely up to you. *

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Tom Lane]

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

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Scott Marlowe]

On Wed, Aug 9, 2017 at 10:10 AM, Tom Lane  wrote:
> Melvin Davidson  writes:
>> *UPDATE pg_extensionSET 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?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Tom Lane]

Melvin Davidson  writes:
> *UPDATE pg_extensionSET 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.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Melvin Davidson]

On Wed, Aug 9, 2017 at 11:20 AM, Melvin Davidson 
wrote:

>
> On Wed, Aug 9, 2017 at 10:42 AM, Colin 't Hart 
> wrote:
>
>> Hi,
>>
>> Why does
>> ALTER EXTENSION name OWNER TO new_owner;
>> not exist?
>>
>> I have a bunch of extensions that were installed by a role that I want
>> to drop. So I thought I would do like I do for other object types:
>> ALTER  name OWNER TO new_owner;
>>
>> But that doesn't exist for extensions. I also can't drop the extension
>> and recreate it because other objects depend on it.
>>
>> What can I do?
>>
>> This is on PostgreSQL 9.3.
>>
>> Thanks,
>>
>> Colin
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> *AFAIK, extensions do not have an owner.  They just exist and are
> available to everyone.*
>
> *If you are having a particular problem (other than owner) with an
> extension, it would be helpful to*
>
> *post a script to illustrate that.  You should be able to drop the role
> without any problem. *
>
> *If an error occurs, then please advise on that and include the exact
> message.*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

*Hmm, I have to retract my previous statement, as the structure of
pg_extension is:*














*CREATE TABLE pg_extension(  extname name NOT NULL,  extowner oid NOT
NULL,  extnamespace oid NOT NULL,  extrelocatable boolean NOT NULL,
extversion text,  extconfig oid[],  extcondition text[])WITH (  OIDS=TRUE);*
*So to solve your problem, as a superuser you can do:*

*SELECT oid, rolname*

* FROM pg_authid *
* WHERE rolname =  '{new_owner}';*

*SELECT oid, rolname*

* FROM pg_authid *
* WHERE rolname = '{user_you_want_to_drop}';*

*Then:*




*UPDATE pg_extensionSET extowner = {oid_of_new_owner} WHERE extowner =
{oid_from_above_statement};*




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

Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ?

[Melvin Davidson]

On Wed, Aug 9, 2017 at 10:42 AM, Colin 't Hart  wrote:

> Hi,
>
> Why does
> ALTER EXTENSION name OWNER TO new_owner;
> not exist?
>
> I have a bunch of extensions that were installed by a role that I want
> to drop. So I thought I would do like I do for other object types:
> ALTER  name OWNER TO new_owner;
>
> But that doesn't exist for extensions. I also can't drop the extension
> and recreate it because other objects depend on it.
>
> What can I do?
>
> This is on PostgreSQL 9.3.
>
> Thanks,
>
> Colin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


*AFAIK, extensions do not have an owner.  They just exist and are available
to everyone.*

*If you are having a particular problem (other than owner) with an
extension, it would be helpful to*

*post a script to illustrate that.  You should be able to drop the role
without any problem. *

*If an error occurs, then please advise on that and include the exact
message.*

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