OpenID + DB Authentication in Pylons is easy with RPX

June 2nd, 2009

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:

One last step of preparation before moving on to Pylons: you will need a , just head over to their site and sign up. Believe me, using this service takes the headache out of tying into OpenID. You will need to create an “Application” and note the API key and insert it into your development.ini file (explained in a moment)

Login Screen for Pylons

Setting up an Application at RPXnow

Great, now, lets move on to Pylons. Let’s create a new pylons project called ‘pylons_openid’:

# paster create -t pylons pylons_openid

When prompted, select ‘mako’ for your template_engine and ‘True’ for sqlalchemy.

Next, let’s create two controllers:

# cd pylons_openid
# paster controller auth
# paster controller root

Next, in the pylons project open model/ and define the SQLAlchemy model. Here is the code:

Next, open development.ini and add the database connection under the [app:main] section - we will also add that RPXnow API key at the same time:

# SQLAlchemy database URL
sqlalchemy.url = mysql://[email protected]:3306/pylons_openid
rpx_token = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


Let’s save all this and test our that our DB connection and SQLAlchemy model are working via paster shell:

# paster shell development.ini
> from pylons_openid.model import *
> print sa.query(User).filter(User.username==’tony’).one()

This should return:

16:21:10,868 INFO  [sqlalchemy.engine.base.Engine.0x...f0f0] ['tony']
Tony Landis


Next, in the ‘lib’ directory, create a file named ‘’. This will contain the decorators needed to protect our actions. Here is the code:


And now lets setup a controller with some of the actions protected with the @require_ decorators. Open controllers/ and change it to this code:


And now controllers/ This controller contains the login/logout actions as well as the action that receives the post-back from RPXNow after an OpenID login.


Now add two templates in the /templates directory (index.html and login.html):


We can now start up this server:

# paster serve –reload development.ini


Now load  in your browser and you should be redirected to the login screen, since we have the @require_login decorator on that action. Screenshot below:


Login Screen for Pylons

Login Screen for Pylons


Thats all there is to it! At this point you should be able login with a username/password of tony/password and customer/password, as well as login using the RPXnow powered OpenID option. After logging in through one of the OpenID providers, you can check the USER and USER_OPENID tables to see that the new records have been inserted as expected with the ‘Customer’ ACL.

If you want to add new ACLs or groups of ACLs, just edit the lib/ file, it is very simple. If you create more decorators and plan to do ‘ from project.lib.auth import * ‘ as shown in the example root controller, then remember to add the new decorator names to the __all__ list at the top of lib/

No doubt my ACL approach is overly simplistic for some projects, and perhaps overly complicated for others, but as the documentation of authentication and authorization in the realm of Pylons fell a bit lower than my expectations, I hope these examples will save others traveling a similar path a bit of headache.

And thanks to RPXNow for hands down one of what has to be the biggest timesaver for developers tackling OpenID!

Tagged: , , , ,

§ 2 Responses to “OpenID + DB Authentication in Pylons is easy with RPX”

    • Name: hrr4
    • Date: June 10th, 2009
    • Really appreciated :D. Was looking for a good up-to-date solution…..I also agree on the topic of the sub-par docs for authenticating and authorizing. Again, thanks :D

    • Name:
    • Date: June 29th, 2009
    • [...] Sinon, pour ceux que ca intéresse, un autre billet devrait bientôt suivre sur comment brancher RPX sur un Pylons. En attendant, le tutoriel sur lequel je me suis principalement basé est disponible ici. [...]

§ Leave a Reply

What's this?

You are currently reading OpenID + DB Authentication in Pylons is easy with RPX at Tony Landis.


Reduslim Kaufen

Cialis 5 mg effetti collaterali Effetti collaterali del Cialis 5 mg Cialis 20mg effetti collaterali Cialis originale in vendita in Svizzera Dove comprare Viagra senza prescrizione in Svizzera Farmacia per Cialis 10mg Prezzo di Viagra in farmacia online Cialis 5mg prezzo Ingredientes de Reduslim Comprare Spedra Avanafil senza ricetta online In Svizzera si compra il Viagra senza ricetta Cialis barato Ordina Viagra Contrareembolso Cialis Adquirir viagra en España Cialis 5 mg efectos secundarios menos frecuentes Reduslim opiniones Incapacidad para mantener una erección tadalafilo barato Авиатор в ПинАп Comprare Cialis 5 mg a buon prezzo Cialis 5 mg effetti collaterali Comprare online Cialis 20mg Farmacia per Cialis originale in Svizzera Ordina Viagra senza ricetta in Svizzera Compra Cialis 10mg Comprare Viagra in farmacia online a buon prezzo Dove trovare Cialis 5mg al miglior prezzo Comprar Reduslim en línea Acquista Spedra Avanafil senza prescrizione Dove comprare il Viagra in Svizzera buy Cialis 5 mg Comprar Viagra Contrareembolso Cuánto cuesta el Cialis 5 mg mejores farmacias para comprar Viagra Efectos secundarios Reduslim adelgazar Impotencia comprar Cialis genérico avanafil prezzo cialis 20 mg prezzo cialis effetti benefici cialis 10 mg Reduslim Kaufen pulse x crypto price prediction avanafil prezzo buy cialis or generic tadalafil