To support passwordless login with passkeys (T321708), the OATHAuth extension needs to be able to identify which user has a given userHandle value. The userHandle is a 64-byte binary string that is randomly chosen for each user. To support this lookup, we propose adding a simple 1-to-1 mapping table between user IDs and userHandle values.
Like the other oathauth_* tables, there would be one global instance of the oathauth_user_handles table in the centralauth database for all SUL wikis together, and a local instance in each database for non-SUL wikis.
Gerrit patch: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/OATHAuth/+/1236403
Proposed schema: https://gerrit.wikimedia.org/r/c/mediawiki/extensions/OATHAuth/+/1236403/2/sql/tables.json
Checklist from https://wikitech.wikimedia.org/wiki/Creating_new_tables:
- Should this table be replicated to wiki replicas (does it not contain private data)?
- No, it should not be replicated, nor should the other oathauth_* tables. They contain data related to user authentication.
- Will you be doing cross-joins with the wiki metadata?
- No. We're currently not planning to join this table on anything. In the future I suppose we might want to join it against other oathauth_* tables, but probably not against anything else.
- Size of the table (number of rows expected).
- The size of this table will always equal the result of the query select count(distinct oad_user) from oathauth_devices join oathauth_types on oad_type=oat_id where oat_name='webauthn';. If we ran the population script in production today, the table in the centralauth DB would have 1,425 rows. This table will always be smaller than the oathauth_devices table.
- Expected growth per year (number of rows).
- We've had relatively high growth recently because we rolled out new WebAuthn-related features, leading to 1100 new users in 2 months. In the long run, growth will probably be less, probably about 5k rows per year.
- Expected amount of queries, both writes and reads (per minute, per hour...per day, any of those are ok).
- Writes only happen when a user adds their first WebAuthn-based 2FA key, or deletes their last one. I estimate this happens ~20 times per day.
- Reads happen when a user who has 2FA tries to log in, or opens the 2FA management UI. I don't have numbers handy on how often this happens, but probably not a huge amount. This table will get the same amount of read traffic as the existing oathauth_devices table (they're usually read together).
- Examples of queries that will be using the table.
- SELECT oah_handle FROM oathauth_user_handles WHERE oah_user=N
- SELECT oah_user FROM oathauth_user_handles WHERE oah_handle='X'
- Simple inserts of one row at a time
- DELETE FROM oathauth_user_handles WHERE oah_user=N
- The release plan for the feature (are there specific wikis you'd like to test first etc).
- Deploy the code from the attached patch (disabled behind a feature flag)
- Enable the feature flag
- Run the maintenance script to populate the table
- Due to the global nature of authentication, it doesn't make sense to roll this out on a subset of wikis, we'd just deploy it everywhere at once