Release note (security update): CockroachDB now supports the
SCRAM-SHA-256 authentication method for SQL clients in a way
compatible with PostgreSQL servers and most PostgreSQL-compatible
client drivers.

SCRAM is a standard authentication protocol defined by IETF RFCs
[5802](https://datatracker.ietf.org/doc/html/rfc5802) and
[7677](https://datatracker.ietf.org/doc/html/rfc7677). In contrast to
the cleartext-based mechanism that CockroachDB was previously using,
SCRAM offers:

- computational burden moved to the client: the computational
  cost to compute the authentication hash is borne by the
  client, and thus prevents DoS attacks enabled by forcing the
  server to compute many hashes simultaneously by malicious clients.
- non-replayability: a malicious intermediary cannot reuse
  a password observed in one session to re-gain access later.
- protection against credential stuffing: a malicious
  intermediary cannot reuse a password to gain access to other services.
- credential secrecy: the server never learns the cleartext
  password and cannot impersonate the client to other servers.

As before, the SCRAM credentials are stored on the server in hashed
form, and prevent a malicious attacker from gaining knowledge about
passwords even if they gain access to a copy of the hashes.

To use SCRAM, an operator must take care of the following:

1. the stored credentials for the SQL accounts that want to use SCRAM
   must use the SCRAM password hash format.

   To store SCRAM hashes in CockroachDB, at this time it is necessary
   to pre-compute the SCRAM hash in a SQL client and store it
   pre-hashed using a CREATE/ALTER USER/ROLE WITH PASSWORD statement.

   This was documented in a previous release note already.

   A mechanism to compute the SCRAM hash server-side from a cleartext
   password might be provided at a later date. Note however that such
   a mechanism is generally undesirable: one of the main benefits of
   SCRAM is to remove the need for the server to know the client's
   cleartext password at any time; a SCRAM hash generation server-side
   would defeat this benefit.

   A plan also exists to auto-migrate existing passwords to the new
   format (refer to a later release note).

2. the SCRAM authentication method must be enabled.

   This can be done e.g. explicitly to require SCRAM specifically
   via a HBA configuration via the cluster setting
   `server.host_based_authentication.configuration`.

   For this, two new authentication methods are available:
   `scram-sha-256` `certs-scram-sha-256`. The first one is akin to
   PostgreSQL and requires a SCRAM authentication flow with the
   client. The second one is CockroachDB-specific and allows SQL
   client to authenticate *either* using a TLS client certificate *or*
   a valid SCRAM authentication flow.

   For example, the configuration line `host all all all
   scram-sha-256` will require a SCRAM authentication flow for all
   clients besides the `root` user.

   A plan also exists to automatically opt existing clusters
   into SCRAM (refer to a later release note).

Known limitations:

- HTTP authentication (web UI, HTTP APIs) still uses cleartext
  passwords.

  Security there can be enhanced in two ways:
  - enable and use OIDC authentication for the web UI.
  - use separate user accounts for access to HTTP than those
    used for access to SQL.

- the CockroachDB implementation of SCRAM differs from PostgreSQL in
  two ways:

  - the extended protocol SCRAM-SHA-256-PLUS is not yet supported.
    SCRAM-SHA-256-PLUS adds *channel binding* over TLS, a mechanism that
    offers MITM protection from malicious intermediaries even when these
    have access to well-signed TLS certificates. Without this extension,
    proper MITM protection requires the client to verify the server
    certificate against a known CA and server fingerprint.

    CockroachDB does not yet support SCRAM-SHA-256-PLUS because we
    have observed that support for channel binding is not yet common
    in SQL client drivers besides PostgreSQL's own `libpq` driver.

  - CockroachDB does not yet implement zero-knowledge authentication
    failures like PostgreSQL.  In PostgreSQL, the implementation ensures
    that a SQL client cannot distinguish the causes of an authentication
    failure: whether a password is missing, expired, invalid, or the
    user account does not exist, the SQL client is forced to pay the
    price of the SCRAM handshake and does not learn the exact cause of
    the failure. This ensures that a malicious attacker cannot use the
    type of authentication failure as a mechanism to learn properties of
    a target SQL account.

    This mechanism may be implemented in CockroachDB at a later time.