security: support SCRAM-SHA-256 authentication method #42519

rolandcrosby · 2019-11-15T21:17:49Z

Summary

Add the SCRAM-SHA-256 auth method:

extend WITH PASSWORD to recognize scram passwords and store the client-supplied hashes in system.users (distinguish pw encoding types using a prefix; our current bcrypt-based encoding always starts with $, and pg's implementation has a standard encoding that starts with scran-sha-256: and 5 fields)
allow specifying the SCRAM method in the HBA config
implement the SCRAM exchange in pgwire w/ tests
Add support for the password_encryption session setting: https://www.postgresql.org/docs/current/runtime-config-connection.html

Protocol summary

Example SCRAM client-server exchange as per RFC 7677:

   This is a simple example of a SCRAM-SHA-256 authentication exchange
   when the client doesn't support channel bindings.  The username
   'user' and password 'pencil' are being used.

   C: n,,n=user,r=rOprNGfwEbeRWgbNEkqO

   S: r=rOprNGfwEbeRWgbNEkqO%hvYDpWUa2RaTCAfuxFIlj)hNlF$k0,
      s=W22ZaJ0SNY7soEsUEjb6gQ==,i=4096

   C: c=biws,r=rOprNGfwEbeRWgbNEkqO%hvYDpWUa2RaTCAfuxFIlj)hNlF$k0,
      p=dHzbZapWIk4jUhN+Ute9ytag9zjfMHgsqmmiz7AndVQ=

   S: v=6rriTRBi23WpRR/wtup+mMhUZUn/dB5nLTJRsjl95G4=

Summary of the protocol as per RFC 5802:

     SaltedPassword  := Hi(Normalize(password), salt, i)
     ClientKey       := HMAC(SaltedPassword, "Client Key")
     StoredKey       := H(ClientKey)
     AuthMessage     := client-first-message-bare + "," +
                        server-first-message + "," +
                        client-final-message-without-proof
     ClientSignature := HMAC(StoredKey, AuthMessage)
     ClientProof     := ClientKey XOR ClientSignature
     ServerKey       := HMAC(SaltedPassword, "Server Key")
     ServerSignature := HMAC(ServerKey, AuthMessage)

   The server authenticates the client by computing the ClientSignature,
   exclusive-ORing that with the ClientProof to recover the ClientKey
   and verifying the correctness of the ClientKey by applying the hash
   function and comparing the result to the StoredKey.  If the ClientKey
   is correct, this proves that the client has access to the user's
   password.

   Similarly, the client authenticates the server by computing the
   ServerSignature and comparing it to the value sent by the server.  If
   the two are equal, it proves that the server had access to the user's
   ServerKey.

What gets stored in the database:

   If the password is
   encrypted with SCRAM-SHA-256, it consists of 5 fields separated by colons.
   The first field is the constant <literal>scram-sha-256</literal>, to
   identify the password as a SCRAM-SHA-256 verifier. The second field is a
   salt, Base64-encoded, and the third field is the number of iterations used
   to generate the password.  The fourth field and fifth field are the stored
   key and server key, respectively, in hexadecimal format. A password that
   does not follow either of those formats is assumed to be unencrypted.

Special note about SCRAM for SQL

SCRAM specifies "n=user" is the username in the initial client message.
However, in PostgreSQL the username is sent in the startup packet, and
the username in the SCRAM exchange is ignored. libpq always sends it
as an empty string.

postgres source code, function read_client_first_message in auth-scram.c

Special note about leaking information

If a user doesn't exist, perform a "mock" authentication, by constructing
an authentic-looking challenge on the fly. The challenge is derived from
a new system-wide random value, "mock authentication nonce", which is
created at initdb, and stored in the control file. We go through these
motions, in order to not give away the information on whether the user
exists, to unauthenticated users.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=818fd4a67d610991757b610755e3065fb99d80a5#patch11

References

Reference docs:
- RFC 5802 defines SCRAM
- RFC 7677 specializes SCRAM to SHA-256
- patch that added SCRAM to the pg source code
- pg docs about scram auth
Example implementations:
- simple SCRAM client code in the Go MongoDB driver
- PR that added SCRAM support in lib/pq

