Add approvals post migration and spec
Coordination
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
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>]