Re: Role problem in Windows

[Melvin Davidson <melvin6925@xxxxxxxxx>]

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. 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!

SELECT c.relname as table, 
       a.rolname as owner,
       c.relacl as permits
  FROM pg_class c
  JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE relname NOT LIKE 'pg_%' 
  AND relname NOT LIKE 'information%' 
  AND relname NOT LIKE 'sql_%' 
  AND relkind = 'r'
  --AND position('cp' in ARRAY_TO_STRING(c.relacl, '') )> 0
ORDER BY relname;