modelling users with Oauth 2

Question

I'm building a web application using rails 3. I have my own user signup, login and authentication working fine and I'm looking to add an Oauth 2 implementation so people can connect with Facebook and login/signup with less steps.

I have the beginnings of this working successfully. So far, I can get facebook to authenticate users and give me permission to access their information. I haven't tried setting up Twitter auth yet but I presume it would work in a similar fashion.

What I'm pondering now is how to integrate this into my existing user model which basically consists of a table of Users who have emails, names and passwords.

Should I leave my users table as it is and set up an Access Providers table:

id | user_id | provider_id | access_key
---------------------------------------
1  |    4    |     1       |   xyz
2  |    4    |     2       |   pqr
3  |    7    |     1       |   dfr

and lets say a "Users Facebook Info" table where I store information about a user which as been gleaned from accessing that user in the facebook graph?

That way I can keep the Users table normalised with core information which every user has regardless of whether they are connected via facebook or not (name, email, password) and supplement that data with data from their facebook profile if it becomes available?

Is there a better way to do this? Is there a good guide or tutorial out there for designing this type of database model (think Quora)? I can handle a PHP tutorial too btw!

Sorry for the open ended question.

Answer 1

You may want to consider Signet or OmniAuth for your OAuth client if you're trying to do both Twitter and Facebook. I'm biased towards Signet, since I wrote it, but OmniAuth might be the better choice for now since it's been around a bit longer, depending on what you're trying to do.

As for data modeling, you've got the right idea. You probably need to make it a bit more generic though. Instead of just an access_key, you probably need access_token and potentially a refresh_token for OAuth 2. Meanwhile, OAuth 1 uses a credential key/secret pair. So maybe something like this (omitting the primary and foreign keys):

auth_scheme | access_token | refresh_token | key | secret | username | password
-------------------------------------------------------------------------------
oauth_1     |              |               | 123 | 456    |          |
oauth_2     | 123          |               |     |        |          |
oauth_2     | 123          | abcd          |     |        |          |
xauth       |              |               |     |        | abcd     | 12345
clientlogin |              |               |     |        | abcd     | 12345