changing ownership of db

Поиск
Список
Период
Сортировка
Искать

changing ownership of db

От:
"Benjamin Thelen (CCGIS)" <thelen@ccgis.de>
Дата:
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




Re: changing ownership of db

От:
Devrim GUNDUZ <devrim@gunduz.org>
Дата:

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




Re: changing ownership of db

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Devrim GUNDUZ  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

Re: changing ownership of db

От:
Colin 't Hart <cthart@yahoo.com>
Дата:
On Tue, 29 Jul 2003 17:41:08 -0400, Tom Lane wrote:

> Devrim GUNDUZ  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

Re: changing ownership of db

От:
"Benjamin Thelen (CCGIS)" <thelen@ccgis.de>
Дата:
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,


FAQ