How to Upgrade Your PostgreSQL Passwords to SCRAM

 In a lot of PostgreSQL environments, it’s common practice to protect user accounts with a password. Starting with PostgreSQL 10, the way PostgreSQL manages password-based authentication got a major upgrade with the introduction of SCRAM authentication, a well-defined standard that is a significant improvement over the current system in PostgreSQL. What’s better is that almost all PostgreSQL drivers now support this new method of password authentication, which should help drive further adoption of this method.

While it may be easy to take advantage of SCRAM authentication in new PostgreSQL deployments, there are a few steps involved in upgrading your existing systems to utilize this method. This article will briefly explain how SCRAM works in PostgreSQL (to try to encourage you to upgrade!) and then walk you through the steps of how to upgrade your existing PostgreSQL clusters to use SCRAM authentication.

A Very Brief Overview of SCRAM

Prior to PostgreSQL 10, password authentication was available using the md5 method (and well, there was also a plaintext for drivers that did not support md5), where PostgreSQL defined its own unique authentication scheme. Using this authentication method, passwords are stored like this in PostgreSQL:

'md5' || md5(password + username)

While this method works, it does present a few challenges:

1. If you have access to someone's username / password combination, or their PostgreSQL MD5-styled hash, you could log into any PostgreSQL cluster where the user has the same username / password.
2. Even though it is fairly simple for each PostgreSQL driver to implement support for this method, It does not follow any particular standard. Without going into details, know that the "shared secret" between the PostgreSQL client and server is effectively shared over the wire every time the PostgreSQL-style md5 authentication method is used.

The Salted Challenge Response Authentication Mechanism, aka SCRAM, is designed to allow two parties to both verify that they know a shared secret (e.g. a password) without ever sending the secret between each other. To make this less abstract, in PostgreSQL, SCRAM is used so that the PostgreSQL server can verify that the client knows the correct password, and likewise, the client can verify that the server knows the correct password too, all without ever sending the actual password, not even in a hashed format!

What’s better, SCRAM is a defined standard (RFC5802); PostgreSQL implements SCRAM-SHA-256 (RFC7677), with the notable difference in that it uses the SHA-256 hashing function instead of SHA-1.

Going into how SCRAM authentication works is a much longer topic (and one that I definitely like to talk about). The rest of this article explains how to upgrade your current systems to take advantage of SCRAM authentication.

Upgrading to SCRAM Authentication

Step 0: Determine if you can upgrade to SCRAM

There are two key criteria to determine if you can upgrade your password-based authentication systems to use SCRAM:

1. You are running PostgreSQL 10 or above
2. All of the drivers that are used to connect to your PostgreSQL cluster have SCRAM compatibility. The PostgreSQL community has conveniently provided a list of drivers as well as if they support SCRAM

If your system meets both of those criteria, you can begin the process of upgrading to SCRAM!

Step 1: Validate your pg_hba.conf settings

The PostgreSQL pg_hba.conf file determines how your clients can connect to PostgreSQL. If you’ve looked at your pg_hba.conf file, you may have also noticed a line similar to this one:

# TYPE	DATABASE		USER		ADDRESS		METHOD
local	all				all						md5

The above states that any user trying to connect to your PostgreSQL cluster through a local connection (e.g. a UNIX socket) must use the md5 authentication method. For the purposes of upgrading to SCRAM, ensure that your password-based authentication methods are set to md5 - we will change this setting later once all of the passwords are upgraded.

Step 2: Change PostgreSQL's password_encryption method

In your postgresql.conf configuration file there is a setting called password_encryption that determines how passwords should be hashed. At this point, it's likely set to md5. To begin the upgrade process, you need to switch this value to scram-sha-256 i.e.

password_encryption = scram-sha-256

When this is done, you will have to reload your PostgreSQL cluster (a restart is not required).

Step 3: Determine Who Needs to Upgrade

The next step is to determine which of your users need to upgrade their passwords. As you may not be able to set all of their passwords on your own, you may want to reach out to these users to have them upgrade their passwords. To determine who needs to upgrade their passwords to SCRAM, as a privileged user (e.g. a superuser), you can run this SQL:

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

This query looks for users that have the LOGIN privilege (i.e. they can login to your PostgreSQL cluster) and determines if their password still exists in a PostgreSQL-style MD5 hash. If has_upgraded is FALSE, then the user needs to re-hash their password.

Note: there are some extreme edge cases where the above query will register a false positive (e.g. if you've had a plaintext password that starts with SCRAM-SHA-256$) but in all likelihood, the above should work.

Step 4: Upgrade the Password

There are two recommended ways to re-hash the password:

Upgrade Method #1: via \password in psql

Using the command-line interface from psql, you can use the \password command, i.e:

\password

Or if you want to set the password for someone else on your system:

\password username

You will be prompted to enter a new password. This new password will be converted to a SCRAM verifier, and the upgrade for this user will be complete.

Upgrade Method #2: Create your own SCRAM Verifier + ALTER ROLE

The second method is to create your own SCRAM verifier and then user ALTER ROLE username PASSWORD '$SCRAM_VERIFIER'. I've written a little Python script (using Python 3.7) that helps with this:

https://gist.github.com/jkatz/e0a1f52f66fa03b732945f6eb94d9c21

which is also embedded below for convenience:

	"""
	Generate the password hashes / verifiers for use in PostgreSQL
	How to use this:
	pw = EncryptPassword(
	user="username",
	password="securepassword",
	algorithm="scram-sha-256",
	)
	print(pw.encrypt())
	The output of the ``encrypt`` function can be stored in PostgreSQL in the
	password clause, e.g.
	ALTER ROLE username PASSWORD {pw.encrypt()};
	where you safely interpolate it in with a quoted literal, of course :)
	"""
	import base64
	import hashlib
	import hmac
	import secrets
	import stringprep
	import unicodedata
	class EncryptPassword:
	ALGORITHMS = {
	'md5': {
	'encryptor': '_encrypt_md5',
	'digest': hashlib.md5,
	'defaults': {},
	},
	'scram-sha-256': {
	'encryptor': '_encrypt_scram_sha_256',
	'digest': hashlib.sha256,
	'defaults': {
	'salt_length': 16,
	'iterations': 4096,
	},
	}
	}
	# List of characters that are prohibited to be used per PostgreSQL-SASLprep
	SASLPREP_STEP3 = (
	stringprep.in_table_a1, # PostgreSQL treats this as prohibited
	stringprep.in_table_c12,
	stringprep.in_table_c21_c22,
	stringprep.in_table_c3,
	stringprep.in_table_c4,
	stringprep.in_table_c5,
	stringprep.in_table_c6,
	stringprep.in_table_c7,
	stringprep.in_table_c8,
	stringprep.in_table_c9,
	)
	def __init__(self, user, password, algorithm='scram-sha-256', **kwargs):
	self.user = user
	self.password = password
	self.algorithm = algorithm
	self.salt = None
	self.encrypted_password = None
	self.kwargs = kwargs
	def encrypt(self):
	try:
	algorithm = self.ALGORITHMS[self.algorithm]
	except KeyError:
	raise Exception('algorithm "{}" not supported'.format(self.algorithm))
	kwargs = algorithm['defaults'].copy()
	kwargs.update(self.kwargs)
	return getattr(self, algorithm['encryptor'])(algorithm['digest'], **kwargs)
	def _bytes_xor(self, a, b):
	"""XOR two bytestrings together"""
	return bytes(a_i ^ b_i for a_i, b_i in zip(a, b))
	def _encrypt_md5(self, digest, **kwargs):
	self.encrypted_password = b"md5" + digest(
	self.password.encode('utf-8') + self.user.encode('utf-8')).hexdigest().encode('utf-8')
	return self.encrypted_password
	def _encrypt_scram_sha_256(self, digest, **kwargs):
	# requires SASL prep
	# password = SASLprep
	iterations = kwargs['iterations']
	salt_length = kwargs['salt_length']
	salted_password = self._scram_sha_256_generate_salted_password(self.password, salt_length, iterations, digest)
	client_key = hmac.HMAC(salted_password, b"Client Key", digest)
	stored_key = digest(client_key.digest()).digest()
	server_key = hmac.HMAC(salted_password, b"Server Key", digest)
	self.encrypted_password = self.algorithm.upper().encode("utf-8") + b"$" + \
	("{}".format(iterations)).encode("utf-8") + b":" + \
	base64.b64encode(self.salt) + b"$" + \
	base64.b64encode(stored_key) + b":" + base64.b64encode(server_key.digest())
	return self.encrypted_password
	def _normalize_password(self, password):
	"""Normalize the password using PostgreSQL-flavored SASLprep. For reference:
	https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/common/saslprep.c
	using the `pg_saslprep` function
	Implementation borrowed from asyncpg implementation:
	https://github.com/MagicStack/asyncpg/blob/master/asyncpg/protocol/scram.pyx#L263
	"""
	normalized_password = password
	# if the password is an ASCII string or fails to encode as an UTF8
	# string, we can return
	try:
	normalized_password.encode("ascii")
	except UnicodeEncodeError:
	pass
	else:
	return normalized_password
	# Step 1 of SASLPrep: Map. Per the algorithm, we map non-ascii space
	# characters to ASCII spaces (\x20 or \u0020, but we will use ' ') and
	# commonly mapped to nothing characters are removed
	# Table C.1.2 -- non-ASCII spaces
	# Table B.1 -- "Commonly mapped to nothing"
	normalized_password = u"".join(
	[' ' if stringprep.in_table_c12(c) else c
	for c in normalized_password if not stringprep.in_table_b1(c)])
	# If at this point the password is empty, PostgreSQL uses the original
	# password
	if not normalized_password:
	return password
	# Step 2 of SASLPrep: Normalize. Normalize the password using the
	# Unicode normalization algorithm to NFKC form
	normalized_password = unicodedata.normalize('NFKC', normalized_password)
	# If the password is not empty, PostgreSQL uses the original password
	if not normalized_password:
	return password
	# Step 3 of SASLPrep: Prohobited characters. If PostgreSQL detects any
	# of the prohibited characters in SASLPrep, it will use the original
	# password
	# We also include "unassigned code points" in the prohibited character
	# category as PostgreSQL does the same
	for c in normalized_password:
	if any([in_prohibited_table(c) for in_prohibited_table in
	self.SASLPREP_STEP3]):
	return password
	# Step 4 of SASLPrep: Bi-directional characters. PostgreSQL follows the
	# rules for bi-directional characters laid on in RFC3454 Sec. 6 which
	# are:
	# 1. Characters in RFC 3454 Sec 5.8 are prohibited (C.8)
	# 2. If a string contains a RandALCat character, it cannot containy any
	# LCat character
	# 3. If the string contains any RandALCat character, an RandALCat
	# character must be the first and last character of the string
	# RandALCat characters are found in table D.1, whereas LCat are in D.2
	if any([stringprep.in_table_d1(c) for c in normalized_password]):
	# if the first character or the last character are not in D.1,
	# return the original password
	if not (stringprep.in_table_d1(normalized_password[0]) and
	stringprep.in_table_d1(normalized_password[-1])):
	return password
	# if any characters are in D.2, use the original password
	if any([stringprep.in_table_d2(c) for c in normalized_password]):
	return password
	# return the normalized password
	return normalized_password
	def _scram_sha_256_generate_salted_password(self, password, salt_length, iterations, digest):
	"""This follows the "Hi" algorithm specified in RFC5802"""
	# first, need to normalize the password using PostgreSQL-flavored SASLprep
	normalized_password = self._normalize_password(password)
	# convert the password to a binary string - UTF8 is safe for SASL (though there are SASLPrep rules)
	p = normalized_password.encode("utf8")
	# generate a salt
	self.salt = secrets.token_bytes(salt_length)
	# the initial signature is the salt with a terminator of a 32-bit string ending in 1
	ui = hmac.new(p, self.salt + b'\x00\x00\x00\x01', digest)
	# grab the initial digest
	u = ui.digest()
	# for X number of iterations, recompute the HMAC signature against the password
	# and the latest iteration of the hash, and XOR it with the previous version
	for x in range(iterations - 1):
	ui = hmac.new(p, ui.digest(), hashlib.sha256)
	# this is a fancy way of XORing two byte strings together
	u = self._bytes_xor(u, ui.digest())
	return u

view rawencrypt_password.py hosted with ❤ by GitHub

 

If you are able to successfully run the script, you should receive output that looks similar to:

SCRAM-SHA-256$4096:UrxBRgDElbaS4iwfRzn59g==$SErsniXa5gEr03cXhcFPLSM4C/22IKTJ9emThT+wPrM=:rSaLPYfC3eor3cq3f1Zq6Dw2Rl7HwIUHCMP7avpJQak=

This is an example of a SCRAM verifier that PostgreSQL stores and is used during SCRAM authentication. If I want to set this to be used as part of my new password:

ALTER ROLE jkatz
    PASSWORD 'SCRAM-SHA-256$4096:UrxBRgDElbaS4iwfRzn59g==$SErsniXa5gEr03cXhcFPLSM4C/22IKTJ9emThT+wPrM=:rSaLPYfC3eor3cq3f1Zq6Dw2Rl7HwIUHCMP7avpJQak=';

Why Not "ALTER ROLE username PASSWORD 'newpassword';"?

While the above command will re-hash your password, one side effect is that your plaintext password could end up being logged based on your cluster logging settings! As such, it effectively defeats one of the nice advantages of SCRAM, i.e. not having to expose the shared secret between two parties. So please don't use the above method.

Step 5: Update pg_hba.conf to use only "scram-sha-256"

Here's a fun fact: to ease the upgrade, your applications can still perform SCRAM verification even if md5 is chosen as the authentication method! If a user account has already had its password upgraded into a SCRAM verifier, it will use SCRAM authentication even if md5 is the authentication method!

However, once all the passwords are upgraded, you will want to update your pg_hba.conf file and switch all of your entries that use md5 to now use scram-sha-256. First, check that all of your users have been upgraded to use SCRAM, i.e.:

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

returns TRUE for has_upgraded for all of your users.

Then, modify your md5 entires in your pg_hba.conf file to use scram-sha-256 e.g. using my example above:

# TYPE	DATABASE		USER		ADDRESS		METHOD
local	all				all						scram-sha-256

When this is done, you will have to reload your PostgreSQL cluster (a restart is not required).

Congratulations! All of your PostgreSQL user accounts will be upgraded to authenticate using SCRAM!