Skip field authorization for work items

What does this MR do and why?

To optimise the queries used for listing work items, we can remove the type authorisation from the fields group.workItems and project.workItems by extending the authorisation already performed by the finder.

We need to handle two cases that are currently not authorised by the finder:

• Redact group-level work items when the licensed feature epics is not available
• Redact project-level epics when the feature flag project_work_item_epics is disabled

To achieve this, I implemented IssueTypesFilter to move the logic from IssuesFinder#by_issue_types and add the redaction for unavailable types.

These changes are added behind the feature flag authorize_issue_types_in_finder so it can be tested safely.

List of changes

• Add skip_type_authorization: [:read_work_item] to WorkItemsResolver - this can be moved to the specific fields once we remove the FF
• Move authorization from LinkedWorkItemType to LinkedItemsResolver - required because this type also uses read_work_item, which is now skipped in all nested fields
• Add Issues::IssueTypesFilter - handles types redaction
• Add skip_type_authorization: [:read_label] in Types::WorkItems::Widgets:: LabelsType - unnecessary check because read_label is available if the user can read the work item
• Add skip_type_authorization: [:read_milestone] in in Types::WorkItems::Widgets:: MilestoneType - same as above
• Skip read_namespace and read_project for the project and namespace fields in WorkItemType - same as above

References

Skip type authorisation for workItems fields (#542421)

Database changes

⚠️ [Extra context: As part of &16919 we are in the process of optimising these queries, so while currently quite slow, we expect them to improve after Use namespace-based queries for issues and work... (&17531) and Use Efficient IN operator query for work items (#542411 - closed) are completed.]

When the WorkItemsFinder does not receive the parameter issue_types, meaning we want to find all types, we might have to filter out unavailable work item types:

Case 1: with group param

group, user = Group.first, User.first
params = { group_id: group, include_descendants: true, exclude_projects: false }
WorkItems::WorkItemsFinder.new(user, params).execute

When the epics feature is not available

Excludes all namespace-level work items and project-level work items with type Epic:

previous query

SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "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)
) ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 101;

new query

SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "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 "issues"."work_item_type_id" != 8 ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 101;

previous query plan	new query plan
console.postgres.ai	console.postgres.ai

When project-level epics are disabled

Include all namespace-level work items and exclude project-level work items with type Epic:

previous query

WITH "namespace_ids" AS MATERIALIZED (SELECT "namespaces"."id" FROM ((SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))
UNION ALL
(SELECT "projects"."project_namespace_id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "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)
))) namespaces) SELECT "issues".* FROM "issues" WHERE (issues.namespace_id IN (SELECT id FROM namespace_ids)) ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 101;

new query

WITH "namespace_ids" AS MATERIALIZED (SELECT "namespaces"."id" FROM ((SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))
UNION ALL
(SELECT "projects"."project_namespace_id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "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)
))) namespaces) SELECT "issues".* FROM "issues" WHERE (issues.namespace_id IN (SELECT id FROM namespace_ids)) AND "issues"."id" NOT IN (WITH "namespace_ids" AS MATERIALIZED (SELECT "namespaces"."id" FROM ((SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))
UNION ALL
(SELECT "projects"."project_namespace_id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "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)
))) namespaces) SELECT "issues"."id" FROM "issues" WHERE (issues.namespace_id IN (SELECT id FROM namespace_ids)) AND "issues"."work_item_type_id" = 8 AND "issues"."project_id" IS NOT NULL) ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 101;

previous query plan	new query plan
console.postgres.ai	console.postgres.ai

Case 2: with project param

project, user = Project.first, User.first
params = { project_id: project }
WorkItems::WorkItemsFinder.new(user, params).execute

Exclude work items with type Epic when epics feature is not enabled or the project_work_item_epics feature flag is disabled. This query only changes if project-level epics exist, which is unlikely for the near future because the feature has not been released.

previous query

SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "projects"."id" = 278964 ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 101;

new query

