Control: Name server records should use public IPs

powerpipe control run net_insights.control.dns_ns_all_ip_public

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

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