Hi,
Thank you for your feedback Jonathan, Laurenz and Holger. I am thinking of using the below approach which will give users more control of when to change "application-user" password.
Is there any drawback if the user uses below steps to change their password?
1. alter user set password_encryption to 'scram-sha-256' 2. In a new session, users can change their passwords
Finally, once all users have changed password, set password_encryption at instance level, make changes in pg_hba and reload.
To use the same password as before, we can do "alter user <username> password <oldpassword>", so this will change to scram-sha-256 but no changes in application code.
Thanks and Regards,
Nikhil
> On May 28, 2021, at 9:27 AM, Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
>
> Hi Team,
>
> Just a quick check
> As per documentation, for upgrading password encryption from md5 to scram-sha-256, we have to set password_encryption to scram-sha-256, reset the user password and then change in pg_hba.conf.
>
> Is there any other way to do this without changing the password? if there are a lot of login users in the database it becomes difficult and it may incur downtime as well.
>
> I see there is a way the users can do it by themselves but still will incur some downtime.
Per downthread, no matter what you will need to rehash the password.
However, if you want to keep the “same” passwords as part of the transition,
you could add a hook to your application that does something like:
1. Have the user enter the password in plaintext
2. Convert that plaintext password to the Postgres md5 version (I described how
that method works here[1])
3. Use that as the password to login.
On the server side, you could then run a single script to convert all of the md5
hashes to SCRAM.
The above method works because the Postgres md5 hash is effectively the
password.
My recommendation would still be to follow the docs and have your users rehash
their passwords manually, given the leakiness of the md5 method. As Laurenz also
mention, it is possible to use both SCRAM + md5 simultaneously while you transition.
Thanks,
Jonathan
[1] https://blog.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram