From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: User/Roles, Owner, and privileges migration strategy
Date: Sun, 22 Oct 2023 14:40:13 -0500
Message-ID: <5e4394dd-99b4-41b8-af38-e41f52415140@gmail.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>
On 10/21/23 21:51, ADITYA DUVURI wrote:
> 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.
Did you first run "pg_dumpall --globals-only" against the on-prem server?
(Maybe you did, and I missed it.)
That has all of the CREATE and ALTER ROLE statements. You'll have to edit
it first, though, to remove references to role "postgres", and other
statements that are outdated, or irrelevant to AWS RDS.
--
Born in Arizona, moved to Babylonia.
Attachments:
[image/png] image.png (12.2K, ../5e4394dd-99b4-41b8-af38-e41f52415140@gmail.com/3-image.png)
download | view image
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: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: User/Roles, Owner, and privileges migration strategy
In-Reply-To: <5e4394dd-99b4-41b8-af38-e41f52415140@gmail.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