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:
- 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.
- 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).
- It must be completely impossible for anybody to read
the hash, otherwise they could intercept the
reset process and set a password for themselves.
- Despite requirement 3 just listed, we must somehow
verify the hash when the user presents it.
- 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:
- 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).
- 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:
-- FIRST TABLE
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.
REVOKE ALL ON TABLE users_pwrequests FROM PUBLIC;
GRANT INSERT ON TABLE users_pwrequests FROM PUBLIC;
-- SECOND TABLE
CREATE TABLE users_pwverifies
(
recnum_pwv integer,
user_id character varying(40),
md5 character(32),
member_password character varying(20),
)
REVOKE ALL ON TABLE users_pwverifies FROM PUBLIC;
GRANT INSERT ON TABLE users_pwverifies FROM PUBLIC;
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
$BODY$
DECLARE
NotifyList text = '';
ErrorList text = '';
ErrorCount int = 0;
AnyInt int;
AnyRow RECORD;
AnyChar varchar;
AnyChar2 varchar;
AnyChar3 varchar;
AnyChar4 varchar;
BEGIN
-- 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
,AnyChar3
,AnyChar4);
EXECUTE ' ALTER ROLE ' || new.user_id || ' NOLOGIN ';
END IF; -- 3000 PK/UNIQUE Insert Validation
END; $BODY$
-- The "SECURITY DEFINER" is crucial, it allows
-- the trigger to run as the super-user who
-- created it
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
The second trigger looks like this:
CREATE OR REPLACE FUNCTION users_pwverifies_ins_bef_r_f()
RETURNS trigger AS
$BODY$
DECLARE
NotifyList text = '';
ErrorList text = '';
ErrorCount int = 0;
AnyInt int;
AnyRow RECORD;
AnyChar varchar;
AnyChar2 varchar;
AnyChar3 varchar;
AnyChar4 varchar;
BEGIN
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;';
ELSE
-- 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;
ELSE
RETURN new;
END IF;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
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
(www.postgresql.org)
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.
Conclusion
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.
Syndicated via RSS From: http://database-programmer.blogspot.com/
