Re: Upgrading password encryption from md5 to scram-sh-256

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 Fri, May 28, 2021 at 8:22 PM Jonathan Katz <
jonathan(dot)katz(at)excoventures(dot)com> wrote:

>
>
> > On May 28, 2021, at 9:27 AM, Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)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