Sunday, September 7, 2008

Advanced Table Design: Secure Password Resets

Most web-based database applications make use of email to allow users to change their passwords. Completing securing this operation can be tricky business, and one of the best ways to do it is to user database server abilities.

Disclaimer 1: Only As Secure as Email

We tend to take it for granted today that password reset systems work through email. We reason that if a user can access an email sent by us then they are who they say they are. Obviously this will not be true if a user's email account has been compromised.

Dealing with the possibility of compromised email accounts is outside the scope of this week's essay. There are other strategies available to reduce that risk, but they will be treated in some future essay.

Disclaimer 2: Only SSL (HTTPS) of Course!

It is not much use giving yourself a super-secure email system if you transmit sensitive information over unencrypted connections. Secure Socket Layers (SSL) should always be used when high security is required. For the end-user this means they are going to a site through HTTPS instead of HTTP.

Password Resets vs. Sending Passwords

On some low-security systems it is acceptable to send a user his password in an email. This approach is very ill-advised in higher security contexts because we have no control over the user's storage of that email. It could end up anywhere, and anybody might read it.

When security requirements are higher, it is better to force the user to reset their password. There are several reasons for this, but the important one here is that we do not want to send the actual password in an email. Therefore we must send a link that sends them to a page where they can provide a new password.

The Requirements

If we spell out the requirements for a secure password reset system, they are at the very least these:

  1. We must generate some hash and send it to the user, this is how she will identify herself so we can let her change her password.
  2. The hash must expire at some point, since we cannot be sure the user will completely purge out the email (or that he even can, depending on the policy of the email host).
  3. It must be completely impossible for anybody to read the hash, otherwise they could intercept the reset process and set a password for themselves.
  4. Despite requirement 3 just listed, we must somehow verify the hash when the user presents it.
  5. We must be able to change the user's password, which is a priveleged operation, even though the user is not even logged in.

It is not actually possible to implement these requirements in application code alone (or perhaps I should say is not possible to do it and meet minimum acceptable risk). There are two problems if you try it:

  1. Requirements 3 and 4 cannot be reconciled. If the application is able to read the hash to verify it, then a vulnerability in the application code could lead to compromise. If we implement in application code we have the burden of ensuring practically zero vulnerabilities, while if we go server-side we have no such burden (at least for this feature).
  2. Requirement five requires the application code to connect at a very high privelege level, which could lead to completely unrelated vulnerabilities.

Implementing In The Database

The system I will now describe meets all 5 of the requirements listed above while never requiring a priveleged connection to the database. The feature is implemented in an isolated system that cannot touch other systems, and it has no burden to be particularly careful in writing the application code.

Since a picture is worth a thousand words, here it is:

The process begins at the top left. The user (Yellow circle) clicks on some "Forgot Password" link and provides an email or account id. This goes to web server which generates an INSERT to the insert-only table of hashes. This insert contains only the user's id, nothing else is needed. There is a trigger on the table that fires on the INSERT. This trigger generates the hash and sends the email to the user.

The salient features here are that the table is insert-only, which is explained below, and that the trigger operates at super-user level, which is also explained below.

Once the user receives the link and clicks on it, our process goes over to the right. The user lands on a page and provides a new password (and probably of course must type it in twice). The web server does basic things like making sure the two values match, that the password is long enough, and like that, and then generates an INSERT into a second table. The insert contains the email or account ID, the hash, and the desired new password.

The magic begins on the INSERT into the second table. An INSERT trigger running at superuser level is allowed to look at the first table and verify the hash and its expiration. If these match, it sets the user's password.

Simple, really, IMHO.

Feature 1: Insert Only Tables

This system depends on creating tables that any unpriveleged user can insert into, but which nobody can SELECT from or UPDATE to or DELETE from.

This may sound like a joke: "Insert Only Table", something like "Write only memory". But the idea is very simple, if nobody can SELECT from the table then nobody can discover active hashes. If nobody can UPDATE the table then nobody can forge hashes. Finally, if nobody can DELETE from the table then nobody can cause mischief.

The code for the tables looks like this:

