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