[Opendnssec-user] planning key rollovers

[Mathieu Arnold]

+--On 23 octobre 2013 14:45:48 +0100 Siôn Lloyd <sion at nominet.org.uk>
wrote:
| On 23/10/13 08:40, Mathieu Arnold wrote:
|> Hi,
|> 
|> I'd like to have the ZSK rollovers spread along the two months period
|> that they last so that I don't get 1500 new keys at once.
|> I could write a script iterating the zones and sleepping <two
|> months>/<number of zones> between them, but it seems a bit counter
|> productive to have a script running that long.
|> So, I'm trying to see what the code does, but I'm not exactly certain I
|> understand it, it seems that if I want to update the database manually,
|> for each zone, I should :
|> update the dnsseckey to retire when I want it to, and update the keypair
|> associed to it with compromisedflag=1, fixedDate=1.
|> 
|> Before I go on and all hell breaks loose, am I missing something ?
|> 
| 
| This should work; although you don't need to set the compromised flag
| (fixed date alone should be enough).

Noted.

| It should then look like keys which were imported from outside of ODS -
| which have their retirement time fixed at the point of import.
| 
| If keys are shared you could have some strange effects where a new zone
| gets a key that would otherwise be about to be retired - so keys would
| be in use for longer than you might expect.

No shared keys, so, ok.

Ended up with the ugly, but functionnal :

#!/usr/bin/env ruby

require 'date'
require 'rubygems'
require 'dbi'

DBI.connect("DBI:Mysql:opendnssec:localhost", "opendnssec", "") do |dbh|
  now = DateTime.now + 1
  time = dbh.select_one("select value from parameters_policies where
parameter_id = 26 and policy_id = 1").first
  zones_sth = dbh.execute("SELECT id, name FROM zones ORDER BY name")
  zones = []
  zones_sth.each do |row|
    zones << [row[0], row[1]]
  end
  time = time / zones.size
  key_select = dbh.prepare("SELECT id, keypair_id FROM dnsseckeys WHERE
state = 4 AND keytype = 256 AND zone_id = ?")
  key_update = dbh.prepare("UPDATE dnsseckeys SET retire = ? WHERE id = ?")
  keypair_update = dbh.prepare("UPDATE keypairs SET fixedDate = 1 WHERE id
= ?")
  zones.shuffle.shuffle.each do |id,zone|
    key_select.execute(id)
    key_id, keypair_id, retire = key_select.fetch

    key_update.execute(now, key_id)
    keypair_update.execute(keypair_id)

    now = now + 1.0/86400*time
  end
end


-- 
Mathieu Arnold