Query: dns_mx_report

with domain_list as (
  select distinct domain from net_dns_record where domain = $1
),
domain_mx_records as (
  select * from net_dns_record where domain = $1 and type = 'MX'
),
domain_mx_count as (
  select domain, count(*) from domain_mx_records where domain = $1 group by domain
),
mx_ips as (
  select * from net_dns_record where domain in (select target from domain_mx_records) and type = 'A'
),
mx_with_public_ips as (
  select
    domain_mx_records.domain,
    domain_mx_records.target,
    count(*)
  from
    domain_mx_records
    inner join mx_ips on domain_mx_records.target = mx_ips.domain
  group by domain_mx_records.domain, domain_mx_records.target
),
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
),
mx_record_with_ip_count as (
  select
    domain,
    count(*)
  from
    domain_mx_records
  where
    domain = $1
    and (select target ~ '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}')
  group by domain
),
mx_count_public_ips as (
  select
    domain_mx_records.domain,
    count(*) as ip_usage_count
  from
    domain_mx_records
    inner join mx_ips on domain_mx_records.target = mx_ips.domain
  where
    mx_ips.type = 'A'
  group by domain_mx_records.domain, mx_ips.ip
),
mx_public_ips_count_by_domain as (
  select domain, count(*) from mx_count_public_ips where ip_usage_count > 1 group by domain
),
mx_records as (
  select domain, rtrim(target, '.') as target, rtrim(target, '.') ~ '^[^.].*[^-_.]$' as is_valid from domain_mx_records where domain = $1
),
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
),
mx_record_count_by_domain as (
  select domain, count(*) from mx_record_with_ip group by domain order by domain
),
domain_name_with_dmarc as (
  select domain as full_domain, concat('_dmarc.', domain) as dmarc_domain from domain_list order by domain
),
domain_dmarc_list as (
  select domain, value from net_dns_record where domain in (select dmarc_domain from domain_name_with_dmarc) order by domain
)
select
  'MX records valid hostname' as "Recommendation",
  case
    when (select count(*) from mx_records where domain = domain_list.domain and not is_valid) > 0 then '❌'
    else '✅'
  end as "Status",
  case
    when (select count(*) from mx_records where domain = domain_list.domain and not is_valid) > 0 then 'Invalid MX record hostname(s): ' || (select string_agg(target, ', ') from mx_records where domain = domain_list.domain and not is_valid) || '.'
    else 'No MX records have invalid hostname.'
  end
    || ' It is recommended that MX record should have a valid domain or sub domain name and the name not starts or ends with a dot(.).' as "Result"
from
  domain_list
UNION
select
  'Multiple MX records' as "Recommendation",
  case
    when mx_record_count_by_domain.domain is null then '❌'
    when mx_record_count_by_domain.count < 2 then '❌'
    else '✅'
  end as "Status",
  case
    when (select count(*) from domain_mx_records where domain = domain_list.domain) < 2 and mx_record_count_by_domain.count > 2 then domain_list.domain || ' has 1 MX record, but that MX record has multiple IPs.'
    else domain_list.domain || ' has ' || (select count(*) from domain_mx_records where domain = domain_list.domain) || ' MX record(s).'
  end
    || ' It is recommended to use at least 2 MX records so that backup server can receive mail when one server goes down.' as "Result"
from
  domain_list
  left join mx_record_count_by_domain on domain_list.domain = mx_record_count_by_domain.domain
UNION
select
  'MX IPs are public' as "Recommendation",
  case
    when mx_record_with_private_ip.domain is null then '✅'
    else '❌'
  end as "Status",
  case
    when mx_record_with_private_ip.domain is null then 'All 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
    || ' For a server to be accessible on the public internet, it needs a public DNS record, and its IP address needs to be reachable on the internet.' as "Result"
from
  domain_list
  left join mx_record_with_private_ip on domain_list.domain = mx_record_with_private_ip.domain
UNION
select
  'MX is not IP' as "Recommendation",
  case
    when i.domain is null then '✅'
    else '❌'
  end as "Status",
  case
    when i.domain is null then 'MX records doesn''t contain IP address.'
    else 'At least one MX record contains IP address.'
  end
    || ' As per RFC1035 MX record domain name must point to a host which itself can be resolved in the DNS.
      An IP address could not be used as it would be interpreted as an unqualified domain name, which cannot be resolved.' as "Result"
from
  domain_list as d
  left join mx_record_with_ip_count as i on d.domain = i.domain
UNION
select
  'No duplicate MX A records' as "Recommendation",
  case
    when p.domain is null then '✅'
    else '❌'
  end as "Status",
  case
    when p.domain is null then 'MX records do not have duplicate IPs.'
    else 'MX records have duplicate IPs.'
  end
    || ' It is recommended to use different IPs for records so that if server goes down, other server can receive mail.' as "Result"
from
  domain_mx_count as d
  left join mx_public_ips_count_by_domain as p on d.domain = p.domain
UNION
select
  'Reverse MX A records' as "Recommendation",
  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 '❌'
    else '✅'
  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 reverse DNS (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
    || ' 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.' as "Result"
from
  domain_list
UNION
select
  'DMARC Record Published' as "Recommendation",
  case
    when dl.domain is null then '❌'
    when not dl.value like 'v=DMARC1%' then '❌'
    else '✅'
  end as "Status",
  case
    when dl.domain is null or not dl.value like 'v=DMARC1%' then 'DMARC record not found.'
    else 'DMARC record found.'
  end 
    || ' Domain-based Message Authentication, Reporting & Conformance (DMARC) is an email authentication, policy, and reporting protocol. It builds on the widely deployed SPF and DKIM protocols, adding linkage to the author ("From:") domain name, published policies for recipient handling of authentication failures, and reporting from receivers to senders, to improve and monitor protection of the domain from fraudulent email.' as "Result"
from
  domain_name_with_dmarc as d
  left join domain_dmarc_list as dl on d.dmarc_domain = dl.domain