Skip to content

Add `securityPolicyProjectSuggestions` query type

Dominic Bauer requested to merge 427778-incorrect-permission-checks-for-modifying-security-policy-project into master

What does this MR do and why?

Adds a GraphQL query type for security policy project suggestions. The query type will auto-complete project suggestions based on their full path for our redesigned policy selection select UI (&12156 (closed)).

One shortcoming of the current auto-completion behaviour is that only projects with direct membership of the current user are suggested. This makes it impossible for users to get suggested security projects they have only inherited access to.

Different projects are suggested depending on (1) the onlyLinked query argument and (2) whether the GitLab installation is SaaS:

  • onlyLinked: whether to suggest only projects already linked as security policy projects.
  • GitLab.com: On self-managed installations, projects are suggested across namespaces. For SaaS, only projects within the current hierarchy are suggested.

How to set up and validate locally

See Simulate a SaaS instance for toggling between SaaS/SM.

query groupSuggestions {
  group(fullPath: "top-group/development") {
    securityPolicyProjectSuggestions(search: "security", onlyLinked: true) {
      nodes {
        fullPath
      }
    }
  }
}

query projectSuggestions {
  project(fullPath: "top-group/development/example-project") {
    securityPolicyProjectSuggestions(search: "security", onlyLinked: true) {
      nodes {
        fullPath
      }
    }
  }
}

Database

Before

The query currently in use:

Toggle query 
-- https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26400/commands/82804
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",
	round(cast((( /* gitlab/database/similarity_score */ Similarity (coalesce("projects"."path", ''), 'security') * cast('1' AS numeric)) + ( /* gitlab/database/similarity_score */ Similarity (coalesce("projects"."name", ''), 'security') * cast('0.7' AS numeric)) + ( /* gitlab/database/similarity_score */ Similarity (coalesce("projects"."description", ''), 'security') * cast('0.2' AS numeric))) AS numeric), 2) AS similarity
FROM
	"projects"
	INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
	INNER JOIN "routes" ON "routes"."source_type" = 'Project'
		AND "routes"."source_id" = "projects"."id"
WHERE
	"project_authorizations"."user_id" = 1614863
	AND "projects"."pending_delete" = FALSE
	AND (("routes"."path" ILIKE '%security%'
			OR "routes"."name" ILIKE '%security%')
		OR "projects"."description" ILIKE '%security%')
	AND "projects"."hidden" = FALSE
ORDER BY
	round(cast((( /* gitlab/database/similarity_score */ similarity (coalesce("projects"."path", ''), 'security') * cast('1' AS numeric)) + ( /* gitlab/database/similarity_score */ similarity (coalesce("projects"."name", ''), 'security') * cast('0.7' AS numeric)) + ( /* gitlab/database/similarity_score */ similarity (coalesce("projects"."description", ''), 'security') * cast('0.2' AS numeric))) AS numeric), 2) DESC,
	"projects"."id" DESC;

After

SaaS without onlyLinked

Toggle query 
-- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26719/commands/83497
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",
	round(cast(( /* gitlab/database/similarity_score */ SIMILARITY (coalesce("projects"."path", ''), 'project') * cast('1' AS numeric)) AS numeric), 2) AS similarity
FROM
	"projects"
	INNER JOIN "routes" ON "routes"."source_type" = 'Project'
		AND "routes"."source_id" = "projects"."id"
WHERE
	"projects"."id" IN (
		SELECT
			"projects"."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}'))
									AND (traversal_ids < ('{9971}'))) namespaces) consistent_query))) AS namespaces (id)))
		AND "projects"."archived" = FALSE
		AND "projects"."pending_delete" = FALSE
		AND (EXISTS (
				SELECT
					1
				FROM
					"project_authorizations"
				WHERE
					"project_authorizations"."user_id" = 1614863
					AND (project_authorizations.project_id = projects.id)
					AND (project_authorizations.access_level >= 30))
				OR projects.visibility_level IN (10, 20))
		AND "routes"."path" ILIKE '%project%'
	ORDER BY
		round(cast(( /* gitlab/database/similarity_score */ SIMILARITY (coalesce("projects"."path", ''), 'project') * cast('1' AS numeric)) AS numeric), 2) DESC,
	"projects"."id" DESC
LIMIT 20;

SaaS with onlyLinked

Toggle query 
-- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26719/commands/83499
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",
	round(cast(( /* gitlab/database/similarity_score */ SIMILARITY (coalesce("projects"."path", ''), 'project') * cast('1' AS numeric)) AS numeric), 2) AS similarity
