Re: Role problem in Windows

Moreno Andreo <moreno.andreo@xxxxxxxxxx> · Fri, 6 Jul 2018 18:44:38 +0200



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