Query: dns_ns_report

with domain_list as (
  select
    distinct domain,
    substring(
      domain
      from
        '^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)'
    ) as tld
  from
    net_dns_record
  where
    domain = $1
),
domain_parent_server as (
  select
    l.domain,
    d.domain as tld,
    d.target as parent_server
  from
    net_dns_record as d
    inner join domain_list as l on d.domain = l.tld
  where
    d.type = 'SOA'
  order by
    l.domain
),
domain_parent_server_ip as (
  select
    *
  from
    net_dns_record
  where
    domain in (
      select
        parent_server
      from
        domain_parent_server
    )
  order by
    domain
),
domain_parent_server_with_ip as (
  select
    domain_parent_server.domain,
    host(domain_parent_server_ip.ip) as ip_text
  from
    domain_parent_server
    inner join domain_parent_server_ip on domain_parent_server.parent_server = domain_parent_server_ip.domain
  where
    domain_parent_server_ip.type = 'A'
  order by
    domain_parent_server.domain
),
domain_parent_server_ns_list as (
  select
    net_dns_record.domain,
    net_dns_record.target
  from
    net_dns_record
    inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain
    and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text
    and net_dns_record.type = 'NS'
  order by
    net_dns_record.domain
),
parent_server_ns_count_by_domain as (
  select
    net_dns_record.domain,
    count(net_dns_record.target)
  from
    net_dns_record
    inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain
    and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text
    and net_dns_record.type = 'NS'
  group by
    net_dns_record.domain
  order by
    net_dns_record.domain
),
domain_ns_count as (
  select
    count(*)
  from
    net_dns_record
  where
    domain = $1
    and type = 'NS'
  group by
    domain
),
domain_ns_records as (
  select
    *
  from
    net_dns_record
  where
    domain in ($1)
    and type = 'NS'
),
ns_ips as (
  select
    domain,
    type,
    ip,
    host(ip) as ip_text
  from
    net_dns_record
  where
    domain in (
      select
        target
      from
        domain_ns_records
    )
    and type = 'A'
),
ns_with_ip as (
  select
    domain_ns_records.domain,
    host(ns_ips.ip) as ip_text
  from
    domain_ns_records
    inner join ns_ips on domain_ns_records.target = ns_ips.domain
  where
    ns_ips.type = 'A'
  order by
    domain_ns_records.domain
),
ns_with_name_server_record as (
  select
    domain_parent_server_ns_list.domain,
    domain_parent_server_ns_list.target,
    (
      select
        count as parent_server_ns_record_count
      from
        parent_server_ns_count_by_domain
      where
        domain = domain_parent_server_ns_list.domain
    ),
    (
      select
        count(*) as name_server_record_count
      from
        net_dns_record
      where
        domain = domain_parent_server_ns_list.domain
        and dns_server = ns_ips.ip_text
        and type = 'NS'
      group by
        domain
    )
  from
    domain_parent_server_ns_list
    left join ns_ips on domain_parent_server_ns_list.target = ns_ips.domain
  where
    ns_ips.ip is not null
  order by
    domain_parent_server_ns_list.domain
),
ns_with_different_ns_count as (
  select
    distinct domain
  from
    ns_with_name_server_record
  where
    parent_server_ns_record_count <> name_server_record_count
),
ns_record_with_ip as (
  select
    domain_ns_records.domain,
    domain_ns_records.target,
    ns_ips.ip,
    (
      ns_ips.ip << '10.0.0.0/8' :: inet
      or ns_ips.ip << '100.64.0.0/10' :: inet
      or ns_ips.ip << '172.16.0.0/12' :: inet
      or ns_ips.ip << '192.0.0.0/24' :: inet
      or ns_ips.ip << '192.168.0.0/16' :: inet
      or ns_ips.ip << '198.18.0.0/15' :: inet
    ) as is_private
  from
    domain_ns_records
    inner join ns_ips on domain_ns_records.target = ns_ips.domain
),
ns_record_with_private_ip as (
  select
    distinct domain
  from
    ns_record_with_ip
  where
    is_private
),
invalid_ns_count as (
  select
    domain,
    count(*)
  from
    net_dns_record
  where
    domain in ($1)
    and type = 'NS'
    and not target ~ '^([a-z0-9]+(-[a-z0-9]+)*\.)+[a-z]{2,}\.?$'
  group by
    domain
),
check_ips as (
  select
    distinct array_to_string(
      array_remove(
        string_to_array(text(ns_ips.ip), '.'),
        split_part(text(ns_ips.ip), '.', 4)
      ),
      '.'
    ),
    domain_ns_records.domain as domain
  from
    domain_ns_records
    inner join ns_ips on domain_ns_records.target = ns_ips.domain
  where
    ns_ips.type = 'A'
    and domain_ns_records.type = 'NS'
),
dns_record_count as (
  select
    domain,
    count(*)
  from
    net_dns_record
  where
    domain in (
      select
        domain
      from
        domain_list
    )
  group by
    domain
),
dns_cname_count as (
  select
    domain,
    count(*)
  from
    net_dns_record
  where
    domain in (
      select
        domain
      from
        domain_list
    )
    and type = 'CNAME'
  group by
    domain
),
count_stats as (
  select
    domain,
    (
      select
        count
      from
        dns_record_count
      where
        domain = domain_list.domain
    ) as all_record_count,
    (
      select
        count
      from
        dns_cname_count
      where
        domain = domain_list.domain
    ) as cname_record_count
  from
    domain_list
)
select
  'Multiple name servers' as "Recommendation",
  case
    when count < 2 then '❌'
    else '✅'
  end as "Status",
  count || ' NS record(s) found. As per RFC2182 section 5 domain record must have at least 3 name servers, and no more than 7.' as "Result"
