Support DAST secret delivery using DastSiteProfile (!60090) · Merge requests · GitLab.org / GitLab

Philip Cunningham requested to merge philipcunningham-investigate-secret-delivery-via-site-profile-328201 into master Apr 23, 2021

What does this MR do?

currently secret variables are made available to ci_builds via an association made between dast_profile and a ci_pipeline. this merge request temporarily amends this to make the association also possible via dast_site_profile until scan policies are moved from the repo to the database.

Why?

we discovered a point of contention in two features from two different groups that meant one feature would not work as expected. this is because scan policies composes dast_site_profiles and dast_scanner_profiles in the same way that dast_profiles do and because secret delivery is currently via the dast_profile. by allowing scan policies to specify a dast_site_profile secrets can still be made available.

Related Issue(s)

Related Merge Request(s)

!56821 (merged)

Database

Migrations

% rails db:migrate:up VERSION=20210423054022 && rails db:migrate:up VERSION=20210423054537 && rails db:migrate:up VERSION=20210423054846                                                                                                                                                                                                                                  130 
== 20210423054022 CreateDastSiteProfilesPipelines: migrating ==================
-- create_table(:dast_site_profiles_pipelines, {:primary_key=>[:dast_site_profile_id, :ci_pipeline_id], :comment=>"{\"owner\":\"group::dynamic analysis\",\"description\":\"Join table between DAST Site Profiles and CI Pipelines\"}"})
   -> 0.0095s
== 20210423054022 CreateDastSiteProfilesPipelines: migrated (0.0097s) =========

== 20210423054537 AddDastSiteProfileIdFkToDastSiteProfilesPipelines: migrating
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:dast_site_profiles_pipelines)
   -> 0.0030s
-- execute("ALTER TABLE dast_site_profiles_pipelines\nADD CONSTRAINT fk_cf05cf8fe1\nFOREIGN KEY (dast_site_profile_id)\nREFERENCES dast_site_profiles (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0047s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- execute("ALTER TABLE dast_site_profiles_pipelines VALIDATE CONSTRAINT fk_cf05cf8fe1;")
   -> 0.0070s
-- execute("RESET ALL")
   -> 0.0007s
== 20210423054537 AddDastSiteProfileIdFkToDastSiteProfilesPipelines: migrated (0.0225s)

== 20210423054846 AddCiPipelineIdFkToDastSiteProfilesPipelines: migrating =====
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:dast_site_profiles_pipelines)
   -> 0.0029s
-- execute("ALTER TABLE dast_site_profiles_pipelines\nADD CONSTRAINT fk_53849b0ad5\nFOREIGN KEY (ci_pipeline_id)\nREFERENCES ci_pipelines (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0042s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- execute("ALTER TABLE dast_site_profiles_pipelines VALIDATE CONSTRAINT fk_53849b0ad5;")
   -> 0.0096s
-- execute("RESET ALL")
   -> 0.0007s
== 20210423054846 AddCiPipelineIdFkToDastSiteProfilesPipelines: migrated (0.0244s)

% rails db:migrate:down VERSION=20210423054846 && rails db:migrate:down VERSION=20210423054537 && rails db:migrate:down VERSION=20210423054022                                                                                                                                                                                                                              137 
== 20210423054846 AddCiPipelineIdFkToDastSiteProfilesPipelines: reverting =====
-- remove_foreign_key(:dast_site_profiles_pipelines, {:column=>:ci_pipeline_id})
   -> 0.0106s
== 20210423054846 AddCiPipelineIdFkToDastSiteProfilesPipelines: reverted (0.0171s)

== 20210423054537 AddDastSiteProfileIdFkToDastSiteProfilesPipelines: reverting
-- remove_foreign_key(:dast_site_profiles_pipelines, {:column=>:dast_site_profile_id})
   -> 0.0048s
== 20210423054537 AddDastSiteProfileIdFkToDastSiteProfilesPipelines: reverted (0.0105s)

== 20210423054022 CreateDastSiteProfilesPipelines: reverting ==================
-- drop_table(:dast_site_profiles_pipelines)
   -> 0.0038s
== 20210423054022 CreateDastSiteProfilesPipelines: reverted (0.0039s) =========

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)
dast_site_profile = dast_profile.dast_site_profile

pipeline = create(:ci_pipeline, project: project, source: :ondemand_dast_scan, config_source: :parameter_source)
dast_site_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" = 1 AND "taggings"."taggable_type" = 'Ci::Runner' AND "taggings"."context" = 'tags' /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
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,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'CommitStatus' /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_builds_runner_session".* FROM "ci_builds_runner_session" WHERE "ci_builds_runner_session"."build_id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_builds_metadata".* FROM "ci_builds_metadata" WHERE "ci_builds_metadata"."build_id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SAVEPOINT active_record_1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
UPDATE "ci_builds" SET "status" = 'running', "runner_id" = 1, "started_at" = '2021-04-27 02:13:48.432783', "processed" = FALSE, "updated_at" = '2021-04-27 02:13:48.441068', "lock_version" = 1 WHERE "ci_builds"."id" = 1 AND "ci_builds"."lock_version" = 0 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
INSERT INTO "ci_builds_runner_session" ("build_id", "url") VALUES (1, 'https://example.com') RETURNING "id" /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "deployments".* FROM "deployments" WHERE "deployments"."deployable_id" = 1 AND "deployments"."deployable_type" = 'CommitStatus' LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
UPDATE "ci_builds_metadata" SET "timeout" = 3600, "timeout_source" = 2 WHERE "ci_builds_metadata"."id" = 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
RELEASE SAVEPOINT active_record_1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
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" = 1 AND (revoked = false AND expires_at >= NOW()) AND "deploy_tokens"."name" = 'gitlab-deploy-token' LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "repository_languages".* FROM "repository_languages" WHERE "repository_languages"."project_id" = 1 ORDER BY share DESC /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 AND "namespaces"."type" = 'Group' LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "project_auto_devops".* FROM "project_auto_devops" WHERE "project_auto_devops"."project_id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT 1 AS one FROM "requirements" WHERE "requirements"."project_id" = 1 AND "requirements"."state" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = 1 AND "merge_requests"."source_branch" = 'master' AND ("merge_requests"."state_id" IN (1)) ORDER BY "merge_requests"."id" DESC LIMIT 4 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
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" = 1 AND "clusters"."cluster_type" = 3 AND "projects"."namespace_id" = 1)
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" = 1)
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 1 THEN 0
  ELSE depth
END) ASC
 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
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,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_freeze_periods".* FROM "ci_freeze_periods" WHERE "ci_freeze_periods"."project_id" = 1 ORDER BY "ci_freeze_periods"."created_at" ASC /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'Ci::Runner' /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
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,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 1 AND ("ci_builds"."retried" = FALSE OR "ci_builds"."retried" IS NULL) AND (stage_idx < 0) /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_instance_variables".* FROM "ci_instance_variables" /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_variables".* FROM "ci_variables" WHERE "ci_variables"."project_id" = 1 AND "ci_variables"."environment_scope" = '*' /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_pipeline_variables".* FROM "ci_pipeline_variables" WHERE "ci_pipeline_variables"."pipeline_id" = 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "ci_job_variables".* FROM "ci_job_variables" WHERE "ci_job_variables"."job_id" = 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
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" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
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,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/
SELECT "project_ci_cd_settings".* FROM "project_ci_cd_settings" WHERE "project_ci_cd_settings"."project_id" = 1 LIMIT 1 /*application:test,correlation_id:a1e5f5f0dc4dba4b52a227e9b4c89fbc*/

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,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
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,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'CommitStatus' /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_builds_runner_session".* FROM "ci_builds_runner_session" WHERE "ci_builds_runner_session"."build_id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_builds_metadata".* FROM "ci_builds_metadata" WHERE "ci_builds_metadata"."build_id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SAVEPOINT active_record_1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
UPDATE "ci_builds" SET "status" = 'running', "runner_id" = 1, "started_at" = '2021-04-27 02:18:23.922521', "processed" = FALSE, "updated_at" = '2021-04-27 02:18:23.931103', "lock_version" = 1 WHERE "ci_builds"."id" = 1 AND "ci_builds"."lock_version" = 0 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
INSERT INTO "ci_builds_runner_session" ("build_id", "url") VALUES (1, 'https://example.com') RETURNING "id" /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "tags".* FROM "tags" INNER JOIN "taggings" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'CommitStatus' AND (taggings.context = 'tags' AND taggings.tagger_id IS NULL) /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "deployments".* FROM "deployments" WHERE "deployments"."deployable_id" = 1 AND "deployments"."deployable_type" = 'CommitStatus' LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
UPDATE "ci_builds_metadata" SET "timeout" = 3600, "timeout_source" = 2 WHERE "ci_builds_metadata"."id" = 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
RELEASE SAVEPOINT active_record_1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
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" = 1 AND (revoked = false AND expires_at >= NOW()) AND "deploy_tokens"."name" = 'gitlab-deploy-token' LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "repository_languages".* FROM "repository_languages" WHERE "repository_languages"."project_id" = 1 ORDER BY share DESC /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 AND "namespaces"."type" = 'Group' LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "project_auto_devops".* FROM "project_auto_devops" WHERE "project_auto_devops"."project_id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT 1 AS one FROM "requirements" WHERE "requirements"."project_id" = 1 AND "requirements"."state" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."source_project_id" = 1 AND "merge_requests"."source_branch" = 'master' AND ("merge_requests"."state_id" IN (1)) ORDER BY "merge_requests"."id" DESC LIMIT 4 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
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" = 1 AND "clusters"."cluster_type" = 3 AND "projects"."namespace_id" = 1)
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" = 1)
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 1 THEN 0
  ELSE depth
