Default `yugabyte` password should be SCRAM-hashed for clusters created with password_encryption= 'scram-sha-256'

[stevebang]

Jira Link: DB-1833 When a new cluster is created with password_encryption = 'scram-sha-256', the default password for the yugabyte user is MD5-hashed. The password should be SCRAM-hashed and not require additional steps by users. The alternative is to "Consider enabling scram-sha-256 as the default postgres authentication method instead of md5" (#4138 - filed by @iSignal).

Without addressing this issue, users must perform additional steps to change the yugabyte password to SCRAM-hashed.

---

Current behavior

@ddorian provided the steps below to enable password_encryption using scram-sha-256, and then change the yugabyte role password from MD5-hased to SCRAM-hashed.

1. Change YSQL password encryption from md5 to scram-sha-256. Because this is a PostgreSQL configuration parameter, you need to add the following flag to your flagfile.

ysql_pg_conf=password_encryption = scram-sha-256

2. Change --yb_hba_conf to use scram-sha-256. In this case, we put:

--ysql_hba_conf=host all postgres 0.0.0.0/0 trust, host all all 0.0.0.0/0 scram-sha-256, host all postgres ::0/0 trust, host all all ::0/0 scram-sha-256

This requires authentication for all users except the postgres role, which doesn't have a password by default.

3. Connect to the server and change the password for the role yugabyte.

$ ./bin/ysqlsh --username=postgres

postgres=# \password yugabyte
Enter new password:
Enter it again:

4. Verify that the password was hashed with SCRAM.

postgres=# SELECT
    rolname, rolpassword
FROM pg_authid
WHERE rolcanlogin;
 rolname  |                                                              rolpassword
----------+---------------------------------------------------------------------------------------------------------------------------------------
 postgres |
 yugabyte | SCRAM-SHA-256$4096:dnsF94xZmdJYZHoW5wXtdA==$4bxAFNHgmz9VXIWUxijReO4zyJ0DnX/iLNndKq32WOg=:TSXL3EF0fCroBXTndmp16Wle4F1MH+qc1P1iVFkqMQ8=
(2 rows)

[stevebang]

Another reason to change the password to match the password_encryption setting:

1. Start a cluster specifying password_encryption=scram_sha_256.
2. Change the yugabyte password using \password so that it is SCRAM-hashed.
3. Stop the cluster and restart it, with password_encryption=scram_sha_256. Log in with the yugabyte password and it works.
4. Instead of stopping the cluster, use yb-ctl destroy and then start the cluster again with password_encryption=scram_sha_256.
5. Try to log in and the password is MD5-hashed again (the default encryption). You cannot log in unless you have both MD5 and SCRAM in the ysql_hba_conf rules.

Just to make it much easier for users, when clusters are created with password_encryption=scram_sha_256, then the yugabyte default password should also be SCRAM-hashed. Otherwise, users have to go through multiple steps to convert the password and then remove the ysql_hba_conf rule that accepts MD5 passwords.

[ddorian]

@stevebang what about users migrating from PostgreSQL ? Their passwords may be saved in md5.