Skip to content

Migrate existing U2F registrations to WebAuthn

Jan Beckmann requested to merge kingjan1999/gitlab-ee:232670-webauthn-background-migration into master

What does this MR do?

This is step 2/5 of replacing U2F with WebAuthn for multi-factor authentication (see &4061). It adds a background migration for migrating all existing U2F registration to WebAuthn. Additionally, newly created u2f registrations are also added as WebAuthn registrations using a after_commit hook. (#232670 (closed))

Migrations

up

AddU2fIdToWebauthnRegistration 
== 20200925125321 AddU2fIdToWebauthnRegistration: migrating ===================
-- add_column(:webauthn_registrations, :u2f_registration_id, :integer)
   -> 0.0248s
== 20200925125321 AddU2fIdToWebauthnRegistration: migrated (0.0248s) ==========
AddForeignKeyToU2fRegIdInWebauthnRegs 
== 20200928125258 AddForeignKeyToU2fRegIdInWebauthnRegs: migrating ============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:webauthn_registrations, :u2f_registration_id, {:where=>"u2f_registration_id IS NOT NULL", :name=>"index_webauthn_registrations_on_u2f_registration_id", :algorithm=>:concurrently})
   -> 0.0050s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:webauthn_registrations, :u2f_registration_id, {:where=>"u2f_registration_id IS NOT NULL", :name=>"index_webauthn_registrations_on_u2f_registration_id", :algorithm=>:concurrently})
   -> 0.0907s
-- execute("RESET ALL")
   -> 0.0003s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:webauthn_registrations)
   -> 0.0062s
-- execute("ALTER TABLE webauthn_registrations\nADD CONSTRAINT fk_13e04d719a\nFOREIGN KEY (u2f_registration_id)\nREFERENCES u2f_registrations (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0011s
-- execute("ALTER TABLE webauthn_registrations VALIDATE CONSTRAINT fk_13e04d719a;")
   -> 0.0076s
== 20200928125258 AddForeignKeyToU2fRegIdInWebauthnRegs: migrated (0.1574s) ===
ScheduleMigrateU2fWebauthn 
== 20200929114107 ScheduleMigrateU2fWebauthn: migrating =======================
-- Scheduling MigrateU2fWebauthn background migration jobs
== 20200929114107 ScheduleMigrateU2fWebauthn: migrated (0.0127s) ==============

down

AddForeignKeyToU2fRegIdInWebauthnRegs 
== 20200928125258 AddForeignKeyToU2fRegIdInWebauthnRegs: reverting ============
-- foreign_keys(:webauthn_registrations)
   -> 0.0048s
-- remove_foreign_key(:webauthn_registrations, {:column=>:u2f_registration_id})
   -> 0.0142s
-- transaction_open?()
   -> 0.0000s
-- indexes(:webauthn_registrations)
   -> 0.0044s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- remove_index(:webauthn_registrations, {:algorithm=>:concurrently, :name=>"index_webauthn_registrations_on_u2f_registration_id"})
   -> 0.0117s
-- execute("RESET ALL")
   -> 0.0004s
== 20200928125258 AddForeignKeyToU2fRegIdInWebauthnRegs: reverted (0.0365s) ===
AddU2fIdToWebauthnRegistration 
== 20200925125321 AddU2fIdToWebauthnRegistration: reverting ===================
-- remove_column(:webauthn_registrations, :u2f_registration_id, :integer)
   -> 0.2680s
== 20200925125321 AddU2fIdToWebauthnRegistration: reverted (0.2937s) ==========
ScheduleMigrateU2fWebauthn 
== 20200929114107 ScheduleMigrateU2fWebauthn: reverting =======================
== 20200929114107 ScheduleMigrateU2fWebauthn: reverted (0.0000s) ==============

SQL Queries

Here are the queries (with the execution plans) for the first couple of iterations of the scheduling migration:

  1. SELECT "u2f_registrations"."id" FROM "u2f_registrations" ORDER BY "u2f_registrations"."id" ASC LIMIT 1 - https://explain.dalibo.com/plan/UEU
  2. SELECT "u2f_registrations"."id" FROM "u2f_registrations" WHERE "u2f_registrations"."id" >= 5 ORDER BY "u2f_registrations"."id" ASC LIMIT 1 OFFSET 1000 - https://explain.dalibo.com/plan/rms
  3. SELECT MIN(id), MAX(id) FROM "u2f_registrations" WHERE "u2f_registrations"."id" >= 5 AND "u2f_registrations"."id" < 102721 - https://explain.dalibo.com/plan/byN
  4. SELECT "u2f_registrations"."id" FROM "u2f_registrations" WHERE "u2f_registrations"."id" >= 102721 ORDER BY "u2f_registrations"."id" ASC LIMIT 1 OFFSET 1000 - https://explain.dalibo.com/plan/Te
  5. SELECT MIN(id), MAX(id) FROM "u2f_registrations" WHERE "u2f_registrations"."id" >= 102721 AND "u2f_registrations"."id" < 105279 - https://explain.dalibo.com/plan/zt

The INSERT query looks like this

INSERT INTO "webauthn_registrations" ("credential_xid", "public_key", "counter", "name", "user_id", "u2f_registration_id", "created_at", "updated_at")
VALUES 
-- values 
ON CONFLICT ("credential_xid") DO NOTHING

and this is the execution plan for the INSERT query with generated fake data (for 100 records, from #database-lab):

ModifyTable on public.webauthn_registrations  (cost=0.00..1.50 rows=100 width=136) (actual time=14.910..14.910 rows=0 loops=1)
   Buffers: shared hit=711 read=19 dirtied=11
   I/O Timings: read=11.866
   ->  Values Scan on "*VALUES*"  (cost=0.00..1.50 rows=100 width=136) (actual time=8.345..8.640 rows=100 loops=1)
         Buffers: shared hit=107 read=5 dirtied=1
         I/O Timings: read=8.205


Time: 526.928 ms  
  - planning: 0.295 ms  
  - execution: 526.633 ms  
    - I/O read: 11.866 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 711 (~5.60 MiB) from the buffer pool  
  - reads: 19 (~152.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 11 (~88.00 KiB)  
  - writes: 0

https://explain.dalibo.com/plan/r0g

Background Migrations

There are 25K records to be migrated on GitLab.com. With batches of 1_000 and delay of 2 minutes this means that 25 jobs will be scheduled and they should be completed in around 50 minutes. Each job will insert its batch in slices of 100 so the total SQL queries executed should be 250 (25 * 10).

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Closes #232670 (closed)

Edited by Krasimir Angelov

Merge request reports