Обсуждение: [ADMIN] Change ownership of a database

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

 

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@gmail.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

 

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

 

REASSIGN OWNED BY

On Wed, Feb 8, 2017 at 6:33 PM, Kevin Duffy <kevind0718@gmail.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

 

--

Thanks,

Jorge Torralba
----------------------------

Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwise use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.