SELECT "issues".* FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND "projects"."id" = 278964 AND "issues"."work_item_type_id" != 8 ORDER BY "issues"."created_at" DESC, "issues"."id" DESC LIMIT 101;

previous query plan	new query plan
console.postgres.ai	console.postgres.ai/

Screenshots or screen recordings

No UI changes

How to set up and validate locally

1. Enable feature flags

Feature.enable(:authorize_issue_types_in_finder)
Feature.enable(:project_work_item_epics)

3. Create testing data

Click to expand

user = User.first
root_group = FactoryBot.create(:group, name: 'Root Group', path: 'root-group', creator: user)
root_project = FactoryBot.create(:project, name: 'Root Project', path: 'root-project', group: root_group, creator: user)

subgroup = FactoryBot.create(:group, name: 'Subgroup', path: 'subgroup', creator: user, parent: root_group)
subgroup_project = FactoryBot.create(:project, name: 'Subgroup Project', path: 'sub-project', group: subgroup, creator: user)

issue_type = WorkItems::Type.default_by_type(:issue)
epic_type = WorkItems::Type.default_by_type(:epic)

[issue_type, epic_type].each do |type|
  WorkItem.create!(title: "Root Group #{type.name}", work_item_type: type, namespace: root_group, author: user)
  WorkItem.create!(title: "Root Project #{type.name}", work_item_type: type, project: root_project, author: user)
  WorkItem.create!(title: "Subroup #{type.name}", work_item_type: type, namespace: subgroup, author: user)
  WorkItem.create!(title: "Sub Project #{type.name}", work_item_type: type, project: subgroup_project, author: user)
end

# We can also use the finder to verify the changes, e.g.:
params = { group_id: root_group, include_descendants: true, exclude_projects: false }
WorkItems::WorkItemsFinder.new(user, params).execute

2. Visit http://gdk.test:3000/-/graphql-explorer and try the following query with the different variables

GQL query

query getAllWorkItems($excludeProjects: Boolean = false, $includeDescendants: Boolean = true, $isGroup: Boolean = true, $fullPath: ID!, $types: [IssueType!]) {
  group(fullPath: $fullPath) @include(if: $isGroup) {
    id
    name
    workItems(
      excludeProjects: $excludeProjects
      includeDescendants: $includeDescendants
      types: $types
    ) {
      nodes {
        title
      }
    }
  }
  project(fullPath: $fullPath) @skip(if: $isGroup) {
    id
    name
    workItems(
      types: $types
    
    ) {
      nodes {
        title
      }
    }
  }
}

variables

# root group level - all work items including descendant groups and projects
# {"excludeProjects":false,"includeDescendants":true,"isGroup":true,"fullPath":"root-group"}
# {"excludeProjects":false,"includeDescendants":true,"isGroup":true,"fullPath":"root-group", "types": "EPIC"}

# root group level - only group items, include descendant groups
# {"excludeProjects":true,"includeDescendants":true,"isGroup":true,"fullPath":"root-group"}
# {"excludeProjects":true,"includeDescendants":true,"isGroup":true,"fullPath":"root-group", "types": "EPIC"}

# root group level - only group items, exclude descendant groups
# {"excludeProjects":true,"includeDescendants":false,"isGroup":true,"fullPath":"root-group"}
# {"excludeProjects":true,"includeDescendants":false,"isGroup":true,"fullPath":"root-group", "types": "EPIC"}

# project
# {"isGroup":false,"fullPath":"root-group/root-project"}
# {"isGroup":false,"fullPath":"root-group/root-project", "types": "EPIC"}

# sub project
# {"isGroup":false,"fullPath":"root-group/subgroup/sub-project"}
# {"isGroup":false,"fullPath":"root-group/subgroup/sub-project", "types": "EPIC"}

3. Verify that namespace-level work items are not returned when epics feature is not available (can be replicated by removing epics from ee/app/models/gitlab_subscriptions/features.rb)
4. Verify that project-level epics are not returned when epics feature is not available
5. Verify that project-level epics are not returned when project_work_item_epics feature flag is disabled

MR acceptance checklist

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