Define CI Pipeline and DAST Profile relationship (!56821) · Merge requests · GitLab.org / GitLab

Philip Cunningham requested to merge philipcunningham-delivery-dast-site-profile-secret-variable-225406 into master Mar 17, 2021

What?

this merge request adds the ability to deliver secrets for dast on-demand scans via the introduction of a new table (dast_profiles_pipelines).

this merge request started as !54397 (closed) but was separated out to make review easier.

Why?

as part of the work on dast on-demand scans, we want to give users the ability to specify login credentials for their on-demand scans in order to perform authenticated scans.

during security review we identified that ci_variables seemed like the correct place to store this data but that one of the risks associated was that sensitive ci_variables could end up being sent to jobs that should not have access to them (e.g. if someone were set the environment scope incorrectly).

following discussion in review on this merge request, we opted to move away from using the ci_variables and simplify the solution by introducing a new table and extending Ci::Contextable.

Related Issue(s)

#225406 (closed)

Related Merge Request(s)

proof of concept: !47943 (closed)
parent: !54397 (closed)

Database

Migrations

% rails db:migrate:up VERSION=20210317035357 && rails db:migrate:up VERSION=20210322063407 && rails db:migrate:up VERSION=20210322063450                                                                                                                                                                                                                                        
== 20210317035357 CreateDastProfilesPipelines: migrating ======================
-- create_table(:dast_profiles_pipelines, {:primary_key=>[:dast_profile_id, :ci_pipeline_id], :comment=>"{\"owner\":\"group::dynamic analysis\",\"description\":\"Join table between DAST Profiles and CI Pipelines\"}"})
   -> 0.0145s
== 20210317035357 CreateDastProfilesPipelines: migrated (0.0147s) =============

== 20210322063407 AddDastProfileIdFkToDastProfilesPipelines: migrating ========
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:dast_profiles_pipelines)
   -> 0.0027s
-- execute("ALTER TABLE dast_profiles_pipelines\nADD CONSTRAINT fk_cc206a8c13\nFOREIGN KEY (dast_profile_id)\nREFERENCES dast_profiles (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0050s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- execute("ALTER TABLE dast_profiles_pipelines VALIDATE CONSTRAINT fk_cc206a8c13;")
   -> 0.0016s
-- execute("RESET ALL")
   -> 0.0007s
== 20210322063407 AddDastProfileIdFkToDastProfilesPipelines: migrated (0.0160s)

== 20210322063450 AddCiPipelineIdFkToDastProfilesPipelines: migrating =========
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:dast_profiles_pipelines)
   -> 0.0033s
