|
 |
74b14c |
INSERT INTO databaseVersion VALUES (NULL, 1, 1);
|
|
|
74b14c |
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
-- ~ ** policy table
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
|
|
|
74b14c |
INSERT INTO policy
|
|
|
74b14c |
SELECT id, 1, name, description,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 0, 0,
|
|
|
74b14c |
86400, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0
|
|
|
74b14c |
FROM REMOTE.policies;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET signaturesResign = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 1
|
|
|
74b14c |
AND REMOTE.parameters.name = 'resign');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET signaturesRefresh = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 1
|
|
|
74b14c |
AND REMOTE.parameters.name = 'refresh') ;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET signaturesJitter = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 1
|
|
|
74b14c |
AND REMOTE.parameters.name = 'jitter');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET signaturesInceptionOffset = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 1
|
|
|
74b14c |
AND REMOTE.parameters.name = 'clockskew');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET signaturesValidityDefault = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 1
|
|
|
74b14c |
AND REMOTE.parameters.name = 'valdefault');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET signaturesValidityDenial = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 1
|
|
|
74b14c |
AND REMOTE.parameters.name = 'valdenial');
|
|
|
74b14c |
|
|
|
74b14c |
--MaxZoneTTL default 86400
|
|
|
74b14c |
|
|
|
74b14c |
-- We need the following mapping 1.4 -> 2.0 for denialType
|
|
|
74b14c |
-- 0 -> 1
|
|
|
74b14c |
-- 3 -> 0
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialType = (
|
|
|
74b14c |
SELECT (~value)&1
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'version');
|
|
|
74b14c |
|
|
|
74b14c |
-- I'm pretty sure this is not the correct way to do it. It is aweful but
|
|
|
74b14c |
-- I can't figure it out how it would work for sqlite.
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialOptout = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'optout')
|
|
|
74b14c |
WHERE null != (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'optout');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialTtl = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'ttl')
|
|
|
74b14c |
WHERE null != (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'ttl');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialResalt = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'resalt')
|
|
|
74b14c |
WHERE null != (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'resalt');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialAlgorithm = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'algorithm')
|
|
|
74b14c |
WHERE null != (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'algorithm');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialIterations = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'iterations')
|
|
|
74b14c |
WHERE null != (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'iterations');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialSaltLength = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'saltlength')
|
|
|
74b14c |
WHERE null != (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 2
|
|
|
74b14c |
AND REMOTE.parameters.name = 'saltlength');
|
|
|
74b14c |
|
|
|
74b14c |
-- clumsy salt update. salt is optional in 1.4 but required in 2.0
|
|
|
74b14c |
-- sqlite is limited in what it can do in an update. I hope there is a
|
|
|
74b14c |
-- better way for this?
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialSalt = (
|
|
|
74b14c |
SELECT salt
|
|
|
74b14c |
FROM REMOTE.policies
|
|
|
74b14c |
WHERE REMOTE.policies.id = policy.id)
|
|
|
74b14c |
WHERE (
|
|
|
74b14c |
SELECT salt
|
|
|
74b14c |
FROM REMOTE.policies
|
|
|
74b14c |
WHERE REMOTE.policies.id = policy.id) != null;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET denialSaltLastChange = (
|
|
|
74b14c |
SELECT salt_stamp
|
|
|
74b14c |
FROM REMOTE.policies
|
|
|
74b14c |
WHERE REMOTE.policies.id = policy.id)
|
|
|
74b14c |
WHERE (
|
|
|
74b14c |
SELECT salt_stamp
|
|
|
74b14c |
FROM REMOTE.policies
|
|
|
74b14c |
WHERE REMOTE.policies.id = policy.id) != null;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET keysTtl = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 5
|
|
|
74b14c |
AND REMOTE.parameters.name = 'ttl');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET keysRetireSafety = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 5
|
|
|
74b14c |
AND REMOTE.parameters.name = 'retiresafety');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET keysPublishSafety = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 5
|
|
|
74b14c |
AND REMOTE.parameters.name = 'publishsafety');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET keysShared = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 5
|
|
|
74b14c |
AND REMOTE.parameters.name = 'zones_share_keys');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET keysPurgeAfter = COALESCE((
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 5
|
|
|
74b14c |
AND REMOTE.parameters.name = 'purge'), 0);
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET zonePropagationDelay = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 7
|
|
|
74b14c |
AND REMOTE.parameters.name = 'propagationdelay');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET zoneSoaTtl = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 7
|
|
|
74b14c |
AND REMOTE.parameters.name = 'ttl');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET zoneSoaMinimum = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 7
|
|
|
74b14c |
AND REMOTE.parameters.name = 'min');
|
|
|
74b14c |
|
|
|
74b14c |
-- Temporary mapping table between 1.4 and 2.0 SOA serial strategy
|
|
|
74b14c |
CREATE TABLE mapping (
|
|
|
74b14c |
soa14 INTEGER,
|
|
|
74b14c |
soa20 INTEGER
|
|
|
74b14c |
);
|
|
|
74b14c |
INSERT INTO mapping SELECT 1, 2;
|
|
|
74b14c |
INSERT INTO mapping SELECT 2, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 3, 1;
|
|
|
74b14c |
INSERT INTO mapping SELECT 4, 3;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET zoneSoaSerial = (
|
|
|
74b14c |
SELECT mapping.soa20
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
INNER JOIN mapping
|
|
|
74b14c |
ON REMOTE.parameters_policies.value = mapping.soa14
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 7
|
|
|
74b14c |
AND REMOTE.parameters.name = 'serial');
|
|
|
74b14c |
|
|
|
74b14c |
DROP TABLE mapping;
|
|
|
74b14c |
|
|
|
74b14c |
-- parentRegistrationDelay = 0 on 1.4
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET parentPropagationDelay = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 8
|
|
|
74b14c |
AND REMOTE.parameters.name = 'propagationdelay');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET parentDsTtl = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 8
|
|
|
74b14c |
AND REMOTE.parameters.name = 'ttlds');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET parentSoaTtl = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 8
|
|
|
74b14c |
AND REMOTE.parameters.name = 'ttl');
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policy
|
|
|
74b14c |
SET parentSoaMinimum = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policy.id
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 8
|
|
|
74b14c |
AND REMOTE.parameters.name = 'min');
|
|
|
74b14c |
|
|
|
74b14c |
-- passthrough = 0
|
|
|
74b14c |
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
-- ~ ** policyKey table
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ** For each policy in 1.4 add two keys: KSK and ZSK
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
|
|
|
74b14c |
-- Insert each KSK
|
|
|
74b14c |
INSERT INTO policyKey
|
|
|
74b14c |
SELECT null, 1, id,
|
|
|
74b14c |
1, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 4
|
|
|
74b14c |
FROM REMOTE.policies;
|
|
|
74b14c |
|
|
|
74b14c |
-- Insert each ZSK
|
|
|
74b14c |
INSERT INTO policyKey
|
|
|
74b14c |
SELECT null, 1, id,
|
|
|
74b14c |
2, 0, 0,
|
|
|
74b14c |
0, 0, 0,
|
|
|
74b14c |
0, 0, 1
|
|
|
74b14c |
FROM REMOTE.policies;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET algorithm = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 3
|
|
|
74b14c |
AND REMOTE.parameters.name = 'algorithm')
|
|
|
74b14c |
WHERE policyKey.role = 1;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET algorithm = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 4
|
|
|
74b14c |
AND REMOTE.parameters.name = 'algorithm')
|
|
|
74b14c |
WHERE policyKey.role = 2;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET bits = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 3
|
|
|
74b14c |
AND REMOTE.parameters.name = 'bits')
|
|
|
74b14c |
WHERE policyKey.role = 1;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET bits = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 4
|
|
|
74b14c |
AND REMOTE.parameters.name = 'bits')
|
|
|
74b14c |
WHERE policyKey.role = 2;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET lifetime = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 3
|
|
|
74b14c |
AND REMOTE.parameters.name = 'lifetime')
|
|
|
74b14c |
WHERE policyKey.role = 1;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET lifetime = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 4
|
|
|
74b14c |
AND REMOTE.parameters.name = 'lifetime')
|
|
|
74b14c |
WHERE policyKey.role = 2;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET repository = (
|
|
|
74b14c |
SELECT REMOTE.securitymodules.name
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
INNER JOIN REMOTE.securitymodules
|
|
|
74b14c |
ON REMOTE.parameters_policies.value = REMOTE.securitymodules.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 3
|
|
|
74b14c |
AND REMOTE.parameters.name = 'repository')
|
|
|
74b14c |
WHERE policyKey.role = 1;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET repository = (
|
|
|
74b14c |
SELECT REMOTE.securitymodules.name
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
INNER JOIN REMOTE.securitymodules
|
|
|
74b14c |
ON REMOTE.parameters_policies.value = REMOTE.securitymodules.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 4
|
|
|
74b14c |
AND REMOTE.parameters.name = 'repository')
|
|
|
74b14c |
WHERE policyKey.role = 2;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET standby = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 3
|
|
|
74b14c |
AND REMOTE.parameters.name = 'standby')
|
|
|
74b14c |
WHERE policyKey.role = 1;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET standby = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 4
|
|
|
74b14c |
AND REMOTE.parameters.name = 'standby')
|
|
|
74b14c |
WHERE policyKey.role = 2;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET manualRollover = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 3
|
|
|
74b14c |
AND REMOTE.parameters.name = 'manual_rollover')
|
|
|
74b14c |
WHERE policyKey.role = 1;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE policyKey
|
|
|
74b14c |
SET manualRollover = (
|
|
|
74b14c |
SELECT value
|
|
|
74b14c |
FROM REMOTE.parameters_policies
|
|
|
74b14c |
INNER JOIN REMOTE.parameters
|
|
|
74b14c |
ON REMOTE.parameters_policies.parameter_id = REMOTE.parameters.id
|
|
|
74b14c |
WHERE REMOTE.parameters_policies.policy_id = policyKey.policyId
|
|
|
74b14c |
AND REMOTE.parameters.category_id = 4
|
|
|
74b14c |
AND REMOTE.parameters.name = 'manual_rollover')
|
|
|
74b14c |
WHERE policyKey.role = 2;
|
|
|
74b14c |
|
|
|
74b14c |
-- rfc5011 = 0. 2.0 has no support
|
|
|
74b14c |
-- minimize already set
|
|
|
74b14c |
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
-- ~ ** hsmKey table
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ** get from keypairs and dnsseckeys
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
|
|
|
74b14c |
INSERT INTO hsmKey
|
|
|
74b14c |
SELECT DISTINCT REMOTE.keypairs.id, 1, REMOTE.keypairs.policy_id,
|
|
|
74b14c |
REMOTE.keypairs.HSMkey_id, 2, REMOTE.keypairs.size,
|
|
|
74b14c |
REMOTE.keypairs.algorithm, (~(REMOTE.dnsseckeys.keytype)&1)+1,
|
|
|
74b14c |
CASE WHEN REMOTE.keypairs.generate IS NOT NULL THEN
|
|
|
74b14c |
strftime('%s', REMOTE.keypairs.generate)
|
|
|
74b14c |
ELSE strftime("%s", "now") END,
|
|
|
74b14c |
0,
|
|
|
74b14c |
1, --only RSA supported
|
|
|
74b14c |
REMOTE.securitymodules.name,
|
|
|
74b14c |
0 --assume no backup
|
|
|
74b14c |
FROM REMOTE.keypairs
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.keypairs.id = REMOTE.dnsseckeys.keypair_id
|
|
|
74b14c |
JOIN REMOTE.securitymodules
|
|
|
74b14c |
ON REMOTE.securitymodules.id = REMOTE.keypairs.securitymodule_id;
|
|
|
74b14c |
|
|
|
74b14c |
-- For some policies put the keys in a shared state
|
|
|
74b14c |
UPDATE hsmKey
|
|
|
74b14c |
SET state = 3
|
|
|
74b14c |
WHERE EXISTS
|
|
|
74b14c |
(SELECT * FROM hsmKey AS h
|
|
|
74b14c |
JOIN policy ON policy.id = h.policyId
|
|
|
74b14c |
WHERE policy.keysShared AND hsmKey.id = h.id);
|
|
|
74b14c |
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
-- ~ ** zone table
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
|
|
|
74b14c |
INSERT INTO zone
|
|
|
74b14c |
SELECT zones.id, 1, zones.policy_id,
|
|
|
74b14c |
zones.name, 1, zones.signconf, 0,
|
|
|
74b14c |
0,0,0,
|
|
|
74b14c |
0,0,0,
|
|
|
74b14c |
zones.in_type, zones.input,
|
|
|
74b14c |
zones.out_type, zones.output,
|
|
|
74b14c |
0,0,0
|
|
|
74b14c |
FROM REMOTE.zones;
|
|
|
74b14c |
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
-- ~ ** keyData table
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
|
|
|
74b14c |
-- Temporary mapping table between 1.4 states and 2.0 ds_at_parent states
|
|
|
74b14c |
-- We are ignoring the fact this may set a DS state for a ZSK; We don't care
|
|
|
74b14c |
CREATE TABLE mapping (
|
|
|
74b14c |
state INTEGER,
|
|
|
74b14c |
ds_state INTEGER
|
|
|
74b14c |
);
|
|
|
74b14c |
INSERT INTO mapping SELECT 1, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 2, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 3, 1;
|
|
|
74b14c |
INSERT INTO mapping SELECT 4, 3;
|
|
|
74b14c |
INSERT INTO mapping SELECT 5, 5;
|
|
|
74b14c |
INSERT INTO mapping SELECT 6, 5;
|
|
|
74b14c |
INSERT INTO mapping SELECT 7, 5;
|
|
|
74b14c |
INSERT INTO mapping SELECT 8, 5;
|
|
|
74b14c |
INSERT INTO mapping SELECT 9, 5;
|
|
|
74b14c |
INSERT INTO mapping SELECT 10, 5;
|
|
|
74b14c |
|
|
|
74b14c |
INSERT INTO keyData
|
|
|
74b14c |
SELECT
|
|
|
74b14c |
NULL, 1, REMOTE.dnsseckeys.zone_id,
|
|
|
74b14c |
REMOTE.dnsseckeys.keypair_id, REMOTE.keypairs.algorithm,
|
|
|
74b14c |
CASE WHEN REMOTE.dnsseckeys.publish IS NOT NULL THEN
|
|
|
74b14c |
strftime('%s', REMOTE.dnsseckeys.publish)
|
|
|
74b14c |
ELSE strftime("%s", "now") END,
|
|
|
74b14c |
(~REMOTE.dnsseckeys.keytype&1)+1,
|
|
|
74b14c |
REMOTE.dnsseckeys.state <= 4, -- introducing
|
|
|
74b14c |
0, -- should revoke, not used
|
|
|
74b14c |
0, -- standby
|
|
|
74b14c |
REMOTE.dnsseckeys.state = 4 AND REMOTE.dnsseckeys.keytype = 256, --activeZSK:
|
|
|
74b14c |
REMOTE.dnsseckeys.state >= 2 AND REMOTE.dnsseckeys.state <= 5, --publish
|
|
|
74b14c |
REMOTE.dnsseckeys.state = 4 AND REMOTE.dnsseckeys.keytype = 257, --activeKSK:
|
|
|
74b14c |
mapping.ds_state, --dsatparent
|
|
|
74b14c |
1<<16, --keytag (crap, will 2.0 regenerate this?)
|
|
|
74b14c |
(REMOTE.dnsseckeys.keytype&1)*3+1 --minimize
|
|
|
74b14c |
FROM REMOTE.dnsseckeys
|
|
|
74b14c |
JOIN REMOTE.keypairs
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = REMOTE.keypairs.id
|
|
|
74b14c |
JOIN mapping
|
|
|
74b14c |
ON REMOTE.dnsseckeys.state = mapping.state
|
|
|
74b14c |
WHERE EXISTS(select REMOTE.zones.id FROM REMOTE.zones WHERE REMOTE.zones.id = REMOTE.dnsseckeys.zone_id);
|
|
|
74b14c |
|
|
|
74b14c |
-- Everything that is just a ZSK must not have dsatparent set.
|
|
|
74b14c |
UPDATE keyData
|
|
|
74b14c |
SET dsatparent = 0
|
|
|
74b14c |
WHERE role = 2;
|
|
|
74b14c |
|
|
|
74b14c |
DROP TABLE mapping;
|
|
|
74b14c |
|
|
|
74b14c |
-- If a active time is set for a ready KSK dsAtParent is submitted
|
|
|
74b14c |
-- instead of submit
|
|
|
74b14c |
UPDATE keyData
|
|
|
74b14c |
SET dsatparent = 2
|
|
|
74b14c |
WHERE keyData.dsAtParent = 1 AND keyData.id IN (
|
|
|
74b14c |
SELECT keyData.id
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
|
|
|
74b14c |
WHERE REMOTE.dnsseckeys.active IS NOT NULL);
|
|
|
74b14c |
|
|
|
74b14c |
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
-- ~ ** Keystate table
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ **
|
|
|
74b14c |
-- ~ ************
|
|
|
74b14c |
|
|
|
74b14c |
CREATE TABLE mapping (
|
|
|
74b14c |
state INTEGER,
|
|
|
74b14c |
ds INTEGER,
|
|
|
74b14c |
dk INTEGER,
|
|
|
74b14c |
ks INTEGER,
|
|
|
74b14c |
rs INTEGER
|
|
|
74b14c |
);
|
|
|
74b14c |
INSERT INTO mapping SELECT 1, 0, 0, 0, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 2, 0, 1, 1, 1;
|
|
|
74b14c |
INSERT INTO mapping SELECT 3, 0, 2, 2, 1;
|
|
|
74b14c |
INSERT INTO mapping SELECT 4, 2, 2, 2, 1;
|
|
|
74b14c |
INSERT INTO mapping SELECT 5, 3, 2, 2, 3;
|
|
|
74b14c |
INSERT INTO mapping SELECT 6, 0, 3, 3, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 7, 3, 0, 0, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 8, 3, 0, 0, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 9, 3, 0, 0, 0;
|
|
|
74b14c |
INSERT INTO mapping SELECT 10, 3, 0, 0, 0;
|
|
|
74b14c |
|
|
|
74b14c |
-- DS RECORDS
|
|
|
74b14c |
INSERT INTO keyState
|
|
|
74b14c |
SELECT NULL, 1, keyData.id, 0, mapping.ds, strftime("%s", "now"), (keyData.minimize>>2)&1, policy.parentDsTtl
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
JOIN zone
|
|
|
74b14c |
ON zone.id = keyData.zoneId
|
|
|
74b14c |
JOIN policy
|
|
|
74b14c |
ON policy.id = zone.policyId
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
|
|
|
74b14c |
JOIN mapping
|
|
|
74b14c |
ON mapping.state = REMOTE.dnsseckeys.state;
|
|
|
74b14c |
|
|
|
74b14c |
UPDATE keyState
|
|
|
74b14c |
SET state = 1
|
|
|
74b14c |
WHERE keyState.state = 0 AND keyState.type = 0 AND keyState.id IN (
|
|
|
74b14c |
SELECT keyState.id
|
|
|
74b14c |
FROM keyState
|
|
|
74b14c |
JOIN keyData
|
|
|
74b14c |
ON keyData.id = keyState.keydataId
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
|
|
|
74b14c |
WHERE REMOTE.dnsseckeys.active IS NOT NULL);
|
|
|
74b14c |
|
|
|
74b14c |
-- DNSKEY RECORDS
|
|
|
74b14c |
INSERT INTO keyState
|
|
|
74b14c |
SELECT NULL, 1, keyData.id, 2, mapping.dk, strftime("%s", "now"), (keyData.minimize>>1)&1, policy.keysTtl
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
JOIN zone
|
|
|
74b14c |
ON zone.id = keyData.zoneId
|
|
|
74b14c |
JOIN policy
|
|
|
74b14c |
ON policy.id = zone.policyId
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
|
|
|
74b14c |
JOIN mapping
|
|
|
74b14c |
ON mapping.state = REMOTE.dnsseckeys.state;
|
|
|
74b14c |
|
|
|
74b14c |
-- RRSIG DNSKEY RECORDS
|
|
|
74b14c |
INSERT INTO keyState
|
|
|
74b14c |
SELECT NULL, 1, keyData.id, 3, mapping.ks, strftime("%s", "now"), (keyData.minimize>>1)&1, policy.keysTtl
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
JOIN zone
|
|
|
74b14c |
ON zone.id = keyData.zoneId
|
|
|
74b14c |
JOIN policy
|
|
|
74b14c |
ON policy.id = zone.policyId
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
|
|
|
74b14c |
JOIN mapping
|
|
|
74b14c |
ON mapping.state = REMOTE.dnsseckeys.state;
|
|
|
74b14c |
|
|
|
74b14c |
-- RRSIG RECORDS
|
|
|
74b14c |
INSERT INTO keyState
|
|
|
74b14c |
SELECT NULL, 1, keyData.id, 1, mapping.rs, strftime("%s", "now"), (keyData.minimize>>0)&1, policy.signaturesMaxZoneTtl
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
JOIN zone
|
|
|
74b14c |
ON zone.id = keyData.zoneId
|
|
|
74b14c |
JOIN policy
|
|
|
74b14c |
ON policy.id = zone.policyId
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
|
|
|
74b14c |
JOIN mapping
|
|
|
74b14c |
ON mapping.state = REMOTE.dnsseckeys.state;
|
|
|
74b14c |
|
|
|
74b14c |
--Set to OMN if Tactive + Dttl < Tnow
|
|
|
74b14c |
UPDATE keyState
|
|
|
74b14c |
SET state = 2
|
|
|
74b14c |
WHERE keyState.state = 1 AND keyState.type = 1 AND keyState.id IN (
|
|
|
74b14c |
SELECT keyState.id
|
|
|
74b14c |
FROM keyState
|
|
|
74b14c |
JOIN keyData
|
|
|
74b14c |
ON keyData.id = keyState.keydataId
|
|
|
74b14c |
JOIN REMOTE.dnsseckeys
|
|
|
74b14c |
ON REMOTE.dnsseckeys.keypair_id = keyData.hsmkeyid
|
|
|
74b14c |
JOIN zone
|
|
|
74b14c |
ON keyData.zoneId = zone.id
|
|
|
74b14c |
JOIN policy
|
|
|
74b14c |
ON policy.id = zone.policyId
|
|
|
74b14c |
WHERE CAST(strftime("%s", REMOTE.dnsseckeys.active) + policy.signaturesValidityDefault as INTEGER) < strftime("%s", "now"));
|
|
|
74b14c |
|
|
|
74b14c |
--Force the RRSIG state in omnipresent if rumoured and there is no old ZSK
|
|
|
74b14c |
-- unretentive
|
|
|
74b14c |
UPDATE keyState
|
|
|
74b14c |
SET state = 2
|
|
|
74b14c |
WHERE keyState.id IN (
|
|
|
74b14c |
SELECT rs.id FROM keyState AS rs
|
|
|
74b14c |
JOIN keystate AS dk ON dk.keyDataId == rs.keyDataId
|
|
|
74b14c |
WHERE rs.type == 1 AND dk.type == 2 AND rs.state == 1 AND dk.state == 2
|
|
|
74b14c |
AND NOT EXISTS(
|
|
|
74b14c |
SELECT* FROM keystate AS rs2
|
|
|
74b14c |
JOIN keystate AS dk2 ON dk2.keyDataId == rs2.keyDataId
|
|
|
74b14c |
WHERE rs2.type == 1 AND dk2.type == 2 AND rs2.state == 3 AND dk2.state == 2
|
|
|
74b14c |
));
|
|
|
74b14c |
|
|
|
74b14c |
DROP TABLE mapping;
|
|
|
74b14c |
|
|
|
74b14c |
-- We need to create records in the keydependency table in case we are in a
|
|
|
74b14c |
-- rollover. Only done for ZSK. For every introducing ZSK with RRSIG rumoured
|
|
|
74b14c |
-- that has an outroducing ZSK with RRSIG unretentive, we add a record.
|
|
|
74b14c |
INSERT INTO keyDependency
|
|
|
74b14c |
SELECT NULL, 0, keyData.zoneID, SUB.IDout, keyData.id, 1
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
JOIN keyState AS KS1
|
|
|
74b14c |
ON KS1.keyDataId == keyData.id
|
|
|
74b14c |
JOIN keyState AS KS2
|
|
|
74b14c |
ON KS2.keyDataId == keyData.id
|
|
|
74b14c |
JOIN (
|
|
|
74b14c |
SELECT keyData.id AS IDout, keyData.zoneID
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
JOIN keyState AS KS1
|
|
|
74b14c |
ON KS1.keyDataId == keyData.id
|
|
|
74b14c |
JOIN keyState AS KS2
|
|
|
74b14c |
ON KS2.keyDataId == keyData.id
|
|
|
74b14c |
WHERE KS1.type == 2
|
|
|
74b14c |
AND ks1.state = 2
|
|
|
74b14c |
AND KS2.type == 1
|
|
|
74b14c |
AND KS2.state == 3
|
|
|
74b14c |
AND keyData.introducing == 0
|
|
|
74b14c |
AND keyData.role == 2
|
|
|
74b14c |
) AS SUB
|
|
|
74b14c |
ON SUB.zoneId == keyData.zoneId
|
|
|
74b14c |
WHERE
|
|
|
74b14c |
KS1.type == 2
|
|
|
74b14c |
AND ks1.state = 2
|
|
|
74b14c |
AND KS2.type == 1
|
|
|
74b14c |
AND KS2.state == 1
|
|
|
74b14c |
AND keyData.introducing == 1
|
|
|
74b14c |
AND keyData.role == 2;
|
|
|
74b14c |
|
|
|
74b14c |
-- ZSK
|
|
|
74b14c |
UPDATE keyState
|
|
|
74b14c |
SET state = 4
|
|
|
74b14c |
WHERE (keyState.type = 0 OR keyState.type = 3) AND keyDataId IN (
|
|
|
74b14c |
SELECT keyData.id
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
WHERE keyData.role = 2);
|
|
|
74b14c |
|
|
|
74b14c |
--KSK
|
|
|
74b14c |
UPDATE keyState
|
|
|
74b14c |
SET state = 4
|
|
|
74b14c |
WHERE keyState.type = 1 AND keyDataId IN (
|
|
|
74b14c |
SELECT keyData.id
|
|
|
74b14c |
FROM keyData
|
|
|
74b14c |
WHERE keyData.role = 1);
|
|
|
74b14c |
|
|
|
74b14c |
-- For rpm based systems to see if db was migrated already. store opendnssec major minor version
|
|
|
74b14c |
CREATE TABLE rpm_migration (
|
|
|
74b14c |
major INTEGER,
|
|
|
74b14c |
minor INTEGER
|
|
|
74b14c |
);
|
|
|
74b14c |
INSERT INTO rpm_migration VALUES(2, 1);
|
|
|
74b14c |
|