Waiting for PostgreSQL 10 – Support SCRAM-SHA-256 authentication (RFC 5802 and 7677).

On 7th of March 2017, Heikki Linnakangas committed patch:

Support SCRAM-SHA-256 authentication (RFC 5802 and 7677).
 
 
This introduces a new generic SASL authentication method, similar to the
GSS and SSPI methods. The server first tells the client which SASL
authentication mechanism to use, and then the mechanism-specific SASL
messages are exchanged in AuthenticationSASLcontinue and PasswordMessage
messages. Only SCRAM-SHA-256 is supported at the moment, but this allows
adding more SASL mechanisms in the future, without changing the overall
protocol.
 
Support for channel binding, aka SCRAM-SHA-256-PLUS is left for later.
 
The SASLPrep algorithm, for pre-processing the password, is not yet
implemented. That could cause trouble, if you use a password with
non-ASCII characters, and a client library that does implement SASLprep.
That will hopefully be added later.
 
Authorization identities, as specified in the SCRAM-SHA-256 specification,
are ignored. SET SESSION AUTHORIZATION provides more or less the same
functionality, anyway.
 
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.
 
Bumps PG_CONTROL_VERSION, because of the new field in control file.
 
Patch by Michael Paquier and Heikki Linnakangas, reviewed at different
stages by Robert Haas, Stephen Frost, David Steele, Aleksander Alekseev,
and many others.
 
Discussion: https://www.postgresql.org/message-id/CAB7nPqRbR3GmFYdedCAhzukfKrgBLTLtMvENOmPrVWREsZkF8g%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAB7nPqSMXU35g%3DW9X74HVeQp0uvgJxvYOuA4A-A3M%2B0wfEBv-w%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/.6080106@iki.fi

Well, this has happened over a month ago, but I somehow missed it. Now we have some more commits related to this:

  1. Fix comments in SCRAM-SHA-256 patch.
  2. A collection of small fixes for the SCRAM patch.
  3. Fix typo in initdb's SCRAM password processing.
  4. Allow plaintext ‘password' authentication when user has a SCRAM verifier.
  5. Allow SCRAM authentication, when pg_hba.conf says ‘md5'.
  6. Use SASLprep to normalize passwords for SCRAM authentication.
  7. Minor cleanup of backend SCRAM code.
  8. Rename “scram" to “scram-sha-256" in pg_hba.conf and password_encryption.

So, let's review.

Currently PostgreSQL stores password (by default) in hashed way:

$ CREATE USER test;
CREATE USER
 
$ ALTER USER test WITH password 'abba1234';
ALTER USER
 
$ SELECT usename, passwd FROM pg_shadow WHERE usename = 'test';
 usename |               passwd                
---------+-------------------------------------
 test    | md5b9ad53e3f2f85cd793091f661832fd34
(1 ROW)

There are couple of issues:

  • it uses not-so-safe MD5 algorithm, which can be a problem
  • the way it hashes the password makes it generate different hashes for the same password for different users, but the same hash for the same password and the same username (for example, when doing the change on another DB)

This has been brought several times as a problem, and now we have a solution.

To enable scram passwords, we will need to change, in postgresql.conf:

password_encryption = scram-sha-256

and restart PostgreSQL. Afterwards, I can create another user:

$ CREATE USER test2 WITH password 'abba1234';
CREATE ROLE
 
$ CREATE USER test3 WITH password 'abba1234';
CREATE ROLE
 
$ SELECT usename, passwd FROM pg_shadow WHERE usename ~ '^test[23]$';
 usename |                                                                                passwd                                                                                 
---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 test2   | scram-sha-256:jEpSN6AcvbKsRw==:4096:ff2ccc8e61b4b638b28dfb1c99e44df1a63a2c6a53424a6f4c2e448caeaa46cd:86ef4490e67117fca17e3cc7f7b8276ea8777a656cfb2e41e82037e4e98d128b
 test3   | scram-sha-256:A1x/O56i589Puw==:4096:de83debb226eebda74a16ddf66f9c878115f3d4d73653bf863f3e8e9e95d95ff:ba05283f9a3620fa290c5ec916e8729d9e7c0f89f728b1eb0a6850f522a0eebc
(2 ROWS)

and now another user with the same name and the same password will have different hash in pg_shadow:

$ DROP USER test2;
DROP ROLE
 
$ CREATE USER test2 WITH password 'abba1234';
CREATE ROLE
 
$ SELECT usename, passwd FROM pg_shadow WHERE usename = 'test2';
 usename |                                                                                passwd                                                                                 
---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 test2   | scram-sha-256:vKJJkdipOsrasw==:4096:6648b76ebb17fa4e13b1900e62829d2a87ddede160954a7bc596cb688034b2b6:fa1321166eb3259fc8ae0ed73ed5725adb2b170ff784b3047d9318b466d3555b
(1 ROW)

What's important – if you have mix of md5 or scram passwords you have to use ‘md5' in pg_hba.conf, as it's possible to use md5 authentication with scram'ed passwords, but it's not possible to use md5'ed passwords when you use the newer “scram-sha-256" authentication method (in pg_hba.conf).

Anyway – looks great, thanks.

5 thoughts on “Waiting for PostgreSQL 10 – Support SCRAM-SHA-256 authentication (RFC 5802 and 7677).”

  1. I think you meant to say “but it’s not possible to use *md5* passwords when you use the newer …” in the next-to-last paragraph here.

  3. The problem with MD5 in Postgres is not really the way it uses the algorithm, the main weakness is in the fact that one can use the MD5 hash to connect without knowing the password. SCRAM prevents such things.

  4. I think you missed the most awesome feature of scram.
    Storing hashed passwords on server and sending them out plain during authorization greatly mitigates the disaster of password table leakage, but of course this this setup is vulnerable to eavesdropping.
    Challenge-response protocol used in PG prevents eavesdropping but unfortunately at the price of high risk of leaking server passwords. And hashing them doesn’t help much: if attacker knows hashed password he has enough information to succeed with challenge-response auth (that would require working with raw PG protocol, of course, not libpq).
    Scram on the other hand cleverly combines the advantages of the above approaches so that neither password db leakage nor eavesdropping pose much threat (although, it cannot help if those two happen at the same time – i.e. if attacker knows the hash and can eavesdrop; afaik only public key cryptography protects from that kind of attack).
    I should probably mention that mysql has had similar (double SHA) auth scheme for ages, and they implemented new public key methods recently (https://mariadb.org/history-of-mysql-mariadb-authentication-protocols/)

Comments are closed.