Hashed Passwords with PostgreSQL's pgcrypto

by on

Users are central to any web application, and when you have users you usually need accounts, and when you have accounts you need password verification and cookies. Almost every web framework in any language has something for storing and comparing passwords and signing cookies to prevent tampering. In this post, we’ll explore how to do password hashing with just PostgreSQL’s pgcrypto.

Part 1: Intro to pgcrytpo and password hashing

To keep track of users’ accounts and verify their identity, we have the following requirements:

  1. A user can register on the site, providing their email address and password
  2. A user can log in to the site, providing their email and password
  3. Password should be stored securely, preventing both an unauthorized login and password discovery

Let’s take this one step at a time, starting with registering a user. Clearly, this will be some kind of INSERT where we provide PostgreSQL with their email and password. But we have to figure out how to set up our users table as well as how to perform the insertion so that the password is properly hashed.

Sidenote: hashed is not the same as encrypted. Encrypted means that the data could be decrypted. This is a feature of an encryption system, and it’s for data we want to be able to recover. Hashing, however, is designed to be one-way. You turn a password into its hash, but you can’t turn a hash back into a password. That means the only way to discover a password from a hash is brute forcing it. Hashing methods designed for passwords are intentionally computationally intensive to make brute forcing really impractical. That’s why, we’re going to use hashing for our password.

PostgreSQL has a module called pgcrypto that provides a variety of cryptographic functions. It can make general hashes, hmac signatures, password hashes, pgp encryption, raw encryption, and a little bit of random data. While you could use pgp encryption or general hashing for passwords, they would not be anywhere near as secure as using the password hashing mechanism. This is for the following reasons:

  1. Any form of encryption can be decrypted, so using pgp encryption means if someone gets our database, they can learn all users passwords quickly
  2. General hashing methods are designed to be fast, not secure. This is for doing stuff like making an ETag from some content. You want to do it quickly so you app is fast. Quick is the enemy of secure when it comes to brute forcing hashes.
  3. Salting: a proper password hash is salted. A salt is some random data combined with the password. Without anything random, everyone who sets their password to 12345 would have the same hash (who would use 12345 anyways? It’s the kind of thing an idiot would have on his luggage!). Attackers use rainbow tables containing precomputed hashes via many hashing functions with common passwords. Then they just look for the hashes that match.

The great thing is, pgcrypto’s has password hashing and salting, using a slow (and configurable) hashing method, and it’s one-way (because it’s a hash). Let’s look at a demo of crypt and gen_salt in action:

CREATE EXTENSION pgcrypto;

/* 1. Hash 12345 with random salt */
# select crypt('12345', gen_salt('bf', 8));
-[ RECORD 1 ]-------------------------------------------------------
crypt | $2a$08$EZNHnalSY.BEFrxijfm7E.ueVYHCGEKA5UPTS0hznAmgL2FN8ESUW

/* 2. Hash 12345 again with random salt */
# select crypt('12345', gen_salt('bf', 8));
-[ RECORD 1 ]-------------------------------------------------------
crypt | $2a$08$znC032zL9ylnNbVoDAlTd.J8gf19Jtts9cHNHpJsd9bYg1FVlhpci

/* 3. Hash 12345 but give it the password from (2) as the salt */
# select crypt('12345',
  '$2a$08$znC032zL9ylnNbVoDAlTd.J8gf19Jtts9cHNHpJsd9bYg1FVlhpci');
-[ RECORD 1 ]-------------------------------------------------------
crypt | $2a$08$znC032zL9ylnNbVoDAlTd.J8gf19Jtts9cHNHpJsd9bYg1FVlhpci

/* 4. Hash 12346 but give it the password from (2) as the salt */
# select crypt('12346',
  '$2a$08$znC032zL9ylnNbVoDAlTd.J8gf19Jtts9cHNHpJsd9bYg1FVlhpci');
-[ RECORD 1 ]-------------------------------------------------------
crypt | $2a$08$znC032zL9ylnNbVoDAlTd.A3lbZhu8OMrWoklAqPB//wtyDdZeGJO

First off, you have to add the extension to your database with CREATE EXTENSION. Next, I am using both the crypt password hashing function and the gen_salt function for generating a salt. crypt takes a password and salt, and gen_salt takes a salting method and arguments for that method. Here, I am using 'bf' which is a Blowfish based cypher, and I give it the argument 8 which tells it to perform 8 iterations. At 8 iterations, the hash is 100,000 times more computationally intensive than an md5. To hash every 8 character alphanumeric password at bf/8 would take at least a thousand years on a nice computer. It’s pretty good :).

