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

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 - 

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