Motivation

Postgres added support for SCRAM-SHA-256 password encryption in version 10. This is a more modern password authentication mechanism than the old "MD5" password authentication mechanism that pg was previously using.

Note that CockroachDB doesn't even support MD5 authentication, and instead requires the client to present the password in cleartext. This is because CRDB uses a non-standard encoding of the password hash in-DB using bcrypt.

The benefit of SCRAM is twofold:

it increases overall server security, by ensuring that the server never sees cleartext passwords even during authn verification.
it pushes the CPU cost of password checks to the client-side.

Note that implementing pg's native MD5 authentication would achieve the same goals, but MD5 authn is vulnerable to various attack vectors where SCRAM is not.

Epic CRDB-5349

The text was updated successfully, but these errors were encountered:

ajwerner · 2019-11-16T00:32:39Z

SCRAM-SHA-256 pushes the expensive hashing step to happen at the time that a password is set, meaning authentication events can be faster and cheaper.

It also pushes hashing to the client on every authentication attempt, though a clients can cache the outcome of this work. The client hashing makes the protocol more resistant to offline attacks; for each password the attacker wants to try, they'll need to hash again.

knz · 2020-04-17T07:14:44Z

cc @bdarnell @aaron-crl -- I have extended/populated the top issue description with the technical details about the work to be done.

knz · 2020-07-20T11:56:31Z

RFC here: #51599

aaron-crl · 2020-07-31T20:33:22Z

cc @thtruo for tracking.

joshimhoff · 2020-11-03T18:17:55Z

I've been paged two times in the last month or so due to excess CPU usage from password hashing causing node liveness problems & thus large scale issues with a CC cluster.

Are we shooting to get this done by a certain date?
Once we have it, will all password authentication happen via this method?

thtruo · 2020-11-03T18:58:36Z

Thanks for the callout Josh. This is something the Security team is planning to take on, as part of the effort to lift the current authentication code into an independent package, though we don't have a specific date in mind yet.

IIRC once we have it, all pw authn should happen with SCRAM. cc @aaron-crl to shed more light

cc @piyush-singh for awareness

joshimhoff · 2020-11-03T22:51:01Z

Understood! Can you give me a sense of how big of an engineering lift it is?

Is it possible to backport this change? I kinda doubt it but why not ask.

If a user creates a username with password before this change is rolled out, are they stuck without SCRAM for ever? Or does a migration get run?

bdarnell · 2020-11-04T16:11:06Z

