Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 10 – Support SCRAM-SHA-256 authenticati...

Datetime:2017-04-19 05:34:00         Topic: PostgreSQL  Encryption and Decryption          Share        Original >>
Here to See The Original Article!!!

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

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;
$ alter user test with password 'abba1234';
$ 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 user test3 with password 'abba1234';
$ 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;
$ create user test2 with password 'abba1234';
$ 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 scram'ed passwords when you use the newer “scram-sha-256" authentication method (in pg_hba.conf).

Anyway – looks great, thanks.