diff options
Diffstat (limited to 'schemas/sql')
| -rw-r--r-- | schemas/sql/pubd.sql | 59 | ||||
| -rw-r--r-- | schemas/sql/rpkid.sql | 250 |
2 files changed, 309 insertions, 0 deletions
diff --git a/schemas/sql/pubd.sql b/schemas/sql/pubd.sql new file mode 100644 index 00000000..3a58ec00 --- /dev/null +++ b/schemas/sql/pubd.sql @@ -0,0 +1,59 @@ +-- $Id$ + +-- Copyright (C) 2009--2010 Internet Systems Consortium ("ISC") +-- +-- Permission to use, copy, modify, and distribute this software for any +-- purpose with or without fee is hereby granted, provided that the above +-- copyright notice and this permission notice appear in all copies. +-- +-- THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH +-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY +-- AND FITNESS. IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT, +-- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM +-- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE +-- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR +-- PERFORMANCE OF THIS SOFTWARE. + +-- Copyright (C) 2008 American Registry for Internet Numbers ("ARIN") +-- +-- Permission to use, copy, modify, and distribute this software for any +-- purpose with or without fee is hereby granted, provided that the above +-- copyright notice and this permission notice appear in all copies. +-- +-- THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH +-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY +-- AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT, +-- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM +-- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE +-- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR +-- PERFORMANCE OF THIS SOFTWARE. + +-- SQL objects needed by pubd.py. + +-- The config table is weird because we're really only using it +-- to store one BPKI CRL, but putting this here lets us use a lot of +-- existing machinery and the alternatives are whacky in other ways. + +DROP TABLE IF EXISTS client; +DROP TABLE IF EXISTS config; + +CREATE TABLE config ( + config_id SERIAL NOT NULL, + bpki_crl LONGBLOB, + PRIMARY KEY (config_id) +) ENGINE=InnoDB; + +CREATE TABLE client ( + client_id SERIAL NOT NULL, + client_handle VARCHAR(255) NOT NULL, + base_uri TEXT, + bpki_cert LONGBLOB, + bpki_glue LONGBLOB, + last_cms_timestamp DATETIME, + PRIMARY KEY (client_id), + UNIQUE (client_handle) +) ENGINE=InnoDB; + +-- Local Variables: +-- indent-tabs-mode: nil +-- End: diff --git a/schemas/sql/rpkid.sql b/schemas/sql/rpkid.sql new file mode 100644 index 00000000..a7e3dc0a --- /dev/null +++ b/schemas/sql/rpkid.sql @@ -0,0 +1,250 @@ +-- $Id$ + +-- Copyright (C) 2009--2011 Internet Systems Consortium ("ISC") +-- +-- Permission to use, copy, modify, and distribute this software for any +-- purpose with or without fee is hereby granted, provided that the above +-- copyright notice and this permission notice appear in all copies. +-- +-- THE SOFTWARE IS PROVIDED "AS IS" AND ISC DISCLAIMS ALL WARRANTIES WITH +-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY +-- AND FITNESS. IN NO EVENT SHALL ISC BE LIABLE FOR ANY SPECIAL, DIRECT, +-- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM +-- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE +-- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR +-- PERFORMANCE OF THIS SOFTWARE. + +-- Copyright (C) 2007--2008 American Registry for Internet Numbers ("ARIN") +-- +-- Permission to use, copy, modify, and distribute this software for any +-- purpose with or without fee is hereby granted, provided that the above +-- copyright notice and this permission notice appear in all copies. +-- +-- THE SOFTWARE IS PROVIDED "AS IS" AND ARIN DISCLAIMS ALL WARRANTIES WITH +-- REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY +-- AND FITNESS. IN NO EVENT SHALL ARIN BE LIABLE FOR ANY SPECIAL, DIRECT, +-- INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM +-- LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE +-- OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR +-- PERFORMANCE OF THIS SOFTWARE. + +-- SQL objects needed by the RPKI engine (rpkid.py). + +-- DROP TABLE commands must be in correct (reverse dependency) order +-- to satisfy FOREIGN KEY constraints. + +DROP TABLE IF EXISTS ee_cert; +DROP TABLE IF EXISTS ghostbuster; +DROP TABLE IF EXISTS roa_prefix; +DROP TABLE IF EXISTS roa; +DROP TABLE IF EXISTS revoked_cert; +DROP TABLE IF EXISTS child_cert; +DROP TABLE IF EXISTS child; +DROP TABLE IF EXISTS ca_detail; +DROP TABLE IF EXISTS ca; +DROP TABLE IF EXISTS parent; +DROP TABLE IF EXISTS repository; +DROP TABLE IF EXISTS bsc; +DROP TABLE IF EXISTS self; + +CREATE TABLE self ( + self_id SERIAL NOT NULL, + self_handle VARCHAR(255) NOT NULL, + use_hsm BOOLEAN NOT NULL DEFAULT FALSE, + crl_interval BIGINT UNSIGNED, + regen_margin BIGINT UNSIGNED, + bpki_cert LONGBLOB, + bpki_glue LONGBLOB, + PRIMARY KEY (self_id), + UNIQUE (self_handle) +) ENGINE=InnoDB; + +CREATE TABLE bsc ( + bsc_id SERIAL NOT NULL, + bsc_handle VARCHAR(255) NOT NULL, + private_key_id LONGBLOB, + pkcs10_request LONGBLOB, + hash_alg ENUM ('sha256'), + signing_cert LONGBLOB, + signing_cert_crl LONGBLOB, + self_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (bsc_id), + CONSTRAINT bsc_self_id + FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, + UNIQUE (self_id, bsc_handle) +) ENGINE=InnoDB; + +CREATE TABLE repository ( + repository_id SERIAL NOT NULL, + repository_handle VARCHAR(255) NOT NULL, + peer_contact_uri TEXT, + bpki_cert LONGBLOB, + bpki_glue LONGBLOB, + last_cms_timestamp DATETIME, + bsc_id BIGINT UNSIGNED NOT NULL, + self_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (repository_id), + CONSTRAINT repository_self_id + FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, + CONSTRAINT repository_bsc_id + FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, + UNIQUE (self_id, repository_handle) +) ENGINE=InnoDB; + +CREATE TABLE parent ( + parent_id SERIAL NOT NULL, + parent_handle VARCHAR(255) NOT NULL, + bpki_cms_cert LONGBLOB, + bpki_cms_glue LONGBLOB, + peer_contact_uri TEXT, + sia_base TEXT, + sender_name TEXT, + recipient_name TEXT, + last_cms_timestamp DATETIME, + self_id BIGINT UNSIGNED NOT NULL, + bsc_id BIGINT UNSIGNED NOT NULL, + repository_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (parent_id), + CONSTRAINT parent_repository_id + FOREIGN KEY (repository_id) REFERENCES repository (repository_id) ON DELETE CASCADE, + CONSTRAINT parent_bsc_id + FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, + CONSTRAINT parent_self_id + FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, + UNIQUE (self_id, parent_handle) +) ENGINE=InnoDB; + +CREATE TABLE ca ( + ca_id SERIAL NOT NULL, + last_crl_sn BIGINT UNSIGNED NOT NULL, + last_manifest_sn BIGINT UNSIGNED NOT NULL, + next_manifest_update DATETIME, + next_crl_update DATETIME, + last_issued_sn BIGINT UNSIGNED NOT NULL, + sia_uri TEXT, + parent_resource_class TEXT, + parent_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (ca_id), + CONSTRAINT ca_parent_id + FOREIGN KEY (parent_id) REFERENCES parent (parent_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE ca_detail ( + ca_detail_id SERIAL NOT NULL, + public_key LONGBLOB, + private_key_id LONGBLOB, + latest_crl LONGBLOB, + crl_published DATETIME, + latest_ca_cert LONGBLOB, + manifest_private_key_id LONGBLOB, + manifest_public_key LONGBLOB, + latest_manifest_cert LONGBLOB, + latest_manifest LONGBLOB, + manifest_published DATETIME, + state ENUM ('pending', 'active', 'deprecated', 'revoked') NOT NULL, + ca_cert_uri TEXT, + ca_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (ca_detail_id), + CONSTRAINT ca_detail_ca_id + FOREIGN KEY (ca_id) REFERENCES ca (ca_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE child ( + child_id SERIAL NOT NULL, + child_handle VARCHAR(255) NOT NULL, + bpki_cert LONGBLOB, + bpki_glue LONGBLOB, + last_cms_timestamp DATETIME, + self_id BIGINT UNSIGNED NOT NULL, + bsc_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (child_id), + CONSTRAINT child_bsc_id + FOREIGN KEY (bsc_id) REFERENCES bsc (bsc_id) ON DELETE CASCADE, + CONSTRAINT child_self_id + FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, + UNIQUE (self_id, child_handle) +) ENGINE=InnoDB; + +CREATE TABLE child_cert ( + child_cert_id SERIAL NOT NULL, + cert LONGBLOB NOT NULL, + published DATETIME, + ski TINYBLOB NOT NULL, + child_id BIGINT UNSIGNED NOT NULL, + ca_detail_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (child_cert_id), + CONSTRAINT child_cert_ca_detail_id + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE, + CONSTRAINT child_cert_child_id + FOREIGN KEY (child_id) REFERENCES child (child_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE revoked_cert ( + revoked_cert_id SERIAL NOT NULL, + serial BIGINT UNSIGNED NOT NULL, + revoked DATETIME NOT NULL, + expires DATETIME NOT NULL, + ca_detail_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (revoked_cert_id), + CONSTRAINT revoked_cert_ca_detail_id + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE roa ( + roa_id SERIAL NOT NULL, + asn BIGINT UNSIGNED NOT NULL, + cert LONGBLOB NOT NULL, + roa LONGBLOB NOT NULL, + published DATETIME, + self_id BIGINT UNSIGNED NOT NULL, + ca_detail_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (roa_id), + CONSTRAINT roa_self_id + FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, + CONSTRAINT roa_ca_detail_id + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE roa_prefix ( + prefix VARCHAR(40) NOT NULL, + prefixlen TINYINT UNSIGNED NOT NULL, + max_prefixlen TINYINT UNSIGNED NOT NULL, + version TINYINT UNSIGNED NOT NULL, + roa_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (roa_id, prefix, prefixlen, max_prefixlen), + CONSTRAINT roa_prefix_roa_id + FOREIGN KEY (roa_id) REFERENCES roa (roa_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE ghostbuster ( + ghostbuster_id SERIAL NOT NULL, + vcard LONGBLOB NOT NULL, + cert LONGBLOB NOT NULL, + ghostbuster LONGBLOB NOT NULL, + published DATETIME, + self_id BIGINT UNSIGNED NOT NULL, + ca_detail_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (ghostbuster_id), + CONSTRAINT ghostbuster_self_id + FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, + CONSTRAINT ghostbuster_ca_detail_id + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE ee_cert ( + ee_cert_id SERIAL NOT NULL, + ski BINARY(20) NOT NULL, + cert LONGBLOB NOT NULL, + published DATETIME, + self_id BIGINT UNSIGNED NOT NULL, + ca_detail_id BIGINT UNSIGNED NOT NULL, + PRIMARY KEY (ee_cert_id), + CONSTRAINT ee_cert_self_id + FOREIGN KEY (self_id) REFERENCES self (self_id) ON DELETE CASCADE, + CONSTRAINT ee_cert_ca_detail_id + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail (ca_detail_id) ON DELETE CASCADE +) ENGINE=InnoDB; + +-- Local Variables: +-- indent-tabs-mode: nil +-- End: |