Query: vpc_security_group_ingress_rule_sankey

with associations as (
  -- attached ec2 instances
  select
    title,
    arn,
    'aws_ec2_instance' as category,
    sg ->> 'GroupId' as group_id
  from
    aws_ec2_instance,
    jsonb_array_elements(security_groups) as sg
  where
    sg ->> 'GroupId' = $1 -- attached lambda functions
  union all
  select
    title,
    arn,
    'aws_lambda_function' as category,
    sg
  from
    aws_lambda_function,
    jsonb_array_elements_text(vpc_security_group_ids) as sg
  where
    sg = $1 -- attached Classic ELBs
  union all
  select
    title,
    arn,
    'aws_ec2_classic_load_balancer' as category,
    sg
  from
    aws_ec2_classic_load_balancer,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached ALBs
  union all
  select
    title,
    arn,
    'aws_ec2_application_load_balancer' as category,
    sg
  from
    aws_ec2_application_load_balancer,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached NLBs
  union all
  select
    title,
    arn,
    'aws_ec2_network_load_balancer' as category,
    sg
  from
    aws_ec2_network_load_balancer,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached GWLBs
  union all
  select
    title,
    arn,
    'aws_ec2_gateway_load_balancer' as category,
    sg
  from
    aws_ec2_gateway_load_balancer,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached aws_ec2_launch_configuration
  union all
  select
    title,
    launch_configuration_arn,
    'aws_ec2_launch_configuration' as category,
    sg
  from
    aws_ec2_launch_configuration,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached DAX Cluster
  union all
  select
    title,
    arn,
    'aws_dax_cluster' as category,
    sg
  from
    aws_dax_cluster,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached aws_dms_replication_instance
  union all
  select
    title,
    arn,
    'aws_dms_replication_instance' as category,
    sg
  from
    aws_dms_replication_instance,
    jsonb_array_elements_text(vpc_security_groups) as sg
  where
    sg = $1 -- attached aws_efs_mount_target
  union all
  select
    title,
    mount_target_id,
    'aws_efs_mount_target' as category,
    sg
  from
    aws_efs_mount_target,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached aws_elasticache_cluster
  union all
  select
    title,
    arn,
    'aws_elasticache_cluster' as category,
    sg
  from
    aws_elasticache_cluster,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1 -- attached aws_rds_db_cluster
  union all
  select
    title,
    arn,
    'aws_rds_db_cluster' as category,
    sg
  from
    aws_rds_db_cluster,
    jsonb_array_elements_text(vpc_security_groups) as sg
  where
    sg = $1 -- attached aws_rds_db_instance
  union all
  select
    title,
    arn,
    'aws_rds_db_instance' as category,
    sg
  from
    aws_rds_db_instance,
    jsonb_array_elements_text(vpc_security_groups) as sg
  where
    sg = $1 -- attached aws_redshift_cluster
  union all
  select
    title,
    arn,
    'aws_redshift_cluster' as category,
    sg
  from
    aws_redshift_cluster,
    jsonb_array_elements_text(vpc_security_groups) as sg
  where
    sg = $1 -- attached aws_sagemaker_notebook_instance
  union all
  select
    title,
    arn,
    'aws_sagemaker_notebook_instance' as category,
    sg
  from
    aws_sagemaker_notebook_instance,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1
),
rules as (
  select
    concat(
      text(cidr_ipv4),
      text(cidr_ipv6),
      referenced_group_id,
      referenced_vpc_id,
      prefix_list_id
    ) as source,
    security_group_rule_id,
    case
      when ip_protocol = '-1' then 'All Traffic'
      when ip_protocol = 'icmp' then 'All ICMP'
      when from_port is not null
      and to_port is not null
      and from_port = to_port then concat(from_port, '/', ip_protocol)
      else concat(from_port, '-', to_port, '/', ip_protocol)
    end as port_proto,
    type,
    case
      when (
        cidr_ipv4 = '0.0.0.0/0'
        or cidr_ipv6 = '::/0'
      )
      and ip_protocol <> 'icmp'
      and (
        from_port = -1
        or (
          from_port = 0
          and to_port = 65535
        )
      ) then 'alert'
      else 'ok'
    end as category,
    group_id
  from
    aws_vpc_security_group_rule
  where
    group_id = $1
    and type = 'ingress'
) -- Nodes  ---------
select
  distinct concat('src_', source) as id,
  source as title,
  0 as depth,
  'source' as category,
  null as from_id,
  null as to_id
from
  rules
union
select
  distinct port_proto as id,
  port_proto as title,
  1 as depth,
  'port_proto' as category,
  null as from_id,
  null as to_id
from
  rules
union
select
  distinct sg.group_id as id,
  sg.group_name as title,
  2 as depth,
  'security_group' as category,
  null as from_id,
  null as to_id
from
  aws_vpc_security_group sg
  inner join rules sgr on sg.group_id = sgr.group_id
union
select
  distinct arn as id,
  title || '(' || category || ')' as title,
  -- TODO: Should this be arn instead?
  3 as depth,
  category,
  group_id as from_id,
  null as to_id
from
  associations -- Edges  ---------
union
select
  null as id,
  null as title,
  null as depth,
  category,
  concat('src_', source) as from_id,
  port_proto as to_id
from
  rules
union
select
  null as id,
  null as title,
  null as depth,
  category,
  port_proto as from_id,
  group_id as to_id
from
  rules