skip to Main Content

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?

Console view

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


  1. 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.

    Login or Signup to reply.
  2. What Laurenz said. However regarding

    I can’t dump all settings because pg_dump requires have superuser role to dump them

    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

    GRANT rds_replication TO root;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search