Let’s look at (1) above. Here I am asking for a password hash of '12345' at bf/8, and PostgreSQL gives me the hash back. Great, we can use this on our user INSERT.

Next, in (2) I ask for the same thing again. This time, the hash is different! That’s because we’re using a random salt again, so we get a different hash. We can’t use this for our “checking the user’s credentials” because we used the same password but didn’t get a hash match. This is how the salt busts rainbow tables.

In (3) we ask for the hash of the password but we give it the hashed password from (2) as the salt. Now we get the same hash! We can use this in our select, since we have the original hash stored from when the users registered.

In (4) we try the wrong password, but we give it the hash from (2). This gives us a different hash, so that means that if you give the wrong password, it won’t be a match.

For extra credit, look at the hashes from 1, 2, and 4:

crypt | $2a$08$EZNHnalSY.BEFrxijfm7E.ueVYHCGEKA5UPTS0hznAmgL2FN8ESUW
crypt | $2a$08$znC032zL9ylnNbVoDAlTd.J8gf19Jtts9cHNHpJsd9bYg1FVlhpci
crypt | $2a$08$znC032zL9ylnNbVoDAlTd.A3lbZhu8OMrWoklAqPB//wtyDdZeGJO
        mmmmmmmsssssssssssssssssssss.hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh

I added a line below, showing the meaning of each section. The m section is the method. $2a$08$ means use blowfish (that’s the $2a$ part) with 8 iterations (that’s the $08$ part). The s section is the salt and the h section is the hash. Notice that 1 and 2 have the same method, but different salts and hashes. 2 and 4 have the same salt, and different hashes.

pgcrypto stores the method, salt, and hash in the same string so you can store it really easily, and you also know how to compare the data in the future. For example, if you decided to up your iterations to 10 once computers have sped up, you can still hash at 8 for an old password, then store the 10 over top on a match to gradually upgrade passwords.

When you give crypt a previously hashed password as the salt, it simply splits the string up and pulls the salt out for you. Handy!

Part 2: Storing email and password securely

Now that we know how crypt works, it’s time to set up our table and perform some insertions! Here’s our users table declaration:

CREATE TABLE users (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  email text NOT NULL,
  password text NOT NULL
);

It’s a pretty basic table, but we do see something new here from pgcrypto: gen_random_uuid. We’re using uuids for user ids instead of an auto incrementing number. This makes them harder to guess. But that’s not the focus of this post :).

Now, when a user registers with their email and password, we can INSERT them, using the crypt function from before:

INSERT INTO users (email, password) VALUES
  ('nick@example.com', crypt('12345', gen_salt('bf', 8)));

Now let’s take a look:

SELECT * FROM users;
-[ RECORD 1 ]----------------------------------------------------------
id       | a804e6a7-15fe-40b8-83a5-e24214c27dfe
email    | nick@example.com
password | $2a$08$ZhyemYyCwO3MfJFwTjxcm.iM8RIQyO0L.G3ZnHOfISeIcX/GS7oWO

We have our random id, our email, and our hashed password. Nice!

Part 3: Looking up a user on login

When a user comes to our site to sign in, they are going to provide their email and password, and we should find them by their email, then verify their password by hashing it. We can do it like we did from Part 1 where we provided the existing password as the salt. But this time, as part of a SELECT:

SELECT * FROM users WHERE email = lower('nick@example.com') AND
                          password = crypt('12345', password);
-[ RECORD 1 ]----------------------------------------------------------
id       | a804e6a7-15fe-40b8-83a5-e24214c27dfe
email    | nick@example.com
password | $2a$08$ZhyemYyCwO3MfJFwTjxcm.iM8RIQyO0L.G3ZnHOfISeIcX/GS7oWO

There we go! It matches. Now, for a sanity check, let’s try the wrong password:

SELECT * FROM users WHERE email = lower('nick@example.com') AND
                          password = crypt('12346', password);
(No rows)

OK, great! Now let’s try with the wrong salt (like from a rainbow table):

SELECT * FROM users WHERE email = lower('nick@example.com') AND
                          password = crypt('12345', gen_salt('bf', 8));
(No rows)

Awesome, we’ve got it! P.S., you probably want a unique index on lower(email) to keep your query running quickly and prevent duplicate accounts. There’s no reason to index the password, because the email is unique and so we don’t have to scan the password column.

— Nick Gauthier

Frustrated with video conferencing?

MeetSpace has the best audio quality and reliability around, and is built for distributed teams.

Start Your Free Trial