Control: MX records should use public IPs

Usage

Run the control in your terminal:

steampipe check net_insights.control.dns_mx_all_ip_public

Snapshot and share results via Steampipe Cloud:

steampipe login
steampipe check --share net_insights.control.dns_mx_all_ip_public

net_dns_record

Params

["github.com","microsoft.com"]

SQL

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;