Skip to content

Add a GraphQL query to get organization users

Abdul Wadood requested to merge 409314-graphql-organization-user-dashboard into master

What does this MR do and why?

Add a GraphQL query to get organization users and the count of groups where user is a member.

Everything is experimental and therefore has been marked as alpha.

Query plans

  1. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21639/commands/70303
Raw query
SELECT
    "organization_users".*
FROM
    "organization_users"
WHERE
        "organization_users"."organization_id" = 1
ORDER BY
    "organization_users"."id" DESC
    LIMIT 101;
  1. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21639/commands/70305
Raw query
SELECT
    COUNT(*) AS "count_all",
    "user_id" AS "user_id"
FROM ((
          SELECT
              namespaces.id,
              namespaces.type,
              members.user_id AS user_id
          FROM
              "namespaces"
                  INNER JOIN "members" ON "members"."source_type" = 'Namespace'
                  AND "members"."requested_at" IS NULL
                  AND "members"."access_level" != 5
                AND "members"."source_id" = "namespaces"."id"
                AND "members"."type" = 'GroupMember'
          WHERE
              "namespaces"."type" = 'Group'
            AND "namespaces"."organization_id" = 1
            AND "members"."user_id" IN (10327656, 5413811)
            AND (access_level >= 10))
      UNION (
          SELECT
              namespaces.id,
              namespaces.type,
              project_authorizations.user_id AS user_id
          FROM
              "namespaces"
                  INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
                  INNER JOIN "project_authorizations" ON "project_authorizations"."project_id" = "projects"."id"
          WHERE
                  "namespaces"."type" = 'Group'
            AND "namespaces"."organization_id" = 1
            AND "project_authorizations"."user_id" IN (10327656, 5413811))) namespaces
GROUP BY
    "user_id";

The above query performs poorly because currently, all the groups are in the default organization with id = 1. Once we start migrating the groups to other organizations the query will start using the index_namespaces_on_organization_id index. Also, the read_organization_user policy is only enabled for non-default organizations for security reasons so this query won't run for the default organization. Currently, organization id = 2 doesn't exist on GitLab.com, I'm adding the query plan for it for reference:

  1. https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21639/commands/70307
Raw Query
SELECT
    COUNT(*) AS "count_all",
    "user_id" AS "user_id"
FROM ((
          SELECT
              namespaces.id,
              namespaces.type,
              members.user_id AS user_id
          FROM
              "namespaces"
                  INNER JOIN "members" ON "members"."source_type" = 'Namespace'
                  AND "members"."requested_at" IS NULL
                  AND "members"."access_level" != 5
                AND "members"."source_id" = "namespaces"."id"
                AND "members"."type" = 'GroupMember'
          WHERE
              "namespaces"."type" = 'Group'
            AND "namespaces"."organization_id" = 2
            AND "members"."user_id" IN (10327656, 5413811)
            AND (access_level >= 10))
      UNION (
          SELECT
              namespaces.id,
              namespaces.type,
              project_authorizations.user_id AS user_id
          FROM
              "namespaces"
                  INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id"
                  INNER JOIN "project_authorizations" ON "project_authorizations"."project_id" = "projects"."id"
          WHERE
                  "namespaces"."type" = 'Group'
            AND "namespaces"."organization_id" = 2
            AND "project_authorizations"."user_id" IN (10327656, 5413811))) namespaces
GROUP BY
    "user_id";

How to set up and validate locally

  1. Create an organization and add users and groups to it:
organization = Organizations::Organization.create!(name: 'GitLab', path: 'gitlab')

Organizations::OrganizationUser.create!(user_id: 1, organization_id: organization.id)
Organizations::OrganizationUser.create!(user_id: 2, organization_id: organization.id)

Group.id_in([22,27]).update(organization_id: organization.id)
  1. Run the below GraphQL query on https://gdk.test:3000/-/graphql-explorer:
{
  organization(id: "gid://gitlab/Organizations::Organization/1") {
    id
    path
    organizationUsers {
      edges {
        node {
          badges
          id
          user {
            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 #409314 (closed)

Edited by Manoj M J

Merge request reports

Loading