Обсуждение: changing ownership of db
hello, I would like to change the ownership of a database. The only thing I could find up to now, is that it is difficult, but I couldn't find any detailed help. Does anybody know how to do this!? Probably create a dump, then create a new db, then import all the data? thanks in advance, Benjamin
Hi, On Tue, 29 Jul 2003, Benjamin Thelen (CCGIS) wrote: > I would like to change the ownership of a database. The only thing I > could find up to now, is that it is difficult, but I couldn't find any > detailed help. Connect to that database via psql. Then, first, update pg_database: UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='new_owner') WHERE datname='db_name'; If you also want to change the owner of the tables, update pg_class: UPDATE pg_class SET relowner=(SELECT usesysid FROM pg_shadow WHERE usename='new_owner') WHERE relname IN (SELECT relname from pg_class WHERE relname NOT LIKE 'pg_%'); Maybe there is a shorter way, but I know this one. HTH, Regards, -- Devrim GUNDUZ devrim@gunduz.org devrim.gunduz@linux.org.tr http://www.tdmsoft.com http://www.gunduz.org
Devrim GUNDUZ <devrim@gunduz.org> writes: > On Tue, 29 Jul 2003, Benjamin Thelen (CCGIS) wrote: >> I would like to change the ownership of a database. > UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE > usename='new_owner') WHERE datname='db_name'; That is all you need to do --- it's the only place CREATE DATABASE records the owner's identity. > If you also want to change the owner of the tables, update pg_class: > UPDATE pg_class SET relowner=(SELECT usesysid FROM pg_shadow WHERE > usename='new_owner') WHERE relname IN (SELECT relname from > pg_class WHERE relname NOT LIKE 'pg_%'); This is likely to be a very bad idea, especially if you give ownership of the system tables to a non-superuser. Ownership of those tables stays with the postgres user during a CREATE DATABASE. regards, tom lane
On Tue, 29 Jul 2003 17:41:08 -0400, Tom Lane wrote: > Devrim GUNDUZ <devrim@gunduz.org> writes: >> On Tue, 29 Jul 2003, Benjamin Thelen (CCGIS) wrote: >>> I would like to change the ownership of a database. > >> UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE >> usename='new_owner') WHERE datname='db_name'; > > That is all you need to do --- it's the only place CREATE DATABASE > records the owner's identity. Would it be an idea to encapsulate this as ALTER DATABASE db_name SET OWNER = 'new_owner' or something similar? What syntax would be desired and would you accept a patch? Cheers, Colin
Thanks very much!! I had a user who was allowed to create dbs, but wasn't superuser...Now I just have a superuser. Benjamin Devrim GUNDUZ wrote: > Hi, > > On Tue, 29 Jul 2003, Benjamin Thelen (CCGIS) wrote: > > >>I would like to change the ownership of a database. The only thing I >>could find up to now, is that it is difficult, but I couldn't find any >>detailed help. > > > Connect to that database via psql. > > Then, first, update pg_database: > > UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE > usename='new_owner') WHERE datname='db_name'; > > If you also want to change the owner of the tables, update pg_class: > > UPDATE pg_class SET relowner=(SELECT usesysid FROM pg_shadow WHERE > usename='new_owner') WHERE relname IN (SELECT relname from > pg_class WHERE relname NOT LIKE 'pg_%'); > > Maybe there is a shorter way, but I know this one. > > HTH, > > Regards,