END) ASC
 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
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,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_freeze_periods".* FROM "ci_freeze_periods" WHERE "ci_freeze_periods"."project_id" = 1 ORDER BY "ci_freeze_periods"."created_at" ASC /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_id" = 1 AND "taggings"."taggable_type" = 'Ci::Runner' /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
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,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 1 AND ("ci_builds"."retried" = FALSE OR "ci_builds"."retried" IS NULL) AND (stage_idx < 0) /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_instance_variables".* FROM "ci_instance_variables" /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_variables".* FROM "ci_variables" WHERE "ci_variables"."project_id" = 1 AND "ci_variables"."environment_scope" = '*' /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_pipeline_variables".* FROM "ci_pipeline_variables" WHERE "ci_pipeline_variables"."pipeline_id" = 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "ci_job_variables".* FROM "ci_job_variables" WHERE "ci_job_variables"."job_id" = 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
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" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "dast_site_profiles".* FROM "dast_site_profiles" INNER JOIN "dast_site_profiles_pipelines" ON "dast_site_profiles"."id" = "dast_site_profiles_pipelines"."dast_site_profile_id" WHERE "dast_site_profiles_pipelines"."ci_pipeline_id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/
SELECT "project_ci_cd_settings".* FROM "project_ci_cd_settings" WHERE "project_ci_cd_settings"."project_id" = 1 LIMIT 1 /*application:test,correlation_id:385e63d6ae722e772add9b744b0d86c6*/

Delta

removed queries:

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

additional queries:

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

Limit  (cost=0.30..4.35 rows=1 width=235) (actual time=0.016..0.017 rows=1 loops=1)
  ->  Nested Loop  (cost=0.30..4.35 rows=1 width=235) (actual time=0.016..0.016 rows=1 loops=1)
        ->  Index Scan using index_dast_site_profiles_pipelines_on_ci_pipeline_id on dast_site_profiles_pipelines  (cost=0.15..2.17 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)
              Index Cond: (ci_pipeline_id = 1)
        ->  Index Scan using dast_site_profiles_pkey on dast_site_profiles  (cost=0.15..2.17 rows=1 width=235) (actual time=0.003..0.004 rows=1 loops=1)
              Index Cond: (id = dast_site_profiles_pipelines.dast_site_profile_id)
Planning Time: 0.142 ms
Execution Time: 0.039 ms

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

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.009..0.010 rows=1 loops=1)
  Index Cond: (dast_site_profile_id = 1)
Planning Time: 0.054 ms
Execution Time: 0.022 ms

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

Screenshots

manual quality assurance through creating a dast_site_profile and creating associated dast_site_profile_secret_variable in the console (HELLO_WORLD_BASE64).

[29] pry(main)> pipeline = Ci::Pipeline.last
  Ci::Pipeline Load (0.6ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" ORDER BY "ci_pipelines"."id" DESC LIMIT 1 /*application:console,line:(pry):29:in `<main>'*/
=> #<Ci::Pipeline:0x00007fb101771d48
 id: 109,
 ref: "master",
 sha: "2cbcbd18543f35d8f8f2932790d87e8b89d212f7",
 before_sha: "0000000000000000000000000000000000000000",
 created_at: Tue, 27 Apr 2021 03:33:44 UTC +00:00,
 updated_at: Tue, 27 Apr 2021 03:33:53 UTC +00:00,
 tag: false,
 yaml_errors: nil,
 committed_at: nil,
 project_id: 20,
 status: "success",
 started_at: Tue, 27 Apr 2021 03:33:47 UTC +00:00,
 finished_at: Tue, 27 Apr 2021 03:33:53 UTC +00:00,
 duration: 6,
 user_id: 1,
 lock_version: 3,
 auto_canceled_by_id: nil,
 pipeline_schedule_id: nil,
 source: "ondemand_dast_scan",
 config_source: "parameter_source",
 protected: true,
 failure_reason: nil,
 iid: 37,
 merge_request_id: nil,
 source_sha: nil,
 target_sha: nil,
 external_pull_request_id: nil,
 ci_ref_id: 1,
 locked: "artifacts_locked">
[30] pry(main)> site_profile = pipeline.dast_site_profile
  DastSiteProfile Load (0.5ms)  SELECT "dast_site_profiles".* FROM "dast_site_profiles" INNER JOIN "dast_site_profiles_pipelines" ON "dast_site_profiles"."id" = "dast_site_profiles_pipelines"."dast_site_profile_id" WHERE "dast_site_profiles_pipelines"."ci_pipeline_id" = 109 LIMIT 1 /*application:console,line:(pry):30:in `<main>'*/
=> #<DastSiteProfile:0x00007fb104084a28
 id: 9,
 project_id: 20,
 dast_site_id: 1,
 created_at: Wed, 11 Nov 2020 12:37:56 UTC +00:00,
 updated_at: Tue, 27 Apr 2021 03:27:39 UTC +00:00,
 name: "Passive",
 variable_secret_key_id: nil,
 excluded_urls: [],
 auth_enabled: false,
 auth_url: nil,
 auth_username_field: nil,
 auth_password_field: nil,
 auth_username: nil,
 target_type: "website">
[31] pry(main)> site_profile.secret_variables.map(&:key)
  Dast::SiteProfileSecretVariable Load (0.4ms)  SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 9 /*application:console,line:(pry):31:in `map'*/
=> ["HELLO_WORLD_BASE64"]

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a 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

Edited May 18, 2021 by Philip Cunningham

Support DAST secret delivery using DastSiteProfile