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

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(dot)daniel(dot)smith1(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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
>