Re: Change ownership of a database

No I did not work in the postgres db.
The owner ship of my db is the issue.

KD

On Feb 8, 2017 11:17 PM, "Steve Crawford" <scrawford(at)pinpointresearch(dot)com>
wrote:

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