-- execute("ALTER TABLE dast_profiles_pipelines\nADD CONSTRAINT fk_a60cad829d\nFOREIGN KEY (ci_pipeline_id)\nREFERENCES ci_pipelines (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0031s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- execute("ALTER TABLE dast_profiles_pipelines VALIDATE CONSTRAINT fk_a60cad829d;")
   -> 0.0025s
-- execute("RESET ALL")
   -> 0.0007s
== 20210322063450 AddCiPipelineIdFkToDastProfilesPipelines: migrated (0.0161s)

% rails db:migrate:down VERSION=20210322063450 && rails db:migrate:down VERSION=20210322063407 && rails db:migrate:down VERSION=20210317035357
== 20210322063450 AddCiPipelineIdFkToDastProfilesPipelines: reverting =========
-- remove_foreign_key(:dast_profiles_pipelines, {:column=>:ci_pipeline_id})
   -> 0.0163s
== 20210322063450 AddCiPipelineIdFkToDastProfilesPipelines: reverted (0.0670s)

== 20210322063407 AddDastProfileIdFkToDastProfilesPipelines: reverting ========
-- remove_foreign_key(:dast_profiles_pipelines, {:column=>:dast_profile_id})
   -> 0.0046s
== 20210322063407 AddDastProfileIdFkToDastProfilesPipelines: reverted (0.0100s)

== 20210317035357 CreateDastProfilesPipelines: reverting ======================
-- drop_table(:dast_profiles_pipelines)
   -> 0.0039s
== 20210317035357 CreateDastProfilesPipelines: reverted (0.0040s) =============

Queries

Code

project = create(:project, shared_runners_enabled: false, group_runners_enabled: false)

runner = create(:ci_runner, :project, projects: [project])

dast_profile = create(:dast_profile, project: project)

pipeline = create(:ci_pipeline, project: project, source: :ondemand_dast_scan, config_source: :parameter_source)
dast_profile.ci_pipelines << pipeline

create(:dast_site_profile_secret_variable, dast_site_profile: dast_profile.dast_site_profile)
create(:ci_build, pipeline: pipeline)

query_recorder = ActiveRecord::QueryRecorder.new do
result = Ci::RegisterJobService.new(runner).execute(session: { 'url' => 'https://example.com' })

result.valid?
result.build_json
end

puts query_recorder.log

Before

SELECT "tags"."id" FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 13 AND "taggings"."taggable_type" = 'Ci::Runner' AND "taggings"."context" = 'tags' /*application:test*/
SELECT "ci_builds"."id" FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND ("ci_builds"."status" IN ('pending')) AND "ci_builds"."runner_id" IS NULL AND "ci_builds"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "ci_runner_projects" ON "projects"."id" = "ci_runner_projects"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "ci_runner_projects"."runner_id" = 13 AND "projects"."pending_delete" = FALSE AND ("project_features"."builds_access_level" > 0 OR "project_features"."builds_access_level" IS NULL)) AND (NOT EXISTS (SELECT 1 FROM "taggings" WHERE "taggings"."taggable_type" = 'CommitStatus' AND "taggings"."context" = 'tags' AND (taggable_id = ci_builds.id) AND 1=1)) ORDER BY id ASC /*application:test*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."id" = 5 LIMIT 1 /*application:test*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 5 AND "taggings"."taggable_type" = 'CommitStatus' /*application:test*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 5 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test*/
SELECT "ci_builds_runner_session".* FROM "ci_builds_runner_session" WHERE "ci_builds_runner_session"."build_id" = 5 LIMIT 1 /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 2 LIMIT 1 /*application:test*/
SELECT "ci_builds_metadata".* FROM "ci_builds_metadata" WHERE "ci_builds_metadata"."build_id" = 5 LIMIT 1 /*application:test*/
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 2 LIMIT 1 /*application:test*/
SAVEPOINT active_record_2 /*application:test*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test*/
UPDATE "ci_builds" SET "status" = 'running', "runner_id" = 13, "started_at" = '2021-03-26 04:23:47.882792', "processed" = FALSE, "updated_at" = '2021-03-26 04:23:47.888932', "lock_version" = 1 WHERE "ci_builds"."id" = 5 AND "ci_builds"."lock_version" = 0 /*application:test*/
INSERT INTO "ci_builds_runner_session" ("build_id", "url") VALUES (5, 'https://example.com') RETURNING "id" /*application:test*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 5 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test*/
SELECT "deployments".* FROM "deployments" WHERE "deployments"."deployable_id" = 5 AND "deployments"."deployable_type" = 'CommitStatus' LIMIT 1 /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 2 LIMIT 1 /*application:test*/
SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."id" = 13 LIMIT 1 /*application:test*/
UPDATE "ci_builds_metadata" SET "timeout" = 3600, "timeout_source" = 2 WHERE "ci_builds_metadata"."id" = 5 /*application:test*/
RELEASE SAVEPOINT active_record_2 /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 2 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 4 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 4 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 4 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 4 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test*/
SELECT "deploy_tokens".* FROM "deploy_tokens" INNER JOIN "project_deploy_tokens" ON "deploy_tokens"."id" = "project_deploy_tokens"."deploy_token_id" WHERE "project_deploy_tokens"."project_id" = 2 AND (revoked = false AND expires_at >= NOW()) AND "deploy_tokens"."name" = 'gitlab-deploy-token' LIMIT 1 /*application:test*/
SELECT "repository_languages".* FROM "repository_languages" WHERE "repository_languages"."project_id" = 2 ORDER BY share DESC /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 4 AND "namespaces"."type" = 'Group' LIMIT 1 /*application:test*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 2 LIMIT 1 /*application:test*/
SELECT "project_auto_devops".* FROM "project_auto_devops" WHERE "project_auto_devops"."project_id" = 2 LIMIT 1 /*application:test*/
SELECT 1 AS one FROM "requirements" WHERE "requirements"."project_id" = 2 AND "requirements"."state" = 1 LIMIT 1 /*application:test*/
SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = 2 AND "merge_requests"."source_branch" = 'master' AND ("merge_requests"."state_id" IN (1)) ORDER BY "merge_requests"."id" DESC LIMIT 4 /*application:test*/
WITH RECURSIVE "clusters_cte" AS ((SELECT "clusters".*, NULL AS group_parent_id, 0 AS depth FROM "clusters" INNER JOIN "cluster_projects" ON "cluster_projects"."cluster_id" = "clusters"."id" INNER JOIN "projects" ON "projects"."id" = "cluster_projects"."project_id" WHERE "clusters"."management_project_id" = 2 AND "clusters"."cluster_type" = 3 AND "projects"."namespace_id" = 4)
UNION
(SELECT "clusters".*, "projects"."namespace_id" AS group_parent_id, 1 AS depth FROM "projects" LEFT OUTER JOIN "cluster_projects" ON "cluster_projects"."project_id" = "projects"."id" LEFT OUTER JOIN "clusters" ON "clusters"."id" = "cluster_projects"."cluster_id" WHERE "projects"."id" = 2)
UNION
(SELECT "clusters".*, "namespaces"."parent_id" AS group_parent_id, ("clusters_cte"."depth" + 1) FROM "clusters_cte", "namespaces" LEFT OUTER JOIN cluster_groups ON cluster_groups.group_id = namespaces.id LEFT OUTER JOIN clusters ON cluster_groups.cluster_id = clusters.id WHERE "namespaces"."id" = "clusters_cte"."group_parent_id")) SELECT "clusters".* FROM "clusters_cte" "clusters" WHERE (clusters.id IS NOT NULL) AND "clusters"."enabled" = TRUE AND "clusters"."environment_scope" = '*' ORDER BY (CASE clusters.management_project_id
  WHEN 2 THEN 0
  ELSE depth
END) ASC
 LIMIT 1 /*application:test*/
