dbstore/sql/init.sql: add cisa kev tables and fts

1 files changed, 189 insertions, 0 deletions

diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index cf9d82a..662893e 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -770,3 +770,192 @@ CREATE TRIGGER tr_cve_fts_refresh_after_insert
            GROUP BY cve_ref_id
         ) g ON (g.cve_ref_id = f.cve_ref_id);
   END;
+
+--
+-- CISA Known Exploited Vulnerabilities (KEV) catalogs.
+--
+CREATE TABLE cisa_catalogs (
+  -- catalog unique ID
+  cat_id            INTEGER PRIMARY KEY,
+
+  -- catalog title
+  title             TEXT NOT NULL CHECK (
+                      -- FIXME: not sure about max length here
+                      LENGTH(title) BETWEEN 1 AND 256
+                    ),
+
+  -- catalog version
+  version           TEXT NOT NULL CHECK (
+                      LENGTH(version) = 10 AND
+                      version LIKE '____.__.__'
+                    ),
+
+  -- catalog release date
+  released_at       TIMESTAMP WITH TIME ZONE NOT NULL
+);
+
+--
+-- CISA catalog vendors and projects.
+--
+CREATE TABLE cisa_vendors (
+  -- vendor/project unique ID
+  vendor_id         INTEGER PRIMARY KEY,
+
+  -- vendor/project name
+  name              TEXT UNIQUE NOT NULL CHECK (
+                      -- FIXME: not sure about max length here
+                      LENGTH(name) BETWEEN 1 AND 256
+                    )
+);
+
+--
+-- CISA catalog products.
+--
+CREATE TABLE cisa_products (
+  -- product unique ID
+  product_id        INTEGER PRIMARY KEY,
+
+  -- product name
+  name              TEXT UNIQUE NOT NULL CHECK (
+                      -- FIXME: not sure about max length here
+                      LENGTH(name) BETWEEN 1 AND 256
+                    )
+);
+
+--
+-- CISA KEV catalog vulnerabilities
+--
+CREATE TABLE cisa_vulns (
+  -- vulnerability unique ID
+  vuln_id           INTEGER PRIMARY KEY,
+
+  -- catalog ID
+  cat_id            INTEGER NOT NULL
+                    REFERENCES cisa_catalogs(cat_id),
+
+  -- year of NVD CVE ID, (e.g. the "XXXX" in "CVE-XXXX-YYYY")
+  cve_year          SMALLINT NOT NULL CHECK (
+                      cve_year BETWEEN 1999 AND 2126
+                    ),
+
+  -- number of NVD CVE ID, (e.g. the "YYYY" in "CVE-XXXX-YYYY")
+  cve_num           SMALLINT NOT NULL CHECK (
+                      -- there is no upper bound, but let's pick
+                      -- something sane
+                      cve_num BETWEEN 1 AND 33554432
+                    ),
+
+  -- vendor/project ID
+  vendor_id         INTEGER NOT NULL
+                    REFERENCES cisa_vendors(vendor_id),
+
+  -- product ID
+  product_id        INTEGER NOT NULL
+                    REFERENCES cisa_products(product_id),
+
+  -- vulnerability name
+  name              TEXT NOT NULL CHECK (
+                      -- FIXME: not sure about max length here
+                      LENGTH(name) BETWEEN 1 AND 1024
+                    ),
+
+  -- date that vulnerability was added to catalog
+  added_at          DATE NOT NULL,
+
+  -- short description of vulnerability
+  description       TEXT NOT NULL CHECK (
+                      -- FIXME: not sure about max length here
+                      LENGTH(description) BETWEEN 1 AND 2048
+                    ),
+
+  -- required action
+  action            TEXT NOT NULL CHECK (
+                      -- FIXME: not sure about max length here
+                      LENGTH(action) BETWEEN 1 AND 2048
+                    ),
+
+  -- date that required action is due
+  due_at            DATE NOT NULL
+);
+
+--
+-- CISA vulnerability CVE IDs (e.g. "CVE-YYYY-NNNN").
+--
+CREATE VIEW cisa_vuln_cve_ids AS
+  SELECT vuln_id,
+         printf('CVE-%04d-%04d', cve_year, cve_num) AS nvd_id
+    FROM cisa_vulns;
+
+--
+-- create CISA vulnerabilities FTS table
+--
+-- this virtual table is populated and cleared by triggers on the
+-- corresponding cve tables (see triggers below).
+--
+CREATE VIRTUAL TABLE cisa_vulns_fts USING fts5(
+  -- parent vulnerability id
+  vuln_id UNINDEXED,
+
+  -- NVD CVE ID (e.g. CVE-XXXX-YYYY)
+  cve_id,
+
+  -- vulnerability vendor/project
+  vendor,
+
+  -- vulnerability product
+  product,
+
+  -- vulnerability name
+  name,
+
+  -- vulnerability description
+  description,
+
+  -- use porter stemming
+  tokenize = 'porter'
+);
+
+--
+-- Dummy table to trigger cve_fts refreshes.
+--
+CREATE TABLE cisa_vulns_fts_refresh (
+  created_at        TIMESTAMP WITH TIME ZONE NOT NULL
+                    DEFAULT CURRENT_TIMESTAMP
+);
+
+-- create before insert trigger on cve_fts_refresh
+CREATE TRIGGER tr_cisa_vulns_fts_refresh_before_insert
+  BEFORE INSERT ON cisa_vulns_fts_refresh
+  FOR EACH ROW BEGIN
+    DELETE FROM cisa_vulns_fts;
+  END;
+
+--
+-- create insert trigger on cisa_vulns_fts_refresh
+--
+CREATE TRIGGER tr_cisa_vulns_fts_refresh_after_insert
+  AFTER INSERT ON cisa_vulns_fts_refresh
+  FOR EACH ROW BEGIN
+    INSERT INTO cisa_vulns_fts(
+      vuln_id,
+      cve_id,
+      vendor,
+      product,
+      name,
+      description
+    )
+      SELECT a.vuln_id, -- vuln ID
+             b.cve_id, -- NVD cve id
+             c.name, -- vendor name
+             d.name, -- product name
+             a.name, -- vuln name
+             a.description -- vuln description
+
+        FROM cisa_vulns a
+        JOIN cve_vuln_cve_ids b
+          ON (b.vuln_id = a.vuln_id)
+        JOIN cisa_vendors c
+          ON (c.vendor_id = a.vendor_id)
+        JOIN cisa_products d
+          ON (c.product_id = a.product_id);
+  END;