Skip to content

Query policy group approvers within group hierarchy

Dominic Bauer requested to merge 390183-inherited-group-members-selected-as-approvers-are-not-displayed-in-policy-drawer-edit-policy into master

What does this MR do and why?

Scan result policies allow specifying users as approvers for MRs that violate a policy.

Scan result policies are inherited in a group hierarchy. However currently, users are queried only within a project, not within the project's group hierarchy. This leads to

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

  1. Create a new group
  2. Add members to the group and note their IDs
  3. Create a project within the group
  4. Add a scan result policy to the project that lists the user IDs as user_approvers, e.g.:
type: scan_result_policy
name: foobar
description: ''
enabled: true
rules:
  - type: license_finding
    branches: []
    match_on_inclusion: true
    license_types:
      - MIT License
    license_states:
      - newly_detected
actions:
  - type: require_approval
    approvals_required: 1
    user_approvers_ids:
      - 16
      - 14
      - 15
  1. Navigate to the policy list, click on the newly created policy and verify the sidebar drawer correctly lists the users.
  2. Click "Edit policy" and verify the approver list correctly lists the users.

Screenshots

Before After

Database query

postgres.ai

SELECT
  "users"."id",
  "users"."email",
  "users"."encrypted_password",
  "users"."reset_password_token",
  "users"."reset_password_sent_at",
  "users"."remember_created_at",
  "users"."sign_in_count",
  "users"."current_sign_in_at",
  "users"."last_sign_in_at",
  "users"."current_sign_in_ip",
  "users"."last_sign_in_ip",
  "users"."created_at",
  "users"."updated_at",
  "users"."name",
  "users"."admin",
  "users"."projects_limit",
  "users"."failed_attempts",
  "users"."locked_at",
  "users"."username",
  "users"."can_create_group",
  "users"."can_create_team",
  "users"."state",
  "users"."color_scheme_id",
  "users"."password_expires_at",
  "users"."created_by_id",
  "users"."last_credential_check_at",
  "users"."avatar",
  "users"."confirmation_token",
  "users"."confirmed_at",
  "users"."confirmation_sent_at",
  "users"."unconfirmed_email",
  "users"."hide_no_ssh_key",
  "users"."admin_email_unsubscribed_at",
  "users"."notification_email",
  "users"."hide_no_password",
  "users"."password_automatically_set",
  "users"."encrypted_otp_secret",
  "users"."encrypted_otp_secret_iv",
  "users"."encrypted_otp_secret_salt",
  "users"."otp_required_for_login",
  "users"."otp_backup_codes",
  "users"."public_email",
  "users"."dashboard",
  "users"."project_view",
  "users"."consumed_timestep",
  "users"."layout",
  "users"."hide_project_limit",
  "users"."note",
  "users"."unlock_token",
  "users"."otp_grace_period_started_at",
  "users"."external",
  "users"."incoming_email_token",
  "users"."auditor",
  "users"."require_two_factor_authentication_from_group",
  "users"."two_factor_grace_period",
  "users"."last_activity_on",
  "users"."notified_of_own_activity",
  "users"."preferred_language",
  "users"."email_opted_in",
  "users"."email_opted_in_ip",
  "users"."email_opted_in_source_id",
  "users"."email_opted_in_at",
  "users"."theme_id",
  "users"."accepted_term_id",
  "users"."feed_token",
  "users"."private_profile",
  "users"."roadmap_layout",
  "users"."include_private_contributions",
  "users"."commit_email",
  "users"."group_view",
  "users"."managing_group_id",
  "users"."first_name",
  "users"."last_name",
  "users"."static_object_token",
  "users"."role",
  "users"."user_type",
  "users"."static_object_token_encrypted",
  "users"."otp_secret_expires_at",
  "users"."onboarding_in_progress"
FROM
  "users"
WHERE
  (
    "users"."username" = 'bauerdominic'
    OR "users"."id" = 8401507
  )
  AND EXISTS (
    SELECT
      1
    FROM
      (
        (
          SELECT
            "members"."id",
            "members"."access_level",
            "members"."source_id",
            "members"."source_type",
            "members"."user_id",
            "members"."notification_level",
            "members"."type",
            "members"."created_at",
            "members"."updated_at",
            "members"."created_by_id",
            "members"."invite_email",
            "members"."invite_token",
            "members"."invite_accepted_at",
            "members"."requested_at",
            "members"."expires_at",
            "members"."ldap",
            "members"."override",
            "members"."state",
            "members"."invite_email_success",
            "members"."member_namespace_id",
            "members"."member_role_id"
          FROM
            "members"
          WHERE
            "members"."type" = 'GroupMember'
            AND "members"."source_type" = 'Namespace'
            AND "members"."source_id" IN (
              SELECT
                "namespaces"."id"
              FROM
                "namespaces"
              WHERE
                "namespaces"."type" = 'Group'
                AND "namespaces"."id" IN (9970, 11787569)
            )
        )
        UNION
          (
            WITH "group_group_links_cte" AS MATERIALIZED (
              SELECT
                "group_group_links".*
              FROM
                "group_group_links"
              WHERE
                "group_group_links"."shared_group_id" IN (
                  SELECT
                    "namespaces"."id"
                  FROM
                    "namespaces"
                  WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" IN (9970, 11787569)
                )
            )
            SELECT
              "members"."id",
              LEAST(
                "group_group_links"."group_access",
                "members"."access_level"
              ) AS access_level,
              "members"."source_id",
              "members"."source_type",
              "members"."user_id",
              "members"."notification_level",
              "members"."type",
              "members"."created_at",
              "members"."updated_at",
              "members"."created_by_id",
              "members"."invite_email",
              "members"."invite_token",
              "members"."invite_accepted_at",
              "members"."requested_at",
              "members"."expires_at",
              "members"."ldap",
              "members"."override",
              "members"."state",
              "members"."invite_email_success",
              "members"."member_namespace_id",
              "members"."member_role_id"
            FROM
              "members",
              "group_group_links_cte" AS "group_group_links"
            WHERE
              "members"."type" = 'GroupMember'
              AND "members"."source_type" = 'Namespace'
              AND "members"."requested_at" IS NULL
              AND "members"."source_id" = "group_group_links"."shared_with_group_id"
              AND "members"."source_type" = 'Namespace'
              AND "members"."state" = 0
              AND (members.access_level > 5)
          )
      ) members
    WHERE
      "members"."type" = 'GroupMember'
      AND "members"."source_type" = 'Namespace'
      AND "members"."state" = 0
      AND "members"."requested_at" IS NULL
      AND (members.access_level > 5)
      AND "members"."user_id" = "users"."id"
  );

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #390183 (closed)

Edited by Dominic Bauer

Merge request reports