SELECT "clusters".* FROM "clusters" WHERE "clusters"."cluster_type" = 1 AND "clusters"."enabled" = TRUE AND "clusters"."environment_scope" = '*' ORDER BY "clusters"."id" ASC LIMIT 1 /*application:test*/
SELECT "ci_freeze_periods".* FROM "ci_freeze_periods" WHERE "ci_freeze_periods"."project_id" = 2 ORDER BY "ci_freeze_periods"."created_at" ASC /*application:test*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 13 AND "taggings"."taggable_type" = 'Ci::Runner' /*application:test*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 13 AND "taggings"."taggable_type" = 'Ci::Runner' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 2 AND ("ci_builds"."retried" = FALSE OR "ci_builds"."retried" IS NULL) AND (stage_idx < 0) /*application:test*/
SELECT "ci_instance_variables".* FROM "ci_instance_variables" /*application:test*/
SELECT "ci_variables".* FROM "ci_variables" WHERE "ci_variables"."project_id" = 2 AND "ci_variables"."environment_scope" = '*' /*application:test*/
SELECT "ci_pipeline_variables".* FROM "ci_pipeline_variables" WHERE "ci_pipeline_variables"."pipeline_id" = 2 /*application:test*/
SELECT "ci_job_variables".* FROM "ci_job_variables" WHERE "ci_job_variables"."job_id" = 5 /*application:test*/
SELECT "dast_profiles".* FROM "dast_profiles" INNER JOIN "dast_profiles_pipelines" ON "dast_profiles"."id" = "dast_profiles_pipelines"."dast_profile_id" WHERE "dast_profiles_pipelines"."ci_pipeline_id" = 2 LIMIT 1 /*application:test*/
SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" INNER JOIN "dast_site_profiles" ON "dast_site_profile_secret_variables"."dast_site_profile_id" = "dast_site_profiles"."id" WHERE "dast_site_profiles"."id" = 1 /*application:test*/
SELECT "project_ci_cd_settings".* FROM "project_ci_cd_settings" WHERE "project_ci_cd_settings"."project_id" = 2 LIMIT 1 /*application:test*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 2 AND ("ci_builds"."retried" = FALSE OR "ci_builds"."retried" IS NULL) AND (stage_idx < 0) /*application:test*/

After

SELECT "tags"."id" FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'Ci::Runner' AND "taggings"."context" = 'tags' /*application:test*/
SELECT "ci_builds"."id" FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND ("ci_builds"."status" IN ('pending')) AND "ci_builds"."runner_id" IS NULL AND "ci_builds"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN "ci_runner_projects" ON "projects"."id" = "ci_runner_projects"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "ci_runner_projects"."runner_id" = 1 AND "projects"."pending_delete" = FALSE AND ("project_features"."builds_access_level" > 0 OR "project_features"."builds_access_level" IS NULL)) AND (NOT EXISTS (SELECT 1 FROM "taggings" WHERE "taggings"."taggable_type" = 'CommitStatus' AND "taggings"."context" = 'tags' AND (taggable_id = ci_builds.id) AND 1=1)) ORDER BY id ASC /*application:test*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."id" = 2 LIMIT 1 /*application:test*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 2 AND "taggings"."taggable_type" = 'CommitStatus' /*application:test*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 2 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test*/
SELECT "ci_builds_runner_session".* FROM "ci_builds_runner_session" WHERE "ci_builds_runner_session"."build_id" = 2 LIMIT 1 /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 2 LIMIT 1 /*application:test*/
SELECT "ci_builds_metadata".* FROM "ci_builds_metadata" WHERE "ci_builds_metadata"."build_id" = 2 LIMIT 1 /*application:test*/
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 2 LIMIT 1 /*application:test*/
SAVEPOINT active_record_2 /*application:test*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test*/
UPDATE "ci_builds" SET "status" = 'running', "runner_id" = 1, "started_at" = '2021-03-26 04:27:11.335162', "processed" = FALSE, "updated_at" = '2021-03-26 04:27:11.343080', "lock_version" = 1 WHERE "ci_builds"."id" = 2 AND "ci_builds"."lock_version" = 0 /*application:test*/
INSERT INTO "ci_builds_runner_session" ("build_id", "url") VALUES (2, 'https://example.com') RETURNING "id" /*application:test*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 2 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test*/
SELECT "deployments".* FROM "deployments" WHERE "deployments"."deployable_id" = 2 AND "deployments"."deployable_type" = 'CommitStatus' LIMIT 1 /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 2 LIMIT 1 /*application:test*/
SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."id" = 1 LIMIT 1 /*application:test*/
UPDATE "ci_builds_metadata" SET "timeout" = 3600, "timeout_source" = 2 WHERE "ci_builds_metadata"."id" = 2 /*application:test*/
RELEASE SAVEPOINT active_record_2 /*application:test*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 2 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 4 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 4 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 4 LIMIT 1 /*application:test*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 4 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test*/
SELECT "deploy_tokens".* FROM "deploy_tokens" INNER JOIN "project_deploy_tokens" ON "deploy_tokens"."id" = "project_deploy_tokens"."deploy_token_id" WHERE "project_deploy_tokens"."project_id" = 2 AND (revoked = false AND expires_at >= NOW()) AND "deploy_tokens"."name" = 'gitlab-deploy-token' LIMIT 1 /*application:test*/
SELECT "repository_languages".* FROM "repository_languages" WHERE "repository_languages"."project_id" = 2 ORDER BY share DESC /*application:test*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 4 AND "namespaces"."type" = 'Group' LIMIT 1 /*application:test*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 2 LIMIT 1 /*application:test*/
SELECT "project_auto_devops".* FROM "project_auto_devops" WHERE "project_auto_devops"."project_id" = 2 LIMIT 1 /*application:test*/
SELECT 1 AS one FROM "requirements" WHERE "requirements"."project_id" = 2 AND "requirements"."state" = 1 LIMIT 1 /*application:test*/
SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = 2 AND "merge_requests"."source_branch" = 'master' AND ("merge_requests"."state_id" IN (1)) ORDER BY "merge_requests"."id" DESC LIMIT 4 /*application:test*/
WITH RECURSIVE "clusters_cte" AS ((SELECT "clusters".*, NULL AS group_parent_id, 0 AS depth FROM "clusters" INNER JOIN "cluster_projects" ON "cluster_projects"."cluster_id" = "clusters"."id" INNER JOIN "projects" ON "projects"."id" = "cluster_projects"."project_id" WHERE "clusters"."management_project_id" = 2 AND "clusters"."cluster_type" = 3 AND "projects"."namespace_id" = 4)
UNION
(SELECT "clusters".*, "projects"."namespace_id" AS group_parent_id, 1 AS depth FROM "projects" LEFT OUTER JOIN "cluster_projects" ON "cluster_projects"."project_id" = "projects"."id" LEFT OUTER JOIN "clusters" ON "clusters"."id" = "cluster_projects"."cluster_id" WHERE "projects"."id" = 2)
UNION
(SELECT "clusters".*, "namespaces"."parent_id" AS group_parent_id, ("clusters_cte"."depth" + 1) FROM "clusters_cte", "namespaces" LEFT OUTER JOIN cluster_groups ON cluster_groups.group_id = namespaces.id LEFT OUTER JOIN clusters ON cluster_groups.cluster_id = clusters.id WHERE "namespaces"."id" = "clusters_cte"."group_parent_id")) SELECT "clusters".* FROM "clusters_cte" "clusters" WHERE (clusters.id IS NOT NULL) AND "clusters"."enabled" = TRUE AND "clusters"."environment_scope" = '*' ORDER BY (CASE clusters.management_project_id
  WHEN 2 THEN 0
  ELSE depth
END) ASC
 LIMIT 1 /*application:test*/
