[Opendnssec-user] Error converting from 1.4.14 to 2.1.8

[Havard Eidnes]

>>> 1) We're using <NSEC3> for denial-of-existence.  NSEC3 uses a
>>> "salt" value as an input value to the process.  If we move away
>>> the old /var/opendnssec/signconf/ directory and create it anew,
>>> OpenDNSSEC will populate it with an xml file per zone.  However,
>>> they all have this part:
>>> 
>>>     <Denial>
>>>       <NSEC3>
>>>         <Hash>
>>>           <Algorithm>1</Algorithm>
>>>           <Iterations>5</Iterations>
>>>           <Salt>0</Salt>
>>>         </Hash>
>>>       </NSEC3>
>>>     </Denial>
>>
>> Okay that salt is clearly wrong, is should be a hex string.  I need
>> to check where this comes from, which takes a bit of time.  Did you
>> have an explicit salt specified in your 1.4 installation?
>> So instead of
>>    <Salt length="8"/>
>> You had 
>>    <Salt>cd79fa0214ff93b7</Salt>
>> in your kasp.xml?

Having just run the "./convert_sqlite" script to convert from the
old kasp.db file to the new kasp.db file & schema, I get when
inspecting the old db:

$ sqlite3 /var/db/opendnssec/kasp-1.db 
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> .tables
KEYALLOC_VIEW        dbadmin              policies           
KEYDATA_VIEW         dnsseckeys           securitymodules    
PARAMETER_LIST       keypairs             serialmodes        
PARAMETER_VIEW       parameters           zones              
categories           parameters_policies
sqlite> .schema policies
CREATE TABLE policies (
  id            integer primary key autoincrement,    -- id
  name          varchar(30) not null,  -- name of the policy
  description   varchar(255), -- description of the
  salt          varchar(512), -- value of the salt
  salt_stamp    varchar(64),  -- when the salt was generated
  audit         text, -- contents of <Audit>

  unique(name)
);
sqlite> SELECT salt from policies;
ccae9067625332c1
sqlite> ^D
$

and the new:

$ sqlite3 /var/db/opendnssec/kasp.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> .tables
databaseVersion  keyData          keyState         policyKey      
hsmKey           keyDependency    policy           zone           
sqlite> .schema policy
CREATE TABLE policy (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    rev INTEGER NOT NULL DEFAULT 1,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    signaturesResign UNSIGNED INT NOT NULL,
    signaturesRefresh UNSIGNED INT NOT NULL,
    signaturesJitter UNSIGNED INT NOT NULL,
    signaturesInceptionOffset UNSIGNED INT NOT NULL,
    signaturesValidityDefault UNSIGNED INT NOT NULL,
    signaturesValidityDenial UNSIGNED INT NOT NULL,
    signaturesValidityKeyset UNSIGNED INT,
    signaturesMaxZoneTtl UNSIGNED INT NOT NULL,
    denialType INT NOT NULL,
    denialOptout UNSIGNED INT NOT NULL,
    denialTtl UNSIGNED INT NOT NULL,
    denialResalt UNSIGNED INT NOT NULL,
    denialAlgorithm UNSIGNED INT NOT NULL,
    denialIterations UNSIGNED INT NOT NULL,
    denialSaltLength UNSIGNED INT NOT NULL,
    denialSalt TEXT NOT NULL,
    denialSaltLastChange UNSIGNED INT NOT NULL,
    keysTtl UNSIGNED INT NOT NULL,
    keysRetireSafety UNSIGNED INT NOT NULL,
    keysPublishSafety UNSIGNED INT NOT NULL,
    keysShared UNSIGNED INT NOT NULL,
    keysPurgeAfter UNSIGNED INT NOT NULL,
    zonePropagationDelay UNSIGNED INT NOT NULL,
    zoneSoaTtl UNSIGNED INT NOT NULL,
    zoneSoaMinimum UNSIGNED INT NOT NULL,
    zoneSoaSerial INT NOT NULL,
    parentRegistrationDelay UNSIGNED INT NOT NULL,
    parentPropagationDelay UNSIGNED INT NOT NULL,
    parentDsTtl UNSIGNED INT NOT NULL,
    parentSoaTtl UNSIGNED INT NOT NULL,
    parentSoaMinimum UNSIGNED INT NOT NULL,
    passthrough UNSIGNED INT NOT NULL
);
CREATE UNIQUE INDEX policyName ON policy ( name );
sqlite> SELECT denialSalt from policy;
0
sqlite> 

Clearly that's not correct.  Looking at the kasp-1.db file with
sqlite and trying to reproduce the statement from the conversion
script

UPDATE policy
SET denialSalt = (
        SELECT salt
        FROM  REMOTE.policies
        WHERE REMOTE.policies.id = policy.id)
WHERE (
        SELECT salt
        FROM  REMOTE.policies
        WHERE REMOTE.policies.id = policy.id) != null;

reveals that this does exactly nothing:

$ sqlite3 /var/db/opendnssec/kasp-1.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> select salt from policies where policies.id=1;
ccae9067625332c1
sqlite> select salt from policies where policies.id=1 and ( select salt from policies where policies.id=1) != null;
sqlite> ^D
$

Well, my colleague says that the "!= null" construct is unusual,
and that it should probably have been "is not null" instead, and
sqlite3 seems to agree:

$ sqlite3 /var/db/opendnssec/kasp-1.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> select salt from policies where policies.id=1 and ( select salt from policies where policies.id=1) != null;
sqlite> select salt from policies where policies.id=1 and ( select salt from policies where policies.id=1) is not null;
ccae9067625332c1
sqlite> ^D
$

There are two occurrences of "!= null" in the sqlite3 conversion
script which needs to be changed to "IS NOT NULL", apparently.
With that, I get after re-running the sqlite3 conversion script:

$ sqlite3 /var/db/opendnssec/kasp.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> select denialSalt from policy;
ccae9067625332c1
sqlite> ^D
$

One down.  More to go, I suspect.

Regards,

- Håvard