Skip to content

Link security policies to compliance frameworks on policy change

Sashi Kumar Kumaresan requested to merge sk/428491-sync-create into master

What does this MR do and why?

This MR adds logic to link the compliance frameworks with security_orchestration_policy_configuration whenever a policy is updated/created. ComplianceManagement::ComplianceFramework::SecurityPolicy stores the mapping of security_orchestration_policy_configuration and compliance_frameworks along with the policy_index to distinguish between which policy has policy_scope

Addresses #428491 (closed)

Database query

BEGIN
  SELECT
      "compliance_framework_security_policies"."id" 
  FROM
      "compliance_framework_security_policies" 
  WHERE
      "compliance_framework_security_policies"."policy_configuration_id" = 39 
  ORDER BY
      "compliance_framework_security_policies"."id" ASC,
      "compliance_framework_security_policies"."updated_at" ASC LIMIT 1;

  SELECT
      "compliance_framework_security_policies"."id" 
  FROM
      "compliance_framework_security_policies" 
  WHERE
      "compliance_framework_security_policies"."policy_configuration_id" = 39 
      AND "compliance_framework_security_policies"."id" >= 4 
  ORDER BY
      "compliance_framework_security_policies"."id" ASC,
      "compliance_framework_security_policies"."updated_at" ASC LIMIT 1 OFFSET 1000;

  DELETE 
  FROM
      "compliance_framework_security_policies" 
  WHERE
      "compliance_framework_security_policies"."policy_configuration_id" = 39 
      AND "compliance_framework_security_policies"."id" >= 4;

  INSERT 
  INTO
      "compliance_framework_security_policies"
      ("framework_id","policy_configuration_id","policy_index","created_at","updated_at") 
  VALUES
      (2, 39, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
      (3, 39, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
      (2, 39, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP) ON CONFLICT  DO NOTHING RETURNING "id";
COMMIT 

How to set up and validate locally

  1. Create a new top level group and create compliance framework from [Settings -> General -> Compliance Framework] and make note of its ID

  2. Create new Policy for the the first top level group [Secure -> Policies -> Create new]

  3. Choose YAML-mode in the Policy Editor

  4. Add policy_scope:

    ...
    policy_scope:
      compliance_frameworks:
      - id:<ID of the other group's compliance framework>
    ...
  5. Verify that a new row is created in compliance_framework_security_policies table for the configuration_id and framework_id

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Sashi Kumar Kumaresan

Merge request reports

Loading