meta-issue: improvements to password authentication

[knz]

This issue is intended to serve as high-level overview of the strategy for password authn in CockroachDB/CC.

It outlines how the engineering discussions were orienting towards a "blue" strategy with limitations. It also explains how a different "green" strategy was developed recently. It explains why/how we are going to target "green" and de-prioritize "blue".

Background

Over the past two years a number of issues have been raised over seemingly disconnected aspects of pw authn, including but not limited to:

• pgwire: verifying user passwords is too aggressive cockroachdb/cockroach#36160
• security: Generated passwords with lower bcrypt cost cockroachdb/cockroach#40873
• Add limit on number of client connections per node cockroachdb/cockroach#35428
• security: support SCRAM-SHA-256 authentication method cockroachdb/cockroach#42519
• sql: recognize client-supplied hashes in WITH PASSWORD like pg cockroachdb/cockroach#50757
• cli,server: enable multiple client listeners on TCP with non-TLS modes cockroachdb/cockroach#44842
• Secure cluster behind nginx-ingress in k8s cockroachdb/helm-charts#228
• pgwire,ui: rate-limit connection attempts cockroachdb/cockroach#47602
• sql,server: cache password hashes and user info using a leasing protocol cockroachdb/cockroach#58869

This collection of issues bury their lede: what is the high-level problem to solve?

They also point to conflicting directions: if we persist to use bcrypt-based authn, that precludes SCRAM-SHA-256 which is desirable for e.g. compatibility. Which one should we choose?

Objectives: security and compatibility

• There are 3 security objectives:

  A. protect CockroachDB/CC clusters against Denial-of-Service attacks, where a malicious users exhausts resources. This can be achieved today using e.g. many connections performing unsuccessful password authn attempts.

  B. protect CockroachDB/CC clusters against various spoofing attacks, where a malicious user gains access to a SQL session they don't have legitimate access to.

  • There are various attack scenarios we want to address: brute-forcing, dictionaries, MITM, credential transfer, credential extension.
  • All these are currently low risk as long as clients always validate the TLS certs of servers. If they don't they can be MITMed and an attacker can sniff passwords, which are currently transmitted as-is over the TLS encrypted session.
  • It becomes higher risk if we support non-TLS listeners cockroachdb/cockroach#44842 cockroachdb/helm-charts#228.

  C. make it possible for CC to offer services securely to end-users without requiring allow-listing of client IP addresses.

  This objective partially overlaps with objectives A and B above, but restricts the range of allowable solutions.

  We also want to offer some protection against the consequences of a CC node compromise (either server compromise or SQL escalation of privileges). We want that a successful attack of this type does not give the attacker access to other CC clusters from the same user/customer.

• We also have one compatibility objective:

  D. Whatever solution is adopted should enable client authn by existing pg drivers, without introducing non-standard authn algorithms.

Two strategies

Over the past year, there have been two strategies discussed to achieve the goals identified above.

• The Blue strategy:

  • has been discussed on Slack and in-between the lines of various issues linked above.
  • The historical proponent of this strategy was Ben.
  • This strategy has grown and consolidated itself organically through discussions from the CockroachDB community and on-prem users.
  • Analysis of the strategy reveals that it does not cover all the goals identified above.

• The Green strategy:

  • has emerged after careful analysis of CockroachCloud requirements and the "Free tier" CC project, but is also satisfying various demands by on-prem users.
  • The proponents of this strategy are the entire security team.
  • This strategy is recent and stems from first principles.
  • It does cover all the goals identified above.

Blue strategy: narrative

In the gray strategy, we perform some incremental changes to various pieces in CockroachDB and achieve 60% of the security objectives.

• Objective A:

  • The pgwire component in crdb is extended to rate-limit client connections. cockroachdb/cockroach#47602
  • We also implement a (configurable) maximum number of client connections per node. cockroachdb/cockroach#35428 cockroachdb/cockroach#51505
  • We reduce the bcrypt cost for certain users so that each authn attempt is cheaper in CPU time. cockroachdb/cockroach#40873 cockroachdb/cockroach#36160
  • The quality of DoS protection depends on the user account: which password was used and whether cert authn is available.

• Objective B:

  • We implement a minimum password length. User creation must always present the cleartext password to users. cockroachdb/cockroach#51502
  • Eventually, we implement a password "complexity check" inspired from PCI-DSS. We use the apparent password complexity to derive the bcrypt cost (see above).

• Objective C: not covered by strategy

• Objective D: the only authn method remains password, i.e. password as-is transferred over encrypted TLS connections. This is compatible with existing pg clients but does not achieve SCRAM compatibility.

Green strategy: narrative

In the white strategy, we adopt a more principled approach.

• Objective A:

  • we mandate the use of a rate-limiting proxy for authn attempts. This component is external to CockroachDB and can use off-the-shelf software. The rate limiting and DoS protection is independent of the authn method and the user account.
  • we use SCRAM for password authn to move the CPU cost of pw auth to client-side. cockroachdb/cockroach#51599 this also addresses cockroachdb/cockroach#36160

• Objective B: we mandate SCRAM for password authentication. SCRAM offers resistance to cred reuse and injection even when the transport is compromised by MITM. cockroachdb/cockroach#51599 this also addresses cockroachdb/cockroach#36160

• Objective C:

  • we make it possible for CC console to configure user accounts without ever presenting a cleartext password to CockroachDB. cockroachdb/cockroach#51599
  • we make it possible to restrict the permission to create/modify passwords to only the CC console via a new role option CREATELOGIN. cockroachdb/cockroach#50601
  • SCRAM ensures that passwords are never transmitted as-is over the wire, even encrypted. Even a successful MITM attack cannot compromise the credentials.

• Objective D: we support the pg-standard scram-sha-256 password method. cockroachdb/cockroach#51599

Detailed explanation here: https://docs.google.com/document/d/1HOpN_P9fJOIyh-bCvOti6lBNLDS4Ia-2nrJEPGeUnA0/edit#

Summary: aiming for green

Strategy	Blue strategy	Green strategy
Objective A	Partial	Strong
Objective B	Partial	Strong
Objective C	Not covered	Strong
Objective D	OK	OK

So we're going to aim for the "green" strategy and de-prioritize the proposals from the "blue" strategy.

[blathers-crl[bot]]

Hi @knz, I've guessed the C-ategory of your issue and suitably labeled it. Please re-label if inaccurate.

While you're here, please consider adding an A- label to help keep our repository tidy.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[knz]

we've achieved most of the stated goals on this.