On Mon, May 13, 2013 at 9:13 AM, Vincent Veyron <vv.lists@wanadoo.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?