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;