There are some non-trivial changes to the authentication protocol that go together with using SCRAM, so it's a substantial amount of work and wouldn't normally qualify for a backport. (one reason I'd like to see us have CC run its own forks instead of being held back by mainline releases and their backport policies)

Once we have it, will all password authentication happen via this method?

It's possible that some client drivers don't support SCRAM (or versions that support it aren't widely rolled out yet - it was a relatively recent addition and these things can move slowly). In that case we'd have to decide whether we want to cut off those clients or continue to allow non-SCRAM logins (for CC without network-level access controls, I think we'd want to go SCRAM-only, but on-prem customers might choose differently).

If a user creates a username with password before this change is rolled out, are they stuck without SCRAM for ever? Or does a migration get run?

In any case, we'd need to continue supporting non-SCRAM logins at least for a transition period. We can't do a SCRAM login when the database contains a bcrypted password, and we can't convert directly from bcrypt to SCRAM. The best migration we could do would be to convert passwords to the SCRAM format the next time they log in. That would solve the "accidental DoS" problem of a legitimate user logging in repeatedly, but would still allow password-guessing attacks to consume a lot of CPU.

We wish to use this in the CC control plane, when provisioning SQL accounts in new clusters, or when users manipulate their user list in the CC management console. 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 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. Release note (security update): CockroachDB now interprets certain strings passed to 'CREATE/ALTER USER/ROLE WITH PASSWORD' specially (see previous release note), subject to the new cluster setting `server.user_login.detect_password_encoding.enabled`. This setting defaults to `true` (i.e. feature enabled). 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.detect_password_encoding.enabled', 'false', 'b'); ``` 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 '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.detect_password_encoding.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 `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()`. Currently, CockroachDB only recognizes password hashes computed using Bcrypt, as follows: 1. take the cleartext password string 2. append the following string to the password: `"\xe3\xb0\xc4B\x98\xfc\x1c\x14\x9a\xfb\xf4șo\xb9$'\xaeA\xe4d\x9b\x93L\xa4\x95\x99\x1bxR\xb8U"` (What is this string? 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. (This entails generating a 16 bytes random salt, base-64 encoding the salt, applying the bcrypt function.) 5. Encode the hash into the format recognized by CockroachDB: the string `BCRYPT$2a$` (Bcrypt major/minor versions), followed by the Bcrypt cost as two decimal digits, followed by `$`, followed by the base-64 encoding of the salt, followed by the base-64 encoding of the bcrypt hash. Note: the base64 encoding should use the following alphabet: `./ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789` Note: at this point, CockroachDB only supports hashes computed using Bcrypt version 2 or earlier. Summary: | Hash method | Recognized by `crdb_internal.check_password_hash` | ALTER/CREATE USER WITH PASSWORD | |-----------------|---------------------------------------------------|-------------------------------------------| | `bcrypt` | yes (`BCRYPT$2a$...`) | recognized if enabled via cluster setting | | `scram-sha-256` | yes (`SCRAM-SHA-256$4096:...`) | not implemented yet (issue cockroachdb#42519) | | `md5` | yes (`md5...`) | obsolete, will not be implemented |

We wish to use this in the CC control plane, when provisioning SQL accounts in new clusters, or when users manipulate their user list in the CC management console. 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.detect_password_encoding.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.detect_password_encoding.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.detect_password_encoding.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. (This entails generating a 16 bytes random salt, base-64 encoding the salt, applying the bcrypt function.) 5. Encode the hash into the format recognized by CockroachDB: the string `CRDB-BCRYPT$2a$` (Bcrypt major/minor versions), followed by the Bcrypt cost as two decimal digits, followed by `$`, followed by the base-64 encoding of the salt, followed by the base-64 encoding of the bcrypt hash. Note: the base64 encoding should use the following alphabet: `./ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789` Note: at this point, CockroachDB only supports hashes computed using Bcrypt versions 2 or 2a. Summary: | Hash method | Recognized by `check_password_hash_format()` | ALTER/CREATE USER WITH PASSWORD | |-----------------|----------------------------------------------|-------------------------------------------| | `bcrypt` | yes (`CRDB-BCRYPT$2a$...`) | recognized if enabled via cluster setting | | `scram-sha-256` | yes (`SCRAM-SHA-256$4096:...`) | not implemented yet (issue cockroachdb#42519) | | `md5` | yes (`md5...`) | obsolete, will likely not be implemented |

We wish to use this in the CC control plane, when provisioning SQL accounts in new clusters, or when users manipulate their user list in the CC management console. 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.detect_password_encoding.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.detect_password_encoding.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.detect_password_encoding.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. (This entails generating a 16 bytes random salt, base-64 encoding the salt, applying the bcrypt function.) 5. Encode the hash into the format recognized by CockroachDB: the string `CRDB-BCRYPT$2a$` (Bcrypt major/minor versions), followed by the Bcrypt cost as two decimal digits, followed by `$`, followed by the base-64 encoding of the salt, followed by the base-64 encoding of the bcrypt hash. Note: the base64 encoding should use the following alphabet: `./ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789` Note: at this point, CockroachDB only supports hashes computed using Bcrypt versions 2 or 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 cockroachdb#42519) | | `md5` | yes (`md5...`) | obsolete, will likely not be implemented |

We wish to use this in the CC control plane, when provisioning SQL accounts in new clusters, or when users manipulate their user list in the CC management console. 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 cockroachdb#42519) | | `md5` | yes (`md5...`) | obsolete, will likely not be implemented |

72579: sql: support client-provided password hashes r=RichardJCai,JeffSwenson,catj-cockroach,rafiss,bdarnell a=knz Fixes #50757 We wish to use this in the CC control plane, when provisioning SQL accounts in new clusters, or when users manipulate their user list in the CC management console. 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 (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 | Co-authored-by: Raphael 'kena' Poss <knz@thaumogen.net>

We wish to use this in the CC control plane, when provisioning SQL accounts in new clusters, or when users manipulate their user list in the CC management console. 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 cockroachdb#42519) | | `md5` | yes (`md5...`) | obsolete, will likely not be implemented |

rolandcrosby added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) A-security labels Nov 15, 2019

knz added the A-sql-pgwire pgwire protocol issues. label Apr 17, 2020

tbg added the A-multitenancy Related to multi-tenancy label Apr 22, 2020

knz mentioned this issue Jun 29, 2020

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

Closed

knz mentioned this issue Jul 20, 2020

meta-issue: improvements to password authentication #51601

Closed

joshimhoff added the O-sre For issues SRE opened or otherwise cares about tracking. label Sep 21, 2020

ajwerner mentioned this issue Apr 7, 2021

pgwire: verifying user passwords is too aggressive #36160

Closed

rafiss added the O-postmortem Originated from a Postmortem action item. label May 12, 2021

lunevalex added the N-followup Needs followup. label Jun 9, 2021

jlinder added the T-server-and-security DB Server & Security label Jun 16, 2021

knz added the X-server-triaged-202105 label Jun 28, 2021

mberhault self-assigned this Jul 16, 2021

knz added A-authentication Pertains to authn subsystems A-cc-enablement Pertains to current CC production issues or short-term projects labels Jul 29, 2021

knz mentioned this issue Dec 1, 2021

pgwire: path to supporting the md5 password authentication #73337

Open

jeffswenson mentioned this issue Dec 15, 2021

release-21.2: sql: support client-provided password hashes #73855

Merged

This was referenced Dec 28, 2021

security: implement SCRAM channel binding #74300

Open

sql,server: support SCRAM authentication for SQL sessions #74301

Merged

jlinder unassigned piyush-singh Jan 6, 2022

This comment has been minimized.

Sign in to view

This was referenced Jan 11, 2022

changefeedccl: support SCRAM-SHA-256 and SCRAM-SHA-512 sasl authentication mechanisms #55788

Closed

ccl: add support for additional sasl mechanisms (SCRAM-SHA-256 and SCRAM-SHA-512) #60150

Merged

knz mentioned this issue Jan 14, 2022

pgwire: support the SCRAM authentication flow (scram 7/10) #74847

Merged

craig bot closed this as completed in 03556f9 Jan 21, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

security: support SCRAM-SHA-256 authentication method #42519

security: support SCRAM-SHA-256 authentication method #42519

rolandcrosby commented Nov 15, 2019 •

edited by exalate-issue-sync bot

Loading

ajwerner commented Nov 16, 2019

knz commented Apr 17, 2020

knz commented Jul 20, 2020

aaron-crl commented Jul 31, 2020

joshimhoff commented Nov 3, 2020

thtruo commented Nov 3, 2020

joshimhoff commented Nov 3, 2020 •

edited

Loading

bdarnell commented Nov 4, 2020

This comment has been minimized.

security: support SCRAM-SHA-256 authentication method #42519

security: support SCRAM-SHA-256 authentication method #42519

Comments

rolandcrosby commented Nov 15, 2019 • edited by exalate-issue-sync bot Loading

Summary

Protocol summary

Special note about SCRAM for SQL

Special note about leaking information

References

Motivation

ajwerner commented Nov 16, 2019

knz commented Apr 17, 2020

knz commented Jul 20, 2020

aaron-crl commented Jul 31, 2020

joshimhoff commented Nov 3, 2020

thtruo commented Nov 3, 2020

joshimhoff commented Nov 3, 2020 • edited Loading

bdarnell commented Nov 4, 2020

This comment has been minimized.

rolandcrosby commented Nov 15, 2019 •

edited by exalate-issue-sync bot

Loading

joshimhoff commented Nov 3, 2020 •

edited

Loading