FROM
	"projects"
	INNER JOIN "routes" ON "routes"."source_type" = 'Project'
		AND "routes"."source_id" = "projects"."id"
WHERE
	"projects"."id" IN (
		SELECT
			"projects"."id"
		FROM
			"projects"
		WHERE
			"projects"."id" IN (
				SELECT
					"security_orchestration_policy_configurations"."security_policy_management_project_id"
				FROM
					"security_orchestration_policy_configurations"
				WHERE
					"security_orchestration_policy_configurations"."security_policy_management_project_id" IN (
						SELECT
							"projects"."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}'))
													AND (traversal_ids < ('{9971}'))) namespaces) consistent_query))) AS namespaces (id)))))
				AND "projects"."archived" = FALSE
				AND "projects"."pending_delete" = FALSE
				AND (EXISTS (
						SELECT
							1
						FROM
							"project_authorizations"
						WHERE
							"project_authorizations"."user_id" = 1614863
							AND (project_authorizations.project_id = projects.id)
							AND (project_authorizations.access_level >= 30))
						OR projects.visibility_level IN (10, 20))
				AND "routes"."path" ILIKE '%project%'
			ORDER BY
				round(cast(( /* gitlab/database/similarity_score */ SIMILARITY (coalesce("projects"."path", ''), 'project') * cast('1' AS numeric)) AS numeric), 2) DESC,
			"projects"."id" DESC
		LIMIT 20;

Self-managed with onlyLinked

Toggle query 
-- https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26683/commands/83468
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",
	round(cast(( /* gitlab/database/similarity_score */ Similarity (coalesce("projects"."path", ''), 'security') * cast('1' AS numeric)) AS numeric), 2) AS similarity
FROM
	"projects"
WHERE
	"projects"."id" IN (
		SELECT
			"routes"."source_id"
		FROM
			"routes"
		WHERE
			"routes"."source_type" = 'Project'
			AND "routes"."source_id" IN (
				SELECT
					"projects"."id"
				FROM
					"projects"
				WHERE
					"projects"."archived" = FALSE
					AND "projects"."pending_delete" = FALSE
					AND "projects"."id" IN (
						SELECT
							"security_orchestration_policy_configurations"."security_policy_management_project_id"
						FROM
							"security_orchestration_policy_configurations")
						AND (EXISTS (
								SELECT
									1
								FROM
									"project_authorizations"
								WHERE
									"project_authorizations"."user_id" = 1614863
									AND (project_authorizations.project_id = projects.id)
									AND (project_authorizations.access_level >= 30))
								OR projects.visibility_level IN (10, 20)))
						AND "routes"."path" ILIKE '%security%')
			ORDER BY
				round(cast(( /* gitlab/database/similarity_score */ similarity (coalesce("projects"."path", ''), 'security') * cast('1' AS numeric)) AS numeric), 2) DESC,
	"projects"."id" DESC
LIMIT 20;

Self-managed without onlyLinked

Toggle query 
-- https://console.postgres.ai/gitlab/gitlab-production-main/sessions/26683/commands/83470
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",
	round(cast(( /* gitlab/database/similarity_score */ SIMILARITY (coalesce("projects"."path", ''), 'alpha') * cast('1' AS numeric)) AS numeric), 2) AS similarity
FROM ((
		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"."archived" = FALSE
			AND "projects"."pending_delete" = FALSE
			AND (EXISTS (
					SELECT
						1
					FROM
						"project_authorizations"
					WHERE
						"project_authorizations"."user_id" = 1614863
						AND (project_authorizations.project_id = projects.id)
						AND (project_authorizations.access_level >= 30))
					OR projects.visibility_level IN (10, 20))
				AND "projects"."id" IN (
					SELECT
						"routes"."source_id"
					FROM
						"routes"
					WHERE
						"routes"."source_type" = 'Project'
						AND "routes"."path" ILIKE '%alpha%'
					LIMIT 250))
		UNION (
			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"
				INNER JOIN "routes" ON "routes"."source_type" = 'Project'
					AND "routes"."source_id" = "projects"."id"
			WHERE
				"routes"."path" = 'alpha')) projects
ORDER BY
	round(cast(( /* gitlab/database/similarity_score */ SIMILARITY (coalesce("projects"."path", ''), 'alpha') * cast('1' AS numeric)) AS numeric), 2) DESC,
	"projects"."id" DESC
LIMIT 20;

Related to #427778 (closed)

Edited by Dominic Bauer

Merge request reports