Control: WWW IPs should use public IPs

powerpipe control run net_insights.control.dns_www_all_ip_public

powerpipe login
powerpipe control run net_insights.control.dns_www_all_ip_public --share

with domains_with_www as (
  select
    distinct fqdn,
    domain
  from
    (
      select
        domain,
        case
          when domain ilike 'www.%' then domain
          else 'www.' || domain
        end as fqdn
      from
        net_dns_record
      where
        domain in (
          select
            jsonb_array_elements_text(to_jsonb($1 :: text [ ]))
        )
    ) as domains
  order by
    fqdn
),
domain_with_www_record as (
  select
    domain,
    ip,
    (
      ip << '10.0.0.0/8' :: inet
      or ip << '100.64.0.0/10' :: inet
      or ip << '172.16.0.0/12' :: inet
      or ip << '192.0.0.0/24' :: inet
      or ip << '192.168.0.0/16' :: inet
      or ip << '198.18.0.0/15' :: inet
    ) as is_private
  from
    net_dns_record
  where
    domain in (
      select
        fqdn
      from
        domains_with_www
    )
    and type = 'A'
    and ip is not null
),
domain_with_www_with_private_ip as (
  select
    distinct domain
  from
    domain_with_www_record
  where
    is_private
)
select
  domains_with_www.domain as resource,
  case
    when domain_with_www_with_private_ip.domain is null then 'ok'
    else 'alarm'
  end as status,
  case
    when domain_with_www_with_private_ip.domain is null then domains_with_www.domain || ' WWW IPs appear to use public IPs.'
    else domains_with_www.domain || ' has WWW records using private IPs: [' || (
      select
        host(ip)
      from
        domain_with_www_record
      where
        domain = domains_with_www.domain
        and is_private
    ) || '].'
  end as reason
from
  domains_with_www
  left join domain_with_www_with_private_ip on domains_with_www.domain = domain_with_www_with_private_ip.domain;