pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUser/Roles, Owner, and privileges migration strategy
13+ messages / 8 participants
[nested] [flat]
* User/Roles, Owner, and privileges migration strategy
@ 2023-10-20 10:20 ADITYA DUVURI <adilove1987@gmail.com>
2023-10-20 14:57 ` Re: User/Roles, Owner, and privileges migration strategy M Sarwar <sarwarmd02@outlook.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: ADITYA DUVURI @ 2023-10-20 10:20 UTC (permalink / raw)
To: pgsql-admin
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? There
are different use cases and how this can be handled like “Alter Owner” of
an object runs first before grant all privileges in pg_dump and it fails in
the PaaS services.
Thanks & Regards,
Aditya
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
@ 2023-10-20 14:57 ` M Sarwar <sarwarmd02@outlook.com>
1 sibling, 0 replies; 13+ messages in thread
From: M Sarwar @ 2023-10-20 14:57 UTC (permalink / raw)
To: ADITYA DUVURI <adilove1987@gmail.com>; pgsql-admin
I do not know what is your source database.
In postgres, roles are instance basis unlike database basis in other database flavours.
Good luck,
Sarwar
________________________________
From: ADITYA DUVURI <adilove1987@gmail.com>
Sent: Friday, October 20, 2023 6:20 AM
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: User/Roles, Owner, and privileges migration strategy
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? There are different use cases and how this can be handled like “Alter Owner” of an object runs first before grant all privileges in pg_dump and it fails in the PaaS services.
Thanks & Regards,
Aditya
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
@ 2023-10-22 02:51 ` ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 16:01 ` Re: User/Roles, Owner, and privileges migration strategy Holger Jakobs <holger@jakobs.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: ADITYA DUVURI @ 2023-10-22 02:51 UTC (permalink / raw)
To: pgsql-admin; david.g.johnston@gmail.com
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: 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.
>
Attachments:
[image/png] image.png (12.2K, ../../CAAWjWXKaJG-kD9FQBAode4358arJ4F5WPQ+Z3wttR5ixvpRxSQ@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
@ 2023-10-22 16:01 ` Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 13+ messages in thread
From: Holger Jakobs @ 2023-10-22 16:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
@ 2023-10-22 19:40 ` Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Ron @ 2023-10-22 19:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
@ 2023-10-24 11:18 ` Aditya D <dsaditya91@gmail.com>
2023-10-24 12:42 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-25 11:27 ` Re: User/Roles, Owner, and privileges migration strategy Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 13+ messages in thread
From: Aditya D @ 2023-10-24 11:18 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Thanks a lot Holger and Ron for the reply and valuable inputs. AWS RDS or
any other PaaS PostgreSQL instances does not support Superuser and to avoid
multiple pg_dump statements from on-premises to PaaS, is there any
recommended way?
Query the catalog tables and form the alter owner statements which will run
post pg_dump - - no owner or any other suggested method?
Regards,
Aditya D
On Mon, 23 Oct 2023 at 01:14, Ron <ronljohnsonjr@gmail.com> wrote:
> 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: 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, ../../CAEATte6+NPnY5o+i1b370PZm3L+LdsJ17uMDenBhAp9pKMGmrA@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
@ 2023-10-24 12:42 ` Ron <ronljohnsonjr@gmail.com>
2023-10-24 13:34 ` Re: User/Roles, Owner, and privileges migration strategy Dan Smith <j.daniel.smith1@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Ron @ 2023-10-24 12:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I regularly logged into an AWS RDS Postgresql instance as user "postgres",
where I created and altered roles.
On 10/24/23 06:18, Aditya D wrote:
> Thanks a lot Holger and Ron for the reply and valuable inputs. AWS RDS or
> any other PaaS PostgreSQL instances does not support Superuser and to
> avoid multiple pg_dump statements from on-premises to PaaS, is there any
> recommended way?
>
> Query the catalog tables and form the alter owner statements which will
> run post pg_dump - - no owner or any other suggested method?
>
> Regards,
> Aditya D
>
> On Mon, 23 Oct 2023 at 01:14, Ron <ronljohnsonjr@gmail.com> wrote:
>
> 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.
>
--
Born in Arizona, moved to Babylonia.
Attachments:
[image/png] image.png (12.2K, ../../5f887ae2-85b4-4fdb-a194-5252d65e516c@gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
2023-10-24 12:42 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
@ 2023-10-24 13:34 ` Dan Smith <j.daniel.smith1@gmail.com>
2023-10-25 10:01 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Dan Smith @ 2023-10-24 13:34 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
You should be able to restore a pg_dump as long as the user role you are
leveraging has the correct permissions. The user role created with the RDS
instance (whatever you name it) is as close as you can get to superuser
(member of rds_superuser role). That said, you can login as that user (or
another user with the same permissions) and create / alter roles. Also,
check if rds.restrict_password_commands is true; if so you may also need to
be a member of rds_password role.
In my opinion, having migrations for the creation of Roles (without
credentials [those do not belong in VCS]), DCL, and DDL is also helpful as
this allows you to quickly review code used to deploy and arrive at the
current state. The code for these roles and migrations is also a
convenient place to comment on any differences between on-prem and cloud
configuration; things never match one to one (rds group roles,
authentication methods, and parameter groups vs configuration files are
often different). Further this supports local development / testing with
Docker or Kubernetes with a bit more work (paying dividends if you are
supporting software teams or need to quickly test).
On Tue, Oct 24, 2023 at 8:47 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
> I regularly logged into an AWS RDS Postgresql instance as user "postgres",
> where I created and altered roles.
>
> On 10/24/23 06:18, Aditya D wrote:
>
> Thanks a lot Holger and Ron for the reply and valuable inputs. AWS RDS or
> any other PaaS PostgreSQL instances does not support Superuser and to avoid
> multiple pg_dump statements from on-premises to PaaS, is there any
> recommended way?
>
> Query the catalog tables and form the alter owner statements which will
> run post pg_dump - - no owner or any other suggested method?
>
> Regards,
> Aditya D
>
> On Mon, 23 Oct 2023 at 01:14, Ron <ronljohnsonjr@gmail.com> wrote:
>
>> 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: 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.
>>
>
> --
> Born in Arizona, moved to Babylonia.
>
--
Best regards,
Dan Smith
Attachments:
[image/png] image.png (12.2K, ../../CAK50JrxQBB8UcxYsX3O3jA2k2SOZdcC1kr9ZczBADqy4c_EjBQ@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
2023-10-24 12:42 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 13:34 ` Re: User/Roles, Owner, and privileges migration strategy Dan Smith <j.daniel.smith1@gmail.com>
@ 2023-10-25 10:01 ` Aditya D <dsaditya91@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Aditya D @ 2023-10-25 10:01 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>; +Cc: Chanderprabh Jain <chanderprabhjain95@gmail.com>
Thanks All.
For this scenario where ownership of the Object in PostgreSQL is getting
changed and while doing pg_restore with the admin user (non-superuser)
since PaaS PostgreSQL services does not provide superuser access in the
target, getting the following error *" pg_restore: error: could not execute
query:ERROR: permission denied for schema event_tracking. Command was:
ALTER TABLE event_tracking.notifications_responses OWNER TO user3"*
In the pg_dump file the Grants to the
*event_tracking.notifications_responses* are coming after the Alter owner
command.
Can you please help me here?
On Tue, Oct 24, 2023 at 7:08 PM Dan Smith <j.daniel.smith1@gmail.com> wrote:
> You should be able to restore a pg_dump as long as the user role you are
> leveraging has the correct permissions. The user role created with the RDS
> instance (whatever you name it) is as close as you can get to superuser
> (member of rds_superuser role). That said, you can login as that user (or
> another user with the same permissions) and create / alter roles. Also,
> check if rds.restrict_password_commands is true; if so you may also need to
> be a member of rds_password role.
>
> In my opinion, having migrations for the creation of Roles (without
> credentials [those do not belong in VCS]), DCL, and DDL is also helpful as
> this allows you to quickly review code used to deploy and arrive at the
> current state. The code for these roles and migrations is also a
> convenient place to comment on any differences between on-prem and cloud
> configuration; things never match one to one (rds group roles,
> authentication methods, and parameter groups vs configuration files are
> often different). Further this supports local development / testing with
> Docker or Kubernetes with a bit more work (paying dividends if you are
> supporting software teams or need to quickly test).
>
> On Tue, Oct 24, 2023 at 8:47 AM Ron <ronljohnsonjr@gmail.com> wrote:
>
>>
>> I regularly logged into an AWS RDS Postgresql instance as user
>> "postgres", where I created and altered roles.
>>
>> On 10/24/23 06:18, Aditya D wrote:
>>
>> Thanks a lot Holger and Ron for the reply and valuable inputs. AWS RDS or
>> any other PaaS PostgreSQL instances does not support Superuser and to avoid
>> multiple pg_dump statements from on-premises to PaaS, is there any
>> recommended way?
>>
>> Query the catalog tables and form the alter owner statements which will
>> run post pg_dump - - no owner or any other suggested method?
>>
>> Regards,
>> Aditya D
>>
>> On Mon, 23 Oct 2023 at 01:14, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>>> 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: 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.
>>>
>>
>> --
>> Born in Arizona, moved to Babylonia.
>>
>
>
> --
> Best regards,
>
> Dan Smith
>
Attachments:
[image/png] image.png (12.2K, ../../CAEATte6vuyOQw4FCk=TNw_DES2CR7A=5WvsDxVjA7YRWDu8p2g@mail.gmail.com/3-image.png)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
@ 2023-10-25 11:27 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-10-25 17:51 ` Re: User/Roles, Owner, and privileges migration strategy Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2023-10-25 11:27 UTC (permalink / raw)
To: Aditya D <dsaditya91@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
On 2023-Oct-24, Aditya D wrote:
> Thanks a lot Holger and Ron for the reply and valuable inputs. AWS RDS or
> any other PaaS PostgreSQL instances does not support Superuser and to avoid
> multiple pg_dump statements from on-premises to PaaS, is there any
> recommended way?
>
> Query the catalog tables and form the alter owner statements which will run
> post pg_dump - - no owner or any other suggested method?
I would do this using the following steps:
1. take a "pg_dumpall -g".
2. take a pg_dump (in -Fc or -Fd mode) of each database to migrate. Do
not use "--no-owner" or anything that breaks or incompletely dumps the
ownership or ACLs or objects.
3. write a script to filter each of the dumps in step 2, to obtain the
list of roles needed in GRANT commands or as object owners.
4. Filter the dump produced by step 1 down to the creation of just the
roles obtained by step 3.
5. Restore each of the dumps from step 2.
If step 5 finishes cleanly, then you're done. If it throws errors about
ownership or GRANTs, then your filtering scripts in steps 3 or 4 have
some mistake. Go back and fix the script, clean up from steps 4 and 5
and restart from 3. Lather, rinse, repeat.
Note that you only need to dump each database exactly twice (first to
produce the dumps you'll test with, second to do the actual migration
once your filtering script for steps 3 and 4 have been perfected.)
If you have objects owned by "postgres" or some other superuser, I'd
change them ahead of time to something else.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"In fact, the basic problem with Perl 5's subroutines is that they're not
crufty enough, so the cruft leaks out into user-defined code instead, by
the Conservation of Cruft Principle." (Larry Wall, Apocalypse 6)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
2023-10-25 11:27 ` Re: User/Roles, Owner, and privileges migration strategy Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-10-25 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-25 19:32 ` Re: User/Roles, Owner, and privileges migration strategy Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2023-10-25 17:51 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Aditya D <dsaditya91@gmail.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2023-Oct-24, Aditya D wrote:
>> Thanks a lot Holger and Ron for the reply and valuable inputs. AWS RDS or
>> any other PaaS PostgreSQL instances does not support Superuser and to avoid
>> multiple pg_dump statements from on-premises to PaaS, is there any
>> recommended way?
> I would do this using the following steps:
> ...
I kind of suspect that that's going to be a dead end. If you can't
run dump and restore as superuser, it's going to be very hard to deal
with a multi-user database, unless you use --no-owner which of course
doesn't restore the multiple object ownerships.
You might have to resort to separately dumping the objects belonging
to each user, and then running each restore as that user.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
2023-10-25 11:27 ` Re: User/Roles, Owner, and privileges migration strategy Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-10-25 17:51 ` Re: User/Roles, Owner, and privileges migration strategy Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-10-25 19:32 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-10-25 20:39 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2023-10-25 19:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Aditya D <dsaditya91@gmail.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
On 2023-Oct-25, Tom Lane wrote:
> I kind of suspect that that's going to be a dead end. If you can't
> run dump and restore as superuser, it's going to be very hard to deal
> with a multi-user database, unless you use --no-owner which of course
> doesn't restore the multiple object ownerships.
>
> You might have to resort to separately dumping the objects belonging
> to each user, and then running each restore as that user.
In Postgres you're probably right, but I understood that this is RDS,
which offers a superuser-of-sorts. It allows some alien stuff to be
done. Though ... re-reading the question, I see now he's saying it may
be RDS or maybe *other* DBaaS providers, so maybe it works and maybe it
doesn't, depending on the specifics.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: User/Roles, Owner, and privileges migration strategy
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 02:51 ` Re: User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 19:40 ` Re: User/Roles, Owner, and privileges migration strategy Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Re: User/Roles, Owner, and privileges migration strategy Aditya D <dsaditya91@gmail.com>
2023-10-25 11:27 ` Re: User/Roles, Owner, and privileges migration strategy Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-10-25 17:51 ` Re: User/Roles, Owner, and privileges migration strategy Tom Lane <tgl@sss.pgh.pa.us>
2023-10-25 19:32 ` Re: User/Roles, Owner, and privileges migration strategy Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-10-25 20:39 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Ron @ 2023-10-25 20:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 10/25/23 14:32, Alvaro Herrera wrote:
> On 2023-Oct-25, Tom Lane wrote:
>
>> I kind of suspect that that's going to be a dead end. If you can't
>> run dump and restore as superuser, it's going to be very hard to deal
>> with a multi-user database, unless you use --no-owner which of course
>> doesn't restore the multiple object ownerships.
>>
>> You might have to resort to separately dumping the objects belonging
>> to each user, and then running each restore as that user.
> In Postgres you're probably right, but I understood that this is RDS,
> which offers a superuser-of-sorts. It allows some alien stuff to be
> done. Though ... re-reading the question, I see now he's saying it may
> be RDS or maybe *other* DBaaS providers, so maybe it works and maybe it
> doesn't, depending on the specifics.
You can definitely run CREATE ROLE, ALTER ROLE and GRANT statements in AWS
RDS Postgresql. You're just not going to get password hashes.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2023-10-25 20:39 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-20 10:20 User/Roles, Owner, and privileges migration strategy ADITYA DUVURI <adilove1987@gmail.com>
2023-10-20 14:57 ` M Sarwar <sarwarmd02@outlook.com>
2023-10-22 02:51 ` ADITYA DUVURI <adilove1987@gmail.com>
2023-10-22 16:01 ` Holger Jakobs <holger@jakobs.com>
2023-10-22 19:40 ` Ron <ronljohnsonjr@gmail.com>
2023-10-24 11:18 ` Aditya D <dsaditya91@gmail.com>
2023-10-24 12:42 ` Ron <ronljohnsonjr@gmail.com>
2023-10-24 13:34 ` Dan Smith <j.daniel.smith1@gmail.com>
2023-10-25 10:01 ` Aditya D <dsaditya91@gmail.com>
2023-10-25 11:27 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-10-25 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-25 19:32 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-10-25 20:39 ` Ron <ronljohnsonjr@gmail.com>
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