Restrict invite search to seat assignment holders

Lukas Wankorequested to merge
lw/608055-restrict-invite-search-to-seat-holders into master

What does this MR do and why?

Under SAM, the invite-members search on the group members page should return only users who already have a seat assignment in the root namespace.

References

Related to https://gitlab.com/gitlab-org/gitlab/-/work_items/608055

How to set up and validate locally

  1. Enable the seat_assignment_model feature flag for a group
  2. Set seat_assignment_model_enabled: true on that group's namespace_settings
  3. Open the group's Members page invite modal and search for users — only users with a GitlabSubscriptions::SeatAssignment in that namespace

Database queries

1. Seat assignment user IDs subquery

Query plan

Introduced in user_ids_for_namespace:

SELECT "subscription_seat_assignments"."user_id"
FROM "subscription_seat_assignments"
WHERE "subscription_seat_assignments"."namespace_id" = 9970

2. InviteUsersFinder only SAM

Query plan

The full query used in seat_assignment_scoped_users, combining the subquery above with the existing invite search conditions:

SELECT "users"."id", ...
FROM "users"
WHERE "users"."state" = 'active'
AND "users"."user_type" IN (0, 6, 4, 13)
AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
AND "users"."id" IN (
  SELECT "subscription_seat_assignments"."user_id"
  FROM "subscription_seat_assignments"
  WHERE "subscription_seat_assignments"."namespace_id" = 9970
)

3. Entire search query only SAM from the controller concern here

Query plan

Query 
EXPLAIN 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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
FROM "users"
INNER JOIN "organization_users" ON "organization_users"."user_id" = "users"."id"
LEFT OUTER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
LEFT JOIN namespaces AS provision_groups ON user_details.provisioned_by_group_id = provision_groups.id
WHERE "users"."state" = 'active'
  AND "users"."user_type" IN (0, 6, 4, 13)
  AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
  AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 9970)
  AND (users.user_type != 13 OR ((
        users.composite_identity_enforced = FALSE
        OR user_details.provisioned_by_group_id IS NULL
        OR user_details.provisioned_by_group_id IN (9970)
      ) AND (
        user_details.provisioned_by_group_id IS NULL
        OR user_details.provisioned_by_group_id IN (9970)
        OR provision_groups.parent_id IS NULL
      ) AND (
        user_details.provisioned_by_project_id IS NULL
        OR (FALSE = TRUE AND user_details.provisioned_by_project_id = NULL)
      )))
  AND ("users"."email" = 'admi'
    OR "users"."id" = (SELECT "emails"."user_id" FROM "emails" WHERE "emails"."email" = 'admi' AND "emails"."confirmed_at" IS NOT NULL LIMIT 1)
    OR "users"."name" ILIKE '%admi%'
    OR "users"."username" ILIKE '%admi%')
  AND "organization_users"."organization_id" = 1
ORDER BY CASE
  WHEN LOWER(users.public_email) = 'admi' THEN 0
  WHEN LOWER(users.username) = 'admi' THEN 1
  WHEN LOWER(users.name) = 'admi' THEN 2
  ELSE 3
END ASC, "users"."name" ASC, "users"."id" ASC
LIMIT 20 OFFSET 0

4. InviteUsersFinder SAM and SAML SSO

Query plan

The full query used in intersect_seat_assignment_and_sso_scoped_users. SAM and SSO enforcement are not mutually exclusive, so a namespace with both active requires a seat assignment and a SAML identity.

Namespace 9970 has no SAML provider, so queries 4 and 5 use namespace 93586 (saml_providers.id 2300) instead, which has both seat assignments and enforced SSO.

