Thread: 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

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

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:

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

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