Assigning new role to already existing user in RDS

[rudder-ashish-bansode]

We have an RDS instance hosted for Aurora PostgreSQL, specifically using the 'db.r5.xlarge' instance type. When we initially set up the RDS instance, a default user named 'rdsadmin' was automatically created with the 'SUPERUSER' role.

During the setup, we also created a master user named 'X'. However, 'X' does not have 'SUPERUSER' permissions.

Our goal is to assign the 'pg_read_server_files' role to the user 'X'. The challenge we face is that we don't have the login credentials for the 'rdsadmin' user since it was a default root user provided by AWS during the RDS instance creation.

We attempted to use the command "GRANT pg_read_server_files to X;", but we encountered an error message:
"ERROR: Cannot issue GRANT on the 'pg_read_server_files' role. Detail: Cannot GRANT on the 'pg_read_server_files' role without being a member of the role."

Are there any possible workarounds to assign a new role to an existing user given this situation?

[aBurmeseDev]

Hi @AshishBansode22 - thanks for reaching out. Please understand that this repo is dedicated for AWS CLI related issues and in order for us to understand the issue you're facing, we're going to need these followings:

• AWS CLI version you're on: run aws --version in your terminal
• AWS CLI command you're running along with the debug logs: append --debug to the command for debug logs

I'm going to list a few resources that I think might be helpful to your use case:

• Assigning new role to already existing user in RDS
• Managing PostgreSQL users and roles
• Common DBA tasks for Amazon RDS for PostgreSQL

As another example, you don't have access to the PostgreSQL superuser account. On RDS for PostgreSQL, the rds_superuser role is the most highly privileged role, and it's granted to postgres at set up time. Whether you're familiar with using PostgreSQL on-premises or completely new to RDS for PostgreSQL, we recommend that you understand the rds_superuser role, and how to work with roles, users, groups, and permissions. For more information, see Understanding PostgreSQL roles and permissions.

Hope that helps!