CREATE TABLE users_pwrequests
  recnum_pwr integer,
  user_id character varying(40),
  md5 character(32),
  ts_ins timestamp without time zone,
-- NOTE! This syntax is PostgreSQL, there may be
-- slight variations on other platforms.

CREATE TABLE users_pwverifies
  recnum_pwv integer,
  user_id character varying(40),
  md5 character(32),
  member_password character varying(20),

Feature 2: Trigger Security Priveleges

It is possible on most servers to severely limit a user's allowed actions on a table, but then to provide trigger code that fires on those actions and executes a super-user level. Today's technique depends upon this ability. Trigger code operating at superuser level can look at the insert-only table to verify a hash, and it can also set the user's password.

This basic ability is what makes triggers so amazing and cool for implementing business logic (see also Triggers and Encapsulation), because there is no way for a user to directly invoke a trigger for his own nefarious purposes, and there is no way for a cracker to avoid the firing of the trigger if he performs an action on a table. Triggers are truly the most powerful example of encapsulation of data and code that is available to today's programmer.

The first trigger looks something like this ( this is PostgreSQL code, your server will likely require variations) (I have also stripped it down for brevity, it may not work exactly without modification):

CREATE OR REPLACE FUNCTION users_pwrequests_ins_bef_r_f()
  RETURNS trigger AS
    NotifyList text = '';
    ErrorList text = '';
    ErrorCount int = 0;
    AnyInt int;
    AnyRow RECORD;
    AnyChar varchar;
    AnyChar2 varchar;
    AnyChar3 varchar;
    AnyChar4 varchar;
    -- necessary for an old glitch in pg security
    SET search_path TO public;

    -- Only execute if the user's id is valid
    SELECT INTO AnyInt Count(*)
           FROM users WHERE user_id = new.user_id;
    IF AnyInt > 0 THEN 
       SELECT INTO AnyChar email
              FROM users WHERE user_id = new.user_id;
       -- This lets you put the email itself into 
       -- a table for admin control
       SELECT INTO AnyChar2 variable_value
              FROM variables
             WHERE variable = 'PW_EMAILCONTENT';
       -- Also the server is stored in a table
       SELECT INTO AnyChar3 variable_value
              FROM variables
             WHERE variable = 'SMTP_SERVER';
       -- This becomes the email FROM Address
       SELECT INTO AnyChar4 variable_value
              FROM variables
             WHERE variable = 'EMAIL_FROM';
       IF AnyChar4 IS NULL THEN AnyChar4 = ''; END IF;
       -- Very important! Set the md5 hash!
       new.md5 := md5(now()::varchar);
       -- Call out to a stored procedure that sends emails
       PERFORM pwmail(AnyChar
          ,'Password Reset Request'
          ,AnyChar2 || new.md5
       EXECUTE ' ALTER ROLE ' || new.user_id || ' NOLOGIN ';
    END IF;    -- 3000 PK/UNIQUE Insert Validation

  -- The "SECURITY DEFINER" is crucial, it allows 
  -- the trigger to run as the super-user who 
  -- created it

The second trigger looks like this:

CREATE OR REPLACE FUNCTION users_pwverifies_ins_bef_r_f()
  RETURNS trigger AS
    NotifyList text = '';
    ErrorList text = '';
    ErrorCount int = 0;
    AnyInt int;
    AnyRow RECORD;
    AnyChar varchar;
    AnyChar2 varchar;
    AnyChar3 varchar;
    AnyChar4 varchar;
    SET search_path TO public;

    -- Read the first table to see if the 
    -- link is valid and has not expired
    SELECT INTO AnyInt Count(*)
           FROM users_pwrequests
          WHERE user_id = new.user_id
            AND md5     = new.md5
            AND age(now(),ts_ins) < '20  min';         
    IF AnyInt = 0 THEN                                
        ErrorCount = ErrorCount + 1; 
        ErrorList  = ErrorList || 'user_id,9005,Invalid Link;';
       -- Magic!  The user's password is set
        EXECUTE 'ALTER ROLE ' ||  new.user_id 
            || ' LOGIN PASSWORD ' 
            || quote_literal(new.member_password);
        -- Very important!  Now that we have set it,
        -- erase it so it is not saved to the table
        new.member_password := '';
    END IF;    -- 3000 PK/UNIQUE Insert Validation

    IF ErrorCount > 0 THEN
        RAISE EXCEPTION '%',ErrorList;
        RETURN null;
        RETURN new;
    END IF;

Feature 3: Sending Email From Database Server

The technique present above requires that your database server be able to send emails. This is not always possible. Postgresql ( can do it, and I have to believe the other big guys can as well, but I have not tried it yet personally.

To send emails through a PostgreSQL server, you must install Perl as an untrusted language, and then install the Perl MAIL package. If anybody wants to know more about that then please leave a comment and I will expand the essay to include that.

Feature 4: The Empty Column

There is one more note that should be made. To use this system, you must tell the server the user's desired new password. To do that, you must actually make it part of the INSERT command and therefore you must have a column for it in the 2nd read-only table. However, you certainly do not want to actually save it, so you have the trigger set the password first and then blank out the value, so the final row saved to the table does not actually contain anything. This is noted in the code comments on the second trigger, which is included above.


The technique presented today makes full use of database server abilities to create a password reset system that is highly resistant to forgery, interception, and evil-admin meddling. It makes use of a combination of restrictive table security, priveleged trigger code, and sending emails from the database server.


Justin said...

A nifty system. The only wart is that an email must be sent from the database server. Is there any way around that?

KenDowns said...

Justin: that's not a wart, that's a feature :)

If you send the email from app code, then the app can read the hash, which is more vulnerable.

perhaps you could code a sproc that makes the insert and returns the hash?

Anonymous said...

HUGE disclaimer #1
Since the email protocol in unencrypted per default, #1 is ALWAYS insecure, as there will be potentially hundreds of people who are able to read your email as it is sent between servers. Still using the email protocol for this without encryption is a huge blunder in todays internet.

Erik Eckhardt said...

Are you sure you want to disallow logins from the user? Perhaps in the meantime he'll remember his password. Unless I'm understanding your code incorrectly.

KenDowns said...

@esquared: Disabling user logins after a reset request is an option.

It's purpose is to alert users that somebody may be trying to gain access to their account. If a user's account has become locked out (and they have not checked their email), it is supposed to raise a flag.

But it is not strictly required by the technique, it is just an additional security step.

Seguridad Informática said...

We have this requirement.

The client software reads from an encrypted file to retrieve his PIN. If he misses the PIN three times, he is locked out and he is presented with a screen showing a hash, and a license number. With these two, he contacts the host system administrator by voice or email with the viewable hash. (within 20 minutes). The system admin asks a few questions (Mother's name, etc), and if he is satisfied, pastes client hash and license numbers into their respective fields, clicks on a button, and a hash validation occurs by generating a new hash. If it is ok, one answer goes back to the client. The client posts it into the field reserved for this, the client software verifies the hash from the system administrator and if accepted, forces the client software into a PIN change mode.

The client choses his own PIN. He does not get one from host administrator. All action must take place within 20 minutes, otherwise a new hash is generated.

Any ideas how to do this?

Curvve said...

Today's technique depends upon this ability. Trigger code operating at superuser level can look at the insert-only table to verify a hash, and it can also set the user's password.

Secure Web Design

Chui Tey said...

You can bcrypt the hash and then store that into a read write table.

Anonymous said...

Fine method of telling, and enjoyable article to acquire factual statements. what will full coverage insurance pay for

Unknown said...

I'd be trampled if all sites gave articles like these awesome articles. get cash now

Anonymous said...

Hi to everybody, here everyone is sharing such knowledge, so it’s fastidious to see this site, and I used to visit this blog day loan

Unknown said...

Thanks for sharing a very helpful and very informative blog
Excellent article and a truly amazing blog About Us Page

harleenamna said...

The posted article is really helpful for me to know more about database programs.

web development company in hyderabad

Yasar Ali said...

Thanks for the WPX Hosting Coupon

tanuhealthdoctor said...

The content looks real with valid information. Good Work this website

periyannan said...

Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
artificial intelligence internship | best final year projects for cse | internship certificate online | internship for mba finance students | internship meaning in tamil