Mar 25, 2025 · Mar 21, 2025 · Mar 21, 2025 · Mar 21, 2025 · Mar 21, 2025 · Mar 21, 2025
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
 return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.GetOrganizationIDsByMemberIDs)(ctx, ids)
 }

 func (q *querier) GetOrganizationResourceCountByID(ctx context.Context, organizationID uuid.UUID) (database.GetOrganizationResourceCountByIDRow, error) {
 // Can read org members
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceOrganizationMember.InOrg(organizationID)); err != nil {
 return database.GetOrganizationResourceCountByIDRow{}, err
 }

 // Can read org workspaces
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceWorkspace.InOrg(organizationID)); err != nil {
 return database.GetOrganizationResourceCountByIDRow{}, err
 }

 // Can read org groups
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceGroup.InOrg(organizationID)); err != nil {
 return database.GetOrganizationResourceCountByIDRow{}, err
 }

 // Can read org templates
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceTemplate.InOrg(organizationID)); err != nil {
 return database.GetOrganizationResourceCountByIDRow{}, err
 }

 // Can read org provisioner daemons
 if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceProvisionerDaemon.InOrg(organizationID)); err != nil {
 return database.GetOrganizationResourceCountByIDRow{}, err
 }

 return q.db.GetOrganizationResourceCountByID(ctx, organizationID)
 }

 func (q *querier) GetOrganizations(ctx context.Context, args database.GetOrganizationsParams) ([]database.Organization, error) {
 fetch := func(ctx context.Context, _ interface{}) ([]database.Organization, error) {
 return q.db.GetOrganizations(ctx, args)
diff --git a/coderd/database/dbauthz/dbauthz_test.go b/coderd/database/dbauthz/dbauthz_test.go
 o := dbgen.Organization(s.T(), db, database.Organization{})
 check.Args(o.ID).Asserts(o, policy.ActionRead).Returns(o)
 }))
 s.Run("GetOrganizationResourceCountByID", s.Subtest(func(db database.Store, check *expects) {
 u := dbgen.User(s.T(), db, database.User{})
 o := dbgen.Organization(s.T(), db, database.Organization{})

 t := dbgen.Template(s.T(), db, database.Template{
 CreatedBy:      u.ID,
 OrganizationID: o.ID,
 })
 dbgen.Workspace(s.T(), db, database.WorkspaceTable{
 OrganizationID: o.ID,
 OwnerID:        u.ID,
 TemplateID:     t.ID,
 })
 dbgen.Group(s.T(), db, database.Group{OrganizationID: o.ID})
 dbgen.OrganizationMember(s.T(), db, database.OrganizationMember{
 OrganizationID: o.ID,
 UserID:         u.ID,
 })

 check.Args(o.ID).Asserts(
 rbac.ResourceOrganizationMember.InOrg(o.ID), policy.ActionRead,
 rbac.ResourceWorkspace.InOrg(o.ID), policy.ActionRead,
 rbac.ResourceGroup.InOrg(o.ID), policy.ActionRead,
 rbac.ResourceTemplate.InOrg(o.ID), policy.ActionRead,
 rbac.ResourceProvisionerDaemon.InOrg(o.ID), policy.ActionRead,
 ).Returns(database.GetOrganizationResourceCountByIDRow{
 WorkspaceCount:      1,
 GroupCount:          1,
 TemplateCount:       1,
 MemberCount:         1,
 ProvisionerKeyCount: 0,
 })
 }))
 s.Run("GetDefaultOrganization", s.Subtest(func(db database.Store, check *expects) {
 o, _ := db.GetDefaultOrganization(context.Background())
 check.Args().Asserts(o, policy.ActionRead).Returns(o)
diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
 return getOrganizationIDsByMemberIDRows, nil
 }

 func (q *FakeQuerier) GetOrganizationResourceCountByID(_ context.Context, organizationID uuid.UUID) (database.GetOrganizationResourceCountByIDRow, error) {
 q.mutex.RLock()
 defer q.mutex.RUnlock()

 workspacesCount := 0
 for _, workspace := range q.workspaces {
 if workspace.OrganizationID == organizationID {
 workspacesCount++
 }
 }

 groupsCount := 0
 for _, group := range q.groups {
 if group.OrganizationID == organizationID {
 groupsCount++
 }
 }

 templatesCount := 0
 for _, template := range q.templates {
 if template.OrganizationID == organizationID {
 templatesCount++
 }
 }

 organizationMembersCount := 0
 for _, organizationMember := range q.organizationMembers {
 if organizationMember.OrganizationID == organizationID {
 organizationMembersCount++
 }
 }

 provKeyCount := 0
 for _, provKey := range q.provisionerKeys {
 if provKey.OrganizationID == organizationID {
 provKeyCount++
 }
 }

 return database.GetOrganizationResourceCountByIDRow{
 WorkspaceCount:      int64(workspacesCount),
 GroupCount:          int64(groupsCount),
 TemplateCount:       int64(templatesCount),
 MemberCount:         int64(organizationMembersCount),
 ProvisionerKeyCount: int64(provKeyCount),
 }, nil
 }

 func (q *FakeQuerier) GetOrganizations(_ context.Context, args database.GetOrganizationsParams) ([]database.Organization, error) {
 q.mutex.RLock()
 defer q.mutex.RUnlock()
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/000310_update_protect_deleting_organization_function.down.sql b/coderd/database/migrations/000310_update_protect_deleting_organization_function.down.sql
 -- Drop trigger that uses this function
 DROP TRIGGER IF EXISTS protect_deleting_organizations ON organizations;

 -- Revert the function to its original implementation
 CREATE OR REPLACE FUNCTION protect_deleting_organizations()
    RETURNS TRIGGER AS
 $$
 DECLARE
    workspace_count int;
    template_count int;
    group_count int;
    member_count int;
    provisioner_keys_count int;
 BEGIN
    workspace_count := (
        SELECT count(*) as count FROM workspaces
        WHERE
            workspaces.organization_id = OLD.id
            AND workspaces.deleted = false
    );

    template_count := (
        SELECT count(*) as count FROM templates
        WHERE
            templates.organization_id = OLD.id
            AND templates.deleted = false
    );

    group_count := (
        SELECT count(*) as count FROM groups
        WHERE
            groups.organization_id = OLD.id
    );

    member_count := (
        SELECT count(*) as count FROM organization_members
        WHERE
            organization_members.organization_id = OLD.id
    );

    provisioner_keys_count := (
        Select count(*) as count FROM provisioner_keys
        WHERE
            provisioner_keys.organization_id = OLD.id
    );

    -- Fail the deletion if one of the following:
    -- * the organization has 1 or more workspaces
    -- * the organization has 1 or more templates
    -- * the organization has 1 or more groups other than "Everyone" group
    -- * the organization has 1 or more members other than the organization owner
    -- * the organization has 1 or more provisioner keys

    IF (workspace_count + template_count + provisioner_keys_count) > 0 THEN
            RAISE EXCEPTION 'cannot delete organization: organization has % workspaces, % templates, and % provisioner keys that must be deleted first', workspace_count, template_count, provisioner_keys_count;
    END IF;

    IF (group_count) > 1 THEN
            RAISE EXCEPTION 'cannot delete organization: organization has % groups that must be deleted first', group_count - 1;
    END IF;

    -- Allow 1 member to exist, because you cannot remove yourself. You can
    -- remove everyone else. Ideally, we only omit the member that matches
    -- the user_id of the caller, however in a trigger, the caller is unknown.
    IF (member_count) > 1 THEN
            RAISE EXCEPTION 'cannot delete organization: organization has % members that must be deleted first', member_count - 1;
    END IF;

    RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

 -- Re-create trigger that uses this function
 CREATE TRIGGER protect_deleting_organizations
    BEFORE DELETE ON organizations
    FOR EACH ROW
    EXECUTE FUNCTION protect_deleting_organizations();
Original file line number	Diff line number	Diff line change
Expand Up		@@ -1985,6 +1985,35 @@ func (q *querier) GetOrganizationIDsByMemberIDs(ctx context.Context, ids []uuid.
		return fetchWithPostFilter(q.auth, policy.ActionRead, q.db.GetOrganizationIDsByMemberIDs)(ctx, ids)
		}

		func (q *querier) GetOrganizationResourceCountByID(ctx context.Context, organizationID uuid.UUID) (database.GetOrganizationResourceCountByIDRow, error) {
		// Can read org members
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceOrganizationMember.InOrg(organizationID)); err != nil {
		return database.GetOrganizationResourceCountByIDRow{}, err
		}

		// Can read org workspaces
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceWorkspace.InOrg(organizationID)); err != nil {
		return database.GetOrganizationResourceCountByIDRow{}, err
		}

		// Can read org groups
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceGroup.InOrg(organizationID)); err != nil {
		return database.GetOrganizationResourceCountByIDRow{}, err
		}

		// Can read org templates
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceTemplate.InOrg(organizationID)); err != nil {
		return database.GetOrganizationResourceCountByIDRow{}, err
		}

		// Can read org provisioner daemons
		if err := q.authorizeContext(ctx, policy.ActionRead, rbac.ResourceProvisionerDaemon.InOrg(organizationID)); err != nil {
		return database.GetOrganizationResourceCountByIDRow{}, err
		}

		return q.db.GetOrganizationResourceCountByID(ctx, organizationID)
		}

		func (q *querier) GetOrganizations(ctx context.Context, args database.GetOrganizationsParams) ([]database.Organization, error) {
		fetch := func(ctx context.Context, _ interface{}) ([]database.Organization, error) {
		return q.db.GetOrganizations(ctx, args)
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -809,6 +809,39 @@ func (s *MethodTestSuite) TestOrganization() {
		o := dbgen.Organization(s.T(), db, database.Organization{})
		check.Args(o.ID).Asserts(o, policy.ActionRead).Returns(o)
		}))
		s.Run("GetOrganizationResourceCountByID", s.Subtest(func(db database.Store, check *expects) {
		u := dbgen.User(s.T(), db, database.User{})
		o := dbgen.Organization(s.T(), db, database.Organization{})

		t := dbgen.Template(s.T(), db, database.Template{
		CreatedBy: u.ID,
		OrganizationID: o.ID,
		})
		dbgen.Workspace(s.T(), db, database.WorkspaceTable{
		OrganizationID: o.ID,
		OwnerID: u.ID,
		TemplateID: t.ID,
		})
		dbgen.Group(s.T(), db, database.Group{OrganizationID: o.ID})
		dbgen.OrganizationMember(s.T(), db, database.OrganizationMember{
		OrganizationID: o.ID,
		UserID: u.ID,
		})

		check.Args(o.ID).Asserts(
		rbac.ResourceOrganizationMember.InOrg(o.ID), policy.ActionRead,
		rbac.ResourceWorkspace.InOrg(o.ID), policy.ActionRead,
		rbac.ResourceGroup.InOrg(o.ID), policy.ActionRead,
		rbac.ResourceTemplate.InOrg(o.ID), policy.ActionRead,
		rbac.ResourceProvisionerDaemon.InOrg(o.ID), policy.ActionRead,
		).Returns(database.GetOrganizationResourceCountByIDRow{
		WorkspaceCount: 1,
		GroupCount: 1,
		TemplateCount: 1,
		MemberCount: 1,
		ProvisionerKeyCount: 0,
		})
		}))
		s.Run("GetDefaultOrganization", s.Subtest(func(db database.Store, check *expects) {
		o, _ := db.GetDefaultOrganization(context.Background())
		check.Args().Asserts(o, policy.ActionRead).Returns(o)
Expand Down
Original file line number	Diff line number	Diff line change
Expand Up		@@ -3976,6 +3976,54 @@ func (q *FakeQuerier) GetOrganizationIDsByMemberIDs(_ context.Context, ids []uui
		return getOrganizationIDsByMemberIDRows, nil
		}

		func (q *FakeQuerier) GetOrganizationResourceCountByID(_ context.Context, organizationID uuid.UUID) (database.GetOrganizationResourceCountByIDRow, error) {
		q.mutex.RLock()
		defer q.mutex.RUnlock()

		workspacesCount := 0
		for _, workspace := range q.workspaces {
		if workspace.OrganizationID == organizationID {
		workspacesCount++
		}
		}

		groupsCount := 0
		for _, group := range q.groups {
		if group.OrganizationID == organizationID {
		groupsCount++
		}
		}

		templatesCount := 0
		for _, template := range q.templates {
		if template.OrganizationID == organizationID {
		templatesCount++
		}
		}

		organizationMembersCount := 0
		for _, organizationMember := range q.organizationMembers {
		if organizationMember.OrganizationID == organizationID {
		organizationMembersCount++
		}
		}

		provKeyCount := 0
		for _, provKey := range q.provisionerKeys {
		if provKey.OrganizationID == organizationID {
		provKeyCount++
		}
		}

		return database.GetOrganizationResourceCountByIDRow{
		WorkspaceCount: int64(workspacesCount),
		GroupCount: int64(groupsCount),
		TemplateCount: int64(templatesCount),
		MemberCount: int64(organizationMembersCount),
		ProvisionerKeyCount: int64(provKeyCount),
		}, nil
		}

		func (q *FakeQuerier) GetOrganizations(_ context.Context, args database.GetOrganizationsParams) ([]database.Organization, error) {
		q.mutex.RLock()
		defer q.mutex.RUnlock()
Expand Down
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,77 @@
		-- Drop trigger that uses this function
		DROP TRIGGER IF EXISTS protect_deleting_organizations ON organizations;

		-- Revert the function to its original implementation
		CREATE OR REPLACE FUNCTION protect_deleting_organizations()
		RETURNS TRIGGER AS
		$$
		DECLARE
		workspace_count int;
		template_count int;
		group_count int;
		member_count int;
		provisioner_keys_count int;
		BEGIN
		workspace_count := (
		SELECT count(*) as count FROM workspaces
		WHERE
		workspaces.organization_id = OLD.id
		AND workspaces.deleted = false
		);

		template_count := (
		SELECT count(*) as count FROM templates
		WHERE
		templates.organization_id = OLD.id
		AND templates.deleted = false
		);

		group_count := (
		SELECT count(*) as count FROM groups
		WHERE
		groups.organization_id = OLD.id
		);

		member_count := (
		SELECT count(*) as count FROM organization_members
		WHERE
		organization_members.organization_id = OLD.id
		);

		provisioner_keys_count := (
		Select count(*) as count FROM provisioner_keys
		WHERE
		provisioner_keys.organization_id = OLD.id
		);

		-- Fail the deletion if one of the following:
		-- * the organization has 1 or more workspaces
		-- * the organization has 1 or more templates
		-- * the organization has 1 or more groups other than "Everyone" group
		-- * the organization has 1 or more members other than the organization owner
		-- * the organization has 1 or more provisioner keys

		IF (workspace_count + template_count + provisioner_keys_count) > 0 THEN
		RAISE EXCEPTION 'cannot delete organization: organization has % workspaces, % templates, and % provisioner keys that must be deleted first', workspace_count, template_count, provisioner_keys_count;
		END IF;

		IF (group_count) > 1 THEN
		RAISE EXCEPTION 'cannot delete organization: organization has % groups that must be deleted first', group_count - 1;
		END IF;

		-- Allow 1 member to exist, because you cannot remove yourself. You can
		-- remove everyone else. Ideally, we only omit the member that matches
		-- the user_id of the caller, however in a trigger, the caller is unknown.
		IF (member_count) > 1 THEN
		RAISE EXCEPTION 'cannot delete organization: organization has % members that must be deleted first', member_count - 1;
		END IF;

		RETURN NEW;
		END;
		$$ LANGUAGE plpgsql;

		-- Re-create trigger that uses this function
		CREATE TRIGGER protect_deleting_organizations
		BEFORE DELETE ON organizations
		FOR EACH ROW
		EXECUTE FUNCTION protect_deleting_organizations();