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

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 -
> image.png
>
> 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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>
>
> On Friday, October 20, 2023, ADITYA DUVURI <adilove1987(at)gmail(dot)com>
> 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