begriffs open source - libpw/blob - share/libpw-schema.sql

   1 PRAGMA foreign_keys = ON;
   2
   3 BEGIN;
   4
   5 CREATE TABLE login (
   6         id INTEGER PRIMARY KEY, -- internal use only
   7         -- case insensitive match for e.g. emails
   8         user TEXT COLLATE NOCASE UNIQUE NOT NULL,
   9         pass TEXT NOT NULL
  10                 CHECK ( -- bcrypt
  11                         -- $2<a/b/x/y>$[cost]$[22 character salt][31 character hash]
  12                         length(pass) >= 58 AND
  13                         -- corrected OpenBSD algorithm (from February 2014)
  14                         pass LIKE '$2b$%'
  15                 )
  16 ) STRICT;
  17
  18 CREATE TABLE token (
  19         login_id INTEGER NOT NULL,
  20         hash BLOB NOT NULL
  21                 CHECK(length(hash) == 32), -- sha256
  22         issued_at REAL NOT NULL
  23                 DEFAULT (unixepoch('now')),
  24         expires_at REAL NOT NULL
  25                 DEFAULT (unixepoch('now', '+2 hours')),
  26         redeemed INTEGER NOT NULL DEFAULT (FALSE)
  27                 CHECK (redeemed = FALSE OR redeemed = TRUE), -- boolean
  28
  29         CHECK (expires_at > issued_at),
  30
  31         FOREIGN KEY(login_id) REFERENCES login(id)
  32                 ON DELETE CASCADE
  33                 ON UPDATE CASCADE
  34 ) STRICT;
  35
  36 CREATE INDEX token_idx ON token (login_id, hash);
  37
  38 -- human-readable view of the tokens
  39 CREATE VIEW token_active AS
  40 SELECT user, hex(hash) AS hash,
  41         strftime('%FT%TZ', datetime(issued_at, 'unixepoch')) as issued_at,
  42         strftime('%FT%TZ', datetime(expires_at, 'unixepoch')) as expires_at
  43   FROM token
  44  INNER JOIN login ON login.id = token.login_id
  45  WHERE unixepoch('now') < token.expires_at
  46    AND NOT redeemed;
  47
  48 COMMIT;