How To: Transfer ALL objects to RDS PostgreSQL "datical_user"

For PostgreSQL, when running Backup if you hit the following error you need to transfer the existing objects to the “datical_user”:

      stderr: pg_dump: [archiver (db)] query failed: ERROR:  permission denied for table <tablename>

pg_dump: [archiver (db)] query was: LOCK TABLE <schema>.<tablename> IN ACCESS SHARE MODE

To transfer existing objects to the “datical_user” you need to run the following command:

For RDS PostgreSQL databases, if you hit the below error, follow the instructions below. The error occurs because the RDS admin does not have “superuser” privilege which is required for reassignment.

Instructions

Run the reassignment as a new “change_owner” user:

1. Login as RDS admin and run:

   CREATE ROLE change_owner LOGIN; GRANT "current_owner" TO change_owner; GRANT "datical_user" TO change_owner; ALTER USER change_owner password 'admin123';

2. Logout and then login to “change_owner” role/user.

3. Run the command:

   REASSIGN OWNED by "current_owner" TO "datical_user";

Related articles

• Postgres EDB, PostgreSQL, and Azure Database for Postgres Setup

• https://sysadmintips.com/services/databases/postgresql-error-permission-denied-to-reassign-objects/