-- SQLite3 schema for Cryptech PKCS #11 implementation.
--
-- Author: Rob Austein
-- Copyright (c) 2015, SUNET
--
-- Redistribution and use in source and binary forms, with or
-- without modification, are permitted provided that the following
-- conditions are met:
--
-- 1. Redistributions of source code must retain the above copyright
--    notice, this list of conditions and the following disclaimer.
--
-- 2. Redistributions in binary form must reproduce the above copyright
--    notice, this list of conditions and the following disclaimer in
--    the documentation and/or other materials provided with the
--    distribution.
--
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
-- FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
-- COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
-- LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
-- CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
-- STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
-- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
-- ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

-- Notes:
--
-- The CHECK constraints in the attribute tables are checking
-- CKA_TOKEN, to make sure we don't accidently file token objects in
-- the session table or vice versa.
--
-- temp.object.token_object_id is a foreign-key reference to
-- main.token_object.id, but we can't use a real foreign key reference
-- because they're in different databases.  If we're careful about how
-- we do our joins, this is harmless, but may lead to some clutter if
-- a long running session has handles on token objects which some
-- other process deletes from the database.  If this happens and we
-- care for some reason, we can clean up such clutter with something
-- like:
--
--     WITH
--         known AS (SELECT token_object_id FROM token_object)
--     DELETE FROM object
--     WHERE  token_object_id IS NOT NULL
--     AND    token_object_id NOT IN known;

PRAGMA foreign_keys = ON;

-- Values we have to store somewhere and for which we have no better
-- place.  This is a table with exactly one row (enforced by the CHECK
-- clause on the primary index).  All columns must either allow NULL
-- or provide default values.

CREATE TABLE IF NOT EXISTS global (
       global_id                INTEGER PRIMARY KEY NOT NULL DEFAULT 1 CHECK (global_id = 1),

       -- Key-encryption-key (KEK)
       --
       -- The KEK **really** should be somewhere else, like in RAM
       -- protected by tamper detection circuitry, but we don't have
       -- that yet.  Not obvious that a separate file would be more
       -- secure, so keep it here until we do have a better place.

       kek                      BLOB CHECK (kek IS NULL OR (typeof(kek) = "blob" AND length(kek) IN (16, 32))),

       -- PBKDF2-based PIN storage and check values.
       --
       -- "so_pin" and "user_pin" are PBKDF2 output, so only
       -- moderately sensitive.
       --
       -- Not obvious that PKCS #11 ever really allows "so_pin" to be
       -- unset, so it may want a NOT NULL constraint, but in that
       -- case we'll need to provide a default value, which doesn't
       -- seem like much of an improvement.  "so_pin" probably
       -- requires out-of-band initialization.  "user-pin" is allowed
       -- to be unset, there's an error code specifically for that
       -- situation.
       --
       -- Numeric minima for PBKDF2 iterations, length of PIN, and
       -- length of PBKDF2 salt are somewhat arbitrary, and will
       -- probably change over time (which is why they are minima).
       -- Initial testing was with 100000, which takes about 8 seconds
       -- on a Novena with the current SHA256 and PBKDF2
       -- implementation, which seems a bit slow, so backed that down
       -- a bit.  Feel free to suggest better minima.

       pbkdf2_iterations        INTEGER NOT NULL DEFAULT 20000,
       so_pin                   BLOB,
       user_pin                 BLOB,
       so_pin_salt,             BLOB,
       user_pin_salt            BLOB,
       CHECK ((pbkdf2_iterations >= 10000)                                                                AND
              (so_pin        IS NULL OR (typeof(so_pin)        = "blob" AND length(so_pin)        >= 32)) AND
              (user_pin      IS NULL OR (typeof(user_pin)      = "blob" AND length(user_pin)      >= 32)) AND
              (so_pin_salt   IS NULL OR (typeof(so_pin_salt)   = "blob" AND length(so_pin_salt)   >= 16)) AND
              (user_pin_salt IS NULL OR (typeof(user_pin_salt) = "blob" AND length(user_pin_salt) >= 16)))
);

INSERT OR IGNORE INTO global DEFAULT VALUES;

CREATE TEMPORARY TABLE IF NOT EXISTS session (
        session_id              INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        session_handle          INTEGER NOT NULL UNIQUE
                                CHECK (session_handle > 0 AND session_handle <= 0xFFFFFFFF)
);

CREATE TEMPORARY TABLE IF NOT EXISTS object (
        object_id               INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        object_handle           INTEGER NOT NULL UNIQUE
                                CHECK (object_handle > 0 AND object_handle <= 0xFFFFFFFF),
        session_id              INTEGER REFERENCES session
                                ON DELETE CASCADE ON UPDATE CASCADE
                                DEFERRABLE INITIALLY DEFERRED,
        token_object_id         INTEGER,
        session_object_id       INTEGER REFERENCES session_object
                                ON DELETE CASCADE ON UPDATE CASCADE
                                DEFERRABLE INITIALLY DEFERRED,
        CHECK                   (token_object_id IS NULL OR (session_id IS NULL AND session_object_id IS NULL)),
        UNIQUE                  (token_object_id),
        UNIQUE                  (session_id, session_object_id)
);

CREATE TEMPORARY TABLE IF NOT EXISTS session_object (
        session_object_id       INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        private_key             BLOB UNIQUE,
        object_id               INTEGER NOT NULL UNIQUE
                                REFERENCES object
                                ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TEMPORARY TABLE IF NOT EXISTS session_attribute (
        type                    INTEGER NOT NULL,
        session_object_id       INTEGER NOT NULL REFERENCES session_object
                                ON DELETE CASCADE ON UPDATE CASCADE,
        value                   BLOB NOT NULL,
        UNIQUE                  (type, session_object_id),
        CHECK                   (type <> 1 OR value = X'00')
);

CREATE TABLE IF NOT EXISTS token_object (
        token_object_id         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        private_key             BLOB UNIQUE
);

CREATE TABLE IF NOT EXISTS token_attribute (
        type                    INTEGER NOT NULL,
        token_object_id         INTEGER NOT NULL REFERENCES token_object
                                ON DELETE CASCADE ON UPDATE CASCADE,
        value                   BLOB NOT NULL,
        UNIQUE                  (type, token_object_id),
        CHECK                   (type <> 1 OR value <> X'00')
);

-- http://sqlite.org/foreignkeys.html says we might want these.

CREATE INDEX IF NOT EXISTS temp.object__session                         ON object(session_id);
CREATE INDEX IF NOT EXISTS temp.object__session_object                  ON object(session_object_id);
CREATE INDEX IF NOT EXISTS temp.session_object__object                  ON session_object(object_id);
CREATE INDEX IF NOT EXISTS temp.session_attribute__session_object       ON session_attribute(session_object_id);
CREATE INDEX IF NOT EXISTS token_attribute__token_object                ON token_attribute(token_object_id);

-- Local variables:
-- indent-tabs-mode: nil
-- End: