Why users cannot change their own password in Aurora Postgres

0

In the Aurora Postgres, I have users reported they cannot change their own passwords, get permission denied error. Is anything I miss while setting up users? I believe users should be able to change their own password. How can I fix this issue? Appreciate all your help.

Lisa
asked 7 months ago437 views
2 Answers
0

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

AWS
answered 7 months ago
  • Thanks for the information. Right now my environment is set by default, which is off. And we want users to be able to change their own password, however they cannot do that and get permission denied, I want to know how to fix this. Thanks again.

0

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.

AWS
SUPPORT ENGINEER
Kyle_B
answered 7 months ago
  • Thank you so much for all details. Some users were able to set their own password before. However after I run: alter role test_user set role test_role, the test_user cannot set its own password anymore. I don't know how to undo set role test_role, I run: revoke test_role from test_user which doesn't do anything. Any idea? Appreciate your help.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions