On 5th of April 2025, Álvaro Herrera committed patch:
Add modern SHA-2 based password hashes to pgcrypto. This adapts the publicly available reference implementation on https://www.akkadia.org/drepper/SHA-crypt.txt and adds the new hash algorithms sha256crypt and sha512crypt to crypt() and gen_salt() respectively. Author: Bernd Helmle <mailings@oopsware.de> Reviewed-by: Japin Li <japinli@hotmail.com> Discussion: https://postgr.es/m/c763235a2757e2f5f9e3e27268b9028349cef659.camel@oopsware.de
In pgcrypto extension, there is pair of functions that are used to generate password hashes in a standardized format, compatible with unix(-ish) crypt tool. The functions are:
- crypt( password, salt ) – one to generate hashed password from clear text, and generated salt
- gen_salt( algorithm, optional-iteration-count ) – generates the salt to be used in crypt()
Important note here is that crypt will hash using algorithm that is being selected by specific salt.
This is because salt values, while they have random information, they have specific format which tells which algorithm it is:
=$ SELECT a AS algorithm, string_agg( gen_salt( a ), E'\n' ) AS sample_hashes FROM unnest( '{des,xdes,bf,md5}'::text[] ) AS a, generate_series( 1, 5 ) AS i GROUP BY a; algorithm │ sample_hashes ───────────┼─────────────────────────────── md5 │ $1$1L8PNF/i ↵ │ $1$.cY0dbiC ↵ │ $1$/1HXfgfS ↵ │ $1$iZzKBoaq ↵ │ $1$lHQIzuOl xdes │ _J9..ckAr ↵ │ _J9..4yrw ↵ │ _J9..jQoD ↵ │ _J9..B/U8 ↵ │ _J9..N3RA bf │ $2a$06$N2xxVZr//Zw/kdrmmb494u↵ │ $2a$06$3YioMMnYRfe2Sns9EWnsQe↵ │ $2a$06$wi3FDo2MXzq2OyHDVIVxMO↵ │ $2a$06$oEqpW0epwCLJ.ib2mRoQne↵ │ $2a$06$RKu0LCc6ZNktlM4b2EpsX. des │ mc ↵ │ bQ ↵ │ Sy ↵ │ .j ↵ │ dc (4 rows)
After passing this salt to crypt(), we get actual hashed password:
=$ select crypt('my secret password', '$1$lHQIzuOl'); crypt ──────────────────────────────────── $1$lHQIzuOl$ksS1zHM2U6X7jKtixBdkB1 (1 row)
Now, in Pg 18, thanks to this patch, we got support to two new hashing algorithms:
- sha256crypt
- sha512crypt
Salt values generated with these algorithms look like this:
=$ select gen_salt('sha256crypt'); gen_salt ───────────────────────────────── $5$rounds=5000$EMd8zclBrUz28RcS (1 row) =$ select gen_salt('sha512crypt'); gen_salt ───────────────────────────────── $6$rounds=5000$QM6XvD.rPA.4ZS9f (1 row)
Interestingly we can see that these are so called “adaptive" hashes, which means that we can change number of rounds (iterations), by providing 2nd argument to gen_salt:
=$ select gen_salt('sha512crypt', 5000000); gen_salt ──────────────────────────────────── $6$rounds=5000000$bZaINfQKHupXekYv (1 row)
But don't make it too high, as higher numbers of iterations make hashing more expensive (in terms of cpu usage/time):
=$ select crypt('my secret password', gen_salt('sha512crypt') ); crypt ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── $6$rounds=5000$hCNnmqehALlEU3v6$B4uYtCl2.SI/PubfDX1GTkxknai3eKvJP728FCRUswzxMoZtoj579q/wzGpFxWfbaehrDHaw13VAGYb3lQbQQ1 (1 row) Time: 4.027 ms =$ select crypt('my secret password', gen_salt('sha512crypt', 50000) ); crypt ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── $6$rounds=50000$CQSAl1AcL.wLatVm$37LDXaPH1j1Py4ozACGIAl0B73Mbbi3MkcwLChhqMMqKfX/Orl8N1V1yp.X0f7txHLgW2lUGEp/rnbriUYwUI/ (1 row) Time: 24.426 ms =$ select crypt('my secret password', gen_salt('sha512crypt', 500000) ); crypt ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── $6$rounds=500000$cJDpAsN2hi9IOmYo$kZbZNf73emCwVvxcG4k4O77J0WkIIexs46SX9hOglq/imYAsy.uZb4Ug20mqvbyUFs/VHxbUzl78Vhgx5Iqch1 (1 row) Time: 194.879 ms =$ select crypt('my secret password', gen_salt('sha512crypt', 5000000) ); crypt ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── $6$rounds=5000000$5.pKEhVF4TRmzl/Z$yveZMgVMgRUOzWKB/UhQz9noeFDBreSu7sNcUMRqZjzBfp/dLollPhE9S4Fs3ElhVPZ.juc.2P2xk68VkJYQb. (1 row) Time: 1912.942 ms (00:01.913)
While there aren't that many situations where we'd want to hash passwords in database, it is definitely good thing that now we can use more modern (and more secure) hashes in such cases.
Thanks to everyone involved 🙂