import - review & rework database incl. import in stored-procedures

[tpurschke]

for current stored procedure and error handling structure overview, see roles/database/files/sql/idempotent/fworch-import-main.sql

Challenges

Import

Rule order calculation

• [ ] decide between recalculating order number for a rulebase with each import the rulebase changs for all rules and using the current algo where the rule order of a rule inserted is calculated as a floating number to lie in between the two rules before and after this rule.

Section header handling

• [ ] if we are only storing a reference to a section header (or even just the name of the section) within a rule itself: when a section header is deleted we need to change all rules within this section to reflect that change (no section header (or worse: now we have to point to the section header before the deleted one)
• [ ] if there were no revisions (old rules), we could simply delete the section header (separate table) and use "ON DELETE SET NULL" to clear section ref

traffic query simulation

• [ ] when checking if a rule allows for a certain flow (ip-svc-ip) to pass a rulebase, we cannot look at each rule in isolation but need to take layer-guard rules of rulebases which contain the current rule into account

Others

• [ ] #2628

Immediate tasks

• [x] remove tables not needed

  • [x] error_log (replaced by alert) - need to modify code
  • [x] error - no, error codes still needed for e.g. change summary output
  • [x] import_changelog? --> no, needed for audit_log syncing
  • [x] report_template_viewable_by_user? --> no, this is used
  • [x] report_template_viewable_by_tenant?
  • [x] request --> re-create in schema request as request.original, add request.detailed (request.change_blueprint)
  • [x] request_object_change?
  • [x] request_rule_change?
  • [x] request_service_change?
  • [x] request_user_change?
  • [x] request_type? (source, ARS)
  • [x] rule_review
  • [x] stm_nattyp
  • [x] tenant_object?
  • [x] tenant_user?
  • [x] tenant_username

• [ ] remove fields not needed

  • [ ] changelog_object, changelog_service, changelog_rule, changelog_user
  • [ ] import_admin?
  • [ ] doku_admin
  • [ ] documented
  • [ ] docu_time
  • [ ] change_time?
  • [ ] unique_name?
  • [ ] device
  • [ ] tenant_id?
  • [ ] dev_active?
  • [ ] import_control
  • [ ] delimiter_group|zone|user|list?
  • [ ] management
  • [ ] tenant_id?
  • [ ] last_import_md5_rules|users|objects?
  • [ ] object
  • [ ] obj_location?
  • [ ] initial_config?
  • [ ] obj_sw?
  • [ ] obj_nat?
  • [ ] nattyp_id?
  • [ ] obj_nat_install|name|location|contact|description|readcom|writecom?
  • [ ] report_schedule
  • [ ] report_schedule_repeat, repetition, counter --> one of these?
  • [ ] rule
  • [ ] rule_num?
  • [ ] tenant
  • [ ] tenant_report??? used for what?
  • [ ] tenant_is_superadmin?
  • [ ] tenant_can_view_all_devices?

• [ ] remove unnecessary API metadata (fields, tables which do not need to be exposed)

• [ ] do we have a problem with multiple rulebases with the same name within a management?

          FOR r_rule IN -- check each rule if it was changed add add accordingly
      SELECT rule_id FROM import_rule WHERE control_id = i_current_import_id AND rulebase_name = v_rulebase_name AND (rule_type = 'access' OR rule_type IS NULL)

• [x] clear import_config after each import to save db space

• [ ] remove unnecessary indexes and possibly add new ones

• [ ] replace composite primary key for import_-tables (primary key ("svc_id","control_id")) with "svc_id" only

• [x] in import_rule_refhandler_main: add catch around each rule and print rule_uid in case of error (prio high)

• [ ] add ref checks for services in python code as well?

• [x] ref errors currently only get thrown in services. nw obj simply stays empty without any error, decide which behaviour is the right one - currently voting for not failing the import but collecting the missing refs as warnings (first textual, later in a separate table)

• [ ] do track and action resolving once and not for each single rule before calling insert_single_rule?

   IF char_length(cast(r_to_import.rule_track as varchar))=0 THEN s_track := 'none';
   ELSE   s_track := lower(r_to_import.rule_track);
        END IF;
   SELECT INTO i_track_id track_id FROM stm_track WHERE track_name = s_track; -- Track-ID holen
   IF NOT FOUND THEN  PERFORM error_handling('ERR_NO_TRACK', s_track);    END IF;
   -- fetch action id ------------------------------------------------------------------------------------------
   SELECT INTO i_action_id action_id FROM stm_action WHERE action_name = lower(r_to_import.rule_action); 
   IF NOT FOUND THEN  PERFORM error_handling('ERR_NO_ACTION', r_to_import.rule_action);   END IF;

• single-step import debugging (can we only get the decisive error in the log?)

• suppress warnings, infos, notices, ...

Midterm tasks

Rename columns

management

• ssh_hostname --> hostname
• ssh_port --> port
• ssh_user --> username
• config_path --> sub_management
• import_hostname --> responsible_importer_hostname

  object --> network

  tenant

• tenant_projekt --> tenant_project

Others

• [x] understand all components!?
• [x] #1554
• [x] decide on moving everything into python (keeping roll-back in mind)
• [ ] change the whole "last_seen" logic to "removed in import"
• [ ] restructure sql files and functions
• [ ] improve exception handling
• [ ] simplify
• [x] check for performance improvements (complexity)
• [ ] introduce proper exception handling (top-most)
• [ ] import fails when running in debug mode=5 (error in debugging?)

• [ ] also try to get rid of running import for the second time in debug mode

  
  begin
  
  create table yyy(a int);
  create table yyy(a int); -- this will cause an error

exception when others then

raise notice 'The transaction is in an uncommittable state. '
             'Transaction was rolled back';

raise notice '% %', SQLERRM, SQLSTATE;

end;

first ideas:
- [x] archive (data older than 3 years)
- [ ] non-active data (changed objects, rules)
- [ ] active (current) data

[tpurschke]

sample import times prod

Mar  4 04:49:12 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 04:49:12,983 - INFO - import_management: import no. 68423 for management fwmforti211__AnonAAAY (id=44) successful, change_count: 0, duration: 2302s
Mar  4 05:06:01 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:06:01,412 - INFO - import_management: import no. 68424 for management fwmforti211__AnonAABS (id=45) successful, change_count: 0, duration: 1008s
Mar  4 05:06:18 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:06:18,864 - INFO - import_management: import no. 68425 for management fwmforti211__AnonAABX (id=46) successful, change_count: 0, duration: 17s
Mar  4 05:06:32 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:06:32,505 - INFO - import_management: import no. 68426 for management fwmforti211__AnonAABX-Myra (id=47) successful, change_count: 0, duration: 14s
Mar  4 05:06:58 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:06:58,019 - INFO - import_management: import no. 68427 for management fwmforti211__AnonAABX-Service (id=48) successful, change_count: 0, duration: 26s
Mar  4 05:07:17 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:07:17,669 - INFO - import_management: import no. 68428 for management fwmforti211__AnonAACF (id=49) successful, change_count: 0, duration: 19s
Mar  4 05:07:23 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:07:23,551 - INFO - import_management: import no. 68429 for management fwmforti211__AnonAACF_V6-2 (id=50) successful, change_count: 0, duration: 6s
Mar  4 05:07:34 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:07:34,901 - INFO - import_management: import no. 68430 for management fwmforti211__AnonAACR (id=51) successful, change_count: 0, duration: 11s
Mar  4 05:22:24 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 05:22:24,006 - INFO - import_management: import no. 68431 for management fwmforti211__AnonAACXS (id=52) successful, change_count: 0, duration: 890s
Mar  4 08:48:17 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 08:48:17,997 - INFO - import_management: import no. 68432 for management fwmforti211__AnonAACXS-fpdc300 (id=56) successful, change_count: 7, duration: 12353s
Mar  4 09:03:05 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:03:05,176 - INFO - import_management: import no. 68433 for management fwmforti211__AnonAACXS-Internet (id=53) successful, change_count: 54, duration: 887s
Mar  4 09:04:43 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:04:43,375 - INFO - import_management: import no. 68434 for management fwmforti211__AnonAACXS-PublicCloud (id=54) successful, change_count: 0, duration: 98s
Mar  4 09:05:47 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:05:47,697 - INFO - import_management: import no. 68435 for management fwmforti211__AnonAACXS-Testnetz-6-0 (id=55) successful, change_count: 0, duration: 64s
Mar  4 09:17:46 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:17:46,297 - INFO - import_management: import no. 68436 for management fwmforti211__AnonAACXS_V6-0 (id=57) successful, change_count: 0, duration: 719s
Mar  4 09:18:06 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:18:06,973 - INFO - import_management: import no. 68437 for management fwmforti211__AnonAADL (id=59) successful, change_count: 0, duration: 20s
Mar  4 09:21:43 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:21:43,816 - INFO - import_management: import no. 68438 for management fwmforti211__AnonAAEJW (id=60) successful, change_count: 0, duration: 216s
Mar  4 09:21:54 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:21:54,611 - INFO - import_management: import no. 68439 for management fwmforti211__AnonAAEQ-IT (id=61) successful, change_count: 0, duration: 11s
Mar  4 09:24:12 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:24:12,819 - INFO - import_management: import no. 68440 for management fwmforti211__root (id=62) successful, change_count: 0, duration: 138s
Mar  4 09:31:14 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:31:14,543 - INFO - import_management: import no. 68441 for management fwmsmarttest21 (id=125) successful, change_count: 4, duration: 422s
Mar  4 09:31:15 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:31:15,253 - INFO - import_management: import no. 68442 for management AnonAAEJW_America_sckp091 (id=9) threw errors, change_count: 0, duration: 1s, ERRORS:   login failed: mgm_id=9, mgm_name=AnonAAEJW_America_sckp091, Login to FW management failed
Mar  4 09:31:15 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:31:15,463 - INFO - import_management - import disabled for mgm 76
Mar  4 09:31:16 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:31:16,068 - INFO - import_management: import no. 68443 for management AnonAAEJW_MDS__Mgmt-Cluster_America (id=124) threw errors, change_count: 0, duration: 1s, ERRORS:   login failed: mgm_id=124, mgm_name=AnonAAEJW_MDS__Mgmt-Cluster_America, Login to FW management failed
Mar  4 09:33:24 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:24,258 - INFO - import_management: import no. 68444 for management fwmcp21 (id=75) successful, change_count: 0, duration: 3s
Mar  4 09:33:26 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:26,399 - INFO - import_management: import no. 68445 for management fwmcp21__AnonAAAZ (id=104) successful, change_count: 0, duration: 2s
Mar  4 09:33:28 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:28,683 - INFO - import_management: import no. 68446 for management fwmcp21__AnonAABT (id=105) successful, change_count: 0, duration: 2s
Mar  4 09:33:30 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:30,688 - INFO - import_management: import no. 68447 for management fwmcp21__AnonAACGbank (id=106) successful, change_count: 0, duration: 2s
Mar  4 09:33:33 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:33,071 - INFO - import_management: import no. 68448 for management fwmcp21__fi (id=107) successful, change_count: 0, duration: 3s
Mar  4 09:33:35 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:35,081 - INFO - import_management: import no. 68449 for management fwmcp21__AnonAACT (id=108) successful, change_count: 0, duration: 2s
Mar  4 09:33:35 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:35,286 - ERROR - import_management - failed to get import lock for management id 109
Mar  4 09:33:37 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 09:33:37,490 - INFO - import_management: import no. 68451 for management fwmcp21__fwras21_maindomain (id=110) successful, change_count: 0, duration: 2s
Mar  4 11:48:31 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:48:31,706 - INFO - import_management: import no. 68452 for management fwmcp21__AnonAADN (id=111) successful, change_count: 11, duration: 8094s
Mar  4 11:48:34 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:48:34,275 - INFO - import_management: import no. 68453 for management fwmcp21__AnonAAEF (id=112) successful, change_count: 0, duration: 2s
Mar  4 11:57:11 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:11,932 - INFO - import_management: import no. 68454 for management fwmcp21__AnonAAEL (id=113) successful, change_count: 4, duration: 517s
Mar  4 11:57:14 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:14,100 - INFO - import_management: import no. 68455 for management fwmcp21__AnonAAENbayern (id=115) successful, change_count: 0, duration: 2s
Mar  4 11:57:16 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:16,176 - INFO - import_management: import no. 68456 for management fwmcp21__AnonAAEN_it (id=114) successful, change_count: 0, duration: 2s
Mar  4 11:57:18 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:18,216 - INFO - import_management: import no. 68457 for management fwmcp21__AnonAAFD (id=116) successful, change_count: 0, duration: 2s
Mar  4 11:57:20 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:20,286 - INFO - import_management: import no. 68458 for management fwmcp21__AnonAAFW (id=117) successful, change_count: 0, duration: 2s
Mar  4 11:57:22 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:22,350 - INFO - import_management: import no. 68459 for management fwmcp21__ras_wartung (id=118) successful, change_count: 0, duration: 2s
Mar  4 11:57:24 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:24,417 - INFO - import_management: import no. 68460 for management fwmcp21__AnonAAFY (id=119) successful, change_count: 0, duration: 2s
Mar  4 11:57:26 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:26,730 - INFO - import_management: import no. 68461 for management fwmcp21__AnonAAGV (id=120) successful, change_count: 0, duration: 2s
Mar  4 11:57:28 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:28,890 - INFO - import_management: import no. 68462 for management fwmcp21__AnonAAGX (id=121) successful, change_count: 0, duration: 2s
Mar  4 11:57:30 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:30,925 - INFO - import_management: import no. 68463 for management fwmcp21__test (id=122) successful, change_count: 0, duration: 2s
Mar  4 11:57:33 orctstAnonAACZ202 fworch-importer-api[1700471]: 2022-03-04 11:57:33,090 - INFO - import_management: import no. 68464 for management fwmcp21__AnonAAHN (id=123) successful, change_count: 0, duration: 2s

Import Tests

• using CP R8x mgm_id_106_config_native.json.anon
  • just importing one out of 3 rulebases with
  • 4280 rules, 6327 nw objects | 2114 service | 1 user,
  • initial import time: 307s
  • 2nd import: 286s (no changes)
• time measurement for phases:
  • get config from CP API (not tested, taken from file)
  • write config to file (debug level 4 only)
  • write data via API to import-tables
  • stored procedures - import_all_main (top level)
  • stored procedures - import_rules
  • stored procedures - import_rules_set_rule_num_numeric
  • stored procedures - import_global_refhandler_main
  • stored procedures - get_active_rules_with_broken_refs_per_mgm
  • stored procedures - import_changelog_sync

2022-03-16 16:22:16,532 - DEBUG - writing debug config json files took 165s (debug level 4)

2022-03-16 16:30:39,464 - DEBUG - import_management: get_config completed (including normalization), duration: 5s
2022-03-16 16:30:43,187 - DEBUG - import_management - writing debug config json files duration 4s
2022-03-16 16:30:43,187 - DEBUG - import_management - getting config total duration 9s
2022-03-16 16:32:43,524 - DEBUG - import_management - writing config to API and stored procedure import duration: 120s
2022-03-16 16:32:44,770 - INFO - import_management: import no. 33223 for management cp4k (id=55) successful, change_count: 0, duration: 130s

Test new (manual)

• get/parse config: 10s (incl. 7s json file dump in debug level 4)
• write to API: 4s (stopped manually with breakpoint in pgadmin import_all_main
• basic objects: 8s (stopped manually with breakpoint in pgadmin import_rules)
• import rules: 76s (stopped manually with breakpoint in pgadmin import_rules_set_rule_num_numeric:)
  • import_rules_xlate - 0s
  • import_rules_access - 71s
  • import_rules_combined - 0s
• up to start of refhandler_main: 76s (stopped manually with breakpoint in pgadmin import_global_refhandler_main)
• stored procedure: 80s

fworch@debian11:~/importer$ ./import-mgm.py -m55 -i /home/tim/mgm_id_106_config_native.json.anon -d4
2022-03-27 11:08:13 [INFO ] [common.py :import_man: 169] starting import of management cp4k(55), import_id=38296
2022-03-27 11:08:15 [DEBUG] [common.py :get_config: 267] has_config_changed: changes found or forced mode -> go ahead with getting config, Force = False
2022-03-27 11:08:16 [DEBUG] [fwcommon.p:get_config:  79] parsing layer fwdwp21_prod Security
2022-03-27 11:08:16 [WARNI] [fwcommon.p:get_config:  90] get_config - found 6 nat rulebases and 3 rulebases
2022-03-27 11:08:16 [DEBUG] [fwcommon.p:get_config:  79] parsing layer xxx-Policy Security
2022-03-27 11:08:16 [WARNI] [fwcommon.p:get_config:  90] get_config - found 6 nat rulebases and 3 rulebases
2022-03-27 11:08:16 [DEBUG] [fwcommon.p:get_config:  79] parsing layer xxx-Policy Security
2022-03-27 11:08:16 [WARNI] [fwcommon.p:get_config:  90] get_config - found 6 nat rulebases and 3 rulebases
2022-03-27 11:08:16 [DEBUG] [common.py :get_config: 289] import_management: get_config completed (including normalization), duration: 3s
2022-03-27 11:08:23 [DEBUG] [common.py :get_config: 310] import_management - writing debug config json files duration 7s
2022-03-27 11:08:23 [DEBUG] [common.py :import_man: 195] import_management - getting config total duration 10s
2022-03-27 11:09:44 [INFO ] [common.py :complete_i: 344] import_management: import no. 38296 for management cp4k (id=55) successful, change_count: 0, duration: 91s
fworch@debian11:~/importer$ 

[tpurschke]

Import without indexes takes 189s:

    fworch@debian11:~/importer$ ./import-mgm.py -m55 -i /home/tim/mgm_id_106_config_native.json.anon -d3
    ...
    2022-03-27 18:00:42 [INFO ] [common.py :complete_i: 344] import_management: import no. 38555 for management cp4k (id=55) successful, change_count: 0, duration: 189s

Import with Indexes takes only 92s

    fworch@debian11:~/importer$ ./import-mgm.py -m55 -i /home/tim/mgm_id_106_config_native.json.anon -d3
    ...
    2022-03-27 18:04:03 [INFO ] [common.py :complete_i: 344] import_management: import no. 38562 for management cp4k (id=55) successful, change_count: 0, duration: 92s
    fworch@debian11:~/importer$ 

Import with 3 additional indexes takes only 20s:

    Create index FwoIndex001 on import_rule (rule_id);
    Create index FwoIndex002 on zone (zone_name,mgm_id);
    Create index FwoIndex003 on rule (rule_uid,mgm_id,dev_id,active,nat_rule,xlate_rule);

    fworch@debian11:~/importer$ ./import-mgm.py -m55 -i /home/tim/mgm_id_106_config_native.json.anon -d3
    ...
    2022-03-27 18:24:05 [INFO ] [common.py :complete_i: 344] import_management: import no. 38584 for management cp4k (id=55) successful, change_count: 0, duration: 20s
    fworch@debian11:~/importer$