Skip to content

Filter projects with multiple compliance frameworks

Hitesh Raghuvanshi requested to merge 468910-compliance-filters into master

What does this MR do and why?

We need to provide ability to filter projects by multiple compliance frameworks in the compliance center. For that, this MR adds the functionality for providing a list of compliance frameworks to be filtered for listing the projects.

For this, we have added argument ids for filtering projects by multiple compliance frameworks.

Database

Query for finding all the project with given framework ids

Sample query 
SELECT DISTINCT "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"
       INNER JOIN "project_compliance_framework_settings"
                  "compliance_framework_settings"
               ON "compliance_framework_settings"."project_id" = "projects"."id"
WHERE  "projects"."namespace_id" = 785414
       AND "compliance_framework_settings"."framework_id" IN ( 3408, 3373 )
ORDER  BY "projects"."id" DESC

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29784/commands/92571

Query for getting projects which do not have certain framework ids

Sample query 
SELECT DISTINCT "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"
       LEFT OUTER JOIN "project_compliance_framework_settings"
                       "compliance_framework_settings"
                    ON "compliance_framework_settings"."project_id" =
                       "projects"."id"
WHERE  "projects"."namespace_id" = 785414
       AND ( "compliance_framework_settings"."framework_id" NOT IN
             ( 3408, 3373 )
              OR "compliance_framework_settings"."framework_id" IS NULL )
ORDER  BY "projects"."id" DESC

Query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29784/commands/92569

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.

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 a group by filtering with compliance 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:
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 #468910 (closed)

Edited by Hitesh Raghuvanshi

Merge request reports