Authenticating Against a Database Table

The design of twisted.cred makes it easy to swap out various parts of your authentication system. This example demonstrates how to replace the dictionaries of usernames and passwords used in Example 6-1 with a SQL database table.

6.2.1. How Do I Do That?

To check usernames and passwords against a database table , write a new class implementing checkers.ICredentialsChecker. To create avatars based on database records, write a new class implementing portal.IRealm. Example 6-2 demonstrates how to modify the server from Example 6-1 to use a MySQL database for authentication.

Example 6-2. dbcred.py

from twisted.enterprise import adbapi, util as dbutil

from twisted.cred import credentials, portal, checkers, error as credError

from twisted.internet import reactor, defer

from zope.interface import implements

import simplecred



class DbPasswordChecker(object):

 implements(checkers.ICredentialsChecker)

 credentialInterfaces = (credentials.IUsernamePassword,

 credentials.IUsernameHashedPassword)



 def _ _init_ _(self, dbconn):

 self.dbconn = dbconn



 def requestAvatarId(self, credentials):

 query = "select userid, password from user where username = %s" % (

 dbutil.quote(credentials.username, "char"))

 return self.dbconn.runQuery(query).addCallback(

 self._gotQueryResults, credentials)



 def _gotQueryResults(self, rows, userCredentials):

 if rows:

 userid, password = rows[0]

 return defer.maybeDeferred(

 userCredentials.checkPassword, password).addCallback(

 self._checkedPassword, userid)

 else:

 raise credError.UnauthorizedLogin, "No such user"



 def _checkedPassword(self, matched, userid):

 if matched:

 return userid

 else:

 raise credError.UnauthorizedLogin("Bad password")



class DbRealm:

 implements(portal.IRealm)



 def _ _init_ _(self, dbconn):

 self.dbconn = dbconn



 def requestAvatar(self, avatarId, mind, *interfaces):

 if simplecred.INamedUserAvatar in interfaces:

 userQuery = """

 select username, firstname, lastname

 from user where userid = %s

 """ % dbutil.quote(avatarId, "int")

 return self.dbconn.runQuery(userQuery).addCallback(

 self._gotQueryResults)

 else:

 raise KeyError("None of the requested interfaces is supported")



 def _gotQueryResults(self, rows):

 username, firstname, lastname = rows[0]

 fullname = "%s %s" % (firstname, lastname)

 return (simplecred.INamedUserAvatar,

 simplecred.NamedUserAvatar(username, fullname),



 lambda: None) # null logout function



DB_DRIVER = "MySQLdb"

DB_ARGS = {

 'db': 'your_db',

 'user': 'your_db_username',

 'passwd': 'your_db_password',

 }



if __name__ == "_ _main_ _":

 connection = adbapi.ConnectionPool(DB_DRIVER, **DB_ARGS)

 p = portal.Portal(DbRealm(connection))

 p.registerChecker(DbPasswordChecker(connection))

 factory = simplecred.LoginTestFactory(p)

 reactor.listenTCP(2323, factory)

 reactor.run( )

Before you run dbcred.py, create a MySQL database table called user, and insert some records for testing:

 CREATE TABLE user (

 userid int NOT NULL PRIMARY KEY,

 username varchar(20) NOT NULL,

 password varchar(50) NOT NULL,

 firstname varchar(100),

 lastname varchar(100),

 );

 INSERT INTO user VALUES (1, 'admin', 'aaa', 'Admin', 'User');

 INSERT INTO user VALUES (2, 'test1', 'bbb', 'Joe', 'Smith');

 INSERT INTO user VALUES (3, 'test2', 'ccc', 'Bob', 'King');

dbcred.py works exactly the same way as simplecred.py from Example 6-1. It has a new authentication backend, but from the user's, perspective nothing has changed:

 $ telnet localhost 2323

 Trying 127.0.0.1...

 Connected to sparky.

 Escape character is '^]'.

 User Name: admin

 Password: aaa

 

 Welcome Admin User!

 Connection closed by foreign host.



 $ telnet localhost 2323

 Trying 127.0.0.1...

 Connected to sparky.

 Escape character is '^]'.

 User Name: admin

 Password: 123

 

 Denied: Bad password.

 Connection closed by foreign host.



 $ telnet localhost 2323

 Trying 127.0.0.1...

 Connected to sparky.

 Escape character is '^]'.

 User Name: someotherguy

 Password: pass

 

 Denied: No such user.

 Connection closed by foreign host.

 

6.2.2. How Does That Work?

The class DbPasswordChecker in Example 6-2 checks usernames and passwords against a database table. The requestAvatarId function takes the provided username and runs a database query to look for a matching record. The _gotQueryResults function handles the results of the query. If there were no records, it raises a twisted.cred.error.UnauthorizedLogin exception. (Because _gotQueryResults is running as the callback handler of a Deferred that was returned from requestAvatarId, this exception will be caught by that Deferred and eventually passed back to the error handler for Portal.login.)

If there was a matching record in the database, _gotQueryResults checks to see whether the password supplied by the user matches the database password. It does this in an indirect way, calling userCredentials.checkPassword, a method supplied by the credentials.IUsernamePassword interface. checkPassword can return either a Boolean value or a Deferred; wrapping the call in defer.maybeDeferred lets you treat the result as a Deferred in either case.

Using the IUsernamePassword.checkPassword method instead of comparing the passwords yourself gives the server-side protocol that created the credentials object more flexibility: it could use a custom implementation of IUserNamePassword that compared the passwords in a case-insensitive way, or asynchronously. Moreover, using the checkPassword method allows DbPasswordChecker to accept a second credentials interfaceIUsernameHashedPassword. If the protocol being used here involved hashed passwords, it wouldn't be possible to do a direct comparison of the hash sent by the user and the plain-text password in our database. The checkPassword method lets the credentials object compare the two using the proper hashing algorithm. This isn't necessary in Example 6-2, since it uses plain-text passwords, but writing your code this way makes it easier to reuse DbPasswordChecker for other services in the future.

The _checkedPassword method handles the Boolean result of checkPassword. If the password matched, it returns the value of the field userid from the database: this is the final result of requestAvatarId. Note that this is a different type of avatar ID than in Example 6-1. In Example 6-1, the avatar ID was equal to the username; this time, it's an integer identifying the database record. This works because the avatar ID is used only by the realm, and Example 6-2 has a new realm as well. DbRealm.requestAvatar takes the avatar ID returned by DbConnector.requestAvatarId and uses it to fetch the full user record from the database. Then it uses the results to construct a NamedUserAvatar object.

Example 6-2 creates a Portal object that uses DbRealm as its realm and registers DbPasswordChecker as a credentials checker. Then the Portal is passed to a LoginTestFactory. This factory and its protocol come directly from the simplecred module in Example 6-1. They are able to function exactly as they did before without any changes, as the Portal hides the implementation details.