Query: network_firewall_outbound_analysis

with rules as (
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r ->> 'protocol' = 'icmp'
      and r ->> 'ports' = '0' then 'All ICMP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' = '0' then 'All TCP'
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' = '0' then 'All UDP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
      else concat('Procotol: ', r ->> 'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'addresses') as cidr
  where
    urn = $1
  union
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r ->> 'protocol' = 'icmp'
      and r ->> 'ports' = '0' then 'All ICMP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' = '0' then 'All TCP'
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' = '0' then 'All UDP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
      else concat('Procotol: ', r ->> 'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'droplet_ids') as cidr
  where
    urn = $1
  union
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r ->> 'protocol' = 'icmp'
      and r ->> 'ports' = '0' then 'All ICMP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' = '0' then 'All TCP'
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' = '0' then 'All UDP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
      else concat('Procotol: ', r ->> 'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'kubernetes_ids') as cidr
  where
    urn = $1
  union
  select
    urn,
    title,
    id,
    r ->> 'protocol' as protocol_number,
    cidr as cidr_block,
    r ->> 'ports' as ports,
    case
      when r ->> 'protocol' = 'icmp'
      and r ->> 'ports' = '0' then 'All ICMP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' = '0' then 'All TCP'
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' = '0' then 'All UDP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/TCP')
      when r ->> 'protocol' = 'udp'
      and r ->> 'ports' <> '0' then concat(r ->> 'ports', '/UDP')
      else concat('Procotol: ', r ->> 'protocol')
    end as rule_description
  from
    digitalocean_firewall,
    jsonb_array_elements(outbound_rules) as r,
    jsonb_array_elements_text(r -> 'destinations' -> 'load_balancer_uids') as cidr
  where
    urn = $1
)
select
  distinct title as id,
  title as title,
  'inbound' as category,
  null as from_id,
  null as to_id,
  0 as depth
from
  rules -- Rule Nodes
union
select
  concat(title, '_', rule_description) as id,
  rule_description as title,
  'rule' as category,
  null as from_id,
  null as to_id,
  1 as depth
from
  rules -- CIDR Nodes
union
select
  distinct cidr_block as id,
  cidr_block as title,
  'cidr_block' as category,
  null as from_id,
  null as to_id,
  2 as depth
from
  rules -- rule -> Firewall edge
union
select
  null as id,
  null as title,
  protocol_number as category,
  concat(title, '_', rule_description) as from_id,
  title as to_id,
  null as depth
from
  rules -- ip -> rule edge
union
select
  null as id,
  null as title,
  protocol_number as category,
  cidr_block as from_id,
  concat(title, '_', rule_description) as from_id,
  null as depth
from
  rules