Query: elasticache_cluster_no_public_subnet

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
),
cluster_public_subnet as (
  select
    distinct arn,
    cache_subnet_group_name
  from
    aws_elasticache_subnet_group,
    jsonb_array_elements(subnets) as s
    left join subnet_accessibility as a on a.subnet_id = s ->> 'SubnetIdentifier'
  where
    a.access = 'public'
)
select
  c.arn as resource,
  case
    when s.cache_subnet_group_name is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when s.cache_subnet_group_name is not null then c.title || ' has public subnet.'
    else c.title || ' has private subnet.'
  end as reason,
  region,
  account_id
from
  aws_elasticache_cluster as c
  left join cluster_public_subnet as s on s.cache_subnet_group_name = c.cache_subnet_group_name;