How can I hash passwords in postgresql?

It’s been a while since I asked this question, and I’m much more familiar with the cryptographic theory now, so here is the more modern approach:

Reasoning

Don’t use md5. Don’t use a single cycle of sha-family quick hashes. Quick hashes help attackers, so you don’t want that.
Use a resource-intensive hash, like bcrypt, instead. Bcrypt is time tested and scales up to be future-proof-able.
Don’t bother rolling your own salt, you might screw up your own security or portability, rely on gen_salt() to generate it’s awesome unique-to-each-use salts on it’s own.
In general, don’t be an idiot, don’t try to write your own homegrown crypto, just use what smart people have provided.

Debian/Ubuntu install packages

sudo apt-get install postgresql   // (of course)
sudo apt-get install postgresql-contrib libpq-dev   // (gets bcrypt, crypt() and gen_salt())
sudo apt-get install php5-pgsql   // (optional if you're using postgresql with php)

Activate crypt() and bcrypt in postgresql in your database

// Create your database first, then:
cd `pg_config --sharedir` // Move to the postgres directory that holds these scripts.
echo "create extension pgcrypto" | psql -d yOuRdATaBaSeNaMe // enable the pgcrypo extension

Use crypt() and gen_salt() in queries

Compare :pass to existing hash with:

select * from accounts where password_hash = crypt(:pass, password_hash);
//(note how the existing hash is used as its own individualized salt)

Create a hash of :password with a great random salt:

insert into accounts (password) values crypt(:password, gen_salt('bf', 8));
//(the 8 is the work factor)

From-in-Php bcrypt hashing is slightly preferrable

There are password_* functions in php 5.5 and above that allow trivially simple password hashing with bcrypt (about time!), and there is a backward compatibility library for versions below that. Generally that hashing falls back to wrapping a linux system call for lower CPU usage anyway, though you may want to ensure it’s installed on your server. See: https://github.com/ircmaxell/password_compat (requires php 5.3.7+)

Be careful of logging

Note that with pg_crypto, the passwords are in plaintext all during the transmission from the browser, to php, to the database. This means they can be logged in plaintext from queries if you’re not careful with your database logs. e.g. having a postgresql slow query log could catch and log the password from a login query in progress.

In Summary

Use php bcrypt if you can, it’ll lessen the time that the password remains unhashed. Try to ensure your linux system has bcrypt installed in it’s crypt() so that is performant. Upgrade to at least php 5.3.7+ is highly recommended as php’s implementation is slightly buggy from php 5.3.0 to 5.3.6.9, and inappropriately falls back to the broken DES without warning in php 5.2.9 and lower.

If you want/need in-postgres hashing, installing bcrypt is the way to go, as the default installed hashes are old and broken (md5, etc).

Here are references for more reading on the topic: