On Tue, Jul 29, 2014 at 2:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@2ndquadrant.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