From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: User/Roles, Owner, and privileges migration strategy
Date: Sun, 22 Oct 2023 18:01:09 +0200
Message-ID: <48169403-a74e-aedc-933a-cdf3f9aa9b6e@jakobs.com> (raw)
In-Reply-To: <CAAWjWXKaJG-kD9FQBAode4358arJ4F5WPQ+Z3wttR5ixvpRxSQ@mail.gmail.com>
References: <CAAWjWX+4OzFiuG5S2c4iGg_p=CmLK3sf4-wMywFWqxS0OmzWBg@mail.gmail.com>
<CAKFQuwZh2ZUUgF7y9AyRdNeuzDYPDJT4X2kvxXwUUyu8Y1uk=Q@mail.gmail.com>
<CAAWjWXKaJG-kD9FQBAode4358arJ4F5WPQ+Z3wttR5ixvpRxSQ@mail.gmail.com>
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.g.johnston@gmail.com> wrote:
>
>
>
> On Friday, October 20, 2023, ADITYA DUVURI <adilove1987@gmail.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
Attachments:
[image/png] image.png (12.2K, ../48169403-a74e-aedc-933a-cdf3f9aa9b6e@jakobs.com/3-image.png)
download | view image
[application/pgp-signature] OpenPGP_signature (202B, ../48169403-a74e-aedc-933a-cdf3f9aa9b6e@jakobs.com/4-OpenPGP_signature)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: User/Roles, Owner, and privileges migration strategy
In-Reply-To: <48169403-a74e-aedc-933a-cdf3f9aa9b6e@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox