Skip to content

Add approvals post migration and spec

Hunter Stewart requested to merge hustewart-migrate-approval-data into master

Coordination

⚠ This needs to be a coordinated merge to coincide with the breaking change FF roll out. ⚠

For the breaking change window of 2024-05-06 09:00 UTC to 2024-05-08 22:00 UTC we want to rollout a feature flag prior to but on the same day as the post deployment migration being executed.

According to our docs https://docs.gitlab.com/ee/development/database/post_deployment_migrations.html

For GitLab.com, these migrations are executed on a daily basis at the discretion of release managers through the post-deploy migration pipeline.

To time this optimally, we need to coordinate with the release manager and complete the following plan:

Plan

  • Merge this MR I'd suggest merging this MR on 2024-05-03 (Friday) EMEA end of the day or AMER morning, to guarantee it is it is deployed by Monday.
  • Roll out feature flag Monday May 6th before post deployment migration. This usually happens EMEA during the day so EMEA morning should work.

The purpose of this coordination is to minimize the time between removal of the unified deployment approval system (which is done by rolling out the feature flag) and the migration of that data to the multi-access one. We don't want to migrate the data first because we could end up with un-migrated data

Why

Issue: [Breaking change] Migrate Deployment Approval s... (#357798 - closed)

Part of a deprecation of unified approval rules, we need to migrate the unified data.

What

  • post deployment migration

Database

DBLab explain

migration output:

main: == [advisory_lock_connection] object_id: 123860, pg_backend_pid: 58188
main: == 20240424183213 BackfillDeploymentApprovalData: migrating ===================
main: == 20240424183213 BackfillDeploymentApprovalData: migrated (0.0253s) ==========

main: == [advisory_lock_connection] object_id: 123860, pg_backend_pid: 58188
ci: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 58190
ci: == 20240424183213 BackfillDeploymentApprovalData: migrating ===================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20240424183213 BackfillDeploymentApprovalData: migrated (0.0065s) ==========

ci: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 58190

Locally testing 5000 protected environments

Script to populate data

# create protected environments

user = User.find(1)

5000.times do
  pe = ProtectedEnvironment.new(
    project_id: 1979, 
    name: "test-script #{SecureRandom.hex}",
    required_approval_count: rand(1..5)
  )
  pe.deploy_access_levels.new(user: user)
  pe.save!
end

SQL output by logging the migrate up

Main point here is showing that batch working

AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 501 AND "protected_environments"."id" < 1001)
click to expand logs of migration 
D, [2024-04-30T11:19:50.417130 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.425936 #94152] DEBUG -- :    (8.1ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 1 AND "protected_environments"."id" < 501)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.426121 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.427174 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 501 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.427356 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.431814 #94152] DEBUG -- :    (4.0ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 501 AND "protected_environments"."id" < 1001)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.432012 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.433088 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 1001 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.433277 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.438193 #94152] DEBUG -- :    (4.4ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 1001 AND "protected_environments"."id" < 1501)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.438383 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.439531 #94152] DEBUG -- :    Load (0.3ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 1501 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.439881 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.444682 #94152] DEBUG -- :    (4.1ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 1501 AND "protected_environments"."id" < 2001)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.444838 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.445970 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 2001 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.446263 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.451037 #94152] DEBUG -- :    (4.2ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 2001 AND "protected_environments"."id" < 2501)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.451254 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.452363 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 2501 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.452529 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.457062 #94152] DEBUG -- :    (4.1ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 2501 AND "protected_environments"."id" < 3001)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.457230 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.458318 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 3001 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.458486 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.462905 #94152] DEBUG -- :    (3.9ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 3001 AND "protected_environments"."id" < 3501)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.463081 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.464269 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 3501 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.464448 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.469075 #94152] DEBUG -- :    (4.1ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 3501 AND "protected_environments"."id" < 4001)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.469234 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.470166 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 4001 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.470326 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.475025 #94152] DEBUG -- :    (4.2ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 4001 AND "protected_environments"."id" < 4501)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.475170 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.476113 #94152] DEBUG -- :    Load (0.2ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 4501 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.476278 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.480733 #94152] DEBUG -- :    (4.1ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 4501 AND "protected_environments"."id" < 5001)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/
D, [2024-04-30T11:19:50.480867 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:99:in `block (2 levels) in each_batch'
D, [2024-04-30T11:19:50.481668 #94152] DEBUG -- :    Load (0.1ms)  SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 5001 ORDER BY "protected_environments"."id" ASC LIMIT 1 OFFSET 500 /*application:web,db_config_name:main,line:/app/models/concerns/each_batch.rb:81:in `block in each_batch'*/
D, [2024-04-30T11:19:50.481847 #94152] DEBUG -- :   ↳ app/models/concerns/each_batch.rb:81:in `block in each_batch'
D, [2024-04-30T11:19:50.482869 #94152] DEBUG -- :    (0.6ms)  INSERT INTO protected_environment_approval_rules
            (protected_environment_id,
             created_at,
             updated_at,
             access_level,
             required_approvals)
SELECT id,
       Now(),
       Now(),
       30,
       required_approval_count
FROM   protected_environments
WHERE  required_approval_count > 0
       AND NOT EXISTS (SELECT 1
                       FROM   protected_environment_approval_rules
                       WHERE  protected_environment_id =
                              protected_environments.id)
AND protected_environments.id IN (SELECT "protected_environments"."id" FROM "protected_environments" WHERE "protected_environments"."id" >= 5001)
 /*application:web,db_config_name:main,line:/db/post_migrate/20240424183213_backfill_deployment_approval_data.rb:16:in `block in up'*/

Local verification

Click to expand

  • on master branch

  • create projects m1 and m2 in UI

  • disable feature flag in rails console pry(main)> Feature.disable :deprecate_unified_approval_rules

  • use API to to use unified approval rules

$ curl --header 'Content-Type: application/json' \
     --header "PRIVATE-TOKEN: ****" \
     --request POST \
     --data '{"name": "production", "deploy_access_levels": [{"user_id": 1}], "required_approval_count": 1}' \
     "https://gdk.test:3443/api/v4/projects/1978/protected_environments"

{"name":"production","deploy_access_levels":[{"id":7,"access_level":null,"access_level_description":"Administrator","user_id":1,"group_id":null,"group_inheritance_type":0}],"required_approval_count":1,"approval_rules":[]}%

$ curl --header 'Content-Type: application/json' \
     --header "PRIVATE-TOKEN: ****" \
     --request POST \
     --data '{"name": "production", "deploy_access_levels": [{"user_id": 1}], "required_approval_count": 2}' \
     "https://gdk.test:3443/api/v4/projects/1979/protected_environments"

{"name":"production","deploy_access_levels":[{"id":8,"access_level":null,"access_level_description":"Administrator","user_id":1,"group_id":null,"group_inheritance_type":0}],"required_approval_count":2,"approval_rules":[]}%

  • approve m1 and see the pipeline run

  • leave m2 unapproved to see what happens in migration

  • in rails console check existing count

    • [13] pry(main)> ProtectedEnvironments::ApprovalRule.count
    • my result was 10

  • chcek required_approval_count for the last two you just created pry(main)> ProtectedEnvironment.order(created_at: :desc).limit(2).map(&:required_approval_count)

  • mine was [2,1] as expected

  • on hustewart-migrate-approval-data branch

  • bundle exec rake db:migrate

main: == [advisory_lock_connection] object_id: 123860, pg_backend_pid: 58188
main: == 20240424183213 BackfillDeploymentApprovalData: migrating ===================
main: == 20240424183213 BackfillDeploymentApprovalData: migrated (0.0253s) ==========

main: == [advisory_lock_connection] object_id: 123860, pg_backend_pid: 58188
ci: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 58190
ci: == 20240424183213 BackfillDeploymentApprovalData: migrating ===================
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20240424183213 BackfillDeploymentApprovalData: migrated (0.0065s) ==========

ci: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 58190

  • check count in rails console

    • pry(main)> ProtectedEnvironments::ApprovalRule.count
    • 12 as expected

  • check new records::

[12] pry(main)> ProtectedEnvironments::ApprovalRule.order(created_at: :desc).limit(2)


=> [#<ProtectedEnvironments::ApprovalRule:0x000000016296b730
  id: 12,
  protected_environment_id: 7,
  user_id: nil,
  group_id: nil,
  created_at: Mon, 29 Apr 2024 22:03:35.725249000 UTC +00:00,
  updated_at: Mon, 29 Apr 2024 22:03:35.725249000 UTC +00:00,
  access_level: 30,  # correct access_level
  required_approvals: 1, # correct required_approvals
  group_inheritance_type: 0>,
 #<ProtectedEnvironments::ApprovalRule:0x000000016296b370
  id: 13,
  protected_environment_id: 8,
  user_id: nil,
  group_id: nil,
  created_at: Mon, 29 Apr 2024 22:03:35.725249000 UTC +00:00,
  updated_at: Mon, 29 Apr 2024 22:03:35.725249000 UTC +00:00,
  access_level: 30,# correct access_level
  required_approvals: 2, # correct required_approvals
  group_inheritance_type: 0>]

Screenshots

Project m1

before migration, awaiting deployment approval

m1_-1-_awaiting-approval

before migration, approving deployment

m1_-2-_approving

before migration, deployment approved

m1_-3-_approved

after migration, new deployment awaiting approval

m1_-4-_after_migration

after migration, approving new deployment

m1_-5-_approving_after_migration

after migration, after approving new deployment and running manual job

m1_-6-_after_approval_manual_run

Project m2 (left with a deployment awaiting approval before running migration)

before migration awaiting approval

m2_-1-_awaiting-approval

after migration, awaiting approval

m2_-_after_migration_awaiting_approval

after migration, approving deployment that was pending before migration

m2_-_2_required_approvals

Edited by Hunter Stewart

Merge request reports