SELECT "clusters".* FROM "clusters" WHERE "clusters"."cluster_type" = 1 AND "clusters"."enabled" = TRUE AND "clusters"."environment_scope" = '*' ORDER BY "clusters"."id" ASC LIMIT 1 /*application:test*/
SELECT "ci_freeze_periods".* FROM "ci_freeze_periods" WHERE "ci_freeze_periods"."project_id" = 2 ORDER BY "ci_freeze_periods"."created_at" ASC /*application:test*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'Ci::Runner' /*application:test*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'Ci::Runner' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 2 AND ("ci_builds"."retried" = FALSE OR "ci_builds"."retried" IS NULL) AND (stage_idx < 0) /*application:test*/
SELECT "ci_instance_variables".* FROM "ci_instance_variables" /*application:test*/
SELECT "ci_variables".* FROM "ci_variables" WHERE "ci_variables"."project_id" = 2 AND "ci_variables"."environment_scope" = '*' /*application:test*/
SELECT "ci_pipeline_variables".* FROM "ci_pipeline_variables" WHERE "ci_pipeline_variables"."pipeline_id" = 2 /*application:test*/
SELECT "ci_job_variables".* FROM "ci_job_variables" WHERE "ci_job_variables"."job_id" = 2 /*application:test*/
SELECT "dast_profiles".* FROM "dast_profiles" INNER JOIN "dast_profiles_pipelines" ON "dast_profiles"."id" = "dast_profiles_pipelines"."dast_profile_id" WHERE "dast_profiles_pipelines"."ci_pipeline_id" = 2 LIMIT 1 /*application:test*/
SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" INNER JOIN "dast_site_profiles" ON "dast_site_profile_secret_variables"."dast_site_profile_id" = "dast_site_profiles"."id" WHERE "dast_site_profiles"."id" = 1 /*application:test*/
SELECT "project_ci_cd_settings".* FROM "project_ci_cd_settings" WHERE "project_ci_cd_settings"."project_id" = 2 LIMIT 1 /*application:test*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 2 AND ("ci_builds"."retried" = FALSE OR "ci_builds"."retried" IS NULL) AND (stage_idx < 0) /*application:test*/

