Re: User/Roles, Owner, and privileges migration strategy

Holger Jakobs <holger@xxxxxxxxxx> · Sun, 22 Oct 2023 18:01:09 +0200



    Am 22.10.23 um 04:51 schrieb ADITYA
      DUVURI:

    
        Hi Team,
          

          Let me take a user scenario here, I have an On-premises
            PostgreSQL instance with 50 databases. Out of which I need
            to migrate only 15 databases. There are around 10 different
            users and roles and millions of different database objects.
          In this example at the database level the owner for some
            objects are - 
          

          When I perform pg_dump with owners and ACL. the
            statements formed are -
          
            
              Create Table2
              Alter Owner Table2 to user2
              Grant All privileges on Table2 to user2
            
            The issue while restoring the above dump file created
              in any on-premises/AWS/GCP PostgreSQL instance fails since
              the grant statements have to be executed first before the
              Alter owner statement.
          
          
          One of the solutions is to have multiple pg_dump
            statements like -
          
            
              pg_dump - schema only with no owner no ACL
              pg_dump  -schema only with ACL | sed "Grant All"
              pg_dump -schame only with owner | sed "Alter owner"
            
            The above solution might have a performance impact
              since the source might contain millions of objects and
              performing pg_dump is an expensive operation multiple
              times. Is there any other way to perform different
              ownership of objects and grant migration from source to
              target PostgreSQL instances for that specific database and
              in an automated way?
          
          
          Source version of PostgreSQL instance can be - 10,11,12
          Target version of PostgreSQL instance can be - 14, 15
          

        Thanks & Regards,
        Aditya D
        

            On Fri, 20 Oct 2023 at
              20:39, David G. Johnston <david.g.johnston@xxxxxxxxx>
              wrote:

            
              On Friday, October 20, 2023, ADITYA DUVURI <adilove1987@xxxxxxxxx>
              wrote:

              Hi Team,
                

                Can you please let us know what is the
                  best strategy or different approaches to migrate
                  users/roles, privileges and owner to different PaaS
                  PostgreSQL instance like AWS RDS or GCP cloud SQL from
                  on-premises?
              
              
              Execute the migration scripts you have sitting in
                version control against the new server then restore the
                dumped data.
              

              David J. 

              
    Ordinary Dump files are made to be restored by a superuser role,
      so that GRANT and ALTER statements can be executed in any order.

    
    Dump files created with the option --no-owner can be played back
      by any user capable of creating objects, but the created objects
      will be owned by the creating user.
    

    -- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
  
Attachment:
OpenPGP_signature

Description: OpenPGP digital signature