Storing OpenID information in database.

Question

This is a database question. I have an openid library already installed and working.

I want to use OpenID on a website I'm working on. There are three types of users.

1. Named accounts using email/password to authenticate
2. Named accounts using OpenID to authenticate
3. Anonymous account using OpenID to authenticate

All of these users are stored in the same table to simply the comments table. Named accounts are linked to a blog that the account can manage. Anonymous users can comment on articles on the blog but they can't do anything else. If an anonymous user signs up for an account I want to automatically transfer that user's old comments to the named account. I'm currently doing this by having the users fill out a form with a unique name and a valid email.

Currently both sets of accounts are stored in the same table using the following schema. (Yes it's a rails migration)

create_table :users do |t|
  t.string :name #unique because it maps to a subdomain
  t.string :openid_url #unique
  t.string :email #unique 
  t.string :password_hash
  t.string :password_salt
  t.boolean :guest #Anonymous user flag

  t.timestamps
end

(openid_url is unique to prevent multiple accounts from being attached to the same openid. email is unique because users login with email/password)

I'm using this setup I've run into a problem with Google's OpenID implementation. Every user that uses Google for OpenID has the same url: https://www.google.com/accounts/o8/ud.

How do I support using google as an openid provider since it's urls are not unique for each user? (Please remember the constraints that exist)

Note: Google Accounts can use any valid email address on the internet so I can't just store [email protected] in the openid_url field because the email could be [email protected] or worse [email protected]! Yahoo also uses this single url method so I have to support them too.

Answer 1

Store the openid.claimed_id value. It is unique per user. Google OpenIDs are indeed unique. They all start with the same value, but they have unique ?id=uniquenesshere querystrings. Remember that these values should be considered case sensitive, so preserve case and match on case when looking up users.

You definitely don't want to consider email addresses to be unique for the reasons you gave, plus for most Providers the email address is not pre-verified (or you can't trust the Provider to have done that) so storing on the email address is a sure way to get your users' identities stolen.

Answer 2

openid_url is not needed here.Only UserID or name (unique) works for both account. You can also create separate table with openid and userid from UserAccount table.