Page 2 of 2
Re: Change owner of schema?
Posted: Sat Apr 22, 2017 6:18 pm
by Krishnamraju
Hi @mesunderlin
have you found any solution to this problem ?, we have also facing same scenario now. Kindly help us
Re: Change owner of schema?
Posted: Wed Apr 26, 2017 2:34 pm
by JimKnicely
Hi,
Having the ability to change the owner of a schema after the schema has been created is on the road map!
In the mean time, the work around is as follows:
- Rename the current schema to a different name
- Recreate the intended schema with the desired owner
- Move the tables to the intended schema with:
ALTER TABLE <table_name> SET SCHEMA <intended_schema_name>;
ALTER TABLE <table_name> owner to <intended_owner_name>;
- Drop the renamed schema
Example:
dbadmin=> select schema_owner from schemata where schema_name = 'the_schema';
schema_owner
--------------
old_user
(1 row)
dbadmin=> select owner_name from tables where table_schema = 'the_schema';
owner_name
------------
old_user
old_user
(2 rows)
dbadmin=> alter schema the_schema rename to the_schema_old;
ALTER SCHEMA
dbadmin=> alter table the_schema_old.t1 set schema the_schema;
ALTER TABLE
dbadmin=> alter table the_schema_old.t2 set schema the_schema;
ALTER TABLE
dbadmin=> alter table the_schema_old.t1 owner to new_user;
ALTER TABLE
dbadmin=> alter table the_schema_old.t2 owner to new_user;
ALTER TABLE
dbadmin=> select schema_owner from schemata where schema_name = 'the_schema';
schema_owner
--------------
new_user
(1 row)
dbadmin=> select owner_name from tables where table_schema = 'the_schema';
owner_name
------------
new_user
new_user
(2 rows)
dbadmin=> drop schema the_schema_old;
DROP SCHEMA