[2006] Coding salted password hash with PostgreSQL

Important Note: These instructions were written in 2006 when md5 was available by default in PostgreSQL and stronger hash functions required some installation work. Today there is no reason to use a weak hash function like md5 since using strong hash functions in PostgreSQL today is as straightforward as using md5 was in 2006. See the documentation on the pgcrypto module.

It took me 1-2 hours including testing to make sure ourdoings.com wouldn't suffer reddit's recent embarrassment or slashdot's not so recent embarrassment. This was installed with zero downtime. Here's how I did it:

1. The salted hash database setup

ALTER TABLE person ADD COLUMN salt varchar(4);
ALTER TABLE person ADD COLUMN salted_md5 char(32);

ALTER TABLE person ALTER COLUMN salt SET DEFAULT substring(cast(random() as varchar) from 3 for 4);

update person 
set salt=substring(md5(email) from 1 for 4);

update person
set salted_md5=md5(password||salt);

Note how nice a relational database is for this kind of change. The salt column gets a random value anywhere a person is created. I don't have to change the registration process, the "invite" feature, or the automatic person creation from PayPal to insert a salt. All I have to change is the code that references the password column.

2. References to the password column.

This was the longest part of the process because I was trying very hard to be sure I didn't leave anything out. In the end I found I needn't have tried so hard. All I had to do was change password=foo everywhere to salted_md5=md5(foo||salt), and anywhere that inserted foo into the password column should insert md5(foo||salt) into the salted_md5 column instead

3. The final SQL

update person
set salted_md5=md5(password||salt);

ALTER TABLE person DROP COLUMN password;

Just in case someone set a password in the few seconds between me running the initial SQL and copying the new code in, I did a second update of the salted_md5 column. Then, knowing I had changed all code referencing the password column, I dropped it. No more plaintext passwords in backups!

Of course I ran everything in my test environment first and made sure I could register new users, log in with passwords, and change passwords. It went off without a hitch.

If I knew how easy it was I would have done it a lot earlier. Hopefully this page will inspire other sites to get it together. If you aren't convinced, maybe your thoughts mirror ragica's comment:

I gave up on storing encrypted passwords long ago (for web applications). Too many headaches and idiot users who get annoyed by password resets, and want their password emailed to them. I trust the users who know better to look after themselves and not reuse passwords... and the rest have absolutely no concept of hashed passwords anyhow, so what does it matter to them? All they care about is that they can get their forgotten password emailed back to themselves quickly and easily every week when they forget it.

Regarding those users I ask you, why give them a password at all? Why not just email them a one-time token to log in whenever they use your site? It does get annoying having to create scores of password-protected accounts for different sites. Why force people who rarely use your site to keep a password for it?

That's what I've done with ourdoings.com. People initially log in with an emailed link. Any time they want to set a password they can, but if they don't want to set a password they can use one-time login links every time.