Control: MX records should have reverse A record (PTR)

powerpipe control run net_insights.control.dns_mx_reverse_a_record

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

with domain_list as (
  select distinct domain from net_dns_record where domain in (select jsonb_array_elements_text(to_jsonb($1::text[])))
),
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_with_reverse_add as (
  select
    domain,
    target,
    (
      select
        concat(
          array_to_string(array(
            select nums[i] from generate_subscripts(nums, 1) as indices(i) order by i desc
          ), '.'), '.in-addr.arpa'
        ) as reversed
      from (select string_to_array(host(ip), '.') as nums) as data
    ) as reverse
  from
    mx_record_with_ip
),
mx_with_ptr_record_stats as (
  select
    domain,
    case
      when (select count(*) from net_dns_record where domain = mx_with_reverse_add.reverse and type = 'PTR' group by domain) is not null then true
      else false
    end as has_ptr_record,
    reverse as rev_add
  from
    mx_with_reverse_add
)
select
  domain as resource,
  case
    when (select count(*) from mx_with_ptr_record_stats where domain = domain_list.domain and not has_ptr_record group by domain) is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when (select count(*) from mx_with_ptr_record_stats where domain = domain_list.domain and not has_ptr_record group by domain) is not null
      then domain || ' MX records have no PTR entries: [' || (select string_agg(rev_add, ', ') from mx_with_ptr_record_stats where domain = domain_list.domain and not has_ptr_record) || '].'
    else domain || ' has PTR records for all MX records.'
  end as reason
from
  domain_list;