Skip to content

Fixing filtering of projects by compliance frameworks

Hitesh Raghuvanshi requested to merge 473696-fix-framework-filters into master

What does this MR do and why?

This MR intends to fix a bug in recently introduced filtering of project by multiple compliance frameworks in !158229 (merged). The bug was that if projects are being filtered with multiple compliance frameworks then the filter operation was an or operation, it was returning all the projects which have at least one of the frameworks in the filters but we need to list down all those projects which have all those frameworks associated with them.

The changes here are similar to what we do for filtering issues by multiple assignees, relevant code is present at https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/finders/issuables/assignee_filter.rb#L49 and https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/models/concerns/issuable.rb#L122.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Database

Query 1: Filter projects with frameworks

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/30675/commands/95233

Click to expand query 
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_last_update_at", 
  "projects"."mirror_last_successful_update_at", 
  "projects"."mirror_user_id", 
  "projects"."shared_runners_enabled", 
  "projects"."runners_token", 
  "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"."max_pages_size", 
  "projects"."max_artifacts_size", 
  "projects"."pull_mirror_branch_prefix", 
  "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", 
  "projects"."hidden", 
  "projects"."organization_id" 
FROM 
  "projects" 
WHERE 
  "projects"."namespace_id" IN (
    SELECT 
      "namespaces"."id" 
    FROM 
      UNNEST(
        COALESCE(
          (
            SELECT 
              ids 
            FROM 
              (
                SELECT 
                  "namespace_descendants"."self_and_descendant_group_ids" AS ids 
                FROM 
                  "namespace_descendants" 
                WHERE 
                  "namespace_descendants"."outdated_at" IS NULL 
                  AND "namespace_descendants"."namespace_id" = 9970
              ) cached_query
          ), 
          (
            SELECT 
              ids 
            FROM 
              (
                SELECT 
                  ARRAY_AGG("namespaces"."id") AS ids 
                FROM 
                  (
                    SELECT 
                      namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1) ] AS id 
                    FROM 
                      "namespaces" 
                    WHERE 
                      "namespaces"."type" = 'Group' 
                      AND (
                        traversal_ids @> ('{9970}')
                      )
                  ) namespaces
              ) consistent_query
          )
        )
      ) AS namespaces(id)
  ) 
  AND EXISTS (
    SELECT 
      "project_compliance_framework_settings".* 
    FROM 
      "project_compliance_framework_settings" 
    WHERE 
      "project_compliance_framework_settings"."framework_id" = 1019512 
      AND (project_id = projects.id)
  ) 
  AND EXISTS (
    SELECT 
      "project_compliance_framework_settings".* 
    FROM 
      "project_compliance_framework_settings" 
    WHERE 
      "project_compliance_framework_settings"."framework_id" = 1018575 
      AND (project_id = projects.id)
  ) 
ORDER BY 
  "projects"."id" DESC 
LIMIT 
  101
;

Query 2: Filter projects without given frameworks

Query plan: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/30675/commands/95234

Click to expand query 
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_last_update_at", 
  "projects"."mirror_last_successful_update_at", 
  "projects"."mirror_user_id", 
  "projects"."shared_runners_enabled", 
  "projects"."runners_token", 
  "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"."max_pages_size", 
  "projects"."max_artifacts_size", 
  "projects"."pull_mirror_branch_prefix", 
  "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", 
  "projects"."hidden", 
  "projects"."organization_id" 
FROM 
  "projects" 
WHERE 
  "projects"."namespace_id" IN (
    SELECT 
      "namespaces"."id" 
    FROM 
      UNNEST(
        COALESCE(
          (
            SELECT 
              ids 
            FROM 
              (
                SELECT 
                  "namespace_descendants"."self_and_descendant_group_ids" AS ids 
                FROM 
                  "namespace_descendants" 
                WHERE 
                  "namespace_descendants"."outdated_at" IS NULL 
                  AND "namespace_descendants"."namespace_id" = 9970
              ) cached_query
          ), 
          (
            SELECT 
              ids 
            FROM 
              (
                SELECT 
                  ARRAY_AGG("namespaces"."id") AS ids 
                FROM 
                  (
                    SELECT 
                      namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1) ] AS id 
                    FROM 
                      "namespaces" 
                    WHERE 
                      "namespaces"."type" = 'Group' 
                      AND (
                        traversal_ids @> ('{9970}')
                      )
                  ) namespaces
              ) consistent_query
          )
        )
      ) AS namespaces(id)
  ) 
  AND NOT (
    EXISTS (
      SELECT 
        "project_compliance_framework_settings".* 
      FROM 
        "project_compliance_framework_settings" 
      WHERE 
        "project_compliance_framework_settings"."framework_id" IN (1019512, 1018575) 
        AND (project_id = projects.id)
    )
  ) 
ORDER BY 
  "projects"."id" DESC 
LIMIT 
  101;

How to set up and validate locally

  1. You need to have a group with GitLab Ultimate licence for this.
  2. Create several compliance frameworks for the group by following steps mentioned in https://docs.gitlab.com/ee/user/compliance/compliance_center/compliance_frameworks_report.html#create-a-new-compliance-framework.
  3. Make sure the group has several projects in it.
  4. Now, add compliance frameworks to the projects by following steps mentioned in https://docs.gitlab.com/ee/user/compliance/compliance_center/compliance_projects_report.html#apply-a-compliance-framework-to-projects-in-a-group.
  5. Open graphql explorer by visiting http://gitlab.localdev:3000/-/graphql-explorer.
  6. Run following query to list down projects of the group by filtering with compliance framework ids, you should note that it should list down only those projects which are associated with all the given framework ids:
query group {
  group(fullPath: "<group_full_path>") {
    id
    name
    projects(
      includeSubgroups: true,
      complianceFrameworkFilters: {
        ids: ["gid://gitlab/ComplianceManagement::Framework/<framework1_id>", 
            "gid://gitlab/ComplianceManagement::Framework/<framework2_id>"]
      }
    ) {
      nodes {
        id
        name
      }
    }
  }
}
  1. For negating the compliance frameworks, you can run following query, it should provide a list of projects of the group which should not be associated with any of the given framework ids
query group {
  group(fullPath: "<group_full_path>") {
    id
    name
    projects(
      includeSubgroups: true,
      complianceFrameworkFilters: {
        not: { 
          ids: ["gid://gitlab/ComplianceManagement::Framework/<framework1_id>", 
            "gid://gitlab/ComplianceManagement::Framework/<framework2_id>"] 
        }
      }
    ) {
      nodes {
        id
        name
      }
    }
  }
}

Related to #473696 (closed)

Edited by Hitesh Raghuvanshi

Merge request reports