Control: MX records should use public IPs

powerpipe control run net_insights.control.dns_mx_all_ip_public

powerpipe login
powerpipe control run net_insights.control.dns_mx_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_mx_records as (
  select domain, target from net_dns_record where domain in (select domain from domain_list) and type = 'MX' order by domain
),
mx_ips as (
  select domain, ip from net_dns_record where domain in (select target from domain_mx_records) and type = 'A'
),
mx_record_with_ip as (
  select
    domain_mx_records.domain,
    domain_mx_records.target,
    mx_ips.ip,
    (mx_ips.ip << '10.0.0.0/8'::inet or mx_ips.ip << '100.64.0.0/10'::inet or mx_ips.ip << '172.16.0.0/12'::inet or mx_ips.ip << '192.0.0.0/24'::inet or mx_ips.ip << '192.168.0.0/16'::inet or mx_ips.ip << '198.18.0.0/15'::inet) as is_private
  from
    domain_mx_records
    inner join mx_ips on domain_mx_records.target = mx_ips.domain
),
mx_record_with_private_ip as (
  select distinct domain from mx_record_with_ip where is_private
)
select
  domain_list.domain as resource,
  case
    when mx_record_with_private_ip.domain is null then 'ok'
    else 'alarm'
  end as status,
  case
    when mx_record_with_private_ip.domain is null then domain_list.domain || ' MX records appear to use public IPs.'
    else domain_list.domain || ' has MX records using private IPs: [' || (select host(ip) from mx_record_with_ip where domain = domain_list.domain and is_private) || '].'
  end as reason
from
  domain_list
  left join mx_record_with_private_ip on domain_list.domain = mx_record_with_private_ip.domain;