[Opendnssec-user] Old policies in database

[Casper Gielen]

On 18-11-11 11:25, Siôn Lloyd wrote:
> 
> There is a command:
> 
> ods-ksmutil policy purge
> 
> which removes policies that have no zones on them. Two things to note 
> though...
> 
> 1) This function is described as experimental as it doesn't get regular 
> use, so I would strongly advise backing up your database and kasp.xml 
> before running it.

Forunately I have a testing environment.

> 2) It might rely on kasp.xml matching the database, so you may need to 
> add at least:
> 
> <Policy name="default"></Policy>
> 
> for it to work.
> 
ists.opendnssec.org/mailman/listinfo/opendnssec-user

Let's find out!
Besides the 'default' zone there are three more old polices to remove:
'nostandby', 'nostandbykeys' and 'testshort'

non-matching kasp.xml:

root at metagross:~# ods-ksmutil policy purge
No zones on policy testshort; purging...
No keys to purge.
ERROR: error executing SQL - Cannot delete or update a parent row: a foreign key constraint fails (`opendnssec`.`keypairs`, CONSTRAINT `keypairs_ibfk_2` FOREIGN KEY (`policy_id`) REFERENCES `policies` (`id`))
SQL failed: Cannot delete or update a parent row: a foreign key constraint fails (`opendnssec`.`keypairs`, CONSTRAINT `keypairs_ibfk_2` FOREIGN KEY (`policy_id`) REFERENCES `policies` (`id`))

Then I added <Policy name="default"></Policy> like entries but it's not
sufficient:

root at metagross:~# ods-ksmutil update kasp
MySQL database host set to: localhost
MySQL database port set to: 3306
MySQL database schema set to: opendnssec
MySQL database user set to: opendnssec
MySQL database password set
zonelist filename set to /etc/opendnssec/zonelist.xml.
kasp filename set to /etc/opendnssec/kasp.xml.
ERROR: Can't find Signatures/Resign in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Signatures/Refresh in default in /etc/opendnssec/kasp.xml
ERROR: The Refresh interval (0 seconds) for default Policy in /etc/opendnssec/kasp.xml is less than or equal to the Resign interval (0 seconds)
ERROR: Can't find Signatures/Validity/Default in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Signatures/Validity/Denial in default in /etc/opendnssec/kasp.xml
ERROR: Validity/Default (0 seconds) for default policy in /etc/opendnssec/kasp.xml is less than the Refresh interval (0 seconds)
ERROR: Validity/Denial (0 seconds) for default policy in /etc/opendnssec/kasp.xml is less than or equal to the Refresh interval (0 seconds)
ERROR: Can't find Signatures/Jitter in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Signatures/InceptionOffset in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Keys/PublishSafety in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Keys/RetireSafety in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Keys/TTL in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Signatures/Resign in default in /etc/opendnssec/kasp.xml
ERROR: Can't find Denial/NSEC3/Resalt in default in /etc/opendnssec/kasp.xml
/usr/lib/opendnssec/kasp_checker.rb:478:in `/': divided by 0 (ZeroDivisionError)
        from /usr/lib/opendnssec/kasp_checker.rb:478:in `check_kasp_file'
        from /usr/lib/ruby/1.8/rexml/element.rb:892:in `each'
        from /usr/lib/ruby/1.8/rexml/xpath.rb:53:in `each'
        from /usr/lib/ruby/1.8/rexml/element.rb:892:in `each'
        from /usr/lib/opendnssec/kasp_checker.rb:354:in `check_kasp_file'
        from /usr/lib/opendnssec/kasp_checker.rb:344:in `open'
        from /usr/lib/opendnssec/kasp_checker.rb:344:in `check_kasp_file'
        from /usr/lib/opendnssec/kasp_checker.rb:67:in `check'
        from /usr/bin/ods-kaspcheck:110
ods-kaspcheck returned an error, please check your policy

So I added full definitions of the old policies and 'ods-ksmutil update kasp' is happy.
Purging still fails:

root at metagross:~# ods-ksmutil policy purge
*WARNING* This feature is experimental and has not been fully tested; are you sure? [y/N] y
zonelist filename set to /etc/opendnssec/zonelist.xml.
kasp filename set to /etc/opendnssec/kasp.xml.
MySQL database host set to: localhost
MySQL database port set to: 3306
MySQL database schema set to: opendnssec
MySQL database user set to: opendnssec
MySQL database password set
No zones on policy default; purging...
Key remove successful.G
Key remove successful.
Key remove successful.
....... many more .... 
Key remove successful.
Key remove successful.
ERROR: error executing SQL - Cannot delete or update a parent row: a foreign key constraint fails (`opendnssec`.`keypairs`, CONSTRAINT `keypairs_ibfk_2` FOREIGN KEY (`policy_id`) REFERENCES `policies` (`id`))
SQL failed: Cannot delete or update a parent row: a foreign key constraint fails (`opendnssec`.`keypairs`, CONSTRAINT `keypairs_ibfk_2` FOREIGN KEY (`policy_id`) REFERENCES `policies` (`id`))


Let's try that again:

root at metagross:~# ods-ksmutil policy purge
*WARNING* This feature is experimental and has not been fully tested; are you sure? [y/N] y
zonelist filename set to /etc/opendnssec/zonelist.xml.
kasp filename set to /etc/opendnssec/kasp.xml.
MySQL database host set to: localhost
MySQL database port set to: 3306
MySQL database schema set to: opendnssec
MySQL database user set to: opendnssec
MySQL database password set
No zones on policy default; purging...
Key not found: e9fb2c7db98f1ddf00e6b0a79bd33b5e
Key purge failed for policy default


I've also experienced the "Duplicate Keys" problem on this machine. The above might be an
artifact of that. As there were dozens of zones to clean up I may have made a few mistakes.

I realize the database has been corrupted. I still consider it an interesting learning
and I assume you do so as well. However, don't stress yourself looking for an answer.
I'm perfectly happy with trashing the entire installation if the situation becomes hopeless.

-- 
Casper Gielen <cgielen at uvt.nl> | LIS UNIX
PGP fingerprint = 16BD 2C9F 8156 C242 F981  63B8 2214 083C F80E 4AF7

Universiteit van Tilburg | Postbus 90153, 5000 LE
Warandelaan 2 | Telefoon 013 466 4100 | G 236 | http://www.uvt.nl