Re: 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(at)gunduz(dot)org devrim(dot)gunduz(at)linux(dot)org(dot)tr
http://www.tdmsoft.com
http://www.gunduz.org

Devrim GUNDUZ <devrim(at)gunduz(dot)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(at)gunduz(dot)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,