Re: Change ownership of a database

Is it the *owner* you are worried about or did you create the tables in the
postgres database instead of creating a new database and using that one?

If the database is correct but not the owner you can just use
"grant/revoke" to update privileges as appropriate.

If you created your tables in the postgres database, you have a few
options. One is to dump the postgres database using the --no-owner option
then create your desired database and restore, as your desired correct new
owner, the dump into your new database.

Another option is to use the "alter database" command to rename postgres to
your desired new name and to change the ownership of the newly renamed
database as desired. You should then recreate the postgres database from
template1.

Finally, you can create the new database using postgres as the template
database. You will still need to alter the newly created database to set
the ownership and, potentially, grant privileges. More on creating database
from templates is here:
https://www.postgresql.org/docs/current/static/manage-ag-templatedbs.html

This assumes you don't have a bunch of detailed roles and
table/view/...-specific permissions in which case things may be more
complicated.

Cheers,
Steve

On Wed, Feb 8, 2017 at 6:33 PM, Kevin Duffy <kevind0718(at)gmail(dot)com> wrote:

> Hello all:
>
> I made a mistake. Maybe I was lazy.
> I created a database and did some amount of development as postgres.
>
> Now I need to get this changed to a log-on role say xxx-db-owner.
> I have a bunch of tables , functions and user defined types.
>
> I have created the role: xxx-db-owner.
> And most important I have db backups and have a copy of the db on the
> server under a different name.
>
> And I have written the sql to to the changes for the tables, functions and
> user defined types.
>
> Question: the database and schema ownership needs to be changed also?
> Is there an order I should do the changes in?
> Is there a way I can shoot myself in the foot here.
>
> Many thanks for your attention to this matter.
>
>
> KBD
>
>
>
>