Tracking a User's Social Logins in your Database

Managing user accounts can be tricky, especially when you allow users to login using different social platforms (i.e. Twitter, Google, GitHub). In this article we will take a look at how you manage the various ways a user might authenticate with an application that allows for additional social platforms in the future.

OpenId Connect (OIDC) is a specification that extends the OAuth 2.0 specification with delegates authorization to a 3rd party provider. When you use OIDC for user authentication you give users the choice to use an existing account on a trusted social platform to authenticate with your application. This reduces the amount a login credentials a user needs to remember and track as well as reducing the overhead of managing a user authentication system. If you’ve ever built a user authentication system you know that it can be far more complex than it might initially seem. You have to deal with password resets, email verification with opt-in, securing and storing credentials, there’s a lot to consider.

Here’s the scenario, we have an application that will use various OIDC providers (i.e. Twitter, Google, GitHub) to allow a user to authenticate with our application. When a user chooses to login using one of these social platforms they are redirected to that platform to complete the authentication. Once they are successful the user is redirected back to our application where we then need to track the login and manage the user’s session from there. That’s where this article starts.

For our example application we’ll be using the PostgreSQL relational database to track user accounts and login attempts, though this pattern could be modified to work with almost any other database type (relational or non-relational). The specific authentication flow isn’t super important here, but let’s assume we’re using the authorization code flow The login process will look something like this:

1. User selects a social platform to use for login (currently unauthenticated)
2. User is redirected to social platform, user authenticates
3. User is returned to our application, OIDC provider passes information that will be used to get more information about the user.
4. Our application requests additional information about the user to help identify the user (i.e. email address)
5. Our application will look up the user, determine user authorization, and begin tracking the user session.

In our database we have two tables, users and logins.

users database table

logins database table

Note: Some columns removed for display purposes

Now when a user authenticates with our application we need to do the following things to complete the authentication process:

1. Look up user by their email address AND the social platform, if they don’t exist, create new user account
2. Update the users table modified_at column value with the current time
3. Create a new record in the logins table noting if the login was successful or not
4. Generate a session identifier or JSON Web Token (JWT) to track the user’s session
5. Return the session identifier to the user interface to officially begin the user’s session

By tracking the user in a users table we can retain just the information we need about the user and the logins table allows us to maintain a record of the users sessions.