Create DB tables to store all policy YAML content

Why are we doing this work

The security policies are stored as YAML files in the security policy project. This approach has a lot of advantages (like version control for policies using git, auditable etc) but it faces some performance drawbacks. Since reading from the git repository requires calls to Gitaly, it gets difficult to add additional features

This issue focusses on adding the columns in new decomposed tables for fields from policy YAML that are redundant in approval_project_rules and approval_merge_request_rules table and are necessary to evaluate and enforce the policies without involving Gitaly. It also aims to update the corresponding models.

erDiagram
    security_orchestration_policy_configurations ||--|{ security_policies : " "
    security_policies ||--o{ scan_execution_policy_rules : " "
    security_policies ||--o{ scan_result_policy_rules : " "
    security_policies }o--o{ projects : "via security_policies_projects"
    scan_result_policy_rules ||--|| approval_group_rules : " "
    scan_result_policy_rules ||--|| approval_project_rules : " "
    scan_result_policy_rules ||--|| approval_merge_request_rules : " "
    scan_result_policy_rules ||--|| software_license_policies : " "
    scan_result_policy_rules ||--|| scan_result_policy_violations : " "

    security_orchestration_policy_configurations {
        int project_id
        int namespace_id
        int security_policy_management_project_id
    }
    security_policies {
        int security_orchestration_policy_configuration_id
        int policy_index
        text checksum
        text name
        text type
        text description
        boolean enabled
        jsonb policy_scope
        jsonb actions
        jsonb approval_settings
    }
    projects {
            int id
            text name
    }
    scan_execution_policy_rules {
        int security_policy_id
        int rule_index
        int type
        text checksum
        jsonb content
    }
    scan_result_policy_rules {
        int security_policy_id
        int rule_index
        int type
        text checksum
        jsonb content
    }

    approval_group_rules {
        int namespace_id
        int scan_result_policy_rule_id
    }
    approval_project_rules {
        int project_id
        int scan_result_policy_rule_id
    }
    approval_merge_request_rules {
        int merge_request_id
        int scan_result_policy_rule_id
    }
    software_license_policies {
        int project_id
        int scan_result_policy_rule_id
    }
    scan_result_policy_violations {
        int project_id
        int merge_request_id
        int scan_result_policy_rule_id
    }

Relevant links

• Epic
• Architecture blueprint

Non-functional requirements

• Documentation:
• Feature flag:
• Performance:
• Testing:

Implementation plan

• Create migration to create security_policies table with the following columns:

  • :security_orchestration_policy_configuration_id
  • :policy_index
  • :checksum
  • :name
  • :type
  • :description
  • :enabled
  • :policy_scope
  • :actions
  • :approval_settings

• Create scan_result_policy_rules table with the following columns:

  • security_policy_id
  • rule_index
  • type
  • checksum
  • content

• Create scan_execution_policy_rules table with the following columns:

  • security_policy_id
  • rule_index
  • type
  • checksum
  • content

• Create a join table security_policies_project:

  • security_policy_id
  • project_id

• Add foreign key for scan_result_policy_rule_id to the following tables:

  • approval_group_rules
  • approval_project_rules
  • approval_merge_request_rules
  • scan_result_policy_violations
  • software_license_policies

• Create SecurityPolicyRead model and add validations and enums

• Create a new service class Security::ScanResultPolicies::Read::CreateService to create SecurityPolicyRead from policy YAML

PoC that partially covers these changes: Draft: Add initial policies tables and save new... (!139352 - closed)

Verification steps