Hashing passwords and UUID in PostgreSQL

One of my first tasks, is to convert my CommunityAssist database into PostGres, and one of the first tests of that is to be able to create hashed passwords for the Person table, although I for this version, I am going to create a separate table just for logins. The password needs a salt to further obfuscate it, and I want each person to have a Unique identifier other than the key that can be concatenated with the password. Ultimately, I want to write stored procedures for entering new people and one to control logging in.

A little research tells me I have to add two extensions to Postgres: a uuid-ossp extension for the UUID and pgcrypto for hashing

First I create the login table and a Login history table. The Person table already exists

Create extension pgcrypto;
CREATE EXTENSION "uuid-ossp";

Create table LoginTable
(
  LoginKey serial primary key,
  PersonKey int references Person(PersonKey),
  PersonIdent uuid DEFAULT uuid_generate_v4 (),
  PersonUserName varchar(50) not null,
  PersonPassword text not null
 
);

Create table LoginHistory
(
  LoginHistoryKey serial primary key,
  LoginKey int references LoginTable(loginKey),
  LoginTimeStamp timestamp default current_timestamp

);

Let's add a record into Person and then do the login. First we will just do the crypt function by itself in the insert.

Insert into Person (PersonLastName, PersonFirstName, PersonEmail, personprimaryphone, personDateAdded)
values('Carol','Linda','LindaCarol@gmail.com','2065551234',current_timestamp);

/*linda's key is 2*/
Insert into LoginTable(PersonUserName, PersonKey, PersonPassword)
values('clinda',2,crypt('CarolPass', gen_salt('bf',8)));

The results are. (I broke the result to keep it on the page)

loginkey PersonKey  PersonUUID                             PersonUserName
2         2    "67e41498-c4ca-4d59-9c92-5c905fffb7bf" "clinda"        

PersonPassword
"$2a$08$3VFMFhs0pc3KjTqEmzZLbu5.liR2wYfW5AetcLS117.cqRVRLn3X2"

Now the trick is to see if we can "log in" using this hashed password. Here is the syntax

Select PersonUserName from LoginTable where PersonUserName='clinda'
and PersonPassword=crypt('CarolPass', PersonPassword);

The result is "clinda". But what if I do a bad password?

Select PersonUserName from LoginTable where PersonUserName='clinda'
and PersonPassword=crypt('karolPass', PersonPassword);

The query results in 0 rows.

So now we have enough to construct a password function of our own. It will embed the encrypt function inside. I am not sure this is really necessary, since the crypt function is not exactly burdensome, but we will do it anyway.

Create function CreatePassword(pass varchar(50))
returns text
As
'Select crypt(pass, gen_salt(''bf'', 8))'
Language sql;

Let's add another record to login using the new function. The first person in the Person table, Lynn Kendle, does not yet have a login. So let's add her.

Insert into LoginTable(PersonUserName, PersonKey, PersonPassword)
values('klynn',1,CreatePassword('LynnPass'));

Select * from LoginTable;

the result is:

LoginKey  PersonKey  PersonUUID                              PersonUserName
3   1      "8cc6e359-12db-43d2-98f6-7a6ccb2ee139" "klynn" 
PersonPassword
"$2a$08$eDP36KhlLNZnub/VPLyPK.Wq8TzO9afit.hgWdIj98qZiAZmefUEa"

The next step is to create a function that validates the login.

Side Note: You might have noticed the UUID is unused. My original thought was to use it for the salt with the hash, but the crypt function makes that unnecessary. It also makes it unnecessary to store the salt in the table, which is a very good thing. I will probably leave the UUID out in the final version of this database.

So here is my function. It is pretty simple but it works ok. If the login validates it returns the PersonKey. If it fails, it returns null.

Create function ValidateLogin(username varchar(50), pass varchar(50))
returns int
as 
'Select PersonKey from LoginTable where PersonUserName =username
and PersonPassword=crypt(pass,PersonPassword)'
Language sql

Now to test it:

select ValidateLogin('clinda', 'CarolPass')

The result is "2"

Now with a bad password:

Select ValidateLogin('clinda','LindaPass')

The result is "null."

The next step, and the next blog, will be to create a stored procedure that will add a new person and use the createPassword function