Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ? - Mailing list pgsql-general

On Wed, Aug 9, 2017 at 11:30 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

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

On Wed, Aug 9, 2017 at 10:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Scott Marlowe <scott.marlowe@gmail.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.

​