# 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 create the tables # dbadmin - holds the version of the database CREATE TABLE dbadmin ( version INTEGER NOT NULL, description varchar(255) ); 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 tinyint not null auto_increment, # id for sm name varchar(30) not null, # name of the sm capacity mediumint not null, requirebackup tinyint default 1, constraint primary key (id) )ENGINE=InnoDB; # categories - stores the possible categories (or uses) of parameters create table categories ( id tinyint not null auto_increment, # id for category_id name varchar(30) not null, # name of the category_id constraint primary key (id) )ENGINE=InnoDB; # parameters - stores the types of parameters available create table parameters ( id mediumint not null auto_increment, # 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 constraint primary key (id), constraint unique (name, category_id), constraint foreign key (category_id) references categories (id) )ENGINE=InnoDB; create table serialmodes ( id tinyint auto_increment, # id for serial mode name varchar(30), # name of the serial mode description varchar(255), # description of the serial mode constraint primary key (id) )ENGINE=InnoDB; # policies - create table policies ( id mediumint not null auto_increment, # 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 timestamp, # when the salt was generated audit varchar(1024), # contents of constraint primary key (id), constraint unique (name) )ENGINE=InnoDB; # zones - stores the zones create table zones( id mediumint not null auto_increment, # id name varchar(300) not null , # name of the parameter policy_id mediumint not null, signconf varchar(4096), # where is the signconf input varchar(4096), # where is the input output varchar(4096), # where is the output constraint primary key (id), constraint foreign key (policy_id) references policies (id) )ENGINE=InnoDB; # stores the private key info create table keypairs( id int not null auto_increment, HSMkey_id varchar(255) not null, algorithm tinyint not null, # algorithm code size smallint, securitymodule_id tinyint, # where the key is stored generate timestamp null default null, # time key inserted into database policy_id mediumint, compromisedflag tinyint, publickey varchar(1024), # public key data pre_backup timestamp null default null, # time when backup was started backup timestamp 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 constraint primary key (id), constraint foreign key (securitymodule_id) references securitymodules (id), constraint foreign key (policy_id) references policies (id) )ENGINE=InnoDB; # stores meta data about keys (actual keys are in a (soft)hsm) create table dnsseckeys ( id int not null auto_increment, # unique id of the key keypair_id int, 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 timestamp null default null, # time when key published into the zone ready timestamp null default null, # time when the key is ready for use active timestamp null default null, # time when the key was made active retire timestamp null default null, # time when the key retires dead timestamp null default null, # time when key is slated for removal constraint primary key (id), constraint dnsseckeys_keypairs_id foreign key (keypair_id) references keypairs (id) )ENGINE=InnoDB; # parameters_policies - join table to hold the values of parameters create table parameters_policies ( id mediumint auto_increment, # id parameter_id mediumint not null, policy_id mediumint not null, value int, # integer value of this key constraint primary key (id), constraint foreign key (parameter_id) references parameters (id), constraint foreign key (policy_id) references policies (id) )ENGINE=InnoDB; # The VIEWS create or replace view PARAMETER_VIEW as select p.name, c.name as category, pp.parameter_id, pp.value, pp.policy_id from parameters_policies pp, parameters p, categories c where pp.parameter_id = p.id and p.category_id = c.id; create or replace view PARAMETER_LIST as select p.name, c.name as category, p.id as parameter_id from parameters p, categories c where p.category_id = c.id; create or replace view KEYDATA_VIEW as select k.id, d.state, k.generate, d.publish, d.ready, d.active, d.retire, d.dead, d.keytype, k.algorithm, k.HSMkey_id as location, d.zone_id, k.policy_id, k.securitymodule_id, k.size, k.compromisedflag, k.fixedDate from keypairs k left outer join dnsseckeys d on k.id = d.keypair_id; create or replace view INT_KEYALLOC_VIEW_FOR_MYSQL as 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 on k.policy_id = z.policy_id; create or replace view KEYALLOC_VIEW as select v.id as id, location, algorithm, policy_id, securitymodule_id, size, compromisedflag, d.zone_id as zone_id from INT_KEYALLOC_VIEW_FOR_MYSQL 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 (id, name) values (1, "signature"); insert into categories (id, name) values (2, "denial"); insert into categories (id, name) values (3, "ksk"); insert into categories (id, name) values (4, "zsk"); insert into categories (id, name) values (5, "keys"); insert into categories (id, name) values (6, "enforcer"); insert into categories (id, name) values (7, "zone"); insert into categories (id, name) values (8, "parent"); insert into categories (id, name) values (9, "audit"); # default serial number modes insert into serialmodes (id, name, description) values (1, "unixtime", "seconds since 1 Jan 1970"); insert into serialmodes (id, name, description) values (2, "counter", "add one everytime updated"); insert into serialmodes (id, name, description) values (3, "datecounter", "YYYYMMDDXX"); insert into serialmodes (id, name, description) values (4, "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 "audit", "placeholder for audit tag", id from categories where name="audit"; insert into parameters (name, description, category_id) select "purge", "interval that dead keys can stay in the database", id from categories where name="keys"; COMMIT;