github.com/quay/claircore@v1.5.28/datastore/postgres/migrations/matcher/01-init.sql (about)

     1  -- Needed for uuid generation in-database.
     2  -- The inline function makes for a nicer error message.
     3  DO $$
     4  DECLARE
     5  	hint text;
     6  	detail text;
     7  	code text;
     8  BEGIN
     9  	EXECUTE 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"';
    10  EXCEPTION WHEN OTHERS THEN
    11  	-- https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS
    12  	GET STACKED DIAGNOSTICS
    13  		code = RETURNED_SQLSTATE,
    14  		detail = PG_EXCEPTION_DETAIL,
    15  		hint = PG_EXCEPTION_HINT;
    16  	RAISE EXCEPTION USING
    17  		MESSAGE = 'Please load the "uuid-ossp" extension.',
    18  		ERRCODE = code,
    19  		DETAIL = detail,
    20  		HINT = hint;
    21  END;
    22  $$ LANGUAGE plpgsql;
    23  -- Update_operation is a table keeping a log of updater runs.
    24  --
    25  -- Ref is used when a specific update_operation needs to be exposed to a
    26  -- client.
    27  CREATE TABLE IF NOT EXISTS update_operation (
    28  	id			BIGSERIAL PRIMARY KEY,
    29  	ref         uuid UNIQUE DEFAULT uuid_generate_v4(),
    30  	updater		TEXT NOT NULL,
    31  	fingerprint TEXT,
    32  	date		TIMESTAMP WITH TIME ZONE DEFAULT now()
    33  );
    34  CREATE INDEX IF NOT EXISTS uo_updater_idx ON update_operation (updater);
    35  -- Create the type used as a column later.
    36  CREATE TYPE VersionRange AS RANGE ( SUBTYPE = integer[10]);
    37  -- Vuln is a write-once table of vulnerabilities.
    38  --
    39  -- Updaters should attempt to insert vulnerabilities and on success or
    40  -- collision, insert a row into ou_vuln.
    41  CREATE TABLE IF NOT EXISTS vuln (
    42  	id                     BIGSERIAL PRIMARY KEY,
    43  	hash_kind              TEXT NOT NULL,
    44  	hash                   BYTEA NOT NULL,
    45  	updater                TEXT,
    46  	name                   TEXT,
    47  	description            TEXT,
    48  	issued                 timestamptz,
    49  	links                  TEXT,
    50  	severity               TEXT,
    51  	normalized_severity    TEXT,
    52  	package_name           TEXT,
    53  	package_version        TEXT,
    54  	package_module         TEXT,
    55  	package_arch           TEXT,
    56  	package_kind           TEXT,
    57  	dist_id                TEXT,
    58  	dist_name              TEXT,
    59  	dist_version           TEXT,
    60  	dist_version_code_name TEXT,
    61  	dist_version_id        TEXT,
    62  	dist_arch              TEXT,
    63  	dist_cpe               TEXT,
    64  	dist_pretty_name       TEXT,
    65  	repo_name              TEXT,
    66  	repo_key               TEXT,
    67  	repo_uri               TEXT,
    68  	fixed_in_version       TEXT,
    69  	arch_operation         TEXT,
    70  	vulnerable_range       VersionRange NOT NULL DEFAULT VersionRange('{}', '{}', '()'),
    71  	version_kind           TEXT,
    72  	UNIQUE (hash_kind, hash)
    73  );
    74  -- this index is tuned for the application. if you change this measure pre and post
    75  -- change query speeds when generating vulnerability reports.
    76  CREATE INDEX vuln_lookup_idx on vuln (package_name, dist_id,
    77                                           dist_name, dist_pretty_name,
    78                                           dist_version, dist_version_id,
    79                                           package_module, dist_version_code_name,
    80                                           repo_name, dist_arch,
    81                                           dist_cpe, repo_key,
    82                                           repo_uri);
    83  -- Uo_vuln is the association table that does the many-many association
    84  -- between update operations and vulnerabilities.
    85  --
    86  -- The FKs enable us to GC the vulnerabilities by first removing old
    87  -- update_operation rows and having that cascade to this table, then
    88  -- remove vulnerabilities that are not referenced from this table.
    89  CREATE TABLE IF NOT EXISTS uo_vuln (
    90  	uo   bigint REFERENCES update_operation (id) ON DELETE CASCADE,
    91  	vuln bigint REFERENCES vuln             (id) ON DELETE CASCADE,
    92  	PRIMARY KEY (uo, vuln)
    93  );
    94  -- Latest_vuln is a helper view to get the current snapshot of the vuln database.
    95  CREATE OR REPLACE VIEW latest_vuln AS
    96  SELECT v.*
    97  FROM (SELECT DISTINCT ON (updater) id FROM update_operation ORDER BY updater, id DESC) uo
    98  	JOIN uo_vuln ON uo_vuln.uo = uo.id
    99  	JOIN vuln v ON uo_vuln.vuln = v.id;