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