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;