social authentication provider is not returning the user email

Question

It is an architectural question rather than a technical one. Our web and mobile application are totally dependant upon social authentication and we are using social authentication providers such as Google, Facebook, and many others.

On successful user login, providers like Google and Facebook return the user's data with email and a unique id. We are storing that data in the database something like this.

+----+--------------------------------------------------------------+---------------+----------+
| id | socialId                                                     | email         | provider |
+----+--------------------------------------------------------------+---------------+----------+
| 1  | $2a$12$Qx.FVkgYkXqJGF4PyJ1kb.JlSDe9bV6TJFodpTx2eBsYxvqn6Gywa | [email protected] | Facebook |
+----+--------------------------------------------------------------+---------------+----------+
| 2  | $2a$12$N2fCTRxymPlcaID5oP617OJTXkrvKAHZ/taFJ.lePZddfW3E6U.Fe | [email protected] | Google   |
+----+--------------------------------------------------------------+---------------+----------+

All database fields are required and not nullable.

Note: We are also storing socialIds in a hash format for security reasons and storing email helps us to know whether the user is a new user or an old user and help to prevent duplicate records.

But there is a problem, there are some providers who are not returning user emails.

So it has become a problem to identify whether the user is new or old, it also creates the risk of duplicate rows.

What is the best way to solve this problem? Is it good to store IDs in raw form in the database?

Answer 1

Good question so here are some architectural points:

APPLICATION LEVEL ACCOUNT LINKING

Feels like you may need to prompt the user upon return from login when you are unsure of their identity. One option might be a Complete Your Profile screen that captures information that enables you to link accounts together when required.

Of course if you ask for email, phone etc you then need to verify that the user owns it, eg via email or SMS verification, so you need to then add that plumbing to your app.

Also bear in mind that the social provider may have asked the user to consent to use of their user name but not their email, so you should explain to the user why your app needs the extra information.

AUTHORIZATION SERVER ACCOUNT LINKING

Out of interest, as a longer term solution, using an Authorization Server (AS) can externalise all of this plumbing from your app and improve the architecture.

The AS can manage social provider differences so that your apps and users get the same qualities, regardless of login method:

• Consistent tokens, issued only by the AS
• Consistent session lifetimes in UIs
• Consistent scopes and claims in tokens received by APIs
• Consistent confidential user IDs in your database, as PPIDs
• Adding a new authentication method requires zero code changes in any of your apps

Your own user data could then look like this, or you could potentially add your own User ID to tokens issued so that you need no storage at all.

Domain Specific User ID	PPID
1	UUID 1
2	UUID 2

The Authorization Server can also play a quite big part in externalising management of Personally Identifiable Information (PII) from your apps - as discussed in this article.

The linking logic still needs to be implemented, since OAuth is a framework rather than an out of the box solution. But a good AS (free / paid) would give you bigger building blocks for this type of solution - eg actions that can include prompting the user.

The key thing to check before committing to an AS is that it satisfies your use cases and there are no blocking issues for your apps.

Answer 2

i don't know why you need hash the socialId, the socialId(or more generally speacking, it's a user identifier relative you registing app in social authentication provider), so it the more generaly identifier about your user while using social authentication

you'd best to split two table. one for your system user info (userid,useremail,address,etc), and the second is maintain (socialid,userid,etc) . If the user email is required for your system, that in the authentication flow, you can conduct the user to complete the login process while the authentication provider don't support return email