Search Postgresql Archives

Re: Role problem in Windows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Il 06/07/2018 17:34, Melvin Davidson ha scritto:


On Fri, Jul 6, 2018 at 10:01 AM, Moreno Andreo <moreno.andreo@xxxxxxxxxx> wrote:
Hi,
Running 9.1 on Windows 10, upgrading to 10 with pg_upgrade.

"Once upon a time" there was a bug in our automatic role creation procedure that did not mask vowels with accent (used in Italian language), like "ò, è" and the result was a role with an empty name.
We are now upgrading to 10, and pg_dumpall exits complaining with this role, showing its name (with mis-encoded UTF-8 accented vowel) as an invalid utf-8 character.

Trying to get rid of the role, that can't be deleted with a drop role because of the empty name, I did
delete from pg_authid where oid = nnnn

Role disappeared from role list.

At the next execution of the pg_upgrade it complains that role "nnnn" does not exist while dumping a trigger function. I tried remove the privilege from function ACL, but "role nnnnn does not exists".

Is there a way to recreate the deleted role, either as a dummy, so I can finish upgrade?
Is there another way to bypass the problem?

Any help would be appreciated.

Cheers,
Moreno.-



>Is there a way to recreate the deleted role, either as a dummy, so I can finish upgrade?
I can't really suggest how to recreate the dummy role, but I do have an alternate solution.
Most probably pg_dump is complaining that role 'xxx' owns some tables.
IIRC the complain was about "role <oid> does not exist"
In the meantime I was able to pg_dump single databases (5 in total, one of them complaining about the role not existing but dumped with all data in its place) and, with my surprise (since I was convinced that pg_dump was working inside a single transaction) I found all roles (all but the "failing" one) at their place in the new server.
So, lesson learned: don't mess with system catalogs before RTFM :-))))))

So you can use the
attached script and add 'AND a.rolname = 'xxx' to the WHERE clause.
Then as a superuser you can use ALTER TABLE xyz OWNER TO new_owner for each table found.
I'll keep it, so if something similar happens maybe it can come in hand.....

Thanks for your time
Moreno.-

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux