How can I hash passwords in postgresql?

Question

I need to hash some passwords with salt on postgresql, and I haven't been able to find any relevant documentation on how to get that done.

So how can I hash passwords (with some salts) in postgresql?

Answer 1

Examples and documentation on: http://www.postgresql.org/docs/8.3/static/pgcrypto.html

UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));

SELECT pswhash = crypt('entered password', pswhash) FROM ... ;

Answer 2

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:

• http://codahale.com/how-to-safely-store-a-password/
• http://www.postgresql.org/docs/9.2/static/pgcrypto.html
• https://github.com/ircmaxell/password_compat

Answer 3

An application should hash its passwords using key derivation function like bcrypt or pbkdf2. Here is more information on secure password storage.

... but sometimes you still need cryptogrpahic functions in a database.

You can use pgcrypto to get access to sha256 which is a member of the sha2 family. Keep in mind sha0,sha1 md4, and md5 are very broken and should never be used for password hashes.

The following is an alright method of hashing passwords:

digest("salt"||"password"||primary_key, "sha256")

The salt should be a large randomly generated value. This salt should be protected, because the hashes cannot be broken until the salt is recovered. If you are storing the salt in the database then it can be obtained along with the password hash using sql injection. Concatenating the primary key is used to prevent 2 people from having the same password hash even if they have the same password. Of course this system could be improved, but this is much better than most systems I have seen.

Generally it is best to do hashing in your application before it hits the database. This is because querys can show up in logs, and if the database server was owned then they could enable logging to get clear text passwords.