How do I create another user with the same privileges as a default user for my Amazon RDS or Aurora DB instance with PostgreSQL?

Resolution

A DB instance that runs PostgreSQL has only one default user (sometimes called a master user) that's created when the instance is created. However, you can create another user that has all the same permissions as the default user.

Important: PostgreSQL logs passwords in clear-text in the log files. To prevent this, see How can I stop Amazon RDS for PostgreSQL from logging my passwords in clear-text in the log files?

Note: 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. To create a new user, run the CREATE ROLE with the CREATEDB, CREATEROLE, and LOGIN options. Replace new_master and password with your user name and password.

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

2. Grant the role that you created rds_superuser permissions. Replace new_master with your user name.

   postgres=> GRANT rds_superuser TO new_master;

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

Related information

Creating roles

Master user account privileges

How do I allow users to connect to Amazon RDS with AWS Identity and Access Management (IAM) credentials?