I use AWS RDS instance and I understand that this is a managed service with some restrictions. But, I’ve faced the problem when trying to dump the entire database and dump logical replication settings. I can’t dump all settings because pg_dump
requires have superuser role to dump them, but only rds_admin
has a superuser role, and my root
user (which was postgres
) doesn’t have these grants. And also I don’t have a password to connect under rds_admin
because it is managed by AWS. What I am supposed to do?
I haven’t tried anything because AWS documentation says
When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is a pre-defined Amazon RDS role similar to the PostgreSQL superuser role (customarily named postgres in local instances), but with some restrictions. As with the PostgreSQL superuser role, the rds_superuser role has the most privileges on your DB instance and you should not assign this role to users unless they need the most access to the DB instance.
2
Answers
You cannot get a superuser on a cloud hosted database, period. That’s one of the many sacrifices you have to make for the benefit of not having to set up your own backups an your own monitoring system.
What Laurenz said. However regarding
I don’t think that is true. Depending on how you use it, you may require the
replication
permission though, which you don’t get the normal way on AWS RDS either. Instead, you would use