Resolve "[BE] Add ability to filter by provisioned users"

What does this MR do and why?

This is the backend implementation of [BE] Add ability to filter by provisioned users (#350400 - closed)

With this change:

• the backend now supports filtering Group members in the group members page, with a new filter: Enterprise: Yes/No
• for Group owners
• for Groups that have Group SAML enabled. (this is available only on GitLab.com, so this filtering capability won't be available on self-managed)

(No changelog added as this is only the backend counterpart, so the feature isn't open to users via the UI yet)

How to set up and validate locally

Setting up SAML locally can be a bit tricky so instead we can create a bunch of Enterprise and non-enterprise users via the console using FactoryBot

• create users & setup Group SAML from console

root_group = Group.find(<id of your root group>)

## setup group-saml for your root group

FactoryBot.definition_file_paths = [
      Rails.root.join('ee', 'spec', 'factories')
]
FactoryBot.find_definitions

FactoryBot.create(:saml_provider, group: root_group)

# create provisioned (enterprise) users
5.times do
root_group.add_developer(FactoryBot.create(:user, provisioned_by_group_id: root_group.id))
end

# create non-provisioned users
5.times do
root_group.add_developer(FactoryBot.create(:user))
end

# in a subgroup
subgroup = Group.find(<id of your subgroup>)

# create provisioned users
5.times do
subgroup.add_developer(FactoryBot.create(:user, provisioned_by_group_id: subgroup.id))
end

# create non-provisioned users
5.times do
subgroup.add_developer(FactoryBot.create(:user))
end

• Cherry pick all commits from !82059 (commits), so that you can have frontend changes in your local.
• Restart GDK
• Visit http://localhost:3000/groups/<group or subgroup>/-/group_members as the group's owner.
• You can now filter group members by 'Enterprise: Yes/No'

Screenshots or screen recordings

Database timings

For root group: (ID: 9970)

Enterprise: No, first page

explain 
SELECT
   "members".* 
FROM
   (
      SELECT DISTINCT
         ON (user_id, invite_email) * 
      FROM
         "members" 
      WHERE
         "members"."type" = 'GroupMember' 
         AND "members"."source_type" = 'Namespace' 
         AND "members"."requested_at" IS NULL 
         AND "members"."source_id" IN 
         (
            SELECT
               "namespaces"."id" 
            FROM
               (
(
                  SELECT
                     "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" 
                  FROM
                     "namespaces" 
                  WHERE
                     "namespaces"."type" = 'Group' 
                     AND "namespaces"."id" = 9970)
               )
               namespaces 
            WHERE
               "namespaces"."type" = 'Group'
         )
      ORDER BY
         user_id,
         invite_email,
         access_level DESC,
         expires_at DESC,
         created_at ASC
   )
   members 
   LEFT OUTER JOIN
      "users" 
      ON "users"."id" = "members"."user_id" 
WHERE
   "members"."type" = 'GroupMember' 
   AND 
   (
      NOT EXISTS 
      (
         SELECT
            1 
         FROM
            "user_details" 
         WHERE
            "user_details"."provisioned_by_group_id" = "members"."source_id" 
            AND "user_details"."user_id" = "members"."user_id"
      )
   )
   AND "members"."invite_token" IS NULL 
   AND "members"."state" != 1 
ORDER BY
   users.name ASC NULLS LAST LIMIT 50 OFFSET 0

• https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8951/commands/31643

Enterprise: No, with some OFFSET

explain 
SELECT
   "members".* 
FROM
   (
      SELECT DISTINCT
         ON (user_id, invite_email) * 
      FROM
         "members" 
      WHERE
         "members"."type" = 'GroupMember' 
         AND "members"."source_type" = 'Namespace' 
         AND "members"."requested_at" IS NULL 
         AND "members"."source_id" IN 
         (
            SELECT
               "namespaces"."id" 
            FROM
               (
(
                  SELECT
                     "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" 
                  FROM
                     "namespaces" 
                  WHERE
                     "namespaces"."type" = 'Group' 
                     AND "namespaces"."id" = 9970)
               )
               namespaces 
            WHERE
               "namespaces"."type" = 'Group'
         )
      ORDER BY
         user_id,
         invite_email,
         access_level DESC,
         expires_at DESC,
         created_at ASC
   )
   members 
   LEFT OUTER JOIN
      "users" 
      ON "users"."id" = "members"."user_id" 
WHERE
   "members"."type" = 'GroupMember' 
   AND 
   (
      NOT EXISTS 
      (
         SELECT
            1 
         FROM
            "user_details" 
         WHERE
            "user_details"."provisioned_by_group_id" = "members"."source_id" 
            AND "user_details"."user_id" = "members"."user_id"
      )
   )
   AND "members"."invite_token" IS NULL 
   AND "members"."state" != 1 
ORDER BY
   users.name ASC NULLS LAST LIMIT 50 OFFSET 300

• https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8951/commands/31644

Enterprise: Yes, WITH SOME OFFSET (no data returned as this group does not have enterprise users)

explain 
SELECT
   "members".* 
FROM
   (
      SELECT DISTINCT
         ON (user_id, invite_email) * 
      FROM
         "members" 
      WHERE
         "members"."type" = 'GroupMember' 
         AND "members"."source_type" = 'Namespace' 
         AND "members"."requested_at" IS NULL 
         AND "members"."source_id" IN 
         (
            SELECT
               "namespaces"."id" 
            FROM
               (
(
                  SELECT
                     "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" 
                  FROM
                     "namespaces" 
                  WHERE
                     "namespaces"."type" = 'Group' 
                     AND "namespaces"."id" = 9970)
               )
               namespaces 
            WHERE
               "namespaces"."type" = 'Group'
         )
      ORDER BY
         user_id,
         invite_email,
         access_level DESC,
         expires_at DESC,
         created_at ASC
   )
   members 
   LEFT OUTER JOIN
      "users" 
      ON "users"."id" = "members"."user_id" 
WHERE
   "members"."type" = 'GroupMember' 
   AND 
   (
      EXISTS 
      (
         SELECT
            1 
         FROM
            "user_details" 
         WHERE
            "user_details"."provisioned_by_group_id" = "members"."source_id" 
            AND "user_details"."user_id" = "members"."user_id"
      )
   )
   AND "members"."invite_token" IS NULL 
   AND "members"."state" != 1 
ORDER BY
   users.name ASC NULLS LAST LIMIT 50 OFFSET 500

• https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/8951/commands/31646

Enterprise: No, 2FA: Yes, Membership: Direct, with some OFFSET

explain 
SELECT
   "members".* 
FROM
   (
      SELECT DISTINCT
         ON (user_id, invite_email) * 
      FROM
         "members" 
      WHERE
         "members"."type" = 'GroupMember' 
         AND "members"."source_type" = 'Namespace' 
         AND "members"."requested_at" IS NULL 
         AND "members"."source_id" IN 
         (
            SELECT
               "namespaces"."id" 
            FROM
               "namespaces" 
            WHERE
               "namespaces"."type" = 'Group' 
               AND "namespaces"."id" = 9970
         )
      ORDER BY
         user_id,
         invite_email,
         access_level DESC,
         expires_at DESC,
         created_at ASC
   )
   members 
   LEFT OUTER JOIN
      "users" 
      ON "users"."id" = "members"."user_id" 
WHERE
   "members"."type" = 'GroupMember' 
   AND 
   (
      "users"."otp_required_for_login" = TRUE 
      OR EXISTS 
      (
         SELECT
            1 
         FROM
            "u2f_registrations" 
         WHERE
            "u2f_registrations"."user_id" = "users"."id"
      )
      OR EXISTS 
      (
         SELECT
            1 
         FROM
            "webauthn_registrations" 
         WHERE
            "webauthn_registrations"."user_id" = "users"."id"
      )
   )
   AND 
   (
      NOT EXISTS 
      (
         SELECT
            1 
         FROM
            "user_details" 
         WHERE
            "user_details"."provisioned_by_group_id" = "members"."source_id" 
            AND "user_details"."user_id" = "members"."user_id"
      )
   )
   AND "members"."invite_token" IS NULL 
   AND "members"."state" != 1 
ORDER BY
   users.name ASC NULLS LAST LIMIT 50 OFFSET 400

• https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/8951/commands/31647

For Subgroup (ID: 4955423, https://gitlab.com/groups/gitlab-org/security)

Enterprise: No, 2FA: Enabled, Membership: Inherited

explain 
SELECT
   "members".* 
FROM
   (
      SELECT DISTINCT
         ON (user_id, invite_email) * 
      FROM
         "members" 
      WHERE
         "members"."type" = 'GroupMember' 
         AND "members"."source_type" = 'Namespace' 
         AND "members"."requested_at" IS NULL 
         AND "members"."source_id" IN 
         (
            WITH RECURSIVE "base_and_ancestors" AS 
            (
(
               SELECT
                  "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" 
               FROM
                  "namespaces" 
               WHERE
                  "namespaces"."type" = 'Group' 
                  AND "namespaces"."id" = 9970) 
               UNION
(
               SELECT
                  "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" 
               FROM
                  "namespaces", "base_and_ancestors" 
               WHERE
                  "namespaces"."type" = 'Group' 
                  AND "namespaces"."id" = "base_and_ancestors"."parent_id")
            )
            SELECT
               "id" 
            FROM
               "base_and_ancestors" AS "namespaces"
         )
         AND 
         (
            members.access_level > 5
         )
      ORDER BY
         user_id,
         invite_email,
         access_level DESC,
         expires_at DESC,
         created_at ASC
   )
   members 
   LEFT OUTER JOIN
      "users" 
      ON "users"."id" = "members"."user_id" 
WHERE
   "members"."type" = 'GroupMember' 
   AND 
   (
      "users"."otp_required_for_login" = TRUE 
      OR EXISTS 
      (
         SELECT
            1 
         FROM
            "u2f_registrations" 
         WHERE
            "u2f_registrations"."user_id" = "users"."id"
      )
      OR EXISTS 
      (
         SELECT
            1 
         FROM
            "webauthn_registrations" 
         WHERE
            "webauthn_registrations"."user_id" = "users"."id"
      )
   )
   AND 
   (
      NOT EXISTS 
      (
         SELECT
            1 
         FROM
            "user_details" 
         WHERE
            "user_details"."provisioned_by_group_id" = "members"."source_id" 
            AND "user_details"."user_id" = "members"."user_id"
      )
   )
   AND "members"."invite_token" IS NULL 
   AND "members"."state" != 1 
ORDER BY
   users.name ASC NULLS LAST LIMIT 50 OFFSET 0

• https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8951/commands/31651

-->

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.