from
  domain_ns_count
UNION
select
  'Name of name servers are valid' as "Recommendation",
  case
    when r.domain is null
    or r.count = 0 then '✅'
    else '❌'
  end as "Status",
  case
    when r.domain is null
    or r.count = 0 then 'Name servers have valid name format.'
    else 'At least one name server with invalid name format.'
  end || ' The names can contain only alphabetical characters (A-Z), numeric characters (0-9), the minus sign (-), and the period (.). Period characters are allowed only when they are used to delimit the components of domain style names.' as "Result"
from
  domain_list as d
  left join invalid_ns_count as r on d.domain = r.domain
UNION
select
  'Missing name servers reported by parent' as "Recommendation",
  case
    when ns_with_different_ns_count.domain is null then '✅'
    else '❌'
  end as "Status",
  case
    when ns_with_different_ns_count.domain is null then 'NS records returned by parent server is same as the one reported by your name servers.'
    else 'Name server records returned by parent server doesn''t match with your name servers [' || (
      select
        string_agg(target, ', ')
      from
        ns_with_name_server_record
      where
        parent_server_ns_record_count <> name_server_record_count
    ) || '].'
  end || ' Unmatched NS records can cause delays when resolving domain records, as it tries to contact a name server that is either non-existent or non-authoritative.' as "Result"
from
  domain_list
  left join ns_with_different_ns_count on domain_list.domain = ns_with_different_ns_count.domain
UNION
select
  'No CNAME record if an NS (or any other) record is present' as "Recommendation",
  case
    when all_record_count > 0
    and (
      cname_record_count is null
      or cname_record_count < 1
    ) then '✅'
    when cname_record_count > 0
    and all_record_count = cname_record_count then '✅'
    else '❌'
  end as "Status",
  case
    when all_record_count > 0
    and (
      cname_record_count is null
      or cname_record_count < 1
    ) then 'No CNAME record found.'
    when cname_record_count > 0
    and all_record_count = cname_record_count then 'CNAME record(s) [' || (
      select
        string_agg(target, ', ')
      from
        net_dns_record
      where
        domain = count_stats.domain
    ) || '].'
    else domain || ' has CNAME record along with NS (or any other) record.'
  end || ' A CNAME record is not allowed to coexist with any other data. This is often attempted by inexperienced administrators as an obvious way to allow your domain name to also be a host. However, DNS servers like BIND will see the CNAME and refuse to add any other resources for that name. Since no other records are allowed to coexist with a CNAME, the NS entries are ignored.' as "Result"
from
  count_stats
UNION
select
  'Different subnets' as "Recommendation",
  case
    when count(*) = 1 then '❌'
    else '✅'
  end as "Status",
  case
    when count(*) = 1 then 'Name servers are on the same subnet.'
    else 'Name servers appear to be dispersed.'
  end || ' As per RFC2182 section 3.1, it is recommended that the secondary servers must be placed at both topologically and geographically dispersed locations on the Internet, to minimize the likelihood of a single failure disabling all of them.' as "Result"
from
  check_ips
group by
  domain
UNION
select
  'IPs of name servers are public' as "Recommendation",
  case
    when ns_record_with_private_ip.domain is null then '✅'
    else '❌'
  end as "Status",
  case
    when ns_record_with_private_ip.domain is null then 'All NS records appear to use public IPs.'
    else domain_list.domain || ' has NS records using private IPs [' || (
      select
        host(ip)
      from
        ns_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 ns_record_with_private_ip on domain_list.domain = ns_record_with_private_ip.domain
UNION
select
  'Different autonomous systems' as "Recommendation",
  case
    when count(*) = 1 then '❌'
    else '✅'
  end as "Status",
  case
    when count(*) = 1 then 'Name servers are in same location.'
    else 'Name servers are located in different location.'
  end || ' As per RFC2182 section 3.1, it is recommended that the secondary servers must be placed at both topologically and geographically dispersed locations on the Internet, to minimize the likelihood of a single failure disabling all of them.' as "Result"
from
  check_ips
group by
  domain