Skip to content

Migrate SAML to SCIM Identities

Drew Blessing requested to merge dblessing-migrate-scim-identities into master

What does this MR do?

Enables scim_identities feature by default, and migrates scim identities as appropriate.

SAML identities will be copied/transitioned to scim_identities if the group has a SCIM token. We use the presence of a scim token to try to predict whether a group is really using scim, since we don't have a better data point to go on. We could just migrate all group SAML identities to SCIM but that would be overkill and might actually cause conflicts in the future if a group decides to use SCIM and don't have matching extern_uid.

Database migration

Note: The below information was prior to moving the migration to batches. The query is effectively the same but will happen in smaller batches, avoiding the timeout.

.== 20200310215714 MigrateSamlIdentitiesToScimIdentities: migrating ============
-- execute("          INSERT INTO scim_identities (extern_uid, user_id, group_id, active, created_at, updated_at)\n            SELECT identities.extern_uid, identities.user_id, saml_providers.group_id, TRUE,\n              identities.created_at, CURRENT_TIMESTAMP FROM \"identities\" INNER JOIN saml_providers ON saml_providers.id = identities.saml_provider_id WHERE (saml_providers.group_id IN (SELECT group_id FROM scim_oauth_access_tokens)) AND \"identities\".\"id\" >= 1\n          ON CONFLICT DO NOTHING\n")
   -> 0.0028s
== 20200310215714 MigrateSamlIdentitiesToScimIdentities: migrated (0.0187s) ===

I ran some of this query in #database-lab to see what the query plan would look like. I got two fairly disparate results, which I assume is due to cold cache.

If the query takes 5 seconds during deploy, is that acceptable?

Query (just without the insert):

SELECT identities.extern_uid, identities.user_id, saml_providers.group_id, TRUE, identities.created_at, CURRENT_TIMESTAMP FROM identities INNER JOIN saml_providers ON saml_providers.id = identities.saml_provider_id WHERE saml_providers.group_id IN ( SELECT group_id FROM scim_oauth_access_tokens )

First run explain:

 Merge Join  (cost=102.59..2429.89 rows=832156 width=41) (actual time=108.504..5655.962 rows=9316 loops=1)
   Merge Cond: (saml_providers.id = identities.saml_provider_id)
   Buffers: shared hit=8090 read=6309 dirtied=86
   I/O Timings: read=5524.769
   ->  Sort  (cost=102.30..102.91 rows=243 width=8) (actual time=33.323..33.682 rows=205 loops=1)
         Sort Key: saml_providers.id
         Sort Method: quicksort  Memory: 34kB
         Buffers: shared hit=376 read=25 dirtied=5
         I/O Timings: read=20.625
         ->  Merge Semi Join  (cost=1.06..92.68 rows=243 width=8) (actual time=4.782..33.060 rows=205 loops=1)
               Merge Cond: (saml_providers.group_id = scim_oauth_access_tokens.group_id)
               Buffers: shared hit=371 read=25 dirtied=5
               I/O Timings: read=20.625
               ->  Index Scan using index_saml_providers_on_group_id on public.saml_providers  (cost=0.28..74.31 rows=589 width=8) (actual time=1.872..21.162 rows=595 loops=1)
                     Buffers: shared hit=346 read=22 dirtied=2
                     I/O Timings: read=18.883
               ->  Index Only Scan using index_scim_oauth_access_tokens_on_group_id_and_token_encrypted on public.scim_oauth_access_tokens  (cost=0.27..13.91 rows=243 width=4) (actual time=0.099..11.627 rows=246 loops=1)
                     Heap Fetches: 98
                     Buffers: shared hit=25 read=3 dirtied=3
                     I/O Timings: read=1.742
   ->  Index Scan using index_identities_on_saml_provider_id on public.identities  (cost=0.29..20431.44 rows=2017037 width=32) (actual time=4.628..5603.315 rows=14754 loops=1)
         Buffers: shared hit=7714 read=6284 dirtied=81
         I/O Timings: read=5504.144

Subsequent run explain:

 Merge Join  (cost=103.21..348.47 rows=838532 width=24) (actual time=0.673..19.132 rows=9045 loops=1)
   Merge Cond: (saml_providers.id = identities.saml_provider_id)
   Buffers: shared hit=14112
   ->  Sort  (cost=102.92..103.51 rows=238 width=8) (actual time=0.558..0.608 rows=201 loops=1)
         Sort Key: saml_providers.id
         Sort Method: quicksort  Memory: 34kB
         Buffers: shared hit=445
         ->  Merge Semi Join  (cost=1.50..93.52 rows=238 width=8) (actual time=0.030..0.500 rows=201 loops=1)
               Merge Cond: (saml_providers.group_id = scim_oauth_access_tokens.group_id)
               Buffers: shared hit=445
               ->  Index Scan using index_saml_providers_on_group_id on public.saml_providers  (cost=0.28..77.56 rows=580 width=8) (actual time=0.010..0.265 rows=587 loops=1)
                     Buffers: shared hit=359
               ->  Index Only Scan using index_scim_oauth_access_tokens_on_group_id_and_token_encrypted on public.scim_oauth_access_tokens  (cost=0.27..13.84 rows=238 width=4) (actual time=0.007..0.098 rows=241 loops=1)
                     Heap Fetches: 166
                     Buffers: shared hit=86
   ->  Index Scan using index_identities_on_saml_provider_id on public.identities  (cost=0.29..20693.35 rows=2043482 width=24) (actual time=0.008..14.373 rows=14461 loops=1)
         Buffers: shared hit=13667

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Drew Blessing

Merge request reports