How can I integrate social login to my existing user schema/login system?

Question

I have a RESTful API that multiple clients connect to (web, iOS, and Android apps). Currently, users can only create accounts with a username and password (email is optional) and they are represented in the database with exactly those columns.

A user "logs in" via our API by POSTing to a /session endpoint. The API response is a new secret token (which we associate with the username). The client uses this token to sign all POST requests initiated by the client which our API later uses to authenticate requests.

However, we now want to allow social login (Google+, FB, Twitter). Our users also want to use social login for the convenience but would prefer that they choose their own random username to be displayed next to their comments, etc.

My question: what changes do I need to make to my API in order to support social login? Do I need to make significant changes to my database schema to support this and, if so, what exactly? Also, does the server need to know anything about access tokens granted by the social login provider?

Please note that the scope of this feature does NOT include the ability to associate multiple social login accounts with the same person. I want to keep things as simple as possible.

Generally confused about how to properly implement this. I'd prefer to keep things as simple as possible so extra points for a simple solution. Any advice would be greatly appreciated.

Answer 1

• I had the exact same issue
• I needed Facebook Twitter Google and GitHub login
• Lets have a look at how this is supposed to work
• I have 2 tables, a users table and a social_tokens table
• My users table looks like this

users (user_id integer, nickname varchar(64), email varchar(255), password varchar(64), email_verified boolean, enabled boolean, created_at timestamptz, updated_at timestamptz)

• Thing to note here is that user_id is the primary key, nickname is unique, email is unique, email_verified has a default value of FALSE, enabled has a default value of TRUE, password is OPTIONAL thanks to social login

• My social_tokens table looks like this

social_tokens (provider_user_id varchar(255), provider_type enum('facebook', 'twitter', 'google', 'github'), user_id integer, access_token varchar(255), refresh_token varchar(255), expires timestamptz, created_at timestamptz, udpated_at timestamptz)

• provider_user_id is the unique ID that a social network assigns to each user
• provider_type is an enum with 4 values in my case, facebook, twitter, google and github
• user_id REFERENCES the user_id from users table
• access_token and refresh_token (if provided) are simply tokens allocated by the provider
• google seems to have the concept of expiry so I added an expires column as well, it is OPTIONAL for the rest
• PRIMARY KEY is the combination of (provider_user_id, provider_type, user_id) because a single user may have more than one social login so user_id CANNOT be the primary key

• While it can be argued that provider_user_id is UNIQUE for each user in a given social network, what about different social networks? hence the combination

• The idea is that each user may have multiple social logins

• If you had a Twitter [email protected] and Facebook [email protected] both and if you login via both atleast once, they both are part of your login now