Why users cannot change their own password in Aurora Postgres

Hello,

As a Database Administrator (DBA), you may consider assigning the task of managing user passwords to others. Alternatively, you might wish to restrict database users from altering their passwords or modifying password constraints, such as password expiration policies. To guarantee that only specific database users, chosen by you, have the authority to modify password settings, you can enable the restricted password management feature. Upon activation, only users with the rds_password role granted can handle password-related tasks.

To use restricted password management, your Aurora PostgreSQL DB cluster must be running Amazon Aurora PostgreSQL 10.6 or higher.

By default, this feature is off, as shown in the following:

SHOW rds.restrict_password_commands;

To turn on this feature, you use a custom parameter group and change the setting for rds.restrict_password_commands to 1. Be sure to reboot your Aurora PostgreSQL's primary DB instance so that the setting takes effect

Please refer doc for details.

Reference Document: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.Roles.html#Appendix.PostgreSQL.CommonDBATasks.RestrictPasswordMgmt

I tested a few things in my own environment, and I was able to have a user alter their own password.

I'll share the following:

I have an Aurora Postgres 14.8 cluster, defaults on everything. I used pgadmin and also tested with psql commands.

Performed the following as the master user:

CREATE ROLE testuser login PASSWORD '1234';
 
 GRANT CONNECT ON DATABASE postgres TO testuser;

Then I connected as the above user in a different connection with the above credentials.

Ran the following:

ALTER ROLE testuser WITH PASSWORD '12345';

Returned with:

ALTER ROLE

Query returned successfully in 97 msec.

Disconnected with the testuser connection and attempted to reconnect, but was unable to do so as it required the new updated password.

Users can also use psql when connected and use:

\password

I would ensure that the session user matches the current user. However, the above user was able to self-change password without additional grants.