Lists: | pgsql-admin |
---|
From: | "Benjamin Thelen (CCGIS)" <thelen(at)ccgis(dot)de> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | changing ownership of db |
Date: | 2003-07-29 16:56:01 |
Message-ID: | 3F26A721.4020100@ccgis.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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
From: | Devrim GUNDUZ <devrim(at)gunduz(dot)org> |
---|---|
To: | "Benjamin Thelen (CCGIS)" <thelen(at)ccgis(dot)de> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: changing ownership of db |
Date: | 2003-07-29 17:23:10 |
Message-ID: | Pine.LNX.4.44.0307291956570.17013-100000@emo.org.tr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Devrim GUNDUZ <devrim(at)gunduz(dot)org> |
Cc: | "Benjamin Thelen (CCGIS)" <thelen(at)ccgis(dot)de>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: changing ownership of db |
Date: | 2003-07-29 21:41:08 |
Message-ID: | 4647.1059514868@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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
From: | Colin 't Hart <cthart(at)yahoo(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: changing ownership of db |
Date: | 2003-07-30 09:17:06 |
Message-ID: | pan.2003.07.30.09.17.06.78262@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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
From: | "Benjamin Thelen (CCGIS)" <thelen(at)ccgis(dot)de> |
---|---|
To: | Devrim GUNDUZ <devrim(at)gunduz(dot)org> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: changing ownership of db |
Date: | 2003-07-31 16:42:33 |
Message-ID: | 3F2946F9.3080505@ccgis.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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,