Re: refactoring a database owner without "reassign owned"

On Mon, May 13, 2013 at 9:13 AM, Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> wrote:

> Le mercredi 08 mai 2013 à 14:11 -0700, Jeff Janes a écrit :
> > Let's say you have a database which is owned (as well as all the
> > contents within it) by the postgres internal user.
> >
> >
> > Having created or inherited a mess, how do you fix it?
> >
>
> with sed on Linux/Unix, you could do this :
>
> pg_dump -f mess.out mess
>
> sed -i 's/OWNER TO postgres/OWNER TO proper_username/' mess.out
>

I wouldn't mind using that in a pinch, but I would be leary of doing it
blindly as that text might occur in the data of the dump itself (for
example, if I had inserted the body of your email into a table). It would
be cleaner to dump just the schema and apply that sed script, then dump the
data in a separate run without filtering.

But even more clean would be to do the pg_dump with the -O flag, and then
"psql -U proper_username -f ...", I think, although I don't know that those
two things are identical--I'm sure there must be some corner cases where
they are not.

But it seems like there should be a good way to do this without needing a
dump/restore.

I think "reassign owned" should detect that it is being invoked on the
internal user (as it does now) but then instead of refusing to run, it
should DWIM. I suppose that was not implemented because it is difficult to
do so (but of course that is all the more reason not to leave it to the dba
to figure out how to do it themselves). Perhaps this is a todo item?

Cheers,

Jeff