Re: [GENERAL] How to ALTER EXTENSION name OWNER TO new_owner ? - Mailing list pgsql-general
On Wed, Aug 9, 2017 at 11:20 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
On Wed, Aug 9, 2017 at 10:42 AM, Colin 't Hart <colinthart@gmail.com> 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 <object_type> 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 post a script to illustrate that. You should be able to drop the role without any problem.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 toIf 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
);
(
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_extension
SET extowner = {oid_of_new_owner}
WHERE extowner = {oid_from_above_statement};
SET 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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: