[dns-operations] TLD key size/stats corrected

[Viktor Dukhovni]

On Wed, Jul 10, 2019 at 11:24:18AM -0400, Viktor Dukhovni wrote:

[ There was an error in my SQL query, making the previously
  posted key size and age stats substantially incorrect (I
  inadvertently took the age from the TLD table, not the DNSKEY
  table).  Updated stats below. This looks better than previously
  posted, as the TLD records have been around longer than
  their various keys. ]

> Presently the TLD ZSK age in quarters (90 day quanta, rounded down)
> and RSA bit size frequencies are:

     tld | age in quarters | rsa bits
    -----+-----------------+----------
     178 |              6+ |     1024
     473 |              6+ |     1280
      52 |              6+ |     2048
       1 |              6+ |     4096
       8 |               5 |     1024
      23 |               4 |     1024
       2 |               4 |     1280
       3 |               4 |     2048
       2 |               3 |     1024
       6 |               3 |     2048
      29 |               2 |     1024
       8 |               2 |     2048
      21 |               1 |     1024
      18 |               1 |     1280
      67 |               1 |     2048
     501 |               0 |     1024
       6 |               0 |     1152
     159 |               0 |     1280
      59 |               0 |     2048

> My data only goes back to Oct-2017, so the 6-quarter number is only
> a lower-bound, so the 6 quarter data points are likely in many cases
> older.  Updates would be especially welcome for the 178 TLDs with
> "aging" 1024-bit ZSKs.

> For comparison, the TLD KSK RSA ages and bit size frequencies are:

     tld  | age in quarters | rsa bits
    ------+-----------------+----------
	5 |              6+ |     1280
     1100 |              6+ |     2048
       11 |              6+ |     4096
       77 |               5 |     2048
       47 |               4 |     2048
	1 |               4 |     4096
       13 |               3 |     2048
	1 |               2 |     1024
       36 |               2 |     2048
	1 |               2 |     4096
       86 |               1 |     2048
	2 |               1 |     4096
	1 |               0 |     1280
       64 |               0 |     2048
	4 |               0 |     4096

> Here, the keys are primarily 2048-bit and up, and should not
> need frequent replacement, but there are still five TLDs with
> "aging" 1280-bit keys that should arguably still be rotated
> more frequently than every 630+ days:

      tld   | age in quarters | rsa bits
    --------+-----------------+----------
     bom    |              6+ |     1280
     final  |              6+ |     1280
     globo  |              6+ |     1280
     rio    |              6+ |     1280
     uol    |              6+ |     1280

-- 
	Viktor.

Relevant Schema:

	      tld:   qname  -- name of TLD
	"#dnskey":   qname, -- name of domain (TLD or other)
		       alg, -- DNSKEY algorithm number
		     flags, -- DNSKEY flags
		       kid, -- "dnskey_key" foreign key
		     stime, -- epoch time of inclusion in DNSKEY RRset
		     etime  -- epoch time of removal from DNSKEY RRset
     "dnskey_key":     seq, -- primary key (referenced by "#dnskey".kid)
		       key  -- DNSKEY byte array

Corrected PostgreSQL query:

    WITH N AS (SELECT extract(epoch from date_trunc('second', now())) AS n)
    SELECT count(distinct T.qname) AS tld,
           floor((N.n - D.stime) / 86400 / 90) AS "age in quarters",
           8*(length(K.key) - 1 - get_byte(K.key, 0)) AS "rsa bits"
    FROM N,
         tld T
         JOIN "#dnskey" D ON D.qname = T.qname
	 JOIN dnskey_key K ON K.seq = D.kid
	 WHERE D.etime IS NULL
           AND ((D.flags & 257) = 256)
           AND D.alg IN (5,7,8,10)
         GROUP BY 2,3
         ORDER BY 2 desc, 3;