Control: Name servers should answer authoritatively

powerpipe control run net_insights.control.dns_ns_authoritative

powerpipe login
powerpipe control run net_insights.control.dns_ns_authoritative --share

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_ns_records as (
  select
    domain,
    target
  from
    net_dns_record
  where
    domain in (
      select
        domain
      from
        domain_list
      order by
        domain
    )
    and type = 'NS'
  order by
    domain
),
ns_ips as (
  select
    domain,
    ip,
    target,
    host(ip) as ip_text
  from
    net_dns_record
  where
    domain in (
      select
        target
      from
        domain_ns_records
    )
    and type = 'A'
  order by
    domain
),
ns_with_authoritative_stats as (
  select
    domain_ns_records.domain,
    domain_ns_records.target,
    case
      when ns_ips.ip is null then false
      else (
        select
          count(*)
        from
          net_dns_record
        where
          domain = domain_ns_records.domain
          and dns_server = ns_ips.ip_text
          and type = 'SOA'
        group by
          domain
      ) is not null
    end as is_authoritative
  from
    domain_ns_records
    left join ns_ips on domain_ns_records.target = ns_ips.domain
    and ns_ips.ip is not null
  order by
    domain_ns_records.target
),
ns_non_authoritative as (
  select
    distinct domain
  from
    ns_with_authoritative_stats
  where
    not is_authoritative
  order by
    domain
)
select
  domain_list.domain as resource,
  case
    when ns_non_authoritative.domain is null then 'ok'
    else 'alarm'
  end as status,
  case
    when ns_non_authoritative.domain is null then domain_list.domain || ' name servers listed at parent server answer authoritatively.'
    else domain_list.domain || ' name servers do not answer authoritatively: [' || (
      select
        string_agg(target, ', ')
      from
        ns_with_authoritative_stats
      where
        domain = domain_list.domain
        and not is_authoritative
    ) || '].'
  end as reason
from
  domain_list
  left join ns_non_authoritative on domain_list.domain = ns_non_authoritative.domain;