Re: BUG #16672: Postgres user passwords are corrupted during migration - Mailing list pgsql-bugs

"akada shigeki" <zz5s-akd@asahi-net.or.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.

            regards, tom lane