Google Sign-In, Database Side

Question

I've read about using Google to handle sign-ins from this website, but it doesn't explain very much about what should be going on with the database.

Normally for user accounts in a database you'd have fields like id, username, password, etc. From what I read in the link, you will eventually get the ID token for a user, and inside is a key called "sub" that has a value in it that you can use to identify the user.

My question is, where would this "sub" be stored in the database? Would it go in the id field? Username field?

I think it shouldn't go in the username field since there's the possibility that someone creates a username that is exactly the same as someone's Google sub id. But it also seems weird to put it in the id field.

Also, would there need to be another field in the user accounts table that is a boolean in order to differentiate between users signed in natively and users signed in through Google?

Answer 1

Create a EXTERNAL_ID column, and use it to store the identifier from the external system. You should add an EXTERNAL_TYPE column and set that to 'GOOGLE'.. when you go to integrate further authentication-providers, this can be set to different values (eg 'OPEN_ID', 'FACEBOOK'.)

When interfacing with external systems, it is always necessary to store the foreign key/ identifier for reliable access & updating to the external record.

The external key is distinct from the ID in the local database, and it's definitely not a username.. it's just it's own column, and should have a reasonably generous size so that any external ID encountered, can be stored.

VARCHAR(64) might be a good start since, by definition, we are not responsible for type or content of the identifier -- these are provided & defined by the external system. We just need to hold it's value unchanged, to use when talking to the external system.

The EXTERNAL_TYPE field lets you distinguish between users "registered natively" and "registered via Google" or other external providers.

For example:

create table "USER" (
    ID            int not null,
    USERNAME      varchar(64),
    DISPLAY_NAME  varchar(64),
    EMAIL         varchar(128),

    PWD_HASH      varchar(32),   -- "native" password, salted & hashed for security
    PWD_SALT      varchar(32),

    EXTERNAL_TYPE varchar(16),   -- external-auth Type & ID.
    EXTERNAL_ID   varchar(64),

    primary key (ID)
);

---

Note that some sites use a slightly more advanced scheme, where a user can be both registered via Google and able to signin via a native password. In this case, signing in via one system of credentials detects the existing email & other system of credentials -- often this prompts the user to authenticate with the other credentials, to link them & use both. This is still based on EXTERNAL_ID and EXTERNAL_TYPE but may place these on a USER_CREDENTIALS table related to USER, rather than USER table itself.

For user authentication, it's also conceivable you might need an additional column for some kind of crypto/key/token. Not sure about that, just a possibility.