How do I create another user with the same privileges as a master user for my Amazon RDS or Aurora DB instance that's running PostgreSQL?

Last updated: 2020-03-03

I have an Amazon Relational Database Service (Amazon RDS) or an Amazon Aurora DB instance that runs PostgreSQL. I want another user to have the same permissions as the master user for my DB instance. How do I duplicate or clone the master user?

Resolution

A DB instance that runs PostgreSQL has only one master user that is created when the instance is created. However, you can create another user that has all the same permissions as the master user.

Important: The rds_superuser role has the most privileges for an RDS DB instance. Don't assign this role to a user unless they need the most access to the RDS DB instance.

1.    Create a new user by running the CREATE ROLE command:

postgres=> CREATE ROLE new_master WITH PASSWORD 'password' CREATEDB CREATEROLE LOGIN;

Note: Replace new_master and password with your user name and password.

2.    Grant the role that you created rds_superuser permissions:

postgres=> GRANT rds_superuser TO new_master;

Note: Replace new_master with your user name.

The new user now has the same permissions as the master user.