orchard

  1package main
  2
  3import (
  4	"database/sql"
  5	"fmt"
  6	"os"
  7	"path/filepath"
  8	"time"
  9
 10	_ "modernc.org/sqlite"
 11)
 12
 13// Nothing here stores a credential in a form it can be read back from. Session
 14// identifiers are kept as a SHA-256 of the cookie value, recovery codes and
 15// login codes as Argon2id, so a copy of this file is not a way in.
 16const schema = `
 17-- One row, enforced by the CHECK. A table rather than a constant because
 18-- renaming is a feature, and because ntfy_account names a different namespace
 19-- that ntfy itself cannot rename, so the two have to be free to drift apart.
 20CREATE TABLE IF NOT EXISTS users (
 21    id           INTEGER PRIMARY KEY CHECK (id = 1),
 22    username     TEXT    NOT NULL,
 23    ntfy_account TEXT    NOT NULL,
 24    created      INTEGER NOT NULL
 25);
 26
 27-- Opaque random identifiers rather than a signed payload, which is what makes
 28-- revocation possible: a signed cookie is valid until it expires no matter what
 29-- this table says, and the only way to end one is to rotate the signing key and
 30-- end every other session with it.
 31CREATE TABLE IF NOT EXISTS sessions (
 32    id        INTEGER PRIMARY KEY AUTOINCREMENT,
 33    hash      BLOB    NOT NULL UNIQUE,
 34    created   INTEGER NOT NULL,
 35    last_seen INTEGER NOT NULL,
 36    expires   INTEGER NOT NULL,
 37    -- When this session last proved possession of the phone. Regenerating
 38    -- recovery codes wants a recent one, so a stolen cookie cannot rotate
 39    -- the credentials it would need to keep itself alive.
 40    sudo_at   INTEGER NOT NULL DEFAULT 0,
 41    ip        TEXT    NOT NULL DEFAULT '',
 42    country   TEXT    NOT NULL DEFAULT '',
 43    city      TEXT    NOT NULL DEFAULT '',
 44    ua        TEXT    NOT NULL DEFAULT '',
 45    cf_ray    TEXT    NOT NULL DEFAULT '',
 46    revoked   INTEGER NOT NULL DEFAULT 0
 47);
 48
 49CREATE INDEX IF NOT EXISTS sessions_live ON sessions(hash) WHERE revoked = 0;
 50
 51-- At most one unconsumed row at a time. A second login request while one is
 52-- outstanding returns the same page and publishes nothing, which is what
 53-- collapses a flood of requests into one notification per window.
 54--
 55-- browser_hash binds the code to whoever asked for it. Without it a code pushed
 56-- to the phone could be typed into somebody else's session, which is the whole
 57-- attack that push OTP is otherwise wide open to.
 58CREATE TABLE IF NOT EXISTS pending_logins (
 59    id           INTEGER PRIMARY KEY AUTOINCREMENT,
 60    code_hash    BLOB    NOT NULL,
 61    code_salt    BLOB    NOT NULL,
 62    browser_hash BLOB    NOT NULL,
 63    created      INTEGER NOT NULL,
 64    expires      INTEGER NOT NULL,
 65    attempts     INTEGER NOT NULL DEFAULT 0,
 66    consumed     INTEGER NOT NULL DEFAULT 0,
 67    ip           TEXT    NOT NULL DEFAULT '',
 68    country      TEXT    NOT NULL DEFAULT '',
 69    city         TEXT    NOT NULL DEFAULT '',
 70    ua           TEXT    NOT NULL DEFAULT ''
 71);
 72
 73-- One row per notification actually published. The per account ceiling counts
 74-- these rather than requests, and it ignores the source address:
 75-- per-IP limits are bypassed by sending one request from each of a thousand
 76-- proxies, and this is the ceiling that still holds when that happens.
 77CREATE TABLE IF NOT EXISTS sends (
 78    id INTEGER PRIMARY KEY AUTOINCREMENT,
 79    ts INTEGER NOT NULL
 80);
 81
 82CREATE INDEX IF NOT EXISTS sends_ts ON sends(ts);
 83
 84-- The break-glass, ten at a time, Argon2id hashed like repos' push tokens.
 85-- prefix is the first four characters in clear, which finds the right row
 86-- without hashing all ten and gives nothing away on its own.
 87CREATE TABLE IF NOT EXISTS recovery_codes (
 88    id      INTEGER PRIMARY KEY AUTOINCREMENT,
 89    prefix  TEXT    NOT NULL,
 90    hash    BLOB    NOT NULL,
 91    salt    BLOB    NOT NULL,
 92    created INTEGER NOT NULL,
 93    used_at INTEGER NOT NULL DEFAULT 0
 94);
 95
 96CREATE INDEX IF NOT EXISTS recovery_live ON recovery_codes(prefix) WHERE used_at = 0;
 97
 98-- The local half of the audit trail. Every row here also ships to
 99-- logging.bythewood.me, and this copy exists because the first thing to do
100-- after an alert is look, and logging may be the thing that is down.
101--
102-- No code, expired or otherwise, is ever written here. Retention outlives
103-- incident response.
104CREATE TABLE IF NOT EXISTS events (
105    id      INTEGER PRIMARY KEY AUTOINCREMENT,
106    ts      INTEGER NOT NULL,
107    kind    TEXT    NOT NULL,
108    ip      TEXT    NOT NULL DEFAULT '',
109    country TEXT    NOT NULL DEFAULT '',
110    city    TEXT    NOT NULL DEFAULT '',
111    ua      TEXT    NOT NULL DEFAULT '',
112    cf_ray  TEXT    NOT NULL DEFAULT '',
113    detail  TEXT    NOT NULL DEFAULT ''
114);
115
116CREATE INDEX IF NOT EXISTS events_ts ON events(ts);
117`
118
119// openDB opens the database and applies the schema. Pragmas go in the DSN
120// because they are per connection and database/sql opens connections lazily.
121func openDB(path string) (*sql.DB, error) {
122	if dir := filepath.Dir(path); dir != "" {
123		if err := os.MkdirAll(dir, 0o755); err != nil {
124			return nil, fmt.Errorf("create data dir: %w", err)
125		}
126	}
127
128	dsn := path +
129		"?_pragma=journal_mode(WAL)" +
130		"&_pragma=synchronous(NORMAL)" +
131		"&_pragma=busy_timeout(5000)" +
132		"&_pragma=foreign_keys(ON)"
133
134	db, err := sql.Open("sqlite", dsn)
135	if err != nil {
136		return nil, fmt.Errorf("open sqlite: %w", err)
137	}
138
139	db.SetMaxOpenConns(8)
140	db.SetMaxIdleConns(8)
141	db.SetConnMaxLifetime(time.Hour)
142
143	if err := db.Ping(); err != nil {
144		return nil, fmt.Errorf("ping sqlite: %w", err)
145	}
146	if _, err := db.Exec(schema); err != nil {
147		return nil, fmt.Errorf("apply schema: %w", err)
148	}
149	return db, nil
150}