CockroachDB currently requires the server to learn about the cleartext password of a SQL user when the password is stored (either in CREATE USER WITH PASSWORD, or ALTER USER WITH PASSWORD).

This is a security problem, and has been deprecated in PostgreSQL since v9.6 (released 2016).

The correct best practice is to have the client negotiate the password, then only provide the server with a hash/fingerprint that is sufficient to validate authentication when clients connect.

The way this works is the following:

• for MD5 authn: the client computes the MD5 hash, and provides the hash in WITH PASSWORD (with a md5: prefix)
• for SCRAM authn: the client chooses the SCRAM parameters, computes the hash, then provides both with WITH PASSWORD (with a scram-sha-256: prefix and 5 parameter/hash fields)

We may not wish to support MD5 auth at all in CockroachDB because it's considered obsolete (and MD5-based authn is vulnerable to various attacks already). However, perhaps it could be provided as a compatibility opt-in for legacy applications that require it.

SCRAM authn, on the other hand, is very much a thing. That particular project is tracked in #42519.

Epic CRDB-5349