Migrate SAML to SCIM Identities
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
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
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