Thread: Changing database owner (7.4)

Changing the owner of a database is easy in 8.x (alter database...) but
seems less so in 7.4.

Is there a method of safely changing the owner of the database (via
command or via updating pg* tables) in 7.4?

Cheers,
Steve

Steve Crawford wrote:
> Changing the owner of a database is easy in 8.x (alter database...)
> but seems less so in 7.4.
>
> Is there a method of safely changing the owner of the database (via
> command or via updating pg* tables) in 7.4?
>
Use a transaction and use update to pg_database

Joshua D. Drake

> Cheers,
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Joshua D. Drake wrote:
> Steve Crawford wrote:
>> Changing the owner of a database is easy in 8.x (alter database...)
>> but seems less so in 7.4.
>>
>> Is there a method of safely changing the owner of the database (via
>> command or via updating pg* tables) in 7.4?
>>
> Use a transaction and use update to pg_database

So just to be sure I don't mess this up...

1: Connect to server as user postgres database template1.
2: "update pg_database set datdba=1 where datname='my_db_name';"

By transaction are you suggesting wrapping the whole thing in a
begin/commit pair and reviewing the changes before a commit?

Also, do I need to be concerned about changing datacl (or any other
column) or just use the normal grant procedure for that?

Cheers,
Steve

Steve Crawford wrote:
> Joshua D. Drake wrote:
>> Steve Crawford wrote:
>>> Changing the owner of a database is easy in 8.x (alter database...)
>>> but seems less so in 7.4.
>>>
>>> Is there a method of safely changing the owner of the database (via
>>> command or via updating pg* tables) in 7.4?
>>>
>> Use a transaction and use update to pg_database
>
> So just to be sure I don't mess this up...
>
> 1: Connect to server as user postgres database template1.
> 2: "update pg_database set datdba=1 where datname='my_db_name';"
>
> By transaction are you suggesting wrapping the whole thing in a
> begin/commit pair and reviewing the changes before a commit?
That is EXACTLY what I am suggesting. That way you can do a select just
in case :) and roll back if you fat finger it.
>
> Also, do I need to be concerned about changing datacl (or any other
> column) or just use the normal grant procedure for that?
The owner will change but the acls won't so you will still have to
execute your grants if that is required.

Joshua D. Drake


>
>
> Cheers,
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match