CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-rds
Laura
Laura

Reputation: 489

Rename the Amazon RDS master username

Changing the password is easily done through the console. Is there any way to change the master username after creation on RDS for PostgreSQL? If so, how?

Upvotes: 36

Views: 41338

Answers (5)

qff
qff

Reputation: 5902

As @tdubs's answer states, it is possible to change the master username for a Postgres DB instance in AWS RDS. Whether it is advisable – probably not.

Here are the SQL commands you need to issue:

  1. Create a temporary user with the CREATEROLE privilege (while being logged in with the old master user)
CREATE ROLE temp_master PASSWORD '<temporary password>' LOGIN CREATEROLE;
  1. Now connect to the database with the temp_master user
ALTER ROLE "<old_master_username>" RENAME TO "<new_master_username>";
-- NOTICE:  MD5 password cleared because of role rename
ALTER ROLE "<new_master_username>" PASSWORD '<new password>';
  1. Now connect to the database with the <new_master_username> user in order to clean up the temporary role
DROP ROLE temp_master;

And you're done!

Warning

AWS RDS does not know that the master username has been changed, so it will keep displaying the old one and assumes that is still the master username. This means that if you use the AWS CLI or website to update the master password, it will have no effect.

And when connecting to the database with psql you'll see:

WARNING:  role "<old_master_username>" does not exist

Screenshot from AWS RDS showing the old master username

Upvotes: 0

tdubs
tdubs

Reputation: 31

Though this may not be ideal for every use-case, I did find a workaround that allows for changing the username of the master user of an AWS RDS DB.

I am using PgAdmin4 with PostgreSQL 14 at the time of writing this answer.

  1. Login with the master user you want to change the name of

  2. Create a new user with the following privileges and membership Privileges and Membership

  • Can login - yes
  • Superuser - no (not possible with a managed AWS RDS DB instance, if you need complete superuser access DO NOT use a managed AWS RDS DB)
  • Create roles - yes
  • Create databases - yes
  • Inherit rights from the parent roles - yes
  • Can initiate streaming replication and backups - no (again, not possible directly without superuser permission)

Be sure to note the password used, as you will need to access this new account at least 1 time to complete the name change

  1. Register a server with the credentials created in step 2. Disconnect from the server but do NOT remove it! Connect to the new server created

  2. Expand Login/Group Roles and click on the master user whom you are changing the name

  3. Click the edit icon, edit the name, and save.

  4. Right click the server with the master username, select Properties

  • Update the name under the General tab if desired
  • Update the username under the Connection tab to whatever you changed the master username above
  • Save and reconnect to the server with the master user

You have successfully updated the master user's name on a managed AWS RDS DB instance, proud of you!

Upvotes: 3

funny_head
funny_head

Reputation: 101

No. As of April 2019 one cannot reset the 'master username'.

Upvotes: 6

Thomas Hunziker
Thomas Hunziker

Reputation: 2038

You cannot do it directly. However you can use the database migration service from AWS:

https://aws.amazon.com/dms/

Essentially you define the current database instance as your source and the new database with the correct username as your target of the migration.

This way you migrate the data from one to another database instance. As such you can change all properties including the username.

This approach has some drawbacks:

  • You need to configure the migration. Which takes a bit of time.
  • The data is migrated. This may lead unexpected behavior since not everything is eventually migrated (e.g. views etc.)
  • It depends how you setup everything you may experience a downtime.

Upvotes: 5

Valeriy Solovyov
Valeriy Solovyov

Reputation: 5648

You can't change username. You can check the following links that describe how to change master password and if Amazon adds the ability to change username you will find there:

Try to find at AWS CLI for RDS:

 modify-db-instance --db-instance-identifier <value> --master-user-password (string)

--master-user-password (string)

The new password for the DB instance master user. Can be any printable ASCII character except "/", """, or "@".

Changing this parameter does not result in an outage and the change is asynchronously applied as soon as possible. Between the time of the request and the completion of the request, the MasterUserPassword element exists in the PendingModifiedValues element of the operation response. Default: Uses existing setting

Constraints: Must be 8 to 41 alphanumeric characters (MySQL, MariaDB, and Amazon Aurora), 8 to 30 alphanumeric characters (Oracle), or 8 to 128 alphanumeric characters (SQL Server).

The Amazon RDS Command Line Interface (CLI) has been deprecated. Instead, use the AWS CLI for RDS.

Via the AWS Management Console, choose the instance you need to reset the password for, click ‘Modify’ then choose a new master password.

If you don’t want to use the AWS Console, you can use the rds-modify-db-instance command (as per Amazon’s documentation for RDS) to reset it directly, given the AWS command line tools: rds-modify-db-instance instance-name --master-user-password examplepassword

Upvotes: 6

Related Questions