Tony Landis home

OpenID + DB Authentication in Pylons is easy with RPX

This post will walk you through the steps I took to enable OpenID login in a project I am working on. I wanted to enable OpenID alongside existing user data stored in MySQL database.

Login Screen for Pylons

This tutorial actually covers more than just the subject of OpenID; it shows how to create your own custom authentication and authorization in Pylons, and how to create your own authorization decorators to protect Pylons actions.

I will assume you already have Pylons, SQLAlchemy, Mako, and MySQL installed, and will start by creating a new Pylons project project for this tutorial.

At this point, I should explain the database schema I have in place for the users who will be able to login with a username/password combination. It is very simple - the USER table stores all the users and their a md5 of their password (for the sake of this tutorial only, you should probably use something more secure) and the USER_OPENID table stores OpenID user information.

When a normal user (with a username/password in the USER table) attempts to login we will check against the USER table.

When a user logs in through OpenID, we will check against the USER_OPENID table to see if they have previously logged in with that OpenID, and if not, we will create both a USER and an USER_OPENID record for them. The end result is that they have a Pylons session which will contain their details from the columns in the USER table.

The ‘acl’ column in the USER table stores a string that identifies the access level of the user, such as ‘admin’, ‘employee’, ‘customer’, etc. You may want to change this column to an ENUM so the field is restricted to whatever your actual access level identities are. For example: enum(‘root’,’staff’,’customer’)

Here are the mysql explain results:

$ mysql> explain user;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| username  | varchar(128) | YES  | UNI | NULL    |                |
| password  | varchar(128) | YES  |     | NULL    |                |
| acl       | varchar(16)  | NO   |     | NULL    |                |
| name      | varchar(32)  | YES  |     | NULL    |                |
| dateLogin | datetime     | YES  |     | NULL    |                |
| sessionId | varchar(32)  | YES  |     | NULL    |                |
| ip        | varchar(16)  | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

$ mysql> explain user_openid;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id           | int(11)      | NO   |     | NULL    |                |
| verifiedEmail     | varchar(200) | YES  |     | NULL    |                |
| displayName       | varchar(200) | YES  |     | NULL    |                |
| preferredUsername | varchar(200) | YES  |     | NULL    |                |
| providerName      | varchar(100) | YES  |     | NULL    |                |
| identifier        | varchar(200) | YES  |     | NULL    |                |
| email             | varchar(200) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

So let’s get started by creating a database named ‘pylons_openid’, and create the tables. If you already have a user table, that you plan to use, that is fine, you will just need to modify the SQLAlchemy model so your columns match up. Here are the MySQL commands to create a database and tables and a few sample users:

blog comments powered by Disqus