Skip to main content

Password storage (for Ducktown Times)

By 7 augustus 2015januari 30th, 2017No Comments

This is the blogpost as promised in the First8 Friday video blog of August 2015. This post presents the idea of storing passwords as safely as possible for the purpose of authentication.

First a word of caution. User management is more work than you might think. It is also done a lot of times over and over again. Role based access? Adding and removing users and accounts? If at all possible, try to avoid the need, by either using LDAP in the enterprise, or public authentication methods like Google, Facebook or Twitter-integration for public sites.

Storing passwords

The requirements demand it, we are asked to do it, so let’s store passwords. In the video, our friend Donald Duck has created a user-table with plaintext passwords. It is surprising how often you still see sites that store passwords in this way. From a user-friendly perspective this is great: you can e-mail the passwords to the users if they have forgotten it, or look it up for them etc. Also, for developement, it’s an easy start. The examples below are based on PostgresQL. As a preparation: create an empty database to play with.

Let’s create Donalds user-table:

CREATE TABLE users ( username text, role text, password_plain text);

And add some users:

INSERT INTO users value ( 'Donald', 'admin', 'Katrien');
INSERT INTO users value ( 'Kartien', 'duck', 'Donald');
INSERT INTO users value ( 'Kwik', 'duckling', 'kwaak');
INSERT INTO users value ( 'Kwek', 'duckling', 'kwaak');
INSERT INTO users value ( 'Kwak', 'admin', 'kwaak');

The result is this table:

 username |   role   | password_plain 
 Donald   | admin    | Katrien
 Kartien  | duck     | Donald
 Kwik     | duckling | kwaak
 Kwek     | duckling | kwaak
 Kwak     | admin    | kwaak

We can easily see the plaintext passwords. We can also see the role every user has, in particular that Kwak is the second admin.

Improve on this: hash the passwords

We were either hacked or we wish to prevent a hack from revealing the passwords instantly. So, we apply safe-looking hashing on the passwords.

Enable the crypto algorithms in PostgresQL (if needed). This applies per database, but has to be done by the database administrator. She has to connect to the database where the extension is needed, while keeping the root role.

postgres=# connect merijnv
You are now connected to database "merijnv" as user "postgres".
merijnv=# CREATE EXTENSION pgcrypto;

Introduce the new column:

ALTER TABLE users ADD COLUMN password_hashed text;

Fill it with data:

UPDATE users SET password_hashed = 
    encode(digest(password_plain, 'md5'), 'base64');

Remove the plain-text column:

ALTER TABLE users DROP COLUMN password_plain;

And inspect the result

SELECT * FROM users;

Hashing is a one-way street: you cannot easily calculate the original from the hash, however there is a catch.
We can still see that Kwik, Kwek and Kwak have the same password. So if an attacker can swindle Kwik to get his password, she can login as Kwak and become an admin!

And there is one other problem with this solution: md5 is designed to be quick. It is designed as an algorithm to check the integrity of large files or .iso-images. Calculating the hash of a short string costs in the order of microseconds. My four year old laptop performs 10M MD5-digest operations in 2.5 seconds, when using Java on a single core. That is 0.25 microsecond per digest. Or, to be even more precise: When performing 20M digest calculations the CPU performed about 26G instructions, That is a bit over 1000 CPU instructions per digest operation.

This speed gives the attacker a few tricks: she can create a list of hashes for every likely password, and then use that as a lookup-table for md5 hashed passwords. John the Ripper uses this kind of tricks to analyse a password table for weak passwords. Attackers know that people will use numbers for letters, so they just create a few variations of every dictionary word.

BCrypt: the solution designed for this problem

In 1999, Niels Provos and David Mazières presented an elegant solution. A hash that was designed to be slow. “Salt bits” are used in initialization, which is an expensive operation and which takes a configurable amount of time. This configuration is stored with the salt and the hash in one text-string. So if over time computers get twice as fast, one can update the strength as soon as users (are forced to) update their password.

So let’s reset to the first table we had, with just the plaintext passwords.


CREATE TABLE users ( username text, role text, password_plain text);

INSERT INTO users values ( 'Donald', 'admin', 'Katrien');
INSERT INTO users values ( 'Kartien', 'duck', 'Donald');
INSERT INTO users values ( 'Kwik', 'duckling', 'kwaak');
INSERT INTO users values ( 'Kwek', 'duckling', 'kwaak');
INSERT INTO users values ( 'Kwak', 'admin', 'kwaak');

And add a column for the bcrypted password:

ALTER TABLE users ADD COLUMN password_bcrypt text;

Then fill that column. Note that this configuration takes quite some time to be performed.

UPDATE users SET password_bcrypt = 
    crypt(password_plain, get_salt('bf', 12));

ALTER TABLE users DROP COLUMN password_plain;

This 12 is a relative large number, the scale is logarithmic: 10 would be four times quicker. It is 2^n rounds of a cryptographic operation to produce the initialization bits from the salt, which are finally combined with the bits from the password to form the hash.

SELECT * FROM users;

 username |   role   |                       password_bcrypt                        
 Donald   | admin    | $2a$12$vm0/CZ7RQN9CjFhwZ46NgOwrHei5CeSMikmUoz4kXRmk.JuOdfWNy
 Kartien  | duck     | $2a$12$pQjU7U7GYA6WjBAqpWoFuuScdPY4oz/mYOMRxyMWMojb7I.kdQV0K
 Kwik     | duckling | $2a$12$dFuVe8yuE5kIlrJJJFIhNeDXu8TU9HIhW40wmq4J6ww3bR4/owNb.
 Kwek     | duckling | $2a$12$Przg.FFPXfquPeiorHYcve.rpdgGiQkrSbSqC5w7TjNgYkZve79vi
 Kwak     | admin    | $2a$12$rxR41vqdFuD9A5RI/jcvAu6CFr5I9qVeSOWtkFZ74TGUCrhrh29YO

The strings that crypt produces contain, amongst other things, the number of rounds. If new passwords are created with 13 as the configuration for the duration, we can still validate these current passwords. Validating is done with a query like this:

SELECT username, role FROM users WHERE username = 'Donald' 
AND password_bcrypt = crypt('Katrien', password_bcrypt);


Try to avoid storing passwords and introducing user management in your application. There are other solutions for that in the enterprise and in the open internet. If you have to store authentication credentials like passwords, try to store them as safe as possible.

With the support of an Open Source (or closed) database we demonstrate that we can use a safe password storage algorithm fairly easily. BCrypt has proven its worth over the last 16 years.

Having the plain text passwords made life easy in this demonstration. In the next blogpost and video we’ll present a migration path from the md5-hashes to the bcrypt hashes, without having plain-text passwords.