sql: support client-provided password hashes

[cockroach-teamcity]

Exalate commented:

https://github.com/cockroachdb/cockroach/pull/72579 --- Release note (security update): It is now possible to pre-compute the hash of the password credentials of a SQL user client-side, and set the SQL user's password using the hash, so that the CockroachDB never sees the password string in clear in the SQL session. This auto-detection is subject to the new cluster setting server.user_login.store_client_pre_hashed_passwords.enabled. This setting defaults to true (i.e. feature enabled). This feature is meant for use in automation/orchestration, when the control plane constructs passwords for users outside of CockroachDB, and there is an architectural desire to ensure that cleartext passwords are not transmitted/stored in-clear. Note: when the client provides the password hash, CockroachDB cannot carry any checks on the internal structure of the password, such as minimum length, special characters, etc. Should a deployment require such checks to be performed database-side, the operator would need to disable the mechanism via the cluster setting named above. When upgrading a cluster from a previous version, to ensure that the feature remains disabled throughout the upgrade, use the following statement prior to the upgrade: sql INSERT INTO system.settings(name, value, "valueType") VALUES('server.user_login.store_client_pre_hashed_passwords.enabled', 'false', 'b'); (We do not recommend relying on the database to perform password checks. Our recommended deployment best practice is to implement credential definitions in a control plane / identity provider that is separate from the database.) Release note (sql change): The CREATE ROLE and ALTER ROLE statements now accept password hashes computed by the client app. For example: CREATE USER foo WITH PASSWORD 'CRDB-BCRYPT$2a$10$.....'. Note: this feature is not meant for use by human users / in interactive sessions; it is meant for use in programs, using the computation algorithm described below. This auto-detection can be disabled by changing the cluster setting server.user_login.store_client_pre_hashed_passwords.enabled to false. Note: this design mimics the behavior of PostgreSQL, which recognizes pre-computed password hashes when presented to the regular PASSWORD option (https://www.postgresql.org/docs/14/sql-createrole.html). The password hashes are auto-detected based on their lexical structure. For example, any password that starts with the prefix CRDB-BCRYPT$, followed by a valid encoding of a bcrypt hash (as detailed below), is considered a candidate password hash. To ascertain whether a password hash will be recognized as such, orchestration code can use the new built-in function crdb_internal.check_password_hash_format(). Currently, CockroachDB only recognizes password hashes computed using Bcrypt, as follows (we detail this algorithm so that orchestration software can implement their own password hash computation, separate from the database): 1. take the cleartext password string. 2. append the following byte array to the password: e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 (these are 32 hex-encoded bytes) (What are these bytes? it's the SHA-256 hash of an empty string. Why is it appended? This is a historical oddity in the CockroachDB with no particular reason. It adds no security.) 3. choose a Bcrypt cost. (CockroachDB servers use cost 10 by default.) 4. generate a bcrypt hash of the string generated at step 2 with the cost chosen at step 3, as per https://en.wikipedia.org/wiki/Bcrypt or https://bcrypt.online/ Note: at this point, CockroachDB only supports hashes computed using Bcrypt version 2a. 5. Encode the hash into the format recognized by CockroachDB: the string CRDB-BCRYPT, followed by the standard bcrypt hash encoding ($2a$...). Summary: | Hash method | Recognized by check_password_hash_format() | ALTER/CREATE USER WITH PASSWORD | |-----------------|----------------------------------------------|-------------------------------------------| | crdb-bcrypt | yes (CRDB-BCRYPT$2a$...) | recognized if enabled via cluster setting | | scram-sha-256 | yes (SCRAM-SHA-256$4096:...) | not implemented yet (issue #42519) | | md5 | yes (md5...) | obsolete, will likely not be implemented |

Jira Issue: DOC-2090

Jira Issue: DOC-4147

[knz]

NB: this change is not covered by PR #13679 and so needs a separate documentation effort.

[mari-crl]

:information_source: Hello! I am a human and not at all a robot! Look at my very human username! :robot: :notes: :thinking: Although I tried very hard to figure out what to do with this issue, more powerful human brains will need to help me. (specifically: Both Github and Jira issues were modified after exalate problems) :confounded: :arrows_counterclockwise: Please visit this issue's mirror at DOC-2090 and try to sync the two sides up manually. :star2: :white_check_mark: When you're finished, comment saying as much asn a member of Developer Infrastructure will be along to finish linking. :link: :no_entry_sign: Note that until this is done, this issue is not and will not be synced to Jira with Exalate. :no_entry_sign: :sweat_smile: Feeling lost? Don't worry about it! A member of @cockroachdb/exalate-22-cleanup-team will be along shortly to help! :+1: :construction_worker: Developer Infrastructure members: when ready, open Exalate from the right-hand menu of the mirror issue in Jira, then choose Connect and enter this issue's URN: cockroachdb/docs-12490. Either way, delete this comment when you're done. :key: :pray: Thank you for your compliance, my fellow humans! :robot: :wave: