Reduce queries for the expose security dashboard check
What does this MR do and why?
Describe in detail what your merge request does and why.
This Merge Request aims to reduce the number of queries to check if the security dashboard should be exposed as suggested on issue #225250 (closed).
The problem
PipelinePresenter#expose_security_dashboard?
executes batch_lookup_report_artifact_for_file_type
per file type, making at least 8 SQL queries so far (plus the ones needed for authorization).
Doing eight queries to get a boolean response it's a bit over-engineering and can lead to ~performance problems. It'd be better to get all the information in a single query
The changes implemented on this Merge Request
We already have a query to batch load the latest reports for each CI job artifact
# This batch loads the latest reports for each CI job artifact
# type (e.g. sast, dast, etc.) in a single SQL query to eliminate
# the need to do N different `job_artifacts.where(file_type:
# X).last` calls.
#
# Return a hash of file type => array of 1 job artifact
def latest_report_artifacts
but the batch_lookup_report_artifact_for_file_type
method checks one file_type at time.
This merge request:
- adds a new method that receives a list of
file_types
- Filters the results of the
latest_report_artifacts
query using the list of file_types. - Updates the pipeline_presenter to query all file_types in one request.
Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
Query count comparison
Steps to compare the old code with the new one
- Add a temporary method with the old code:
def old_expose_security_dashboard?
return false unless can?(current_user, :read_security_resource, pipeline.project)
Ci::JobArtifact::SECURITY_REPORT_FILE_TYPES.any? { |file_type| batch_lookup_report_artifact_for_file_type(file_type.to_sym) }
end
- Add a test, with all features available, comparing the count of the old method and the new one.
context 'when all features are available' do
before do
stub_licensed_features(dependency_scanning: true, secret_detection: true, sast: true, container_scanning: true,
cluster_image_scanning:true, dast: true, coverage_fuzzing:true, api_fuzzing:true,
license_scanning: true, security_dashboard: true)
end
it 'reduce query count' do
presenter.old_expose_security_dashboard?
new_version_count = ActiveRecord::QueryRecorder.new(skip_cached: false) { subject }.count
old_version_count = ActiveRecord::QueryRecorder.new(skip_cached: false) { presenter.old_expose_security_dashboard? }.count
expect(new_version_count).to be < old_version_count
end
end
All features should be available in the project to cause the issue.
In the previous code the method batch_lookup_report_artifact_for_file_type
was called for each file type.
However, it would only invoke the method latest_report_artifacts
, triggering a database call if the feature is available on the project.
def batch_lookup_report_artifact_for_file_type(file_type)
return unless available_licensed_report_type?(file_type)
super
end
def available_licensed_report_type?(file_type)
feature_names = REPORT_LICENSED_FEATURES.fetch(file_type)
feature_names.nil? || feature_names.any? { |feature| project.feature_available?(feature) }
end
Test output:
From: /Users/marcosrocha/gitlab-development-kit/gitlab/ee/spec/presenters/ci/pipeline_presenter_spec.rb:78 :
73:
74: new_version_count = ActiveRecord::QueryRecorder.new(skip_cached: false) { subject }.count
75:
76: old_version_count = ActiveRecord::QueryRecorder.new(skip_cached: false) { presenter.old_expose_security_dashboard? }.count
77: binding.pry
=> 78: expect(new_version_count).to be < old_version_count
79: end
80:
81: end
82:
83: context 'when features are disabled' do
[1] pry(#<RSpec::ExampleGroups::CiPipelinePresenter::ExposeSecurityDashboard::WithDeveloper::WhenAllFeaturesAreAvailable>)> old_version_count
=> 9
[2] pry(#<RSpec::ExampleGroups::CiPipelinePresenter::ExposeSecurityDashboard::WithDeveloper::WhenAllFeaturesAreAvailable>)> new_version_count
=> 2
Test log:
Project Load (0.7ms) SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_coverage_regex", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."emails_disabled", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id" FROM "projects" WHERE "projects"."id" = 19 LIMIT 1 /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ ee/app/presenters/ee/ci/pipeline_presenter.rb:16:in `old_expose_security_dashboard?'
Group Load (0.5ms) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 37 LIMIT 1 /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ ee/app/policies/ee/project_policy.rb:320:in `block (2 levels) in <module:ProjectPolicy>'
(0.2ms) SELECT MAX("project_authorizations"."access_level") AS maximum_access_level, "project_authorizations"."user_id" AS project_authorizations_user_id FROM "project_authorizations" WHERE "project_authorizations"."project_id" = 19 AND "project_authorizations"."user_id" = 38 GROUP BY "project_authorizations"."user_id" /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/project_team.rb:178:in `block in max_member_access_for_user_ids'
Ci::JobArtifact Load (4.4ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
Ci::JobArtifact Load (0.9ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
Ci::JobArtifact Load (1.0ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
Ci::JobArtifact Load (1.0ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
Ci::JobArtifact Load (0.8ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
Ci::JobArtifact Load (0.9ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
Ci::JobArtifact Load (0.9ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
Ci::JobArtifact Load (1.0ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 19 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
TRANSACTION (0.3ms) ROLLBACK TO SAVEPOINT active_record_1 /*application:test,correlation_id:0bf322b9f7200bed5e3e66d4d30969e3,db_config_name:main*/
↳ spec/spec_helper.rb:408:in `block (3 levels) in <top (required)>'
TRANSACTION (1.0ms) ROLLBACK /*application:test,db_config_name:main*/
↳ lib/gitlab/database.rb:312:in `rollback'
Project Load (0.6ms) SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_coverage_regex", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."emails_disabled", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id" FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:test,correlation_id:8eee71e4b365ff4ec6e24dfc5aed536e,db_config_name:main*/
↳ ee/app/presenters/ee/ci/pipeline_presenter.rb:10:in `expose_security_dashboard?'
Group Load (0.5ms) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 39 LIMIT 1 /*application:test,correlation_id:8eee71e4b365ff4ec6e24dfc5aed536e,db_config_name:main*/
↳ ee/app/policies/ee/project_policy.rb:320:in `block (2 levels) in <module:ProjectPolicy>'
(0.2ms) SELECT MAX("project_authorizations"."access_level") AS maximum_access_level, "project_authorizations"."user_id" AS project_authorizations_user_id FROM "project_authorizations" WHERE "project_authorizations"."project_id" = 20 AND "project_authorizations"."user_id" = 40 GROUP BY "project_authorizations"."user_id" /*application:test,correlation_id:8eee71e4b365ff4ec6e24dfc5aed536e,db_config_name:main*/
↳ app/models/project_team.rb:178:in `block in max_member_access_for_user_ids'
Ci::JobArtifact Load (7.9ms) SELECT "ci_job_artifacts".* FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."id" IN (SELECT max(ci_job_artifacts.id) as id FROM "ci_job_artifacts" INNER JOIN "ci_builds" ON "ci_job_artifacts"."job_id" = "ci_builds"."id" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."commit_id" = 20 AND "ci_job_artifacts"."file_type" IN (4, 5, 6, 7, 8, 9, 101, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27) GROUP BY "ci_job_artifacts"."file_type") /*application:test,correlation_id:8eee71e4b365ff4ec6e24dfc5aed536e,db_config_name:main*/
↳ app/models/ci/pipeline.rb:683:in `group_by'
TRANSACTION (0.3ms) ROLLBACK TO SAVEPOINT active_record_1 /*application:test,correlation_id:8eee71e4b365ff4ec6e24dfc5aed536e,db_config_name:main*/
↳ spec/spec_helper.rb:408:in `block (3 levels) in <top (required)>'
TRANSACTION (0.5ms) ROLLBACK /*application:test,db_config_name:main*/
↳ lib/gitlab/database.rb:312:in `rollback'
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.