Mar 25, 2025 · Mar 12, 2025 · Mar 13, 2025 · Mar 13, 2025 · Mar 13, 2025 · Mar 13, 2025
diff --git a/cli/server.go b/cli/server.go

 if defaultEligibleNotSet {
 // nolint:gocritic // User count requires system privileges
 userCount, err := db.GetUserCount(dbauthz.AsSystemRestricted(ctx))
 userCount, err := db.GetUserCount(dbauthz.AsSystemRestricted(ctx), false)
 if err != nil {
 return nil, xerrors.Errorf("get user count: %w", err)
 }
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
 return update(q.log, q.auth, fetch, q.db.ActivityBumpWorkspace)(ctx, arg)
 }

 func (q *querier) AllUserIDs(ctx context.Context) ([]uuid.UUID, error) {
 func (q *querier) AllUserIDs(ctx context.Context, includeSystem bool) ([]uuid.UUID, error) {
 // Although this technically only reads users, only system-related functions should be
 // allowed to call this.
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
 return nil, err
 }
 return q.db.AllUserIDs(ctx)
 return q.db.AllUserIDs(ctx, includeSystem)
 }

 func (q *querier) ArchiveUnusedTemplateVersions(ctx context.Context, arg database.ArchiveUnusedTemplateVersionsParams) ([]uuid.UUID, error) {

 func (q *querier) DeleteOrganizationMember(ctx context.Context, arg database.DeleteOrganizationMemberParams) error {
 return deleteQ[database.OrganizationMember](q.log, q.auth, func(ctx context.Context, arg database.DeleteOrganizationMemberParams) (database.OrganizationMember, error) {
 member, err := database.ExpectOne(q.OrganizationMembers(ctx, database.OrganizationMembersParams(arg)))
 member, err := database.ExpectOne(q.OrganizationMembers(ctx, database.OrganizationMembersParams{
 OrganizationID: arg.OrganizationID,
 UserID:         arg.UserID,
 IncludeSystem:  false,
 }))
 if err != nil {
 return database.OrganizationMember{}, err
 }
 return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.GetAPIKeysLastUsedAfter)(ctx, lastUsed)
 }

 func (q *querier) GetActiveUserCount(ctx context.Context) (int64, error) {
 func (q *querier) GetActiveUserCount(ctx context.Context, includeSystem bool) (int64, error) {
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
 return 0, err
 }
 return q.db.GetActiveUserCount(ctx)
 return q.db.GetActiveUserCount(ctx, includeSystem)
 }

 func (q *querier) GetActiveWorkspaceBuildsByTemplateID(ctx context.Context, templateID uuid.UUID) ([]database.WorkspaceBuild, error) {
 return fetch(q.log, q.auth, q.db.GetUserByID)(ctx, id)
 }

 func (q *querier) GetUserCount(ctx context.Context) (int64, error) {
 func (q *querier) GetUserCount(ctx context.Context, includeSystem bool) (int64, error) {
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
 return 0, err
 }
 return q.db.GetUserCount(ctx)
 return q.db.GetUserCount(ctx, includeSystem)
 }

 func (q *querier) GetUserLatencyInsights(ctx context.Context, arg database.GetUserLatencyInsightsParams) ([]database.GetUserLatencyInsightsRow, error) {
 member, err := database.ExpectOne(q.OrganizationMembers(ctx, database.OrganizationMembersParams{
 OrganizationID: arg.OrgID,
 UserID:         arg.UserID,
 IncludeSystem:  false,
 }))
 if err != nil {
 return database.OrganizationMember{}, err
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
 s.Run("AllUserIDs", s.Subtest(func(db database.Store, check *expects) {
 a := dbgen.User(s.T(), db, database.User{})
 b := dbgen.User(s.T(), db, database.User{})
 check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(slice.New(a.ID, b.ID))
 check.Args(false).Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(slice.New(a.ID, b.ID))
 }))
 s.Run("CustomRoles", s.Subtest(func(db database.Store, check *expects) {
 check.Args(database.CustomRolesParams{}).Asserts(rbac.ResourceAssignRole, policy.ActionRead).Returns([]database.CustomRole{})
 check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead)
 }))
 s.Run("GetActiveUserCount", s.Subtest(func(db database.Store, check *expects) {
 check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
 check.Args(false).Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
 }))
 s.Run("GetUnexpiredLicenses", s.Subtest(func(db database.Store, check *expects) {
 check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead)
 check.Args(time.Now().Add(time.Hour*-1)).Asserts(rbac.ResourceSystem, policy.ActionRead)
 }))
 s.Run("GetUserCount", s.Subtest(func(db database.Store, check *expects) {
 check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
 check.Args(false).Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
 }))
 s.Run("GetTemplates", s.Subtest(func(db database.Store, check *expects) {
 dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
 return sql.ErrNoRows
 }

 func (q *FakeQuerier) AllUserIDs(_ context.Context) ([]uuid.UUID, error) {
 // nolint:revive // It's not a control flag, it's a filter.
 func (q *FakeQuerier) AllUserIDs(_ context.Context, includeSystem bool) ([]uuid.UUID, error) {
 q.mutex.RLock()
 defer q.mutex.RUnlock()
 userIDs := make([]uuid.UUID, 0, len(q.users))
 for idx := range q.users {
 if !includeSystem && q.users[idx].IsSystem {
 continue
 }

 userIDs = append(userIDs, q.users[idx].ID)
 }
 return userIDs, nil
 return apiKeys, nil
 }

 func (q *FakeQuerier) GetActiveUserCount(_ context.Context) (int64, error) {
 // nolint:revive // It's not a control flag, it's a filter.
 func (q *FakeQuerier) GetActiveUserCount(_ context.Context, includeSystem bool) (int64, error) {
 q.mutex.RLock()
 defer q.mutex.RUnlock()

 active := int64(0)
 for _, u := range q.users {
 if !includeSystem && u.IsSystem {
 continue
 }

 if u.Status == database.UserStatusActive && !u.Deleted {
 active++
 }
 return q.getUserByIDNoLock(id)
 }

 func (q *FakeQuerier) GetUserCount(_ context.Context) (int64, error) {
 // nolint:revive // It's not a control flag, it's a filter.
 func (q *FakeQuerier) GetUserCount(_ context.Context, includeSystem bool) (int64, error) {
 q.mutex.RLock()
 defer q.mutex.RUnlock()

 if !u.Deleted {
 existing++
 }

 if !includeSystem && u.IsSystem {
 continue
 }
 }
 return existing, nil
 }
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/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000301_system_user.down.sql b/coderd/database/migrations/000301_system_user.down.sql
 -- Remove system user from organizations
 DELETE FROM organization_members
 WHERE user_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0';

 -- Drop triggers first
 DROP TRIGGER IF EXISTS prevent_system_user_updates ON users;
 DROP TRIGGER IF EXISTS prevent_system_user_deletions ON users;

 -- Drop function
 DROP FUNCTION IF EXISTS prevent_system_user_changes();

 -- Delete user status changes
 DELETE FROM user_status_changes
 WHERE user_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0';

 -- Delete system user
 DELETE FROM users
 WHERE id = 'c42fdf75-3097-471c-8c33-fb52454d81c0';

 -- Drop index
 DROP INDEX IF EXISTS user_is_system_idx;

 -- Drop column
 ALTER TABLE users DROP COLUMN IF EXISTS is_system;
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1894,7 +1894,7 @@ func getGithubOAuth2ConfigParams(ctx context.Context, db database.Store, vals *c

		if defaultEligibleNotSet {
		// nolint:gocritic // User count requires system privileges
		userCount, err := db.GetUserCount(dbauthz.AsSystemRestricted(ctx))
		userCount, err := db.GetUserCount(dbauthz.AsSystemRestricted(ctx), false)
		if err != nil {
		return nil, xerrors.Errorf("get user count: %w", err)
		}
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1056,13 +1056,13 @@ func (q *querier) ActivityBumpWorkspace(ctx context.Context, arg database.Activi
		return update(q.log, q.auth, fetch, q.db.ActivityBumpWorkspace)(ctx, arg)
		}

		func (q *querier) AllUserIDs(ctx context.Context) ([]uuid.UUID, error) {
		func (q *querier) AllUserIDs(ctx context.Context, includeSystem bool) ([]uuid.UUID, error) {
		// Although this technically only reads users, only system-related functions should be
		// allowed to call this.
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
		return nil, err
		}
		return q.db.AllUserIDs(ctx)
		return q.db.AllUserIDs(ctx, includeSystem)
		}

		func (q *querier) ArchiveUnusedTemplateVersions(ctx context.Context, arg database.ArchiveUnusedTemplateVersionsParams) ([]uuid.UUID, error) {
Expand DownExpand Up		@@ -1315,7 +1315,11 @@ func (q *querier) DeleteOldWorkspaceAgentStats(ctx context.Context) error {

		func (q *querier) DeleteOrganizationMember(ctx context.Context, arg database.DeleteOrganizationMemberParams) error {
		return deleteQ[database.OrganizationMember](q.log, q.auth, func(ctx context.Context, arg database.DeleteOrganizationMemberParams) (database.OrganizationMember, error) {
		member, err := database.ExpectOne(q.OrganizationMembers(ctx, database.OrganizationMembersParams(arg)))
		member, err := database.ExpectOne(q.OrganizationMembers(ctx, database.OrganizationMembersParams{
		OrganizationID: arg.OrganizationID,
		UserID: arg.UserID,
		IncludeSystem: false,
		}))
		if err != nil {
		return database.OrganizationMember{}, err
		}
Expand DownExpand Up		@@ -1501,11 +1505,11 @@ func (q *querier) GetAPIKeysLastUsedAfter(ctx context.Context, lastUsed time.Tim
		return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.GetAPIKeysLastUsedAfter)(ctx, lastUsed)
		}

		func (q *querier) GetActiveUserCount(ctx context.Context) (int64, error) {
		func (q *querier) GetActiveUserCount(ctx context.Context, includeSystem bool) (int64, error) {
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
		return 0, err
		}
		return q.db.GetActiveUserCount(ctx)
		return q.db.GetActiveUserCount(ctx, includeSystem)
		}

		func (q *querier) GetActiveWorkspaceBuildsByTemplateID(ctx context.Context, templateID uuid.UUID) ([]database.WorkspaceBuild, error) {
Expand DownExpand Up		@@ -2529,11 +2533,11 @@ func (q *querier) GetUserByID(ctx context.Context, id uuid.UUID) (database.User,
		return fetch(q.log, q.auth, q.db.GetUserByID)(ctx, id)
		}

		func (q *querier) GetUserCount(ctx context.Context) (int64, error) {
		func (q *querier) GetUserCount(ctx context.Context, includeSystem bool) (int64, error) {
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceSystem); err != nil {
		return 0, err
		}
		return q.db.GetUserCount(ctx)
		return q.db.GetUserCount(ctx, includeSystem)
		}

		func (q *querier) GetUserLatencyInsights(ctx context.Context, arg database.GetUserLatencyInsightsParams) ([]database.GetUserLatencyInsightsRow, error) {
Expand DownExpand Up		@@ -3745,6 +3749,7 @@ func (q *querier) UpdateMemberRoles(ctx context.Context, arg database.UpdateMemb
		member, err := database.ExpectOne(q.OrganizationMembers(ctx, database.OrganizationMembersParams{
		OrganizationID: arg.OrgID,
		UserID: arg.UserID,
		IncludeSystem: false,
		}))
		if err != nil {
		return database.OrganizationMember{}, err
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1664,7 +1664,7 @@ func (s *MethodTestSuite) TestUser() {
		s.Run("AllUserIDs", s.Subtest(func(db database.Store, check *expects) {
		a := dbgen.User(s.T(), db, database.User{})
		b := dbgen.User(s.T(), db, database.User{})
		check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(slice.New(a.ID, b.ID))
		check.Args(false).Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(slice.New(a.ID, b.ID))
		}))
		s.Run("CustomRoles", s.Subtest(func(db database.Store, check *expects) {
		check.Args(database.CustomRolesParams{}).Asserts(rbac.ResourceAssignRole, policy.ActionRead).Returns([]database.CustomRole{})
Expand DownExpand Up		@@ -3649,7 +3649,7 @@ func (s *MethodTestSuite) TestSystemFunctions() {
		check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead)
		}))
		s.Run("GetActiveUserCount", s.Subtest(func(db database.Store, check *expects) {
		check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
		check.Args(false).Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
		}))
		s.Run("GetUnexpiredLicenses", s.Subtest(func(db database.Store, check *expects) {
		check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead)
Expand DownExpand Up		@@ -3692,7 +3692,7 @@ func (s *MethodTestSuite) TestSystemFunctions() {
		check.Args(time.Now().Add(time.Hour*-1)).Asserts(rbac.ResourceSystem, policy.ActionRead)
		}))
		s.Run("GetUserCount", s.Subtest(func(db database.Store, check *expects) {
		check.Args().Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
		check.Args(false).Asserts(rbac.ResourceSystem, policy.ActionRead).Returns(int64(0))
		}))
		s.Run("GetTemplates", s.Subtest(func(db database.Store, check *expects) {
		dbtestutil.DisableForeignKeysAndTriggers(s.T(), db)
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1549,11 +1549,16 @@ func (q *FakeQuerier) ActivityBumpWorkspace(ctx context.Context, arg database.Ac
		return sql.ErrNoRows
		}

		func (q *FakeQuerier) AllUserIDs(_ context.Context) ([]uuid.UUID, error) {
		// nolint:revive // It's not a control flag, it's a filter.
		func (q *FakeQuerier) AllUserIDs(_ context.Context, includeSystem bool) ([]uuid.UUID, error) {
		q.mutex.RLock()
		defer q.mutex.RUnlock()
		userIDs := make([]uuid.UUID, 0, len(q.users))
		for idx := range q.users {
		if !includeSystem && q.users[idx].IsSystem {
		continue
		}

		userIDs = append(userIDs, q.users[idx].ID)
		}
		return userIDs, nil
Expand DownExpand Up		@@ -2644,12 +2649,17 @@ func (q *FakeQuerier) GetAPIKeysLastUsedAfter(_ context.Context, after time.Time
		return apiKeys, nil
		}

		func (q *FakeQuerier) GetActiveUserCount(_ context.Context) (int64, error) {
		// nolint:revive // It's not a control flag, it's a filter.
		func (q *FakeQuerier) GetActiveUserCount(_ context.Context, includeSystem bool) (int64, error) {
		q.mutex.RLock()
		defer q.mutex.RUnlock()

		active := int64(0)
		for _, u := range q.users {
		if !includeSystem && u.IsSystem {
		continue
		}

		if u.Status == database.UserStatusActive && !u.Deleted {
		active++
		}
Expand DownExpand Up		@@ -6200,7 +6210,8 @@ func (q *FakeQuerier) GetUserByID(_ context.Context, id uuid.UUID) (database.Use
		return q.getUserByIDNoLock(id)
		}

		func (q *FakeQuerier) GetUserCount(_ context.Context) (int64, error) {
		// nolint:revive // It's not a control flag, it's a filter.
		func (q *FakeQuerier) GetUserCount(_ context.Context, includeSystem bool) (int64, error) {
		q.mutex.RLock()
		defer q.mutex.RUnlock()

Expand All		@@ -6209,6 +6220,10 @@ func (q *FakeQuerier) GetUserCount(_ context.Context) (int64, error) {
		if !u.Deleted {
		existing++
		}

		if !includeSystem && u.IsSystem {
		continue
		}
		}
		return existing, nil
		}
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,24 @@
		-- Remove system user from organizations
		DELETE FROM organization_members
		WHERE user_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0';

		-- Drop triggers first
		DROP TRIGGER IF EXISTS prevent_system_user_updates ON users;
		DROP TRIGGER IF EXISTS prevent_system_user_deletions ON users;

		-- Drop function
		DROP FUNCTION IF EXISTS prevent_system_user_changes();

		-- Delete user status changes
		DELETE FROM user_status_changes
		WHERE user_id = 'c42fdf75-3097-471c-8c33-fb52454d81c0';

		-- Delete system user
		DELETE FROM users
		WHERE id = 'c42fdf75-3097-471c-8c33-fb52454d81c0';

		-- Drop index
		DROP INDEX IF EXISTS user_is_system_idx;

		-- Drop column
		ALTER TABLE users DROP COLUMN IF EXISTS is_system;