While doing the migration from self hosted PostgreSQL to RDS data migration can be done via dump and restore.
But if we try to restore the users data it will fail with the following error.
ERROR: must be superuser to alter superusers
This happens because RDS will not allow superuser privilege to any user as its a cloud service. Roles like SuperUser and Replication are not supported in RDS.
This article explains the steps to migrate PostgreSQL users to RDS without any error.
Step1: Dump the user data.
#pg_dumpall -g > users.sql
Step2: Select the user queries and save to another file.
#awk '/CREATE/' users.sql > migrate.sql
Step3: Remove the superuser and replication roles.
#sed -i -e's/NOSUPERUSER//g; s/SUPERUSER//g; s/NOREPLICATION//g; s/REPLICATION//g' migrate.sql
Step4: Export superusers from current servers and grant rds_superuser privilege.
#psql -d postgres -t -c"select 'grant rds_superuser to '||rolname ||';' from pg_roles where rolsuper='t';" -P "footer=off" >> migrate.sql
Step5: Restore the user data to RDS.
#psql -h rds -U user -d postgres < migrate.sql
Where rds need to be replaced with RDS endpoint and user need to be replaced with the RDS PostgreSQL admin user.