Re: Help in vetting Switch from "MD5" to "scram-sha-256" - during DB Upgrade from EC2- PGS - Community Edn ver 13.X to 15.X

On 2/6/25 16:37, Bharani SV-forum wrote:
>  Team
> I am in the process of doing DB Upgrade from EC2- PGS - Community Edn
> ver 13.X to 15.X including switching from "MD5 " to "scram-sha-256" for
> password hashing and authentication.
>
> We are having tightly integrated appln tech stack having 256+ roles used
> by application with MD5 mechanism and having Password setting's
> replicated in each of the four server's (used to load balance the data
> flow to each server wise and  each stream Wise (e.g
> LAYER#A-server#1,LAYER#A-server#2,LAYER#A-server#3,LAYER#A-server#4
> LAYER#B-server#1,LAYER#B-server#2,LAYER#B-server#3,LAYER#B-server#4
> etc and had been embedded in an config file in each server wise
>
> We want to have very minimum down time, during the PG upgrade from ver
> 13.X to 15.X, need to , how to switch over "MD5" to "scram-sha-256" for
> password hashing and authentication.

https://www.postgresql.org/docs/15/auth-password.html

"md5

The method md5 uses a custom less secure challenge-response
mechanism. It prevents password sniffing and avoids storing passwords on
the server in plain text but provides no protection if an attacker
manages to steal the password hash from the server. Also, the MD5 hash
algorithm is nowadays no longer considered secure against determined
attacks.

The md5 method cannot be used with the db_user_namespace feature.

To ease transition from the md5 method to the newer SCRAM method,
if md5 is specified as a method in pg_hba.conf but the user's password
on the server is encrypted for SCRAM (see below), then SCRAM-based
authentication will automatically be chosen instead.
"

This means you can upgrade with the md5 passwords and then change over
to scram-sha-256 as needed.

>
> Need the best practice including steps to avoid mandated change of
> Application related Role Password during db migration ,as the # of
> roles/userid count is more.
>
> Regards
> Bharani
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com