RDS - Need to add "REPLICATION" privilege, but do not have superuser access

0

We have an RDS instance (Postgres) that we need to run the below command, but receive an error

ALTER USER <user_name> REPLICATION;
SQL Error [42501]: ERROR: must be superuser to alter replication roles or change replication attribute

The user "rdsadmin" is the only role listed as a superuser, but we do not have credentials for this user.

How can I create a user with REPLICATION? We have a few users with elevated access, but none of them with "rolsuper=true".

asked 2 days ago17 views
3 Answers
0

You may grant REPLICATION privilege by below command:

GRANT rds_replication TO rdsadmin;

Verify by:

SELECT rolname, rolreplication 
FROM pg_roles 
WHERE rolname = 'rdsadmin';
answered 2 days ago
  • Hello Kidd, thank you for your response. But I am trying to add REPLICATION to a new user I've created that is not "rdsadmin". Here is an example of how I created the new user:

    -- create new user
    CREATE USER my_new_user LOGIN PASSWORD 'mypassword';
    
    -- attempt to add REPLICATION to "my_new_user", but this does not work due to lack of superuser privileges.
    ALTER USER my_new_user REPLICATION;
    

    The issue is that we do not have the password for "rdsadmin". We are logging in as an elevated user, but it lacks superuser privileges.

0

You need to get access with to the DB with a user that have the ability to grant you that privilege. If you have access to manage the DB, use the console or CLI to change rdsadmin password, and than connect to the database as rdsadmin and grant the new user the needed privilege. If you don't have access work with the person that have access to grant you access.

profile pictureAWS
answered 2 days ago
0

Hi,

Why do you need REPLICATION privileges and rds_replication is not enough? In RDS if you want to use logical replication you can just grant the rds_replication role to your user as rds_superuser.

See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication.html

AWS
answered 10 hours ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions