coder · Emyrk · May 20, 2026 · May 18, 2026 · May 18, 2026 · May 19, 2026
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -3476,6 +3476,15 @@ func (q *querier) GetGroupMembersCountByGroupID(ctx context.Context, arg databas
 	return memberCount, nil
 }
 
+func (q *querier) GetGroupMembersCountByGroupIDs(ctx context.Context, arg database.GetGroupMembersCountByGroupIDsParams) ([]database.GetGroupMembersCountByGroupIDsRow, error) {
+	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceGroup); err != nil {
+		// Ideally we would check read access on each group ID, but that would be N queries.
+		// So this function is really only usable by admins.
+		return nil, err
+	}
+	return q.db.GetGroupMembersCountByGroupIDs(ctx, arg)
+}
+
 func (q *querier) GetGroups(ctx context.Context, arg database.GetGroupsParams) ([]database.GetGroupsRow, error) {
 	if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err == nil {
 		// Optimize this query for system users as it is used in telemetry.

diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
@@ -1820,6 +1820,18 @@ func (s *MethodTestSuite) TestGroup() {
 		check.Args(arg).Asserts(g, policy.ActionRead)
 	}))
 
+	s.Run("GetGroupMembersCountByGroupIDs", s.Mocked(func(dbm *dbmock.MockStore, faker *gofakeit.Faker, check *expects) {
+		g1 := testutil.Fake(s.T(), faker, database.Group{})
+		g2 := testutil.Fake(s.T(), faker, database.Group{})
+		arg := database.GetGroupMembersCountByGroupIDsParams{GroupIds: []uuid.UUID{g1.ID, g2.ID}, IncludeSystem: false}
+		rows := []database.GetGroupMembersCountByGroupIDsRow{
+			{GroupID: g1.ID, MemberCount: 1},
+			{GroupID: g2.ID, MemberCount: 2},
+		}
+		dbm.EXPECT().GetGroupMembersCountByGroupIDs(gomock.Any(), arg).Return(rows, nil).AnyTimes()
+		check.Args(arg).Asserts(rbac.ResourceGroup, policy.ActionRead).Returns(rows)
+	}))
+
 	s.Run("GetGroupMembers", s.Mocked(func(dbm *dbmock.MockStore, _ *gofakeit.Faker, check *expects) {
 		dbm.EXPECT().GetGroupMembers(gomock.Any(), false).Return([]database.GroupMember{}, nil).AnyTimes()
 		check.Args(false).Asserts(rbac.ResourceSystem, policy.ActionRead)

diff --git a/coderd/database/dbmetrics/querymetrics.go b/coderd/database/dbmetrics/querymetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/groupmembers.sql b/coderd/database/queries/groupmembers.sql
@@ -142,6 +142,22 @@ WHERE group_id = @group_id
         user_is_system = false
         END;
 
+-- name: GetGroupMembersCountByGroupIDs :many
+-- Returns the total member count for each of the given group IDs in a
+-- single query. Used to avoid N+1 lookups when listing many groups. Like
+-- GetGroupMembersCountByGroupID, the count is returned even when the
+-- caller does not have read access to individual group members.
+SELECT
+	group_id,
+	COUNT(*) AS member_count
+FROM group_members_expanded
+WHERE group_id = ANY(@group_ids :: uuid[])
+	AND CASE
+		WHEN @include_system::bool THEN TRUE
+		ELSE user_is_system = false
+	END
+GROUP BY group_id;
+
 -- InsertUserGroupsByID adds a user to all provided groups, if they exist.
 -- name: InsertUserGroupsByID :many
 WITH groups AS (

diff --git a/coderd/database/queries/groups.sql b/coderd/database/queries/groups.sql
@@ -78,6 +78,15 @@ WHERE
 				groups.id = ANY(@group_ids)
 			ELSE true
 		END
+		-- Filter by group name or display name (substring, case-insensitive).
+		AND CASE WHEN @search :: text != '' THEN (
+				groups.name ILIKE concat('%', @search, '%')
+				OR groups.display_name ILIKE concat('%', @search, '%')
+			)
+			ELSE true
+		END
+-- A limit of 0 means "no limit".
+LIMIT NULLIF(@limit_opt :: int, 0)
 ;
 
 -- name: InsertGroup :one

diff --git a/codersdk/templates.go b/codersdk/templates.go
@@ -375,9 +375,19 @@ func (c *Client) UpdateTemplateACL(ctx context.Context, templateID uuid.UUID, re
 	return nil
 }
 
-// TemplateACLAvailable returns available users + groups that can be assigned template perms
-func (c *Client) TemplateACLAvailable(ctx context.Context, templateID uuid.UUID) (ACLAvailable, error) {
-	res, err := c.Request(ctx, http.MethodGet, fmt.Sprintf("/api/v2/templates/%s/acl/available", templateID), nil)
+// TemplateACLAvailable returns available users + groups that can be assigned
+// template perms. The optional req controls the q/limit/offset query
+// parameters applied server-side; pass codersdk.UsersRequest{} when no
+// filtering is desired.
+func (c *Client) TemplateACLAvailable(ctx context.Context, templateID uuid.UUID, req UsersRequest) (ACLAvailable, error) {
+	res, err := c.Request(
+		ctx,
+		http.MethodGet,
+		fmt.Sprintf("/api/v2/templates/%s/acl/available", templateID),
+		nil,
+		req.Pagination.asRequestOption(),
+		req.asRequestOption(),
+	)
 	if err != nil {
 		return ACLAvailable{}, err
 	}

diff --git a/enterprise/coderd/parameters_test.go b/enterprise/coderd/parameters_test.go
@@ -35,7 +35,9 @@ func TestDynamicParametersOwnerGroups(t *testing.T) {
 	_, noGroupUser := coderdtest.CreateAnotherUser(t, ownerClient, owner.OrganizationID)
 
 	// Create the group to be asserted
-	group := coderdtest.CreateGroup(t, ownerClient, owner.OrganizationID, "bloob", templateAdminUser)
+	// Make the group name something after "Everyone" when sorted alphabetically.
+	// The test wants to check that `Everyone` is the default, which is the first alphabetical group in the test.
+	group := coderdtest.CreateGroup(t, ownerClient, owner.OrganizationID, "zebra", templateAdminUser)
 
 	dynamicParametersTerraformSource, err := os.ReadFile("testdata/parameters/groups/main.tf")
 	require.NoError(t, err)

diff --git a/enterprise/coderd/templates.go b/enterprise/coderd/templates.go
@@ -9,6 +9,7 @@ import (
 	"golang.org/x/xerrors"
 
 	"cdr.dev/slog/v3"
+	agpl "github.com/coder/coder/v2/coderd"
 	"github.com/coder/coder/v2/coderd/audit"
 	"github.com/coder/coder/v2/coderd/database"
 	"github.com/coder/coder/v2/coderd/database/db2sdk"
@@ -17,6 +18,7 @@ import (
 	"github.com/coder/coder/v2/coderd/httpmw"
 	"github.com/coder/coder/v2/coderd/rbac/acl"
 	"github.com/coder/coder/v2/coderd/rbac/policy"
+	"github.com/coder/coder/v2/coderd/searchquery"
 	"github.com/coder/coder/v2/coderd/util/slice"
 	"github.com/coder/coder/v2/codersdk"
 )
@@ -50,39 +52,63 @@ func (api *API) templateAvailablePermissions(rw http.ResponseWriter, r *http.Req
 		return
 	}
 
+	// Apply the same q/limit semantics to groups as the users half of this response.
+	// The query semantics are defined for the users, which is awkward. But we can
+	// just reuse the search part of the query which is a fuzzy match.
+	userFilter, verr := searchquery.Users(r.URL.Query().Get("q"))
+	if len(verr) > 0 {
+		httpapi.Write(ctx, rw, http.StatusBadRequest, codersdk.Response{
+			Message:     "Invalid user search query.",
+			Validations: verr,
+		})
+		return
+	}
+	groupPagination, ok := agpl.ParsePagination(rw, r)
+	if !ok {
+		return
+	}
+
 	// Perm check is the template update check.
 	// nolint:gocritic
 	groups, err := api.Database.GetGroups(dbauthz.AsSystemRestricted(ctx), database.GetGroupsParams{
 		OrganizationID: template.OrganizationID,
+		Search:         userFilter.Search,
+		// #nosec G115 - Pagination limits are small and fit in int32
+		LimitOpt: int32(groupPagination.Limit),
 	})
 	if err != nil {
 		httpapi.InternalServerError(rw, err)
 		return
 	}
 
-	sdkGroups := make([]codersdk.Group, 0, len(groups))
-	for _, group := range groups {
-		// nolint:gocritic
-		members, err := api.Database.GetGroupMembersByGroupID(dbauthz.AsSystemRestricted(ctx), database.GetGroupMembersByGroupIDParams{
-			GroupID:       group.Group.ID,
-			IncludeSystem: false,
-		})
-		if err != nil {
-			httpapi.InternalServerError(rw, err)
-			return
-		}
+	// Fetch member counts for all groups in a single query to avoid an
+	// N+1 lookup pattern that was making this endpoint extremely slow on
+	// deployments with many groups. The per-group member lists are
+	// intentionally not populated here: callers of this endpoint only
+	// surface total_member_count (see Group.TotalMemberCount, which is
+	// already documented as the canonical value).
+	groupIDs := make([]uuid.UUID, len(groups))
+	for i, g := range groups {
+		groupIDs[i] = g.Group.ID
+	}
 
-		// nolint:gocritic
-		memberCount, err := api.Database.GetGroupMembersCountByGroupID(dbauthz.AsSystemRestricted(ctx), database.GetGroupMembersCountByGroupIDParams{
-			GroupID:       group.Group.ID,
-			IncludeSystem: false,
-		})
-		if err != nil {
-			httpapi.InternalServerError(rw, err)
-			return
-		}
+	// nolint:gocritic // Same justification as the GetGroups call above.
+	countRows, err := api.Database.GetGroupMembersCountByGroupIDs(dbauthz.AsSystemRestricted(ctx), database.GetGroupMembersCountByGroupIDsParams{
+		GroupIds:      groupIDs,
+		IncludeSystem: false,
+	})
+	if err != nil {
+		httpapi.InternalServerError(rw, err)
+		return
+	}
+	countByGroup := make(map[uuid.UUID]int64, len(countRows))
+	for _, row := range countRows {
+		countByGroup[row.GroupID] = row.MemberCount
+	}
 
-		sdkGroups = append(sdkGroups, db2sdk.Group(group, members, int(memberCount)))
+	sdkGroups := make([]codersdk.Group, 0, len(groups))
+	for _, group := range groups {
+		sdkGroups = append(sdkGroups, db2sdk.Group(group, nil, int(countByGroup[group.Group.ID])))
 	}
 
 	httpapi.Write(ctx, rw, http.StatusOK, codersdk.ACLAvailable{