Changing DB ownership
Dumb question maybe, but how does one change database ownership? I've
tried several permutations of:
ALTER DATABASE SET ("|'| )owner("|'| ) TO ("|'| )newowner("|'| );
and I tried looking at pg_database and I *was* able to hack this (got a
clue half way through writing this email... sorry):
UPDATE pg_database SET datdba = 504 WHERE datname='chris';
And that worked as expected, but I'm worried that I might have missed
something somewhere else. Will the above UPDATE cause problems down the
road?
Any info/clue would be much appreciated. Thanks in advance.
Cheers,
Chris
Why would you want to do that?
Why not do it an easier way and dump the database and restore it into
your new database?
There's got to be a lot of stuff to consider when doing something as
radical as renaming a database.
I am a developer of dental computer systems using Postgres within Red
Hat Advanced Server 2.1.
Jim Apsey
Christopher Murtagh wrote:
Show quoted text
Dumb question maybe, but how does one change database ownership? I've
tried several permutations of:ALTER DATABASE SET ("|'| )owner("|'| ) TO ("|'| )newowner("|'| );
and I tried looking at pg_database and I *was* able to hack this (got a
clue half way through writing this email... sorry):UPDATE pg_database SET datdba = 504 WHERE datname='chris';
And that worked as expected, but I'm worried that I might have missed
something somewhere else. Will the above UPDATE cause problems down the
road?Any info/clue would be much appreciated. Thanks in advance.
Cheers,
Chris
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
On Tue, 2003-08-05 at 11:11, Jimmie H. Apsey wrote:
Why would you want to do that?
Why would I want to change ownership of a database? I could think of
numerous reasons.
Why not do it an easier way and dump the database and restore it into
your new database?
Because that would mean that the database would be down while I did
that. The users to whom I gave ownership to the DB already had
permissions on the tables in the DB. This meant no down time.
There's got to be a lot of stuff to consider when doing something as
radical as renaming a database.
I didn't rename any database, nor do anything 'radical'. Please re-read
my message and you'll see.
This SQL query:
UPDATE pg_database SET datdba = 504 WHERE datname='chris';
is obviously simpler than a pg_dump, dropdb, createdb, pg_restore.
Cheers,
Chris
--
Christopher Murtagh
Webmaster / Sysadmin
Web Communications Group
McGill University
Montreal, Quebec
Canada
Tel.: (514) 398-3122
Fax: (514) 398-2017
Hi,
Why would you want to do that?
Why not do it an easier way and dump the database and restore it into
your new database?There's got to be a lot of stuff to consider when doing something as
radical as renaming a database.
He is not talking about renaming his database, he is talking about changing
the OWNER of the database.
UPDATE pg_database SET datdba = 504 WHERE datname='chris';
This is how I change the owner of the database too. It's not that diffucult,
but it would be nice if it could be changed using an ALTER statement.
I have noticed in the past that the dumps produced by pg_dump are difficult to
restore if the datdba you change to has no rights to create databases. I
haven't tested this with recent releases though. I suspect that this has
already been fixed in pg_dump.
Bye,
Sander.
Added to TODO, to match ALTER TABLE ... OWNER:
o Add ALTER DATABASE ... OWNER TO newowner
---------------------------------------------------------------------------
Sander Steffann wrote:
Hi,
Why would you want to do that?
Why not do it an easier way and dump the database and restore it into
your new database?There's got to be a lot of stuff to consider when doing something as
radical as renaming a database.He is not talking about renaming his database, he is talking about changing
the OWNER of the database.UPDATE pg_database SET datdba = 504 WHERE datname='chris';
This is how I change the owner of the database too. It's not that diffucult,
but it would be nice if it could be changed using an ALTER statement.I have noticed in the past that the dumps produced by pg_dump are difficult to
restore if the datdba you change to has no rights to create databases. I
haven't tested this with recent releases though. I suspect that this has
already been fixed in pg_dump.Bye,
Sander.---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073