Why Did My RDS for PostgreSQL Migration Fail?

Symptom

An error is reported when user root uses DRS to migrate RDS for PostgreSQL database data. In this case, you need to switch to another account or grant permissions to the current account.

Possible Causes

The migration may have failed because user root may not have permissions to operate certain objects when migrating the database using DRS.

Solution

1. Log in to the database as user root and run the following statements.

   grant USAGE on schema public to root;
   grant SELECT,REFERENCES,TRIGGER on all tables in schema public to root;
   grant EXECUTE on ALL FUNCTIONS IN SCHEMA public to root;

2. After the migration is complete, revoke the permissions.

   revoke USAGE schema public from root;
   revoke SELECT,REFERENCES,TRIGGER on all tables in schema public from root;
   revoke EXECUTE on ALL FUNCTIONS IN SCHEMA public from root;

You can run the commands to grant permissions as user root only when your database kernel version supports root privilege escalation. For details, see Privileges of the root User.