Generating Salted Hashes for FreeRADIUS in SQL

freeradius-salted-hash.md

FreeRADIUS supports using salted hashes for user authentication but there is very little material on how to generate these OpenLDAP style hashes.

When using an SQL backend for your user management it really is useful to have an SQL statement that can create these salted hashes for you which is what this snippet provides below:

• insert.sql: adds a new user to your radcheck table
• update.sql: updates the password for an existing user in your radcheck table

Remember to replace the username (bob) and password (hello) in your prepared statements with templated parameters.

N.B. FreeRADIUS for some awful reason (there are no good reasons!) records the users plaintext password by default in radpostauth; I strongly recommend you fix that in your local installation by editing the relevant query in /etc/freeradius/mods-config/sql/main/mysql/queries.conf

N.B. take care that your binary logs (including your replication logs) do not use a mode of operation that stores the users plaintext password on INSERT/UPDATEs; for example do not use statement based binary logs though mixed is okay as UUID() will force row based logging to be utilised

insert.sql

INSERT INTO radcheck(username, attribute, op, value)
SELECT t.username, 'Password-With-Header', ':=', CONCAT('{ssha512}', REPLACE(TO_BASE64(CONCAT(UNHEX(SHA2(CONCAT(t.password, t.salt), 512)), t.salt)), '\n', ''))
FROM (SELECT 'bob' AS username, 'hello' AS password, UNHEX(MD5(UUID())) AS salt) t;

update.sql

UPDATE radcheck rc
JOIN (SELECT 'bob' AS username, 'hello' AS password, UNHEX(MD5(UUID())) AS salt) t ON rc.username = t.username
SET rc.value = CONCAT('{ssha512}', REPLACE(TO_BASE64(CONCAT(UNHEX(SHA2(CONCAT(t.password, t.salt), 512)), t.salt)), '\n', ''))
WHERE rc.attribute = 'Password-With-Header';