pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpgrading password encryption from md5 to scram-sh-256
6+ messages / 4 participants
[nested] [flat]
* Upgrading password encryption from md5 to scram-sh-256
@ 2021-05-28 13:27 Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Nikhil Shetty @ 2021-05-28 13:27 UTC (permalink / raw)
To: pgsql-admin
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.
Thanks and Regards,
Nikhil
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrading password encryption from md5 to scram-sh-256
@ 2021-05-28 14:29 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2021-05-28 14:29 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrading password encryption from md5 to scram-sh-256
@ 2021-05-28 14:41 Holger Jakobs <holger@jakobs.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Holger Jakobs @ 2021-05-28 14:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 28. Mai 2021 15:27:43 MESZ schrieb Nikhil Shetty <nikhil.dba04@gmail.com>:
>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.
>
>Thanks and Regards,
>Nikhil
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 -
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrading password encryption from md5 to scram-sh-256
@ 2021-05-28 14:52 Jonathan Katz <jonathan.katz@excoventures.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Jonathan Katz @ 2021-05-28 14:52 UTC (permalink / raw)
To: Nikhil Shetty <nikhil.dba04@gmail.com>; +Cc: pgsql-admin
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrading password encryption from md5 to scram-sh-256
@ 2021-05-28 16:31 Nikhil Shetty <nikhil.dba04@gmail.com>
parent: Jonathan Katz <jonathan.katz@excoventures.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nikhil Shetty @ 2021-05-28 16:31 UTC (permalink / raw)
To: Jonathan Katz <jonathan.katz@excoventures.com>; Laurenz Albe <laurenz.albe@cybertec.at>; holger@jakobs.com; +Cc: pgsql-admin
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.katz@excoventures.com> wrote:
>
>
> > 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Upgrading password encryption from md5 to scram-sh-256
@ 2021-05-29 08:10 Holger Jakobs <holger@jakobs.com>
parent: Nikhil Shetty <nikhil.dba04@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Holger Jakobs @ 2021-05-29 08:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-05-29 08:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-28 13:27 Upgrading password encryption from md5 to scram-sh-256 Nikhil Shetty <nikhil.dba04@gmail.com>
2021-05-28 14:29 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-05-28 14:41 ` Holger Jakobs <holger@jakobs.com>
2021-05-28 14:52 ` Jonathan Katz <jonathan.katz@excoventures.com>
2021-05-28 16:31 ` Nikhil Shetty <nikhil.dba04@gmail.com>
2021-05-29 08:10 ` Holger Jakobs <holger@jakobs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox