When migrating roles from one Aurora PostgreSQL cluster to another with existing passwords, you face a challenge since pg_dumpall with --roles-only won't work due to restricted access to pg_authid in Aurora.

Here's how you can approach this:

1. Use pg_dumpall to export just the global objects (roles) without passwords:

pg_dumpall -U <username> -h <source-hostname> -f roles.sql --no-role-passwords -g

2. You'll need to manually add the password statements to your SQL script. For each role, you'll need to add ALTER ROLE statements:

ALTER ROLE role_name WITH PASSWORD 'password';

3. To execute these password changes, you'll need appropriate permissions. In Aurora PostgreSQL, password management can be restricted. If the rds.restrict_password_commands parameter is set to 1, you'll need the rds_password role to execute password-related commands.

4. Import the modified script to your target database:

psql -h <target-hostname> -U <username> -f roles.sql

Note that this approach requires you to know the current passwords. If you don't have access to the current passwords, you might need to reset them after migration.

For a more automated approach, consider contacting AWS Support for guidance on migrating roles with passwords intact, as there might be AWS-specific tools or procedures available for Aurora PostgreSQL clusters.
Sources
PostgreSQL pg_dump and pg_restore utility - Database Migration Guide
Delegating and controlling user password management - Amazon Aurora

Hello,

As you may already know due to the managed nature of RDS/Aurora, we restrict access to certain system views/tables and pg_authid is part of the restrict list of system objects, because it contains sensitive information about database authorization roles i.e. passwords. Further, as you have already pointed out, if you are looking to migrate roles with passwords, you'll face certain limitations due to RDS's managed nature.

This is by design and limitation of RDS to maintain the security and integrity of the database instance. Considering the limitation with AWS RDS being a managed service, the only possible workaround is to transfer the role without password supplying "--no-role-passwords" to pg_dumpall command [2] (please note that this requires PostgreSQL client of version 10 and above).

Once the roles have been created, you will need to manually supply passwords for all users using "ALTER USER WITH PASSWORD". I would like to sincerely apologize for the inconvenience caused due to this limitation.

I have provided an example of a command that exports existing roles:

pg_dumpall -f users.sql -h <rds_end_point> -U <master_user> --no-role-passwords --roles-only -v

Unfortunately, even though it allows to export Users/Roles, it does not export passwords. Hence, it requires to explicitly manually supply passwords for all users using "ALTER USER WITH PASSWORD" to set passwords.

psql=> ALTER USER abc WITH PASSWORD 'xyz'; 

Due to RDS security restrictions, passwords cannot be directly copied. New passwords must be set on the target database.

We are aware of this limitation and we are working on a fix to allow the dumping of passwords as well.

I would humbly request you, to keep an eye on below links for feature announcements:

[+] Regularly checking the Aurora PostgreSQL release notes: notes:https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/Welcome.html [+] Subscribing to the AWS Database Blog: https://aws.amazon.com/blogs/database/ [+] Following the AWS News Blog : https://aws.amazon.com/blogs/aws/

I have tested and verified this solution in my environment. If you encounter any issues while implementing these steps or need further assistance, I encourage you to open a support case with AWS. Please ensure to Create the case from the AWS account where your Aurora PostgreSQL clusters reside.

Link to raise case: https://support.console.aws.amazon.com/support/home#/case/create

Thanks!

Reference:

[1] Importing Data into PostgreSQL on Amazon RDS - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html [2] "--no-role-passwords" - https://www.postgresql.org/docs/10/app-pg-dumpall.html