There are 3 basic rules for keeping user credentials secure:
- NEVER store passwords as plain text.
- ALWAYS use a random salt when encrypting passwords.
- DO NOT roll your own crypto.
Lucky for us, the pgcrypto module in PostgreSQL makes it very easy to follow these rules. Let us take a look at an example.
First, we need to enable pgcrypto:
CREATE EXTENSION pgcrypto;
Then, we can create a table for storing user credentials:
CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL UNIQUE, password TEXT NOT NULL );
When creating a new user, we can use the
crypt function to encrypt the password.
INSERT INTO users (email, password) VALUES ( 'firstname.lastname@example.org', crypt('johnspassword', gen_salt('bf')) );
crypt function accepts two arguments:
- The password to encrypt
- The salt to use when encrypting
We should always use the
gen_salt function, to let PostgreSQL generate a random salt for us. I prefer using the blowfish algorithm (
gen_salt, but here is a list of the algorithms you can use:
To authenticate a user, we use
crypt again, but this time we pass these arguments:
- The submitted password
- The encrypted password we already have in the database
If the password matches,
crypt will return the same value as the one we already have in the database.
SELECT id FROM users WHERE email = 'email@example.com' AND password = crypt('johnspassword', password); id ---- 1 (1 row) SELECT id FROM users WHERE email = 'firstname.lastname@example.org' AND password = crypt('wrongpassword', password); id ---- (0 rows)