Query: vpc_subnet_public_and_private

with subnets_with_explicit_route as (
  select
    distinct (a ->> 'SubnetId') as all_sub
  from
    aws_vpc_route_table as t,
    jsonb_array_elements(associations) as a
  where
    a ->> 'SubnetId' is not null
),
public_subnets_with_explicit_route as (
  select
    distinct a ->> 'SubnetId' as SubnetId
  from
    aws_vpc_route_table as t,
    jsonb_array_elements(associations) as a,
    jsonb_array_elements(routes) as r
  where
    r ->> 'DestinationCidrBlock' = '0.0.0.0/0'
    and (
      r ->> 'GatewayId' like 'igw-%'
      or r ->> 'NatGatewayId' like 'nat-%'
    )
    and a ->> 'SubnetId' is not null
),
public_subnets_with_implicit_route as (
  select
    distinct route_table_id,
    vpc_id,
    region
  from
    aws_vpc_route_table as t,
    jsonb_array_elements(associations) as a,
    jsonb_array_elements(routes) as r
  where
    a ->> 'Main' = 'true'
    and r ->> 'DestinationCidrBlock' = '0.0.0.0/0'
    and (
      r ->> 'GatewayId' like 'igw-%'
      or r ->> 'NatGatewayId' like 'nat-%'
    )
),
subnet_accessibility as (
  select
    subnet_id,
    vpc_id,
    case
      when s.subnet_id in (
        select
          all_sub
        from
          subnets_with_explicit_route
        where
          all_sub not in (
            select
              SubnetId
            from
              public_subnets_with_explicit_route
          )
      ) then 'private'
      when p.SubnetId is not null
      or s.vpc_id in (
        select
          vpc_id
        from
          public_subnets_with_implicit_route
      ) then 'public'
      else 'private'
    end as access
  from
    aws_vpc_subnet as s
    left join public_subnets_with_explicit_route as p on p.SubnetId = s.subnet_id
)
select
  arn as resource,
  case
    when v.vpc_id not in (
      select
        vpc_id
      from
        subnet_accessibility
    ) then 'alarm'
    when 'public' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    )
    and 'private' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    ) then 'ok'
    when 'public' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    )
    and not 'private' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    ) then 'alarm'
    when 'private' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    )
    and not 'public' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    ) then 'alarm'
  end as status,
  case
    when v.vpc_id not in (
      select
        vpc_id
      from
        subnet_accessibility
    ) then v.title || ' has no subnet.'
    when 'public' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    )
    and 'private' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    ) then v.title || ' having both private and public subnet(s).'
    when 'public' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    )
    and not 'private' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    ) then v.title || ' having only public subnet(s).'
    when 'private' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    )
    and not 'public' in (
      select
        access
      from
        subnet_accessibility
      where
        vpc_id = v.vpc_id
    ) then v.title || ' having only private subnet(s).'
  end as reason,
  region,
  account_id
from
  aws_vpc as v;