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

Description

A PTR record is reverse version of an A record. In general A record maps a domain name to an IP address, but PTR record maps IP address to a hostname. It is recommended to use PTR record when using both internal or external mail servers. It allows the receiving end to check the hostname of your IP address.

Usage

Run the control in your terminal:

powerpipe control run net_insights.control.dns_mx_reverse_a_record

Snapshot and share results via Turbot Pipes:

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

Steampipe Tables

net_dns_record

Params

Args	Name	Default	Description	Variable
$1	domain_names	`["github.com","microsoft.com"]`	DNS domain names.	domain_names

SQL

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;