Disable DAST joins in Ci::Build and Ci::Pipeline (!66709) · Merge requests · GitLab.org / GitLab

Philip Cunningham requested to merge philipcunningham-spike-out-dast-on-demand-ci-composition-336195 into master Jul 23, 2021

What does this MR do?

adds disabled_joins attributes behind a feature flag for dast associations in ci
adds new feature flags enabled by default to enable us to turn the functionality off if necessary

Related Issue(s)

Assess impact of CI Decomposition on DAST On-Demand

Depends On

Backport :disable_joins option from Rails main (!66400 (merged))

Queries

EE::Ci::Build#variables is where this functionality is used.

Looking up secret variables via `dast_profile` via `pipeline` association

Before

Ci::Build Load (4.7ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' ORDER BY "ci_builds"."id" DESC LIMIT 1 /*application:console,line:(pry):1:in `__pry__'*/
Ci::Pipeline Load (2.8ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 83 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:202:in `block in dast_on_demand_variables'*/
Dast::Profile Load (1.4ms)  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" = 83 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:205:in `block in dast_on_demand_variables'*/
User Load (5.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:209:in `block in dast_on_demand_variables'*/
DastSiteProfile Load (0.7ms)  SELECT "dast_site_profiles".* FROM "dast_site_profiles" WHERE "dast_site_profiles"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/dast/profile.rb:31:in `secret_ci_variables'*/
Project Load (6.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/policies/dast_site_profile_policy.rb:4:in `block in <class:DastSiteProfilePolicy>'*/
Group Load (3.5ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/policies/ee/project_policy.rb:315:in `block (2 levels) in <module:ProjectPolicy>'*/
Dast::SiteProfileSecretVariable Load (0.6ms)  SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 1 /*application:console,line:/lib/gitlab/ci/variables/collection.rb:34:in `block in concat'*/

After

Ci::Build Load (4.7ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' ORDER BY "ci_builds"."id" DESC LIMIT 1 /*application:console,line:(pry):1:in `__pry__'*/
Ci::Pipeline Load (3.3ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 83 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:202:in `block in dast_on_demand_variables'*/
 (0.8ms)  SELECT "dast_profiles_pipelines"."dast_profile_id" FROM "dast_profiles_pipelines" WHERE "dast_profiles_pipelines"."ci_pipeline_id" = 83 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:122:in `block in last_scope_chain'*/
Dast::Profile Load (1.0ms)  SELECT "dast_profiles".* FROM "dast_profiles" WHERE "dast_profiles"."id" = 1 ORDER BY "dast_profiles"."id" ASC LIMIT 1 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:72:in `find_target'*/
User Load (4.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:209:in `block in dast_on_demand_variables'*/
DastSiteProfile Load (0.7ms)  SELECT "dast_site_profiles".* FROM "dast_site_profiles" WHERE "dast_site_profiles"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/dast/profile.rb:28:in `secret_ci_variables'*/
Project Load (6.9ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/policies/dast_site_profile_policy.rb:4:in `block in <class:DastSiteProfilePolicy>'*/
License Load (0.7ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100 /*application:console,line:/ee/app/models/license.rb:317:in `load_license'*/
ApplicationSetting Load (3.0ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1 /*application:console,line:/app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'*/
Group Load (4.7ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/policies/ee/project_policy.rb:315:in `block (2 levels) in <module:ProjectPolicy>'*/
Dast::SiteProfileSecretVariable Load (0.7ms)  SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 1 /*application:console,line:/lib/gitlab/ci/variables/collection.rb:34:in `block in concat'*/

Looking up secret variables via `dast_site_profile` via `pipeline` association

Before

Ci::Build Load (5.5ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' ORDER BY "ci_builds"."id" ASC LIMIT 1 /*application:console,line:(pry):1:in `__pry__'*/
Ci::Pipeline Load (2.6ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:202:in `block in dast_on_demand_variables'*/
Dast::Profile Load (1.4ms)  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:console,line:/ee/app/models/ee/ci/build.rb:205:in `block in dast_on_demand_variables'*/
DastSiteProfile Load (2.0ms)  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:console,line:/ee/app/models/ee/ci/build.rb:205:in `block in dast_on_demand_variables'*/
User Load (4.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:209:in `block in dast_on_demand_variables'*/
Project Load (6.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/policies/dast_site_profile_policy.rb:4:in `block in <class:DastSiteProfilePolicy>'*/
Group Load (3.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/policies/ee/project_policy.rb:315:in `block (2 levels) in <module:ProjectPolicy>'*/
Dast::SiteProfileSecretVariable Load (0.7ms)  SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 1 /*application:console,line:/lib/gitlab/ci/variables/collection.rb:34:in `block in concat'*/

After

Ci::Build Load (4.6ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' ORDER BY "ci_builds"."id" ASC LIMIT 1 /*application:console,line:(pry):1:in `__pry__'*/
Ci::Pipeline Load (2.9ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:202:in `block in dast_on_demand_variables'*/
 (0.7ms)  SELECT "dast_profiles_pipelines"."dast_profile_id" FROM "dast_profiles_pipelines" WHERE "dast_profiles_pipelines"."ci_pipeline_id" = 1 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:122:in `block in last_scope_chain'*/
 (0.8ms)  SELECT "dast_site_profiles_pipelines"."dast_site_profile_id" FROM "dast_site_profiles_pipelines" WHERE "dast_site_profiles_pipelines"."ci_pipeline_id" = 1 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:122:in `block in last_scope_chain'*/
DastSiteProfile Load (1.7ms)  SELECT "dast_site_profiles".* FROM "dast_site_profiles" WHERE "dast_site_profiles"."id" = 1 ORDER BY "dast_site_profiles"."id" ASC LIMIT 1 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:72:in `find_target'*/
User Load (4.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:209:in `block in dast_on_demand_variables'*/
Project Load (6.4ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/policies/dast_site_profile_policy.rb:4:in `block in <class:DastSiteProfilePolicy>'*/
Group Load (3.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/policies/ee/project_policy.rb:315:in `block (2 levels) in <module:ProjectPolicy>'*/
Dast::SiteProfileSecretVariable Load (0.7ms)  SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 1 /*application:console,line:/lib/gitlab/ci/variables/collection.rb:34:in `block in concat'*/

Looking up variables via `dast_site_profile` and `dast_scanner_profile` via `build` association

Before

Ci::Build Load (4.8ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' ORDER BY "ci_builds"."id" DESC LIMIT 1 /*application:console,line:(pry):1:in `__pry__'*/
Project Load (6.4ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:215:in `block in dast_configuration_variables'*/
Feature::FlipperFeature Load (2.3ms)  SELECT "features".* FROM "features" /*application:console,line:/lib/feature.rb:47:in `persisted_names'*/
Feature::FlipperGate Load (1.0ms)  SELECT "feature_gates".* FROM "feature_gates" WHERE "feature_gates"."feature_key" = 'dast_configuration_ui' /*application:console,line:/lib/feature.rb:84:in `enabled?'*/
Ci::BuildMetadata Load (1.9ms)  SELECT "ci_builds_metadata".* FROM "ci_builds_metadata" WHERE "ci_builds_metadata"."build_id" = 136 LIMIT 1 /*application:console,line:/app/models/concerns/ci/metadatable.rb:75:in `read_metadata_attribute'*/
DastSiteProfile Load (1.7ms)  SELECT "dast_site_profiles".* FROM "dast_site_profiles" INNER JOIN "dast_site_profiles_builds" ON "dast_site_profiles"."id" = "dast_site_profiles_builds"."dast_site_profile_id" WHERE "dast_site_profiles_builds"."ci_build_id" = 136 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:218:in `block in dast_configuration_variables'*/
DastSite Load (0.8ms)  SELECT "dast_sites".* FROM "dast_sites" WHERE "dast_sites"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/dast_site_profile.rb:41:in `ci_variables'*/
User Load (4.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:220:in `block in dast_configuration_variables'*/
Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/policies/dast_site_profile_policy.rb:4:in `block in <class:DastSiteProfilePolicy>'*/
Group Load (4.1ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/policies/ee/project_policy.rb:315:in `block (2 levels) in <module:ProjectPolicy>'*/
Dast::SiteProfileSecretVariable Load (1.0ms)  SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 1 /*application:console,line:/lib/gitlab/ci/variables/collection.rb:34:in `block in concat'*/
DastScannerProfile Load (2.7ms)  SELECT "dast_scanner_profiles".* FROM "dast_scanner_profiles" INNER JOIN "dast_scanner_profiles_builds" ON "dast_scanner_profiles"."id" = "dast_scanner_profiles_builds"."dast_scanner_profile_id" WHERE "dast_scanner_profiles_builds"."ci_build_id" = 136 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:223:in `block in dast_configuration_variables'*/

After

Ci::Build Load (4.6ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' ORDER BY "ci_builds"."id" DESC LIMIT 1 /*application:console,line:(pry):1:in `__pry__'*/
Project Load (6.4ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:215:in `block in dast_configuration_variables'*/
Ci::BuildMetadata Load (1.1ms)  SELECT "ci_builds_metadata".* FROM "ci_builds_metadata" WHERE "ci_builds_metadata"."build_id" = 136 LIMIT 1 /*application:console,line:/app/models/concerns/ci/metadatable.rb:75:in `read_metadata_attribute'*/
 (0.7ms)  SELECT "dast_site_profiles_builds"."dast_site_profile_id" FROM "dast_site_profiles_builds" WHERE "dast_site_profiles_builds"."ci_build_id" = 136 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:122:in `block in last_scope_chain'*/
DastSiteProfile Load (1.0ms)  SELECT "dast_site_profiles".* FROM "dast_site_profiles" WHERE "dast_site_profiles"."id" = 1 ORDER BY "dast_site_profiles"."id" ASC LIMIT 1 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:72:in `find_target'*/
DastSite Load (3.1ms)  SELECT "dast_sites".* FROM "dast_sites" WHERE "dast_sites"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/dast_site_profile.rb:35:in `ci_variables'*/
User Load (5.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/models/ee/ci/build.rb:220:in `block in dast_configuration_variables'*/
Project Load (0.9ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 9 LIMIT 1 /*application:console,line:/ee/app/policies/dast_site_profile_policy.rb:4:in `block in <class:DastSiteProfilePolicy>'*/
Group Load (3.6ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1 /*application:console,line:/ee/app/policies/ee/project_policy.rb:315:in `block (2 levels) in <module:ProjectPolicy>'*/
Dast::SiteProfileSecretVariable Load (0.8ms)  SELECT "dast_site_profile_secret_variables".* FROM "dast_site_profile_secret_variables" WHERE "dast_site_profile_secret_variables"."dast_site_profile_id" = 1 /*application:console,line:/lib/gitlab/ci/variables/collection.rb:34:in `block in concat'*/
 (0.6ms)  SELECT "dast_scanner_profiles_builds"."dast_scanner_profile_id" FROM "dast_scanner_profiles_builds" WHERE "dast_scanner_profiles_builds"."ci_build_id" = 136 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:122:in `block in last_scope_chain'*/
DastScannerProfile Load (1.0ms)  SELECT "dast_scanner_profiles".* FROM "dast_scanner_profiles" WHERE "dast_scanner_profiles"."id" = 1 ORDER BY "dast_scanner_profiles"."id" ASC LIMIT 1 /*application:console,line:/config/initializers/00_rails_disable_joins.rb:72:in `find_target'*/

Does this MR meet the acceptance criteria?

Conformity

I have included changelog trailers, or none are needed. (Does this MR need a changelog?)
I have added/updated documentation, or it's not needed. (Is documentation required?)
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?)
I have self-reviewed this MR per code review guidelines.
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines)
I have followed the style guides.
This change is backwards compatible across updates, or this does not apply.

Availability and Testing

I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.)
I have tested this MR in all supported browsers, or it's not needed.
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

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 Aug 05, 2021 by Philip Cunningham

Disable DAST joins in Ci::Build and Ci::Pipeline

What does this MR do?

Related Issue(s)

Depends On

Queries

Looking up secret variables via dast_profile via pipeline association

Before

After

Looking up secret variables via dast_site_profile via pipeline association

Before

After

Looking up variables via dast_site_profile and dast_scanner_profile via build association

Before

After

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Merge request reports

Looking up secret variables via `dast_profile` via `pipeline` association

Looking up secret variables via `dast_site_profile` via `pipeline` association

Looking up variables via `dast_site_profile` and `dast_scanner_profile` via `build` association