Change Details

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=N` - 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

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=N` - 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

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=N` - 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