Added limit of 100 for free tier service accounts

Smriti Gargrequested to merge
smriti-540776/add_limit_of_100_sa_to_free_tier into master

What does this MR do and why?

Introduces a limit of 100 service accounts for free tier (SaaS) and starter/unlicensed (self-managed) plans, gated behind the service_accounts_available_on_free_or_unlicensed feature flag.

Currently, service account creation is only allowed on paid plans (Premium/Ultimate on self-managed, paid subscriptions on SaaS). This MR adds the ability to create service accounts on free/starter tiers with a hard cap of 100, preparing for broader service account availability.

Relates to #540776

How it works

Instance-level (self-managed): When the license is not Premium/Ultimate, checks the feature flag and enforces User.service_accounts.count < 100 across the entire instance.

Namespace-level (SaaS): When a namespace has no subscription, no plan, or a free plan, falls back to a free tier check that:

  • Verifies the service_accounts_available_on_free_or_unlicensed feature flag is enabled for the namespace
  • Counts all service accounts in the namespace hierarchy (including composite identity SAs, unlike trial which excludes them)
  • Enforces the count is below 100

The limit logic is centralized in Authn::ServiceAccounts.free_tier_with_available_service_accounts? to avoid duplication across service classes.

Feature flag

When disabled, behavior is unchanged — free/starter plans cannot create service accounts.

Database queries introduced

New SQL queries

Query 1: Instance-level free tier service account count (self-managed)

Used in Users::ServiceAccounts::CreateService#active_subscription? when service_accounts_available_on_free_or_unlicensed FF is enabled and license is not Premium/Ultimate.

SELECT COUNT(*) FROM "users" WHERE "users"."user_type" = 13

https://console.postgres.aiundefined/shared/07d7d20b-ee93-43d2-89d0-0b2cd2ed7e0f

Query 2: SaaS free tier - all service accounts in hierarchy count (including composite identity)

Used in Authn::ServiceAccounts.all_service_accounts_in_hierarchy_count (called from BaseCreateService#active_subscription? and CreateService#active_subscription?) when service_accounts_available_on_free_or_unlicensed FF is enabled and namespace has no paid plan.

SELECT COUNT(*)
FROM (
  (SELECT "users"."id"
   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 UNNEST(
         COALESCE(
           (SELECT ids
            FROM (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids
                  FROM "namespace_descendants"
                  WHERE "namespace_descendants"."outdated_at" IS NULL
                    AND "namespace_descendants"."namespace_id" = 9970) cached_query),
           (SELECT ids
            FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids
                  FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                        FROM "namespaces"
                        WHERE "namespaces"."type" = 'User'
                          AND (traversal_ids @> ('{1}'))) namespaces) consistent_query))
       ) AS namespaces(id)
     ))
  UNION ALL
  (SELECT "users"."id"
   FROM "users"
   INNER JOIN "user_details" ON "user_details"."user_id" = "users"."id"
   WHERE "users"."user_type" = 13
     AND "user_details"."provisioned_by_project_id" IN (
       SELECT "projects"."id"
       FROM "projects"
       WHERE "projects"."namespace_id" IN (
         SELECT "namespaces"."id"
         FROM UNNEST(
           COALESCE(
             (SELECT ids
              FROM (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids
                    FROM "namespace_descendants"
                    WHERE "namespace_descendants"."outdated_at" IS NULL
                      AND "namespace_descendants"."namespace_id" = 9970) cached_query),
             (SELECT ids
              FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids
                    FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                          FROM "namespaces"
                          WHERE "namespaces"."type" = 'User'
                            AND (traversal_ids @> ('{1}'))) namespaces) consistent_query))
         ) AS namespaces(id)
       )))
) users;

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/49416/commands/147377

Note: Query 2 is structurally identical to the existing service_accounts_in_hierarchy_count query used for trial limits, except it uses .service_accounts (all service accounts including composite identity) instead of .service_accounts_without_composite_identity. The deduplication of these two methods is tracked in https://gitlab.com/gitlab-org/gitlab/-/work_items/592070.

DB analyis for same - !220148 (comment 3037207563). For current change we are removing the filter of composite_identity_id=true while fetching User rows from table. I am mentioning queries here accordingly

MR acceptance checklist

These are the checkboxes from the Definition of Done.

  • Tests added for all new/changed behavior
  • Changelog entry added (if needed)
Edited by Smriti Garg

Merge request reports