-- database_create - Create Database -- -- Description: -- This script creates the tables required for the KASP database. -- Matches kaspimport.pl at 12/03/2009 -- Get rid of tables in reverse order drop table if exists parameters_policies; drop table if exists serialmodes; drop table if exists parameters; drop table if exists categories; drop table if exists dnsseckeys; drop table if exists zones; drop table if exists keypairs; drop table if exists securitymodules; drop table if exists policies; drop table if exists dbadmin; -- Now for the tables that actually hold real data. -- dbadmin - holds the version of the database CREATE TABLE "dbadmin" ( "version" INTEGER NOT NULL DEFAULT (1), "description" TEXT ); insert into dbadmin values (2, "This needs to be in sync with the version defined in database.h"); -- security modules - store information about all the sms used create table securitymodules ( id integer primary key autoincrement, -- id for sm name varchar(30) not null, -- name of the sm capacity mediumint not null, requirebackup tinyint default 1 ); -- categories - stores the possible categories (or uses) of parameters create table categories ( id integer primary key autoincrement, -- id for category_id name varchar(30) not null -- name of the category_id ); -- parameters - stores the types of parameters available create table parameters ( id integer primary key autoincrement, -- id for parameters name varchar(30) not null, -- name of the parameter description varchar(255), -- description of the paramter category_id tinyint not null, -- category_id of the parameter unique(name, category_id), foreign key (category_id) references categories (id) ); create table serialmodes ( id integer primary key autoincrement, -- id for serial mode name varchar(30), -- name of the serial mode description varchar(255) -- description of the serial mode ); -- 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 unique(name) ); -- zones - stores the zones create table zones( id integer primary key autoincrement, -- id name varchar(300) not null , -- name of the parameter policy_id mediumint not null, foreign key (policy_id) references policies (id) ); -- stores the private key info create table keypairs( id integer primary key autoincrement, HSMkey_id varchar(255) not null, algorithm tinyint not null, -- algorithm code size smallint, securitymodule_id tinyint, -- where the key is stored generate varchar(64) null default null, -- time key inserted into database policy_id mediumint, compromisedflag tinyint, publickey varchar(1024), -- public key data pre_backup varchar(64) null default null, -- time when backup was started backup varchar(64) null default null, -- time when backup was finished fixedDate tinyint default 0, -- Set to 1 to stop dates from being set according to the policy timings foreign key (securitymodule_id) references securitymodules (id), foreign key (policy_id) references policies (id) ); -- stores meta data about keys (actual keys are in a (soft)hsm) create table dnsseckeys ( id integer primary key autoincrement, -- unique id of the key keypair_id smallint, zone_id mediumint, keytype smallint not null, -- zsk or ksk (use code in dnskey record) state tinyint, -- state of the key (defines valid fields) publish varchar(64) null default null, -- time when key published into the zone ready varchar(64) null default null, -- time when the key is ready for use active varchar(64) null default null, -- time when the key was made active retire varchar(64) null default null, -- time when the key retires dead varchar(64) null default null, -- time when key is slated for removal foreign key (zone_id) references zones (id), foreign key (keypair_id) references keypairs (id) ); -- parameters_policies - join table to hold the values of parameters create table parameters_policies ( id integer primary key autoincrement, -- id parameter_id mediumint not null, policy_id mediumint not null, value int, -- integer value of this key foreign key (parameter_id) references parameters (id), foreign key (policy_id) references policies (id) ); -- The VIEWS drop view if exists PARAMETER_VIEW; create view PARAMETER_VIEW as select p.name as name, c.name as category, pp.parameter_id as parameter_id, pp.value as value, pp.policy_id as policy_id from parameters_policies pp, parameters p, categories c where pp.parameter_id = p.id and p.category_id = c.id; drop view if exists PARAMETER_LIST; create view PARAMETER_LIST as select p.name as name, c.name as category, p.id as parameter_id from parameters p, categories c where p.category_id = c.id; drop view if exists KEYDATA_VIEW; create view KEYDATA_VIEW as select k.id as id, d.state as state, k.generate as generate, d.publish as publish, d.ready as ready, d.active as active, d.retire as retire, d.dead as dead, d.keytype as keytype, k.algorithm as algorithm, k.HSMkey_id as location, d.zone_id as zone_id, k.policy_id as policy_id, k.securitymodule_id as securitymodule_id, k.size as size, k.compromisedflag as compromisedflag, k.fixedDate as fixedDate from keypairs k left outer join dnsseckeys d on k.id = d.keypair_id; drop view if exists KEYALLOC_VIEW; create view KEYALLOC_VIEW as select v.id as id, location, algorithm, policy_id, securitymodule_id, size, compromisedflag, d.zone_id as zone_id from (select k.id as id, k.HSMkey_id as location, z.id as zone_id, k.algorithm as algorithm, k.policy_id as policy_id, k.securitymodule_id as securitymodule_id, k.size as size, k.compromisedflag as compromisedflag from keypairs k left join zones z where k.policy_id = z.policy_id ) v left outer join dnsseckeys d on d.zone_id = v.zone_id and d.keypair_id = v.id; -- insert default data -- default categories insert into categories (name) values ("signature"); insert into categories (name) values ("denial"); insert into categories (name) values ("ksk"); insert into categories (name) values ("zsk"); insert into categories (name) values ("keys"); insert into categories (name) values ("enforcer"); insert into categories (name) values ("zone"); insert into categories (name) values ("parent"); --insert into categories (name) values ("audit"); -- default serial number modes insert into serialmodes (name, description) values ("unixtime", "seconds since 1 Jan 1970"); insert into serialmodes (name, description) values ("counter", "add one everytime updated"); insert into serialmodes (name, description) values ("datecounter", "YYYYMMDDXX"); insert into serialmodes (name, description) values ("keep", "Signer should not change the serial"); -- default parameters insert into parameters (name, description, category_id) select "resign", "re-signing interval", id from categories where name="signature"; insert into parameters (name, description, category_id) select "refresh", "how old a signature may become before it needs to be re-signed",id from categories where name="signature"; insert into parameters (name, description, category_id) select "jitter", "jitter to use in signature inception and expiration times", id from categories where name="signature"; insert into parameters (name, description, category_id) select "clockskew", "estimated max clockskew expected in clients", id from categories where name="signature"; insert into parameters (name, description, category_id) select "ttl", "ttl for RRSIGS", id from categories where name="signature"; insert into parameters (name, description, category_id) select "valdefault", "signature validity period", id from categories where name="signature"; insert into parameters (name, description, category_id) select "valdenial", "nsec(3) validity period", id from categories where name="signature"; insert into parameters (name, description, category_id) select "ttl", "ttl for nsec(3) rrs", id from categories where name="denial"; insert into parameters (name, description, category_id) select "version", "nsec version (0 or 3)", id from categories where name="denial"; insert into parameters (name, description, category_id) select "optout", "opt out flag for nsec3", id from categories where name="denial"; insert into parameters (name, description, category_id) select "resalt", "re-salting interval", id from categories where name="denial"; insert into parameters (name, description, category_id) select "algorithm", "nsec3 algorithm", id from categories where name="denial"; insert into parameters (name, description, category_id) select "iterations", "nsec3 iterations", id from categories where name="denial"; insert into parameters (name, description, category_id) select "saltlength", "nsec3 salt length", id from categories where name="denial"; insert into parameters (name, description, category_id) select "ttl", "ttl for ksk rrs", id from categories where name="keys"; insert into parameters (name, description, category_id) select "retiresafety", "ksk retirement safety factor", id from categories where name="keys"; insert into parameters (name, description, category_id) select "publishsafety", "ksk publish safety factor", id from categories where name="keys"; insert into parameters (name, description, category_id) select "algorithm", "ksk algorithm", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "bits", "ksk key size", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "lifetime", "ksk lifetime", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "standby", "number of ksks is use at any one time", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "repository", "default ksk sm (for newly generated keys)", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "rfc5011", "are we doing rfc5011?", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "algorithm", "zsk algorithm", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "bits", "zsk key size", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "lifetime", "zsk lifetime", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "standby", "number of zsks is use at any one time", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "repository", "default zsk sm (for newly generated keys)", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "propagationdelay", "Dp", id from categories where name="zone"; insert into parameters (name, description, category_id) select "ttl", "ttl of the soa", id from categories where name="zone"; insert into parameters (name, description, category_id) select "min", "min of the soa", id from categories where name="zone"; insert into parameters (name, description, category_id) select "serial", "how serial no are changed", id from categories where name="zone"; insert into parameters (name, description, category_id) select "propagationdelay", "Dp", id from categories where name="parent"; insert into parameters (name, description, category_id) select "ttl", "ttl of the soa", id from categories where name="parent"; insert into parameters (name, description, category_id) select "min", "min of the soa", id from categories where name="parent"; insert into parameters (name, description, category_id) select "ttlds", "ttl of the ds", id from categories where name="parent"; --insert into parameters (name, description, category_id) select "keycreate", "policy for key creation 0=fill the hsm, 1=only generate minimum needed", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "interval", "run interval", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "keygeninterval", "interval between key generation runs", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "backupdelay", "how old must a new key be before it can be assumed to have been backed up", id from categories where name="enforcer"; insert into parameters (name, description, category_id) select "zones_share_keys", "do all zones on this policy share the same keys", id from categories where name="keys"; insert into parameters (name, description, category_id) select "registrationdelay", "Dr", id from categories where name="parent"; insert into parameters (name, description, category_id) select "manual_rollover", "Do not automatically roll ksks when their time is up", id from categories where name="ksk"; insert into parameters (name, description, category_id) select "manual_rollover", "Do not automatically roll zsks when their time is up", id from categories where name="zsk"; insert into parameters (name, description, category_id) select "purge", "interval that dead keys can stay in the database", id from categories where name="keys"; --insert into parameters (name, description, category_id) select "audit", "placeholder for audit tag", id from categories where name="audit";