opendnssec-common 1:1.4.9-2 / usr / share / opendnssec / database_create.mysql

# 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 (4, "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 <Audit>

  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
  in_type       varchar(512),   # input adapter type
  out_type      varchar(512),   # output adapter type

  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
  rfc5011       tinyint        default 0,
  revoked       tinyint        default 0,

  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, d.rfc5011, d.revoked
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 "revoked", "key is revoked?", 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;