Re: BUG #11090: Unclear error message in pg_upgrade

On Tue, Jul 29, 2014 at 2:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > Jeff Janes wrote:
> >> I have one database with a retrospectively poor choice of initdb -U. I
> >> certainly wish there was a way, short of dump and restore, to change the
> >> name of that user. Although I don't think pg_upgrade would be the ideal
> >> way to do that.
>
> > Have you tried an UPDATE pg_authid?
>

No. Updating the system catalog directly sounds like one of those 'you get
to keep both pieces' situations, and it wasn't that important to me.

>
> Or even just ALTER USER RENAME?
>

I thought I tried that. On a test system, I see that it works once I
create yet another superuser to be logged in as as I do the renaming. I
think the problem I was having before I gave up is that the desired new
role already existed and owned some inconsequential objects. I tried to
REASSIGN OWNED the rest of the objects to it, which wouldn't work. What I
should have done is REASSIGNED OWNED the few stray objects from the desired
role back to the -U role (or to a completely different role), drop the
desired one, and then rename the -U user to the desired one.

So yeah, I think there is a path to do this, it is just quite convoluted.

Cheers,

Jeff