Delta

additional queries:

SELECT "dast_profiles".* FROM "dast_profiles" INNER JOIN "dast_profiles_pipelines" ON "dast_profiles"."id" = "dast_profiles_pipelines"."dast_profile_id" WHERE "dast_profiles_pipelines"."ci_pipeline_id" = 2 LIMIT 1
SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" INNER JOIN "dast_site_profiles" ON "dast_site_profile_secret_variables"."dast_site_profile_id" = "dast_site_profiles"."id" WHERE "dast_site_profiles"."id" = 1

Analysis

SELECT "dast_profiles".* FROM "dast_profiles" INNER JOIN "dast_profiles_pipelines" ON "dast_profiles"."id" = "dast_profiles_pipelines"."dast_profile_id" WHERE "dast_profiles_pipelines"."ci_pipeline_id" = 2 LIMIT 1

Limit  (cost=0.30..4.34 rows=1 width=144) (actual time=0.014..0.014 rows=1 loops=1)
  ->  Nested Loop  (cost=0.30..4.34 rows=1 width=144) (actual time=0.013..0.014 rows=1 loops=1)
        ->  Index Scan using index_dast_profiles_pipelines_on_ci_pipeline_id on dast_profiles_pipelines  (cost=0.15..2.17 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1)
              Index Cond: (ci_pipeline_id = 2)
        ->  Index Scan using dast_profiles_pkey on dast_profiles  (cost=0.15..2.17 rows=1 width=144) (actual time=0.003..0.003 rows=1 loops=1)
              Index Cond: (id = dast_profiles_pipelines.dast_profile_id)

Planning Time: 0.136 ms
Execution Time: 0.032 ms

https://explain.depesz.com/s/Y6YA

SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" INNER JOIN "dast_site_profiles" ON "dast_site_profile_secret_variables"."dast_site_profile_id" = "dast_site_profiles"."id" WHERE "dast_site_profiles"."id" = 1

Nested Loop  (cost=0.29..6.40 rows=3 width=130) (actual time=0.014..0.015 rows=1 loops=1)
  ->  Index Only Scan using dast_site_profiles_pkey on dast_site_profiles  (cost=0.15..2.17 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)
        Index Cond: (id = 1)
        Heap Fetches: 1
  ->  Index Scan using index_site_profile_secret_variables_on_site_profile_id_and_key on dast_site_profile_secret_variables  (cost=0.15..4.20 rows=3 width=130) (actual time=0.003..0.003 rows=1 loops=1)
        Index Cond: (dast_site_profile_id = 1)

Planning Time: 0.092 ms
Execution Time: 0.031 ms

https://explain.depesz.com/s/eFKY

Screenshots

what the build log looks like when a dast_site_profile_secret_variable is provided (HELLO_WORLD_BASE64):

^ manual quality assurance through creating a dast_profile and creating associated dast_site_profile_secret_variable in the console.

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because _____.
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

Edited Mar 29, 2021 by Mayra Cabrera

Define CI Pipeline and DAST Profile relationship