[Security Solution] Prebuilt rule is duplicated during upgrade when its new version has a different rule type

[banderror]

🚨🚨🚨 This is a blocker for the 8.9.0 release 🚨🚨🚨

Related to: #161247 (comment)

Summary

When you have an installed prebuilt rule that can be upgraded, if Elastic has changed its type and in the new version it has a different rule type, then during the upgrade it will be duplicated -- instead of having 1 upgraded instance you will get 2 upgraded instances of the same rule.

Steps to reproduce

1. I started a fresh Kibana instance with xpack.securitySolution.prebuiltRulesPackageVersion: '8.3.1' and with empty ES data.
2. Made sure that 8.3.1 package version is installed in Integrations.
3. Installed all prebuilt rules.
4. Restarted Kibana without the xpack.securitySolution.prebuiltRulesPackageVersion setting.
5. Checked that 8.9.1 package version is installed in Integrations.
6. Saw 600+ prebuilt rules to upgrade and 100+ new prebuilt rules to install.
7. Upgraded the 4 rules from [Security Solution] Support rule type changes in the rule upgrade workflow #161247 (comment) one by one (you might need to find them by their old names that you can see in the linked diffs). All those rules have a different rule type in 8.9.1 compared to 8.3.1.
8. Upgraded the rest of the rules all at once.
9. Installed all the new rules (UPD: the duplication seems to happen at this step).

Actual behavior: The 4 rules I was upgrading one by one became duplicated. Each of the 2 duplicates has the same signature rule_id, but different object ids. The other rules were upgraded correctly without creating duplicates.

Expected behavior: Rules should not be duplicated during an upgrade, regardless of the way you upgrade them: one by one, a few of them in bulk, all of them in bulk.

Screenshots

Data

I was able to verify that only the 4 rules I was upgrading one by one have been duplicated:

## Number of rules in security-rule assets: 810
GET .kibana_security_solution/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        { "term": { "type": "security-rule" } }
      ]
    }
  },
  "aggs": {
    "rules_by_signature_id": {
      "cardinality": {
        "field": "security-rule.rule_id"
      }
    }
  }
}

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1775,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "rules_by_signature_id": {
      "value": 810
    }
  }
}

## Number of installed prebuilt rules: 817
## 4 of them are duplicates, 3 more were probably deprecated and removed from one of the newer packages
GET .kibana_alerting_cases/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        { "term": { "type": "alert" } },
        { "term": { "alert.consumer": "siem" } }
      ]
    }
  },
  "aggs": {
    "rules_by_signature_id": {
      "terms": {
        "field": "alert.params.ruleId"
      }
    }
  }
}

#! this request accesses system indices: [.kibana_alerting_cases_8.10.0_001], but in a future major version, direct access to system indices will be prevented by default
{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 817,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "rules_by_signature_id": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 803,
      "buckets": [
        {
          "key": "2d8043ed-5bda-4caf-801c-c1feb7410504",
          "doc_count": 2
        },
        {
          "key": "a00681e3-9ed6-447c-ab2c-be648821c622",
          "doc_count": 2
        },
        {
          "key": "cd66a5af-e34b-4bb0-8931-57d0a043f2ef",
          "doc_count": 2
        },
        {
          "key": "d76b02ef-fc95-4001-9297-01cb7412232f",
          "doc_count": 2
        },
        {
          "key": "000047bb-b27a-47ec-8b62-ef1a5d2c9e19",
          "doc_count": 1
        },
        {
          "key": "00140285-b827-4aee-aa09-8113f58a08f3",
          "doc_count": 1
        },
        {
          "key": "0022d47d-39c7-4f69-a232-4fe9dc7a3acd",
          "doc_count": 1
        },
        {
          "key": "00678712-b2df-11ed-afe9-f661ea17fbcc",
          "doc_count": 1
        },
        {
          "key": "0136b315-b566-482f-866c-1d8e2477ba16",
          "doc_count": 1
        },
        {
          "key": "015cca13-8832-49ac-a01b-a396114809f6",
          "doc_count": 1
        }
      ]
    }
  }
}

[elasticmachine]

Pinging @elastic/security-detections-response (Team:Detections and Resp)

[elasticmachine]

Pinging @elastic/security-solution (Team: SecuritySolution)

[vgomez-el]

Bug fixed and validated for 8.9 BC4.

Upgraded prebuilt rules from version 8.3.3 to 8.9.2

REC-20230718192721.mp4

[MadameSheema]

@vgomez-el can we close this ticket or is there any testing missing? Thanks! :)

[vgomez-el]

sorry @MadameSheema, I forgot to close it. Everything was ok.