Query 
EXPLAIN SELECT "users".*
FROM "users"
WHERE "users"."state" = 'active'
  AND "users"."user_type" IN (0, 6, 4, 13)
  AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
  AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
  AND "users"."id" IN (
    SELECT "users"."id" FROM (
      (SELECT "users".*
       FROM "users"
       INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
       WHERE "users"."state" = 'active'
         AND "users"."user_type" IN (0, 6, 4, 13)
         AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
         AND "identities"."saml_provider_id" = 2300)
      UNION
      (SELECT "users".*
       FROM "users"
       INNER JOIN "user_details" "user_detail" ON "user_detail"."user_id" = "users"."id"
       WHERE "users"."state" = 'active'
         AND "users"."user_type" IN (0, 6, 4, 13)
         AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
         AND "users"."user_type" = 13
         AND "user_detail"."provisioned_by_group_id" IN (
           SELECT "namespaces"."id" FROM "namespaces"
           WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
      UNION
      (SELECT "users".*
       FROM "users"
       INNER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
       INNER JOIN "projects" ON "projects"."id" = "user_details"."provisioned_by_project_id"
       WHERE "users"."state" = 'active'
         AND "users"."user_type" IN (0, 6, 4, 13)
         AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
         AND "users"."user_type" = 13
         AND "projects"."namespace_id" IN (
           SELECT "namespaces"."id" FROM "namespaces"
           WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
    ) users)

5. Entire search query SAM and SAML SSO from the controller concern

Query plan

Query 4 as issued by the controller, so with the organization scope, the service account eligibility conditions, the search term, and the ordering and pagination applied.

Query 
EXPLAIN SELECT "users".*
FROM "users"
INNER JOIN "organization_users" ON "organization_users"."user_id" = "users"."id"
LEFT OUTER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
LEFT JOIN namespaces AS provision_groups ON user_details.provisioned_by_group_id = provision_groups.id
WHERE "users"."state" = 'active'
  AND "users"."user_type" IN (0, 6, 4, 13)
  AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
  AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
  AND "users"."id" IN (
    SELECT "users"."id" FROM (
      (SELECT "users".*
       FROM "users"
       INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
       WHERE "users"."state" = 'active'
         AND "users"."user_type" IN (0, 6, 4, 13)
         AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
         AND "identities"."saml_provider_id" = 2300)
      UNION
      (SELECT "users".*
       FROM "users"
       INNER JOIN "user_details" "user_detail" ON "user_detail"."user_id" = "users"."id"
       WHERE "users"."state" = 'active'
         AND "users"."user_type" IN (0, 6, 4, 13)
         AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
         AND "users"."user_type" = 13
         AND "user_detail"."provisioned_by_group_id" IN (
           SELECT "namespaces"."id" FROM "namespaces"
           WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
      UNION
      (SELECT "users".*
       FROM "users"
       INNER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
       INNER JOIN "projects" ON "projects"."id" = "user_details"."provisioned_by_project_id"
       WHERE "users"."state" = 'active'
         AND "users"."user_type" IN (0, 6, 4, 13)
         AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
         AND "users"."user_type" = 13
         AND "projects"."namespace_id" IN (
           SELECT "namespaces"."id" FROM "namespaces"
           WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
    ) users)
  AND (users.user_type != 13 OR ((
        users.composite_identity_enforced = FALSE
        OR user_details.provisioned_by_group_id IS NULL
        OR user_details.provisioned_by_group_id IN (93586)
      ) AND (
        user_details.provisioned_by_group_id IS NULL
        OR user_details.provisioned_by_group_id IN (93586)
        OR provision_groups.parent_id IS NULL
      ) AND (
        user_details.provisioned_by_project_id IS NULL
        OR (FALSE = TRUE AND user_details.provisioned_by_project_id = NULL)
      )))
  AND ("users"."email" = 'admi'
    OR "users"."id" = (SELECT "emails"."user_id" FROM "emails" WHERE "emails"."email" = 'admi' AND "emails"."confirmed_at" IS NOT NULL LIMIT 1)
    OR "users"."name" ILIKE '%admi%'
    OR "users"."username" ILIKE '%admi%')
  AND "organization_users"."organization_id" = 1
ORDER BY CASE
  WHEN LOWER(users.public_email) = 'admi' THEN 0
  WHEN LOWER(users.username) = 'admi' THEN 1
  WHEN LOWER(users.name) = 'admi' THEN 2
  ELSE 3
END ASC, "users"."name" ASC, "users"."id" ASC
LIMIT 20 OFFSET 0

6. Entire search query for SAM and SAML SSO from the controller concern 2026-09-07

Query plan

Query 
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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
FROM (
  (SELECT "users".*
   FROM "users"
   INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
   WHERE "users"."state" = 'active'
     AND "users"."user_type" IN (0, 6, 4, 13)
     AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
     AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
     AND "identities"."saml_provider_id" = 2300)
  UNION
  (SELECT "users".*
   FROM "users"
   INNER JOIN "user_details" "user_detail" ON "user_detail"."user_id" = "users"."id"
   WHERE "users"."state" = 'active'
     AND "users"."user_type" IN (0, 6, 4, 13)
     AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
     AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
     AND "users"."user_type" = 13
     AND "user_detail"."provisioned_by_group_id" IN (
       SELECT "namespaces"."id" FROM "namespaces"
       WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
  UNION
  (SELECT "users".*
   FROM "users"
   INNER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
   INNER JOIN "projects" ON "projects"."id" = "user_details"."provisioned_by_project_id"
   WHERE "users"."state" = 'active'
     AND "users"."user_type" IN (0, 6, 4, 13)
     AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
     AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
     AND "users"."user_type" = 13
     AND "projects"."namespace_id" IN (
       SELECT "namespaces"."id" FROM "namespaces"
       WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
) users

7. Entire SQL query with SAM and SSO 2026-09-09

Query Plan

Query 
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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
FROM (
  (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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
   FROM "users"
   INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
   WHERE "users"."state" = 'active'
     AND "users"."user_type" IN (0, 6, 4, 13)
     AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
     AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
     AND "identities"."saml_provider_id" = 2300)
  UNION
  (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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
   FROM "users"
   INNER JOIN "user_details" "user_detail" ON "user_detail"."user_id" = "users"."id"
   WHERE "users"."state" = 'active'
     AND "users"."user_type" IN (0, 6, 4, 13)
     AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
     AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
     AND "users"."user_type" = 13
     AND "user_detail"."provisioned_by_group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
  UNION
  (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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
   FROM "users"
   INNER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
   INNER JOIN "projects" ON "projects"."id" = "user_details"."provisioned_by_project_id"
   WHERE "users"."state" = 'active'
     AND "users"."user_type" IN (0, 6, 4, 13)
     AND "users"."user_type" IN (0, 1, 2, 3, 4, 5, 8, 9, 10, 11, 13, 15, 16, 17)
     AND "users"."id" IN (SELECT "subscription_seat_assignments"."user_id" FROM "subscription_seat_assignments" WHERE "subscription_seat_assignments"."namespace_id" = 93586)
     AND "users"."user_type" = 13
     AND "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
) users
INNER JOIN "organization_users" ON "organization_users"."user_id" = "users"."id"
LEFT OUTER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
LEFT JOIN namespaces AS provision_groups ON user_details.provisioned_by_group_id = provision_groups.id
WHERE (users.user_type != 13 OR ((
    users.composite_identity_enforced = FALSE
    OR user_details.provisioned_by_group_id IS NULL
    OR user_details.provisioned_by_group_id IN (93586)
  ) AND (
    user_details.provisioned_by_group_id IS NULL
    OR user_details.provisioned_by_group_id IN (93586)
    OR provision_groups.parent_id IS NULL
  ) AND (
    user_details.provisioned_by_project_id IS NULL
    OR (FALSE = TRUE AND user_details.provisioned_by_project_id = NULL)
  )))
  AND ("users"."email" = 'admin'
    OR "users"."id" = (SELECT "emails"."user_id" FROM "emails" WHERE "emails"."email" = 'admin' AND "emails"."confirmed_at" IS NOT NULL LIMIT 1)
    OR "users"."name" ILIKE '%admin%'
    OR "users"."username" ILIKE '%admin%')
  AND "organization_users"."organization_id" = 1
ORDER BY CASE
    WHEN LOWER(users.public_email) = 'admin' THEN 0
    WHEN LOWER(users.username) = 'admin' THEN 1
    WHEN LOWER(users.name) = 'admin' THEN 2
    ELSE 3
  END ASC, "users"."name" ASC, "users"."id" ASC
LIMIT 20 OFFSET 0;

8. with_seat_assignment_in

Query Plan

Query 
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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
FROM "users"
WHERE "users"."id" IN (
  SELECT "subscription_seat_assignments"."user_id"
  FROM "subscription_seat_assignments"
  WHERE "subscription_seat_assignments"."namespace_id" = 93586
);

9. with_single_sign_on_access_to

Query plan

Query 
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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
FROM (
  (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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
   FROM "users"
   INNER JOIN "identities" ON "identities"."user_id" = "users"."id"
   WHERE "identities"."saml_provider_id" = 2300)
  UNION
  (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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
   FROM "users"
   INNER JOIN "user_details" "user_detail" ON "user_detail"."user_id" = "users"."id"
   WHERE "users"."user_type" = 13
     AND "user_detail"."provisioned_by_group_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
  UNION
  (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"."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"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress", "users"."color_mode_id", "users"."composite_identity_enforced", "users"."organization_id", "users"."otp_secret"
   FROM "users"
   INNER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
   INNER JOIN "projects" ON "projects"."id" = "user_details"."provisioned_by_project_id"
   WHERE "users"."user_type" = 13
     AND "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{93586}'))))
) users;
Edited by Lukas Wanko

Merge request reports