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

> 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