sql: recognize client-supplied hashes in WITH PASSWORD like pg

[knz]

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

[bdarnell]

It has been suggested that this works already if you update the system.users table by hand. But it's better to have a supported option with clear syntax.

We should not support md5 auth at all, even as an opt-in. We should, however, support client-supplied bcrypt hashes.

I'd strongly prefer an explicit syntax like WITH HASHED PASSWORD instead of recognizing a magic prefix inside the password string.

This feature would make it impossible to enforce password complexity requirements. Given the existence of regulatory requirements around password complexity, and (until we implement scram) the fact that this change would not completely prevent the server from seeing the cleartext password (at login time), it's not clear to me that this is a good tradeoff.

[knz]

RFC here: #51599

[vbabenkoru]

@bdarnell What's the hashing algorithm used by CockroachDB? How can we create a hash outside of CockroachDB to then update this field with it?

[knz]

@vbabenkoru

// HashPassword takes a raw password and returns a bcrypt hashed password.
func HashPassword(ctx context.Context, password string) ([]byte, error) {
        sem := getBcryptSem(ctx)
        alloc, err := sem.Acquire(ctx, 1)
        if err != nil {
                return nil, err
        }
        defer alloc.Release()
        return bcrypt.GenerateFromPassword(appendEmptySha256(password), BcryptCost)

[knz]

NB: postgres has an ENCRYPTED keyword that's optional:

The ENCRYPTED keyword has no effect, but is accepted for backwards compatibility. The method of encryption is determined by the configuration parameter password_encryption. If the presented password string is already in MD5-encrypted or SCRAM-encrypted format, then it is stored as-is regardless of password_encryption (since the system cannot decrypt the specified encrypted password string, to encrypt it in a different format).

We could diverge from pg by requiring the ENCRYPTED keyword to specify pwd hashes perhaps.