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

On Fri, 2021-05-28 at 18:57 +0530, Nikhil Shetty wrote:
> 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.

There is no other way than for the users to set the password again, because PostgreSQL
doesn't know the original password.

You can leave the "pg_hba.conf" entry set to "md5", then users can login with
scram-sha-256 passwords as well as with md5 passwords.  That can make the transition
painless.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

It's no problem to leave the setting at md5 as this accepts the new scram passwords as well.

Just check after a while whether all passwords have been changed to scram.

Since there is no way of retrieving a password in clear it's also impossible to change them to scram automatically.

Hope this helps.



--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

> 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
toscram-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
becomesdifficult 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

Am 28.05.21 um 18:31 schrieb Nikhil Shetty:
> 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
>
Yes, that's exactly the way to go.


--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012