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 pgwire: verifying user passwords is too aggressive #36160
• security: Generated passwords with lower bcrypt cost security: Generated passwords with lower bcrypt cost #40873
• Add limit on number of client connections per node Add limit on number of client connections per node #35428
• security: support SCRAM-SHA-256 authentication method security: support SCRAM-SHA-256 authentication method #42519
• sql: recognize client-supplied hashes in WITH PASSWORD like pg sql: recognize client-supplied hashes in WITH PASSWORD like pg #50757
• cli,server: enable multiple client listeners on TCP with non-TLS modes cli,server: enable SQL clients on TCP with non-TLS modes #44842
• Secure cluster behind nginx-ingress in k8s server,ui: infinite HTTP redirect when accessing UI for secure cluster behind nginx-ingress in k8s helm-charts#228
• pgwire,ui: rate-limit connection attempts pgwire,ui: rate-limit connection attempts #47602
• sql,server: cache password hashes and user info using a leasing protocol sql,server: cache password hashes and user info using a leasing protocol #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 cli,server: enable SQL clients on TCP with non-TLS modes #44842 server,ui: infinite HTTP redirect when accessing UI for secure cluster behind nginx-ingress in k8s 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. pgwire,ui: rate-limit connection attempts #47602
  • We also implement a (configurable) maximum number of client connections per node. Add limit on number of client connections per node #35428 server,sql: limit max SQL connections over TCP #51505
  • We reduce the bcrypt cost for certain users so that each authn attempt is cheaper in CPU time. security: Generated passwords with lower bcrypt cost #40873 pgwire: verifying user passwords is too aggressive #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. security,sql: add a setting to constrain the minimum password length #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. docs: new RFC on password modernization #51599 this also addresses pgwire: verifying user passwords is too aggressive #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. docs: new RFC on password modernization #51599 this also addresses pgwire: verifying user passwords is too aggressive #36160

• Objective C:

  • we make it possible for CC console to configure user accounts without ever presenting a cleartext password to CockroachDB. docs: new RFC on password modernization #51599
  • we make it possible to restrict the permission to create/modify passwords to only the CC console via a new role option CREATELOGIN. sql: introduce the new role option CREATELOGIN #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. docs: new RFC on password modernization #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]

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.

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

[knz]

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