Role problem in Windows
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.-
On Fri, Jul 6, 2018 at 10:01 AM, Moreno Andreo <moreno.andreo@evolu-s.it>
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 = nnnnRole 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. 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.
--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
Attachments:
tables_and_owners.sqltext/plain; charset=UTF-8; name=tables_and_owners.sqlDownload
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Il 06/07/2018 17:34, Melvin Davidson ha
scritto:<br>
</div>
<blockquote type="cite"
cite="mid:CANu8Fiz-ozRcBq_Y02_A5MV+BjneMPyn8Yp-SSGZqVb6Awjk7Q@mail.gmail.com">
<div dir="ltr"><br>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Fri, Jul 6, 2018 at 10:01 AM,
Moreno Andreo <span dir="ltr"><<a
href="mailto:moreno.andreo@evolu-s.it" target="_blank"
moz-do-not-send="true">moreno.andreo@evolu-s.it</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
Running 9.1 on Windows 10, upgrading to 10 with
pg_upgrade.<br>
<br>
"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.<br>
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.<br>
<br>
Trying to get rid of the role, that can't be deleted with
a drop role because of the empty name, I did<br>
delete from pg_authid where oid = nnnn<br>
<br>
Role disappeared from role list.<br>
<br>
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".<br>
<br>
Is there a way to recreate the deleted role, either as a
dummy, so I can finish upgrade?<br>
Is there another way to bypass the problem?<br>
<br>
Any help would be appreciated.<br>
<br>
Cheers,<br>
Moreno.-<br>
<br>
<br>
</blockquote>
</div>
<br>
>Is there a way to recreate the deleted role, either as a
dummy, so I can finish upgrade?
<br clear="all">
</div>
<div class="gmail_extra">I can't really suggest how to recreate
the dummy role, but I do have an alternate solution.</div>
<div class="gmail_extra">Most probably pg_dump is complaining
that role 'xxx' owns some tables.</div>
</div>
</blockquote>
IIRC the complain was about "role <oid> does not exist"<br>
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.<br>
So, lesson learned: don't mess with system catalogs before RTFM
:-))))))<br>
<br>
<blockquote type="cite"
cite="mid:CANu8Fiz-ozRcBq_Y02_A5MV+BjneMPyn8Yp-SSGZqVb6Awjk7Q@mail.gmail.com">
<div dir="ltr">
<div class="gmail_extra"> So you can use the <br>
</div>
<div class="gmail_extra">attached script and add 'AND a.rolname
= 'xxx' to the WHERE clause.</div>
<div class="gmail_extra">Then as a superuser you can use ALTER
TABLE xyz OWNER TO new_owner for each table found.<br>
</div>
</div>
</blockquote>
I'll keep it, so if something similar happens maybe it can come in
hand.....<br>
<br>
Thanks for your time<br>
Moreno.-<br>
</body>
</html>