Re: BUG #16672: Postgres user passwords are corrupted during migration

On Wed, Oct 14, 2020 at 09:07:31PM -0400, Tom Lane wrote:
> "akada shigeki" <zz5s-akd(at)asahi-net(dot)or(dot)jp> writes:
> > solved.
> > I was able to restore by changing SCRAM-SHA-256 in HBA.Conf to MD5.
> > Does this mean that PSQL doesn't support SHA-256 encryption?
>
> No, it means if you want to use SCRAM authentication then you need the
> password that's stored in pg_authid to be hashed with SCRAM. The password
> you carried forward from v12 evidently isn't. (Again, look into pg_authid
> to confirm that.)
>
> Do this, in either v12 or v13, to update the password:
>
> set password_encryption to "scram-sha-256";
> alter user postgres with password 'clear text password here';
>
> The system cannot do this for you because there's no way to recover
> the cleartext password from the md5 hash.

I am surprised we don't get more problem reports about this.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee