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.

Answer 2

I may have a decent answer for this as I am currently facing this problem.

My solution has the following requirements

• A user has one or more accounts
• An account can be a password based account or any other social login based account
• One email can be used only once per account, if you signed up with [email protected] via Facebook, you cannot have another row which also uses Facebook with [email protected] in the table
• You can link any number of accounts with a given user and
• The user does not have the concept of an email anymore but the account does

Under this scheme the user table only has 2 fields

users (user_id, enabled)

The entire user and all their accounts can be enabled or disabled with a single flag

The authentication_types table contains details of which login methods are supported

authentication_types (authentication_type_id, authentication_type_name)

The accounts table holds all user data

accounts (account_id, email, email_verified, password, nickname, picture_url, is_primary_email, authentication_type_id, created_at, updated_at)

the user_accounts table will link the correct user_id with the correct account_id

user_accounts (user_id, account_id)

The password will be null where authentication_type_id indicates social login The external_login_id will be null where authentication_type_id indicates password login

Here is the full schema

-- The below database I believe can handle multiple accounts per user with ease.
-- As a user, you can have an account with [email protected] as email and a hashed password
-- You can also link your account via Facebook with the same email [email protected] or
-- a different one such as [email protected]

CREATE TABLE IF NOT EXISTS authentication_types (
authentication_type_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
authentication_type_name VARCHAR NOT NULL,
PRIMARY KEY(authentication_type_id),
UNIQUE(authentication_type_name)
);

INSERT INTO authentication_types VALUES
(1, 'password'), 
(2, 'facebook'), 
(3, 'google'), 
(4, 'github'), 
(5, 'twitter');

-- The user has one or more accounts
-- The user can have only one account of a given type
-- Example: only 1 Facebook account and 1 Google account
-- If you feel this is restrictive let me know a better way

CREATE TABLE IF NOT EXISTS users (
user_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
enabled BOOLEAN NOT NULL DEFAULT TRUE, 
PRIMARY KEY(user_id)
);

CREATE TABLE IF NOT EXISTS accounts (
account_id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, 
email VARCHAR NOT NULL, 
password VARCHAR, 
email_verified BOOLEAN NOT NULL DEFAULT FALSE, 
nickname VARCHAR, 
picture_url VARCHAR, 
is_primary_email BOOLEAN NOT NULL DEFAULT FALSE,
authentication_type_id INTEGER NOT NULL,
external_login_id VARCHAR,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY(account_id),
UNIQUE(email),
UNIQUE(authentication_type_id, email),
UNIQUE(authentication_type_id, external_login_id),
FOREIGN KEY (authentication_type_id) REFERENCES authentication_types (authentication_type_id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- the users with authentication_type_id as password will actually have a password
-- If we say email is unique, it becomes problematic
-- What if you have the same email on your Facebook and Google account?
-- So instead we say that the combination of authentication_type_id and email is unique
-- external_login_id is nothing but the unique login ID assigned by Twitter, Github etc
-- There is nothing to say that they are truly unique
-- It is possible that the Facebook ID for a user may be the same as the Pinterest ID for another user
-- So instead we say that the combination of authentication_type_id and external_login_id is unique

CREATE TABLE IF NOT EXISTS user_accounts (
user_id INTEGER NOT NULL,
account_id INTEGER NOT NULL,
PRIMARY KEY(user_id, account_id),
FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- user A with only password based account

INSERT INTO accounts(account_id, email, password, email_verified, nickname, picture_url, is_primary_email, authentication_type_id, external_login_id) VALUES (
1, 
'[email protected]', 
'$2b$11$oHR4Tdcy8Mse1lB5Hmgj5O3u3SPgqolHRgBEVXvzLt5BjS8ujGXKS',
false,
null,
null,
true,
1,
null
);

INSERT INTO users VALUES(1, true);
INSERT INTO user_accounts VALUES(1, 1);

-- user B with password and facebook account

INSERT INTO accounts(account_id, email, password, email_verified, nickname, picture_url, is_primary_email, authentication_type_id, external_login_id) VALUES (
2, 
'[email protected]', 
'$2b$11$oHR4Tdcy8Mse1lB5Hmgj5O3u3SPgqolHRgBEVXvzLt5BjS8ujGXKS',
false,
null,
null,
true,
1,
null
);

INSERT INTO accounts(account_id, email, password, email_verified, nickname, picture_url, is_primary_email, authentication_type_id, external_login_id) VALUES (
3, 
'[email protected]', 
null,
true,
null,
null,
false,
1,
'hjdigodgjaigfshg123461'
);

INSERT INTO users VALUES(2, true);
INSERT INTO user_accounts VALUES(2, 2);
INSERT INTO user_accounts VALUES(2, 3);

SELECT * FROM accounts;
SELECT * FROM users;
SELECT * FROM user_accounts;

Link to DBFIDDLE

Any suggestions on how to improve this further are most welcome!