Sending out a URL for password reset

Quite some time ago, FreshPorts and The FreeBSD Diary both moved from clear text passwords to password hashes. As such, you are no longer able to recover your password. You had to email me. This is extra work for everyone. Let us automate this.

The first, step, is a table:

create table user_password_reset(
  user_id        int not null,
  date_requested timestamp with time zone default ('now'::text)::timestamp(6) with time zone,
  ip_address     inet not null,
  token          text not null
);

alter table user_password_reset
    add foreign key  (user_id)
       references users (id) on update cascade on delete cascade;

With this, I can record:

• the user requesting the password change
• the date/time it was requested
• the IP address from which the request came
• the token sent to the user which will permit them to change the password

Next, we need a function to populate the token field, I could do this with a default value on the column, but I think this solution better. There is more flexibility with a trigger.

CREATE OR REPLACE FUNCTION user_password_reset_token() RETURNS TRIGGER AS $$
  BEGIN
    NEW.token := ENCODE(DIGEST((random() * NEW.user_id )::text || now(), 'sha256'), 'hex');
    RETURN NEW;
  END
$$ LANGUAGE 'plpgsql';


  DROP TRIGGER user_password_reset_token ON user_password_reset;
CREATE TRIGGER user_password_reset_token
    BEFORE INSERT on user_password_reset
    FOR EACH ROW
    EXECUTE PROCEDURE user_password_reset_token();

The token is created from a random value, multiplied by the user id and appended with the current time. The resulting string is then hashed using sha256 and encoded into hex and stored in the column. At any time, I can change the token scheme by adjusting that function.

An example of what this produces:

$ psql freshports.org
psql (8.4.3)
Type "help" for help.

freshports.org=# select * from user_password_reset ;
 user_id | date_requested | ip_address | token
---------+----------------+------------+-------
(0 rows)

freshports.org=# insert into user_password_reset (user_id, ip_address) values (1, '127.0.0.1') returning token;
                              token
------------------------------------------------------------------
 a781b65b027e447e67b37f45ea6f150fade0b75f89652577953bbdc1784e8e50
(1 row)

INSERT 0 1
freshports.org=# select * from user_password_reset ;
 user_id |        date_requested        | ip_address |                              token
---------+------------------------------+------------+------------------------------------------------------------------
       1 | 2010-09-16 19:51:18.37022+01 | 127.0.0.1  | a781b65b027e447e67b37f45ea6f150fade0b75f89652577953bbdc1784e8e50
(1 row)

freshports.org=# insert into user_password_reset (user_id, ip_address) values (1, '127.0.0.1') returning token;
                              token
------------------------------------------------------------------
 1df39361bd1ac458da7c23a3de64fe0277cb247846dc2a654f5b0123d9069188
(1 row)

INSERT 0 1
freshports.org=# select * from user_password_reset ;
 user_id |        date_requested         | ip_address |                              token
---------+-------------------------------+------------+------------------------------------------------------------------
       1 | 2010-09-16 19:51:18.37022+01  | 127.0.0.1  | a781b65b027e447e67b37f45ea6f150fade0b75f89652577953bbdc1784e8e50
       1 | 2010-09-16 19:51:24.505328+01 | 127.0.0.1  | 1df39361bd1ac458da7c23a3de64fe0277cb247846dc2a654f5b0123d9069188
(2 rows)

freshports.org=#

When the user submits their changed password, the following procedure is used:

CREATE OR REPLACE FUNCTION reset_password_token(text, text) RETURNS int AS $$
DECLARE
 in_password ALIAS for $1;
 in_token    ALIAS for $2;
 RowCount    bigint;

BEGIN
  UPDATE users SET password_hash = crypt( in_password, gen_salt('md5'))
    WHERE id = (SELECT user_id from user_password_reset
                 WHERE token = in_token);
  GET DIAGNOSTICS RowCount = ROW_COUNT;
  IF RowCount = 1 THEN
    DELETE FROM user_password_reset WHERE token = in_token;
  END IF;
  RETURN RowCount;
END
$$ LANGUAGE 'plpgsql';

And for purging old expired tokens:

CREATE OR REPLACE FUNCTION user_password_reset_purge() RETURNS int AS $$
DECLARE
 RowCount int8;

BEGIN
 DELETE FROM user_password_reset WHERE date_requested < now() - interval '2 days';
 GET DIAGNOSTICS RowCount = ROW_COUNT;
 RETURN RowCount;
END
$$ LANGUAGE 'plpgsql';

This was originally published at http://news.freshports.org/2010/09/16/sending-out-a-url-for-password-reset/.