We all know that we must salt and hash our passwords before we store them in the database.
Handily, Postgres can do this for you using the
pgcrypto module. That means we don’t need to worry about writing the code to do it ourselves, and we can create and authenticate users using simple SQL statements.
We’ll be using the
psql command line tool for this, but in a real app we’d be doing this in code.
We’ll need somewhere to put our fake users, so let’s create a database:
Now, we need to add the
pgcrypto module ..
.. and create a table to store our users in:
Of course, in a real implementation our user would have an ID, and probably some other attributes, but this will do for testing.
When we add new users to our table, we use the
crypt function. The first parameter is the user’s password. The second is the
This generates the salt for our password, and also tells the
crypt function which hashing algorithm to use.
Here, we use 8 iterations of the Blowfish (
That’s it! If we take a look at the
users table, we can see our user has been added with a hashed password:
Authenticating a user is just a simple
We pass the password the user has entered into the
crypt function, as well as the column containing the hashed password. If the password is correct, the row will be returned.
Let’s try it with the correct password (‘password1’):
Now, let’s try the same thing, but this time we’ll try to find the user using the incorrect password:
Authenticating a user is now a simple as checking whether a user is returned from this
So, you can see that it’s really easy to do hashing and user authentication using just Postgres and pgcrypto - by letting the database do all of the work for us.
Remember that we’re passing the passwords to Postgres in plain text, so we need to make sure that communications between the app and the database are secure. That means we must be connecting locally, or using Postgres’ native SSL support.
If you want to find out more about pgcryto, check out: