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:

1. it increases overall server security, by ensuring that the server never sees cleartext passwords even during authn verification.

2. 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