Old passwords, new hashes
FreshPorts has stored user passwords as bcrypt hashes since April 2021. Yet most of the hashes in the production database were still MD5-crypt. This post covers why, what I did about it, and two bugs found along the way.
How the hashes get upgraded
Passwords are hashed in the database with pgcrypto. The login query checks the password and, in the same query, tells the website whether the stored hash is out of date:
select *, password_hash not like '$2_$14$%' as insecure_hash from users where lower(name) = lower($1) and password_hash = crypt($2, password_hash)
When insecure_hash is true, login.php rehashes the password the user just typed, with bcrypt cost 14:
UPDATE users SET password_hash = crypt($2, gen_salt('bf', 14)) WHERE lower(name) = lower($1)
That only works when someone types their password. An account which is never used again keeps its old hash forever.
What production looked like
| hash | accounts |
|---|---|
| $1$ (MD5-crypt) | 12,440 |
| $2a$14$ (bcrypt, cost 14) | 3,815 |
I looked at users.lastlogin for those 12,440 MD5 accounts. That column is updated on every page view by a logged-in user, so it records last activity, not just the last time a password was typed.
- 8 were seen in the last year
- 19 in the last 5 years
- 1,484 in the last 10 years
- the rest go back to 2000
The yearly counts drop to 107 in 2021, then to none in 2022. That matches the history. In April 2021 Jethro Nederhof switched FreshPorts to bcrypt. The next day he added the upgrade of insecure hashes on successful login and made the hash settings overridable per host. After 2022 there are only a handful of MD5 accounts each year. Nearly all of these accounts have simply not logged in since bcrypt came along.
Those handful of recent ones had a different cause. The function behind the password reset page still hashed with MD5:
UPDATE users SET password_hash = crypt( in_password, gen_salt('md5'))
So every password reset wrote a fresh MD5 hash, which stayed until that user’s next login.
Why bother
MD5-crypt is fast. If the database ever leaked, those hashes would be cracked quickly, and people reuse passwords. bcrypt at cost 14 takes about 0.8 seconds per attempt on the database server, which is what we want.
The options
- Invalidate the MD5 hashes. Affected users reset their password by email.
- Wrap each MD5 hash in bcrypt, storing bcrypt(md5crypt(password)), and upgrade at login. Users notice nothing, but it needs new login code. Wrapping 12,440 hashes at about 0.8 s each is close to three hours of database CPU.
- Delete accounts inactive for N years. That cannot be undone, and it takes their watch lists with it.
With 99.8% of those accounts idle for more than five years, invalidating them was the simplest option, and the strongest: no hash is left to crack. Watch list notification emails are not affected, because they do not need a login. Anyone who comes back uses “Forgotten your password?”.
The changes
The reset function hashes with bcrypt
reset_password_token() now uses gen_salt('bf', 14), like sign-up and the account settings page already did. This had to go in first. Otherwise every invalidated user who reset their password would get a new MD5 hash.
The login query only calls crypt() on a hash
An invalidated hash is the string !, which no password can match. Before setting it, I tried crypt('secret', '!') on dvl. I expected it to fall back to DES crypt and quietly not match. Instead:
ERROR: invalid salt
With the old query, an invalidated user trying to log in would have got “query failed” instead of “Login Failed!”. The login query now only calls crypt() when the stored value looks like a crypt hash:
select *, password_hash not like '$2_$14$%' as insecure_hash from users where lower(name) = lower($1) and case when password_hash like '$%' then password_hash = crypt($2, password_hash) else false end
The CASE is deliberate. PostgreSQL does not promise to evaluate the parts of an AND in any particular order, but it does evaluate a CASE branch only when its condition holds. EXPLAIN shows the lookup still uses the new lower(name) index.
Keep the old hashes, for now
Invalidating hashes cannot be undone, so the old hashes are copied aside first. My first thought was a new column on users. That would have broken the website. users_record is a composite type with exactly the same columns as users, and user_fetch_by_cookie(), which runs on every page view by a logged-in user, does RETURNING users.* into it. Add a column to users and that function fails for every logged-in visitor.
Instead, the old hashes go into their own table:
CREATE TABLE users_md5_hash_backup (
user_id integer NOT NULL PRIMARY KEY
REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
password_hash text NOT NULL,
invalidated timestamptz NOT NULL DEFAULT now()
);
INSERT INTO users_md5_hash_backup (user_id, password_hash)
SELECT id, password_hash
FROM users
WHERE password_hash LIKE '$1$%';
UPDATE users u
SET password_hash = '!'
FROM users_md5_hash_backup b
WHERE b.user_id = u.id
AND u.password_hash = b.password_hash;
Yes, that function should not return users.*. It should return a record of its own, or name the columns it wants.
The website’s database role has no access to that table. Undoing it restores the old hash only for users who have not reset their password since. The table will be dropped once it is clear nobody wants the old hashes back. Until then, those hashes are as exposed as before, so that should not take long.
Two bugs on the reset page
Reading the reset page turned up two more problems.
- Passwords containing a quote. The page ran pg_escape_string() on the new password and then passed it to pg_query_params(), which does its own quoting. A password containing ' was stored with the quote doubled, and the user could not log in with the password they had chosen. The page now passes the password as typed.
- The token was not HTML-escaped. The reset token from the URL was echoed into a hidden form field as-is. pg_escape_string() does not escape ", so a crafted link could inject HTML into the page. It now goes through htmlentities().
Testing on dvl (a private instance)
On dvl, which runs a copy of production data:
- 12,636 MD5 hashes were copied to the backup table and replaced with !. None are left.
- Logging in to an invalidated account gives “Login Failed!”, not “query failed”.
- A test account went through “Forgotten your password?” with a new password containing '. The stored hash is $2a$14$, the reset token was used up, and the login worked.
Rollout
For each host, the order matters:
- the new reset_password_token(), so resets write bcrypt
- the website release, so the login page handles !
- the invalidation, with its backup table
It goes from dvl to dev, test and stage, then on to production.
These changes are now in production. If you have not logged in since about 2021, your password no longer works; use “Forgotten your password?” on the login page. Your watch lists are still there.
