Proposal - Migrating User Authentication to a new table to support multiple log-in sources.

[Neilk1021]

What we're proposing

---

Split the current authentication from all existing on the User table to a new AuthProvider table.

Why

Currently everytime we want to add a new authentication service (e.g. facebook), we need to add a whole new set of columns to TexeraDDL and also provide code to migrate older databases. It prevents the Users table from being a bloated god table, and better follows SOC.

Considerations

There is a small performance overhead introduced by the needed psql query to join the User to their Authentication Providers, however, this is O(p) (where p is the number of providers a user has) operation and since p is small its effectively an O(1) op.

Questions for discussion:

• Are there modifications we want to this schema?
• Is there any current reason it needs to be a single table, if so what?

[Yicong-Huang]

hmm this is still in discussion phase, let's not create issue and PRs directly: we may not want to go with this direction yet.

[chenlica]

Agreed.

[Yicong-Huang]

for this topic, I don't think we should store password for providers. we can view this way:

• local auth: require password + username
• third-party oauth: provider id, provider specific info (not password)

[Yicong-Huang]

Thanks, I am not looking at any PR. let's stay on design for now. Do you think we should have 3 tables? 1. users, 2. local auth credentials, 3. oauth provider metadata?

[Neilk1021]

I favor keeping it to 2 tables (users and auth_credentials). From an application perspective, local auth and OAuth are heavily coupled in responsibility: both serve to verify credentials and yield a user_id so our servlets can issue a JWT. Modeling local auth as just another provider (provider_type = 'local') gives us a single, uniform interface across all authentication strategies.

While a 3-table design avoids NULL columns, Postgres represents NULLs via a tuple header bitmap, making them virtually free in terms of storage and performance. The minor tradeoff of using a DB CHECK constraint to enforce field rules is worth avoiding the schema fragmentation imo.

[aglinxinyuan]

Local authentication and third-party authentication should be treated in the same way. We don't need to introduce provider-specific terminology such as OAuth, password, or ID.

Conceptually, the second table only needs two fields: provider and secret. The second column can be named password, oauth_id, provider_id, token, or anything similar—it simply stores the credential (i.e., a secret string) used to authenticate the user with that provider. We shouldn't care what technology or stack is behind the provider, as we should always keep the credential secret, whether it's a provider_id or a password hash.

For example:

uid	provider	secret
2	google	124re4244
2	local	hash64e2f2f2f24f2f

Also, if we only have 3 columns in the second table, we can avoid NULL columns.

[Neilk1021]

Sounds good, I couldn't think of a shared name for both columns but secret makes sense.

[Yicong-Huang]

I like the idea if you can unify them. But I want to double check if just provider id is enough for third party oauth, and if that's really a secret (not public) field?

[Yicong-Huang]

we should also make created_at on the user table. This is describing when a texera user is created. a texera user can be created with local login or an oauth provider

[Neilk1021]

sounds good

[aglinxinyuan]

The user table already has an account_creation_time column. If we want to keep the terminology consistent across all tables, we can simply rename it to created_at.

[carloea2]

What is the purpose of provider id?
Is email column just for metadata? what is its purpose? to potentially contact users?
Is provider_id = email for local auth?

[aglinxinyuan]

I agree with most of the design, except for two points:

1. The password should remain in the user table. Why does it need to be stored in this table? While allowing NULL values is inexpensive, that's not a sufficient reason to move it here.
2. google_avatar is unrelated to this discussion. Let's handle it separately.

[Neilk1021]

The password should remain in the user table. Why does it need to be stored in this table? While allowing NULL values is inexpensive, that's not a sufficient reason to move it here.

Fair pushback. The case for moving the password is the code path, not storage. If the hash stays on users, local auth is permanently a special case: one branch reads users, another reads auth_provider, and every new strategy has to pick a side. Additionally a SELECT * on a profile endpoint can't leak credential material, and the credential table can be locked down separately.

Worth noting your example table above already had local | hash64e2f2f2f24f2f as a row, so I think we're closer than it reads.
The real crux is whether local is a provider or the absence of one. Django/Devise do keep passwords on the user model, so that position isn't arbitrary, but I just think uniformity wins here.

google_avatar is unrelated to this discussion. Let's handle it separately.

To clarify, I'm not moving it, just renaming in place on users. It's two lines in the same migration and we're already altering that table, so folding it in avoids a second pass. Happy to split if you'd rather, but its not a semantic change.

[carloea2]

I support moving "password" out of the user table and renaming it to "secret", having NULLS is not a problem, other login options in the future may need this, and also it looks cleaner.

[aglinxinyuan]

The password should remain in the user table. Why does it need to be stored in this table? While allowing NULL values is inexpensive, that's not a sufficient reason to move it here.

Fair pushback. The case for moving the password is the code path, not storage. If the hash stays on users, local auth is permanently a special case: one branch reads users, another reads auth_provider, and every new strategy has to pick a side. Additionally a SELECT * on a profile endpoint can't leak credential material, and the credential table can be locked down separately.

Worth noting your example table above already had local | hash64e2f2f2f24f2f as a row, so I think we're closer than it reads.

The real crux is whether local is a provider or the absence of one. Django/Devise do keep passwords on the user model, so that position isn't arbitrary, but I just think uniformity wins here.

google_avatar is unrelated to this discussion. Let's handle it separately.

To clarify, I'm not moving it, just renaming in place on users. It's two lines in the same migration and we're already altering that table, so folding it in avoids a second pass. Happy to split if you'd rather, but its not a semantic change.

I'm ok to have the password in the new table. I didn't consider the read path. What's the pair for local login in your final design?

[Neilk1021]

What's the pair for local login in your final design?

For local login
provider_id => username
password => encrypted password

Provider_id stores username since thats the current functionality. We could discuss moving this to email or some other identifier in another discussion (I can see some valid arguments against it but it seems out of scope for our current discussion.).