Add groups endpoint for Projects API

What does this MR do?

Contributes to #28902 (closed)

Problem

Currently, we use List groups API endpoint to fetch merge request approval groups. But it returns all available groups for the user. This endpoint response contains groups that don't even have an access to the project.

For merge request approval rules we want to display only those groups that have access to approve the MR.

Solution

• Add a new endpoint /projects/:id/groups that will return ancestor groups for the project.
• Provide optional arguments to include shared groups to the response (with_shared, shared_min_access_level)

URL example

http://127.0.0.1:3000/api/v4/projects/<project_id>/groups.json?with_shared=true&shared_min_access_level=30

Will return ancestor groups for <project_id> and groups shared with the project with at least Developer(shared_min_access_level=30) permissions.

Screenshots (strongly suggested)

Database

Query project ancestors

ProjectGroupsFinder.new(project: project, current_user: user).execute

Explain query plan (postgres.ai)

WITH RECURSIVE "base_and_ancestors" AS (
(
        SELECT
            "namespaces".*
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."id" = 9970)
    UNION (
        SELECT
            "namespaces".*
        FROM
            "namespaces",
            "base_and_ancestors"
        WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
    "namespaces".*
FROM
    "base_and_ancestors" AS "namespaces"
ORDER BY
    "namespaces"."id" DESC

Query project ancestors and shared groups

ProjectGroupsFinder.new(project: project, current_user: user, params: { with_shared: true, shared_min_access_level: 30 }).execute

Explain query plan (postgres.ai)

SELECT
    "namespaces".*
FROM (( WITH RECURSIVE "base_and_ancestors" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = 9970)
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_ancestors"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
            SELECT
                "namespaces".*
            FROM
                "base_and_ancestors" AS "namespaces")
        UNION (
            SELECT
                "namespaces".*
            FROM
                "namespaces"
                INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
            WHERE
                "namespaces"."type" = 'Group'
                AND "project_group_links"."project_id" = 278964
                AND (project_group_links.group_access >= 30))) namespaces
WHERE
    "namespaces"."type" = 'Group'
ORDER BY
    "namespaces"."id" DESC

Query project ancestors, shared groups and skip groups

ProjectGroupsFinder.new(project: project, current_user: user, params: { with_shared: true, shared_min_access_level: 30, skip_groups: [8552558, 9970] }).execute

SELECT
  "namespaces".*
FROM (( WITH RECURSIVE "base_and_ancestors" AS (
(
        SELECT
          "namespaces".*
        FROM
          "namespaces"
        WHERE
          "namespaces"."type" = 'Group'
          AND "namespaces"."id" = 9970)
      UNION (
        SELECT
          "namespaces".*
        FROM
          "namespaces",
          "base_and_ancestors"
        WHERE
          "namespaces"."type" = 'Group'
          AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
      SELECT
        "namespaces".*
      FROM
        "base_and_ancestors" AS "namespaces" WHERE "namespaces"."id" NOT IN (8552558, 9970))
    UNION (
      SELECT
        "namespaces".*
      FROM
        "namespaces"
        INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
      WHERE
        "namespaces"."type" = 'Group'
        AND "project_group_links"."project_id" = 278964
        AND (project_group_links.group_access >= 30)
        AND "namespaces"."id" NOT IN (8552558, 9970))) namespaces
WHERE
  "namespaces"."type" = 'Group'
ORDER BY
  "namespaces"."id" DESC

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• [-] Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• [-] Database guides
• [-] Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• [-] Tested in all supported browsers
• [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• [-] Label as security and @ mention @gitlab-com/gl-security/appsec
• [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• [-] Security reports checked/validated by a reviewer from the AppSec team