| 1 | package main |
| 2 | |
| 3 | import ( |
| 4 | "crypto/rand" |
| 5 | "crypto/sha256" |
| 6 | "database/sql" |
| 7 | "encoding/base64" |
| 8 | "encoding/hex" |
| 9 | "encoding/json" |
| 10 | "errors" |
| 11 | "strconv" |
| 12 | "time" |
| 13 | |
| 14 | "github.com/go-webauthn/webauthn/webauthn" |
| 15 | _ "modernc.org/sqlite" |
| 16 | ) |
| 17 | |
| 18 | // sqliteStore is vaultStore implementation #1 — the zero-config default. The |
| 19 | // home server is content-blind: the one blob per user is opaque, |
| 20 | // client-encrypted bytes; credentials are WebAuthn public keys (useless |
| 21 | // without the authenticator); tokens are stored hashed. There is deliberately |
| 22 | // no email, username, or display name anywhere. |
| 23 | // |
| 24 | // Versions are integer rowversions internally but rendered as opaque strings |
| 25 | // at the vaultStore boundary — the interface (and the wire) never learns |
| 26 | // they're integers. |
| 27 | type sqliteStore struct{ sql *sql.DB } |
| 28 | |
| 29 | var _ vaultStore = (*sqliteStore)(nil) |
| 30 | |
| 31 | func openSQLiteStore(path string) (*sqliteStore, error) { |
| 32 | d, err := sql.Open("sqlite", path) |
| 33 | if err != nil { |
| 34 | return nil, err |
| 35 | } |
| 36 | // One writer at a time keeps WAL happy and avoids "database is locked". |
| 37 | d.SetMaxOpenConns(1) |
| 38 | for _, p := range []string{ |
| 39 | "PRAGMA journal_mode=WAL", |
| 40 | "PRAGMA busy_timeout=5000", |
| 41 | "PRAGMA synchronous=NORMAL", |
| 42 | "PRAGMA foreign_keys=ON", |
| 43 | } { |
| 44 | if _, err := d.Exec(p); err != nil { |
| 45 | return nil, err |
| 46 | } |
| 47 | } |
| 48 | db := &sqliteStore{sql: d} |
| 49 | if err := db.migrate(); err != nil { |
| 50 | return nil, err |
| 51 | } |
| 52 | return db, nil |
| 53 | } |
| 54 | |
| 55 | func (db *sqliteStore) migrate() error { |
| 56 | _, err := db.sql.Exec(` |
| 57 | CREATE TABLE IF NOT EXISTS users ( |
| 58 | id TEXT PRIMARY KEY, -- random; the only identity there is |
| 59 | created_at INTEGER NOT NULL |
| 60 | ); |
| 61 | CREATE TABLE IF NOT EXISTS credentials ( |
| 62 | cred_id TEXT PRIMARY KEY, -- base64url WebAuthn credential id |
| 63 | user_id TEXT NOT NULL, |
| 64 | credential TEXT NOT NULL, -- webauthn.Credential JSON (public key, sign count, flags) |
| 65 | created_at INTEGER NOT NULL |
| 66 | ); |
| 67 | -- One logical blob per user, kept as its last few versions (overwrite |
| 68 | -- protection). The latest version is the blob; older rows are history. |
| 69 | CREATE TABLE IF NOT EXISTS blobs ( |
| 70 | user_id TEXT NOT NULL, |
| 71 | version INTEGER NOT NULL, |
| 72 | data BLOB NOT NULL, |
| 73 | updated_at INTEGER NOT NULL, |
| 74 | PRIMARY KEY (user_id, version) |
| 75 | ); |
| 76 | -- Device link tokens: bearer credentials minted after a verified ceremony. |
| 77 | -- Only the hash is stored (cf. device_tokens in the messenger) — a stolen DB |
| 78 | -- yields no live credential. id is a separate random handle so the devices |
| 79 | -- list can name a token without revealing anything derived from it. |
| 80 | CREATE TABLE IF NOT EXISTS link_tokens ( |
| 81 | id TEXT PRIMARY KEY, |
| 82 | token_hash TEXT NOT NULL UNIQUE, |
| 83 | user_id TEXT NOT NULL, |
| 84 | created_at INTEGER NOT NULL, |
| 85 | last_seen INTEGER NOT NULL |
| 86 | ); |
| 87 | -- RETIRED: ceremony proofs became stateless HMAC tokens (stateless.go) — |
| 88 | -- nothing reads or writes this table any more. It stays declared because |
| 89 | -- migrations are additive-only; any leftover rows are expired and inert. |
| 90 | CREATE TABLE IF NOT EXISTS ceremony_proofs ( |
| 91 | proof_hash TEXT PRIMARY KEY, |
| 92 | user_id TEXT NOT NULL, |
| 93 | created_at INTEGER NOT NULL |
| 94 | ); |
| 95 | -- Sign in with Apple anchors (siwa.go): the HMAC(pepper, sub) hash is the |
| 96 | -- only trace of the Apple identity — nothing mailable, nothing reversible. |
| 97 | CREATE TABLE IF NOT EXISTS siwa_anchors ( |
| 98 | sub_hash TEXT PRIMARY KEY, |
| 99 | user_id TEXT NOT NULL, |
| 100 | created_at INTEGER NOT NULL |
| 101 | ); |
| 102 | -- Email anchors (email.go): only the blind index — HMAC(pepper, address) — |
| 103 | -- is ever stored; no plaintext address exists anywhere at rest. |
| 104 | CREATE TABLE IF NOT EXISTS email_anchors ( |
| 105 | email_hash TEXT PRIMARY KEY, |
| 106 | user_id TEXT NOT NULL, |
| 107 | created_at INTEGER NOT NULL |
| 108 | ); |
| 109 | -- OIDC provider anchors (oidc.go): siwa_anchors generalized — one row per |
| 110 | -- (provider, HMAC(provider pepper, sub)). Apple deliberately STAYS in |
| 111 | -- siwa_anchors (deployed wire compat); these rows are the newer doors |
| 112 | -- (google, microsoft). |
| 113 | CREATE TABLE IF NOT EXISTS oidc_anchors ( |
| 114 | provider TEXT NOT NULL, |
| 115 | sub_hash TEXT NOT NULL, |
| 116 | user_id TEXT NOT NULL, |
| 117 | created_at INTEGER NOT NULL, |
| 118 | PRIMARY KEY (provider, sub_hash) |
| 119 | );`) |
| 120 | return err |
| 121 | } |
| 122 | |
| 123 | func (s *sqliteStore) siwaUser(subHash string) (string, bool) { |
| 124 | var uid string |
| 125 | err := s.sql.QueryRow(`SELECT user_id FROM siwa_anchors WHERE sub_hash = ?`, subHash).Scan(&uid) |
| 126 | if err != nil { |
| 127 | return "", false |
| 128 | } |
| 129 | return uid, true |
| 130 | } |
| 131 | |
| 132 | func (s *sqliteStore) insertSiwaAnchor(uid, subHash string) (bool, error) { |
| 133 | // ON CONFLICT DO NOTHING + re-read: the insert either won the row or |
| 134 | // someone holds it — same-user is idempotent, different-user is the |
| 135 | // conflict the handler turns into a 409. |
| 136 | if _, err := s.sql.Exec( |
| 137 | `INSERT INTO siwa_anchors (sub_hash, user_id, created_at) VALUES (?, ?, ?) |
| 138 | ON CONFLICT (sub_hash) DO NOTHING`, |
| 139 | subHash, uid, time.Now().Unix()); err != nil { |
| 140 | return false, err |
| 141 | } |
| 142 | owner, ok := s.siwaUser(subHash) |
| 143 | if !ok { |
| 144 | return false, errors.New("siwa anchor vanished") |
| 145 | } |
| 146 | return owner != uid, nil |
| 147 | } |
| 148 | |
| 149 | func (s *sqliteStore) listSiwaAnchors(userID string) ([]Anchor, error) { |
| 150 | return s.listAnchorRows("SELECT sub_hash, created_at FROM siwa_anchors WHERE user_id = ? ORDER BY created_at", userID) |
| 151 | } |
| 152 | |
| 153 | func (s *sqliteStore) deleteSiwaAnchor(userID, subHash string) (deleted, last bool, err error) { |
| 154 | return s.deleteAnchorRow("siwa_anchors", "sub_hash", userID, subHash) |
| 155 | } |
| 156 | |
| 157 | func (s *sqliteStore) emailUser(index string) (string, bool) { |
| 158 | var uid string |
| 159 | err := s.sql.QueryRow(`SELECT user_id FROM email_anchors WHERE email_hash = ?`, index).Scan(&uid) |
| 160 | if err != nil { |
| 161 | return "", false |
| 162 | } |
| 163 | return uid, true |
| 164 | } |
| 165 | |
| 166 | func (s *sqliteStore) insertEmailAnchor(uid, index string) (bool, error) { |
| 167 | // Same shape as insertSiwaAnchor: win the row or read who holds it. |
| 168 | if _, err := s.sql.Exec( |
| 169 | `INSERT INTO email_anchors (email_hash, user_id, created_at) VALUES (?, ?, ?) |
| 170 | ON CONFLICT (email_hash) DO NOTHING`, |
| 171 | index, uid, time.Now().Unix()); err != nil { |
| 172 | return false, err |
| 173 | } |
| 174 | owner, ok := s.emailUser(index) |
| 175 | if !ok { |
| 176 | return false, errors.New("email anchor vanished") |
| 177 | } |
| 178 | return owner != uid, nil |
| 179 | } |
| 180 | |
| 181 | func (s *sqliteStore) listEmailAnchors(userID string) ([]Anchor, error) { |
| 182 | return s.listAnchorRows("SELECT email_hash, created_at FROM email_anchors WHERE user_id = ? ORDER BY created_at", userID) |
| 183 | } |
| 184 | |
| 185 | func (s *sqliteStore) deleteEmailAnchor(userID, index string) (deleted, last bool, err error) { |
| 186 | return s.deleteAnchorRow("email_anchors", "email_hash", userID, index) |
| 187 | } |
| 188 | |
| 189 | // The OIDC anchor methods mirror the siwa set with the provider name beside |
| 190 | // the hash — the same row shape, one more key column. |
| 191 | |
| 192 | func (s *sqliteStore) oidcUser(provider, subHash string) (string, bool) { |
| 193 | var uid string |
| 194 | err := s.sql.QueryRow(`SELECT user_id FROM oidc_anchors WHERE provider = ? AND sub_hash = ?`, provider, subHash).Scan(&uid) |
| 195 | if err != nil { |
| 196 | return "", false |
| 197 | } |
| 198 | return uid, true |
| 199 | } |
| 200 | |
| 201 | func (s *sqliteStore) insertOidcAnchor(uid, provider, subHash string) (bool, error) { |
| 202 | // Same shape as insertSiwaAnchor: win the row or read who holds it. |
| 203 | if _, err := s.sql.Exec( |
| 204 | `INSERT INTO oidc_anchors (provider, sub_hash, user_id, created_at) VALUES (?, ?, ?, ?) |
| 205 | ON CONFLICT (provider, sub_hash) DO NOTHING`, |
| 206 | provider, subHash, uid, time.Now().Unix()); err != nil { |
| 207 | return false, err |
| 208 | } |
| 209 | owner, ok := s.oidcUser(provider, subHash) |
| 210 | if !ok { |
| 211 | return false, errors.New("oidc anchor vanished") |
| 212 | } |
| 213 | return owner != uid, nil |
| 214 | } |
| 215 | |
| 216 | func (s *sqliteStore) listOidcAnchors(userID, provider string) ([]Anchor, error) { |
| 217 | return s.listAnchorRows("SELECT sub_hash, created_at FROM oidc_anchors WHERE user_id = ? AND provider = ? ORDER BY created_at", userID, provider) |
| 218 | } |
| 219 | |
| 220 | // deleteOidcAnchor is deleteAnchorRow with the provider in the predicate — |
| 221 | // the same transaction-atomic last-method guard. |
| 222 | func (s *sqliteStore) deleteOidcAnchor(userID, provider, subHash string) (deleted, last bool, err error) { |
| 223 | tx, err := s.sql.Begin() |
| 224 | if err != nil { |
| 225 | return false, false, err |
| 226 | } |
| 227 | defer tx.Rollback() |
| 228 | var owned int |
| 229 | e := tx.QueryRow("SELECT 1 FROM oidc_anchors WHERE user_id = ? AND provider = ? AND sub_hash = ?", |
| 230 | userID, provider, subHash).Scan(&owned) |
| 231 | if e == sql.ErrNoRows { |
| 232 | return false, false, nil |
| 233 | } |
| 234 | if e != nil { |
| 235 | return false, false, e |
| 236 | } |
| 237 | n, err := signInMethods(tx, userID) |
| 238 | if err != nil { |
| 239 | return false, false, err |
| 240 | } |
| 241 | if n <= 1 { |
| 242 | return false, true, nil |
| 243 | } |
| 244 | if _, err := tx.Exec("DELETE FROM oidc_anchors WHERE user_id = ? AND provider = ? AND sub_hash = ?", |
| 245 | userID, provider, subHash); err != nil { |
| 246 | return false, false, err |
| 247 | } |
| 248 | return true, false, tx.Commit() |
| 249 | } |
| 250 | |
| 251 | // listAnchorRows runs one of the fixed (id, created_at) listing queries above |
| 252 | // — the anchor listings differ only in table and key column(s). |
| 253 | func (s *sqliteStore) listAnchorRows(query string, args ...any) ([]Anchor, error) { |
| 254 | rows, err := s.sql.Query(query, args...) |
| 255 | if err != nil { |
| 256 | return nil, err |
| 257 | } |
| 258 | defer rows.Close() |
| 259 | out := []Anchor{} |
| 260 | for rows.Next() { |
| 261 | var a Anchor |
| 262 | if err := rows.Scan(&a.ID, &a.CreatedAt); err != nil { |
| 263 | return nil, err |
| 264 | } |
| 265 | out = append(out, a) |
| 266 | } |
| 267 | return out, rows.Err() |
| 268 | } |
| 269 | |
| 270 | // deleteAnchorRow removes one identity anchor (siwa or email — table and key |
| 271 | // column are in-code constants from the two callers above, never input) under |
| 272 | // the same transaction-atomic last-method guard as deleteCredential. |
| 273 | func (s *sqliteStore) deleteAnchorRow(table, keyCol, userID, hash string) (deleted, last bool, err error) { |
| 274 | tx, err := s.sql.Begin() |
| 275 | if err != nil { |
| 276 | return false, false, err |
| 277 | } |
| 278 | defer tx.Rollback() |
| 279 | var owned int |
| 280 | e := tx.QueryRow("SELECT 1 FROM "+table+" WHERE user_id = ? AND "+keyCol+" = ?", userID, hash).Scan(&owned) |
| 281 | if e == sql.ErrNoRows { |
| 282 | return false, false, nil |
| 283 | } |
| 284 | if e != nil { |
| 285 | return false, false, e |
| 286 | } |
| 287 | n, err := signInMethods(tx, userID) |
| 288 | if err != nil { |
| 289 | return false, false, err |
| 290 | } |
| 291 | if n <= 1 { |
| 292 | return false, true, nil |
| 293 | } |
| 294 | if _, err := tx.Exec("DELETE FROM "+table+" WHERE user_id = ? AND "+keyCol+" = ?", userID, hash); err != nil { |
| 295 | return false, false, err |
| 296 | } |
| 297 | return true, false, tx.Commit() |
| 298 | } |
| 299 | |
| 300 | // mergeAccounts (#1098) folds the absorbed account into the survivor in ONE |
| 301 | // transaction: every credential, link token, and anchor row repoints its |
| 302 | // user_id to the survivor, then the absorbed user row goes. Repointing can't |
| 303 | // collide — each table's key column (cred_id, token_hash, the anchor hashes) |
| 304 | // is unique across ALL accounts, and the absorbed account by definition holds |
| 305 | // keys the survivor doesn't. The blob check inside the transaction is the |
| 306 | // store-side belt for the handler's precondition: the client empties the |
| 307 | // absorbed vault first, so merge itself destroys nothing but the empty shell. |
| 308 | func (db *sqliteStore) mergeAccounts(survivor, absorbed string) (mergeMoved, error) { |
| 309 | var moved mergeMoved |
| 310 | if survivor == absorbed { |
| 311 | // Belt: the handler 400s this, but a same-account "merge" reaching the |
| 312 | // store would delete the account's own user row below. |
| 313 | return moved, errors.New("merge: survivor and absorbed are the same account") |
| 314 | } |
| 315 | tx, err := db.sql.Begin() |
| 316 | if err != nil { |
| 317 | return moved, err |
| 318 | } |
| 319 | defer tx.Rollback() |
| 320 | var blobs int |
| 321 | if err := tx.QueryRow("SELECT COUNT(*) FROM blobs WHERE user_id = ?", absorbed).Scan(&blobs); err != nil { |
| 322 | return moved, err |
| 323 | } |
| 324 | if blobs > 0 { |
| 325 | return moved, errAbsorbedHasBlob |
| 326 | } |
| 327 | // created_at rides along untouched everywhere — a merged anchor keeps its |
| 328 | // original enrolment time. |
| 329 | move := func(table string) (int, error) { |
| 330 | res, err := tx.Exec("UPDATE "+table+" SET user_id = ? WHERE user_id = ?", survivor, absorbed) |
| 331 | if err != nil { |
| 332 | return 0, err |
| 333 | } |
| 334 | n, err := res.RowsAffected() |
| 335 | return int(n), err |
| 336 | } |
| 337 | for _, table := range []string{"siwa_anchors", "email_anchors", "oidc_anchors"} { |
| 338 | n, err := move(table) |
| 339 | if err != nil { |
| 340 | return mergeMoved{}, err |
| 341 | } |
| 342 | moved.Anchors += n |
| 343 | } |
| 344 | if moved.Passkeys, err = move("credentials"); err != nil { |
| 345 | return mergeMoved{}, err |
| 346 | } |
| 347 | if moved.Devices, err = move("link_tokens"); err != nil { |
| 348 | return mergeMoved{}, err |
| 349 | } |
| 350 | if _, err := tx.Exec("DELETE FROM users WHERE id = ?", absorbed); err != nil { |
| 351 | return mergeMoved{}, err |
| 352 | } |
| 353 | return moved, tx.Commit() |
| 354 | } |
| 355 | |
| 356 | // signInMethods counts every way into the account — passkeys plus SiwA, |
| 357 | // email, and OIDC-provider anchors — inside the caller's transaction, so the |
| 358 | // last-method guard is atomic with the delete it protects. |
| 359 | func signInMethods(tx *sql.Tx, userID string) (int, error) { |
| 360 | var n int |
| 361 | err := tx.QueryRow(`SELECT |
| 362 | (SELECT COUNT(*) FROM credentials WHERE user_id = ?) |
| 363 | + (SELECT COUNT(*) FROM siwa_anchors WHERE user_id = ?) |
| 364 | + (SELECT COUNT(*) FROM email_anchors WHERE user_id = ?) |
| 365 | + (SELECT COUNT(*) FROM oidc_anchors WHERE user_id = ?)`, |
| 366 | userID, userID, userID, userID).Scan(&n) |
| 367 | return n, err |
| 368 | } |
| 369 | |
| 370 | func hashToken(t string) string { |
| 371 | h := sha256.Sum256([]byte(t)) |
| 372 | return hex.EncodeToString(h[:]) |
| 373 | } |
| 374 | |
| 375 | func randToken() (string, error) { |
| 376 | b := make([]byte, 32) |
| 377 | if _, err := rand.Read(b); err != nil { |
| 378 | return "", err |
| 379 | } |
| 380 | return base64.RawURLEncoding.EncodeToString(b), nil |
| 381 | } |
| 382 | |
| 383 | // ---- users + credentials -------------------------------------------------- |
| 384 | |
| 385 | func (db *sqliteStore) createUser(id string) error { |
| 386 | _, err := db.sql.Exec("INSERT INTO users(id, created_at) VALUES(?, ?)", id, time.Now().Unix()) |
| 387 | return err |
| 388 | } |
| 389 | |
| 390 | func (db *sqliteStore) userExists(id string) bool { |
| 391 | var x int |
| 392 | return db.sql.QueryRow("SELECT 1 FROM users WHERE id = ?", id).Scan(&x) == nil |
| 393 | } |
| 394 | |
| 395 | // insertCredential stores a verified WebAuthn credential as the library's own |
| 396 | // JSON — public key, sign count, and flags travel together, so upgrades that |
| 397 | // add fields stay additive. |
| 398 | func (db *sqliteStore) insertCredential(userID string, cred *webauthn.Credential) error { |
| 399 | blob, err := json.Marshal(cred) |
| 400 | if err != nil { |
| 401 | return err |
| 402 | } |
| 403 | _, err = db.sql.Exec( |
| 404 | "INSERT INTO credentials(cred_id, user_id, credential, created_at) VALUES(?, ?, ?, ?)", |
| 405 | base64.RawURLEncoding.EncodeToString(cred.ID), userID, string(blob), time.Now().Unix(), |
| 406 | ) |
| 407 | return err |
| 408 | } |
| 409 | |
| 410 | // updateCredential persists post-ceremony state (sign count, backup flags). |
| 411 | func (db *sqliteStore) updateCredential(cred *webauthn.Credential) error { |
| 412 | blob, err := json.Marshal(cred) |
| 413 | if err != nil { |
| 414 | return err |
| 415 | } |
| 416 | _, err = db.sql.Exec( |
| 417 | "UPDATE credentials SET credential = ? WHERE cred_id = ?", |
| 418 | string(blob), base64.RawURLEncoding.EncodeToString(cred.ID), |
| 419 | ) |
| 420 | return err |
| 421 | } |
| 422 | |
| 423 | func (db *sqliteStore) credentialsFor(userID string) ([]webauthn.Credential, error) { |
| 424 | rows, err := db.sql.Query("SELECT credential FROM credentials WHERE user_id = ? ORDER BY created_at", userID) |
| 425 | if err != nil { |
| 426 | return nil, err |
| 427 | } |
| 428 | defer rows.Close() |
| 429 | out := []webauthn.Credential{} |
| 430 | for rows.Next() { |
| 431 | var blob string |
| 432 | if err := rows.Scan(&blob); err != nil { |
| 433 | return nil, err |
| 434 | } |
| 435 | var c webauthn.Credential |
| 436 | if err := json.Unmarshal([]byte(blob), &c); err != nil { |
| 437 | return nil, err |
| 438 | } |
| 439 | out = append(out, c) |
| 440 | } |
| 441 | return out, rows.Err() |
| 442 | } |
| 443 | |
| 444 | // credentialUser resolves a credential id to its user — the creds/<credId> |
| 445 | // pointer read. In SQLite the pointer and the credential are one row, so |
| 446 | // resolution and the "still listed" check coincide. |
| 447 | func (db *sqliteStore) credentialUser(credID string) (string, bool) { |
| 448 | var uid string |
| 449 | err := db.sql.QueryRow("SELECT user_id FROM credentials WHERE cred_id = ?", credID).Scan(&uid) |
| 450 | return uid, err == nil |
| 451 | } |
| 452 | |
| 453 | // deleteCredential removes one of a user's passkeys (a rogue or lost one). |
| 454 | // It refuses to remove the account's LAST sign-in method — passkeys plus |
| 455 | // siwa/email anchors (#1089 evolved the guard from last-passkey to |
| 456 | // last-method): an account keeping an anchor may shed its final passkey, but |
| 457 | // no removal may leave zero ways in while the blob stays live on the server — |
| 458 | // the explicit path for "I'm done" is the ceremony-gated blob delete. |
| 459 | // deleted=false with last=false means the id is unknown (or someone else's). |
| 460 | func (db *sqliteStore) deleteCredential(userID, credID string) (deleted, last bool, err error) { |
| 461 | tx, err := db.sql.Begin() |
| 462 | if err != nil { |
| 463 | return false, false, err |
| 464 | } |
| 465 | defer tx.Rollback() |
| 466 | var owned int |
| 467 | e := tx.QueryRow("SELECT 1 FROM credentials WHERE user_id = ? AND cred_id = ?", userID, credID).Scan(&owned) |
| 468 | if e == sql.ErrNoRows { |
| 469 | return false, false, nil |
| 470 | } |
| 471 | if e != nil { |
| 472 | return false, false, e |
| 473 | } |
| 474 | n, err := signInMethods(tx, userID) |
| 475 | if err != nil { |
| 476 | return false, false, err |
| 477 | } |
| 478 | if n <= 1 { |
| 479 | return false, true, nil |
| 480 | } |
| 481 | if _, err := tx.Exec("DELETE FROM credentials WHERE user_id = ? AND cred_id = ?", userID, credID); err != nil { |
| 482 | return false, false, err |
| 483 | } |
| 484 | return true, false, tx.Commit() |
| 485 | } |
| 486 | |
| 487 | // listCredentialMeta returns passkey listing metadata (base64url id + |
| 488 | // enrolment time), enrolment order — the anchors list needs no public keys. |
| 489 | func (db *sqliteStore) listCredentialMeta(userID string) ([]Anchor, error) { |
| 490 | return db.listAnchorRows("SELECT cred_id, created_at FROM credentials WHERE user_id = ? ORDER BY created_at", userID) |
| 491 | } |
| 492 | |
| 493 | // ---- the blob --------------------------------------------------------------- |
| 494 | |
| 495 | // getBlob returns the latest version of a user's blob. |
| 496 | func (db *sqliteStore) getBlob(userID string) (data []byte, version string, found bool, err error) { |
| 497 | var v int64 |
| 498 | err = db.sql.QueryRow( |
| 499 | "SELECT data, version FROM blobs WHERE user_id = ? ORDER BY version DESC LIMIT 1", userID, |
| 500 | ).Scan(&data, &v) |
| 501 | if err == sql.ErrNoRows { |
| 502 | return nil, "", false, nil |
| 503 | } |
| 504 | if err != nil { |
| 505 | return nil, "", false, err |
| 506 | } |
| 507 | return data, strconv.FormatInt(v, 10), true, nil |
| 508 | } |
| 509 | |
| 510 | // putBlob writes the next version if ifMatch equals the current one ("0" = no |
| 511 | // blob yet). On mismatch it reports conflict=true with the current version so |
| 512 | // the caller can 409 + ETag; the client merges and retries. Old versions past |
| 513 | // blobKeepVersions are pruned. A version string that was never one of ours |
| 514 | // (non-numeric, negative) is errBadVersion, not a conflict. |
| 515 | func (db *sqliteStore) putBlob(userID string, data []byte, ifMatch string) (version string, conflict bool, err error) { |
| 516 | want, err := strconv.ParseInt(ifMatch, 10, 64) |
| 517 | if err != nil || want < 0 { |
| 518 | return "", false, errBadVersion |
| 519 | } |
| 520 | tx, err := db.sql.Begin() |
| 521 | if err != nil { |
| 522 | return "", false, err |
| 523 | } |
| 524 | defer tx.Rollback() |
| 525 | var cur int64 |
| 526 | if err := tx.QueryRow("SELECT COALESCE(MAX(version), 0) FROM blobs WHERE user_id = ?", userID).Scan(&cur); err != nil { |
| 527 | return "", false, err |
| 528 | } |
| 529 | if cur != want { |
| 530 | return strconv.FormatInt(cur, 10), true, nil |
| 531 | } |
| 532 | next := cur + 1 |
| 533 | if _, err := tx.Exec( |
| 534 | "INSERT INTO blobs(user_id, version, data, updated_at) VALUES(?, ?, ?, ?)", |
| 535 | userID, next, data, time.Now().Unix(), |
| 536 | ); err != nil { |
| 537 | return "", false, err |
| 538 | } |
| 539 | if _, err := tx.Exec( |
| 540 | "DELETE FROM blobs WHERE user_id = ? AND version <= ?", userID, next-blobKeepVersions, |
| 541 | ); err != nil { |
| 542 | return "", false, err |
| 543 | } |
| 544 | return strconv.FormatInt(next, 10), false, tx.Commit() |
| 545 | } |
| 546 | |
| 547 | // listBlobVersions returns the retained versions, newest first. |
| 548 | func (db *sqliteStore) listBlobVersions(userID string) ([]string, error) { |
| 549 | rows, err := db.sql.Query("SELECT version FROM blobs WHERE user_id = ? ORDER BY version DESC", userID) |
| 550 | if err != nil { |
| 551 | return nil, err |
| 552 | } |
| 553 | defer rows.Close() |
| 554 | out := []string{} |
| 555 | for rows.Next() { |
| 556 | var v int64 |
| 557 | if err := rows.Scan(&v); err != nil { |
| 558 | return nil, err |
| 559 | } |
| 560 | out = append(out, strconv.FormatInt(v, 10)) |
| 561 | } |
| 562 | return out, rows.Err() |
| 563 | } |
| 564 | |
| 565 | // deleteBlob removes every version — history included, that's the point of the |
| 566 | // ceremony gate in front of it. |
| 567 | func (db *sqliteStore) deleteBlob(userID string) error { |
| 568 | _, err := db.sql.Exec("DELETE FROM blobs WHERE user_id = ?", userID) |
| 569 | return err |
| 570 | } |
| 571 | |
| 572 | // ---- device link tokens ----------------------------------------------------- |
| 573 | |
| 574 | // createLinkToken mints a bearer token for a user and returns the plaintext |
| 575 | // (shown/stored client-side once; only the hash persists here). |
| 576 | func (db *sqliteStore) createLinkToken(userID string) (string, error) { |
| 577 | tok, err := randToken() |
| 578 | if err != nil { |
| 579 | return "", err |
| 580 | } |
| 581 | id, err := randToken() |
| 582 | if err != nil { |
| 583 | return "", err |
| 584 | } |
| 585 | now := time.Now().Unix() |
| 586 | _, err = db.sql.Exec( |
| 587 | "INSERT INTO link_tokens(id, token_hash, user_id, created_at, last_seen) VALUES(?, ?, ?, ?, ?)", |
| 588 | id, hashToken(tok), userID, now, now, |
| 589 | ) |
| 590 | return tok, err |
| 591 | } |
| 592 | |
| 593 | // linkTokenUser resolves a token hash to its user and bumps last_seen — but |
| 594 | // only when the recorded value is over a day stale. last_seen exists solely |
| 595 | // for the devices list, where day granularity is plenty, and on a CAS'd |
| 596 | // object store a write-per-read would turn every authenticated GET into a |
| 597 | // PUT (#550 Phase 0). |
| 598 | func (db *sqliteStore) linkTokenUser(tokenHash string) (string, bool) { |
| 599 | var uid string |
| 600 | if db.sql.QueryRow("SELECT user_id FROM link_tokens WHERE token_hash = ?", tokenHash).Scan(&uid) != nil { |
| 601 | return "", false |
| 602 | } |
| 603 | now := time.Now().Unix() |
| 604 | db.sql.Exec("UPDATE link_tokens SET last_seen = ? WHERE token_hash = ? AND last_seen < ?", |
| 605 | now, tokenHash, now-lastSeenGranularity) |
| 606 | return uid, true |
| 607 | } |
| 608 | |
| 609 | func (db *sqliteStore) listDevices(userID string) ([]Device, error) { |
| 610 | rows, err := db.sql.Query( |
| 611 | "SELECT id, created_at, last_seen FROM link_tokens WHERE user_id = ? ORDER BY created_at", userID) |
| 612 | if err != nil { |
| 613 | return nil, err |
| 614 | } |
| 615 | defer rows.Close() |
| 616 | out := []Device{} |
| 617 | for rows.Next() { |
| 618 | var d Device |
| 619 | if err := rows.Scan(&d.ID, &d.CreatedAt, &d.LastSeen); err != nil { |
| 620 | return nil, err |
| 621 | } |
| 622 | out = append(out, d) |
| 623 | } |
| 624 | return out, rows.Err() |
| 625 | } |
| 626 | |
| 627 | // linkTokenID returns the device id behind a token hash, so the devices list |
| 628 | // can mark "this device". |
| 629 | func (db *sqliteStore) linkTokenID(tokenHash string) string { |
| 630 | var id string |
| 631 | db.sql.QueryRow("SELECT id FROM link_tokens WHERE token_hash = ?", tokenHash).Scan(&id) |
| 632 | return id |
| 633 | } |
| 634 | |
| 635 | // deleteDevice revokes one of the user's link tokens. ok=false when the id |
| 636 | // doesn't exist or belongs to someone else. |
| 637 | func (db *sqliteStore) deleteDevice(userID, id string) (bool, error) { |
| 638 | res, err := db.sql.Exec("DELETE FROM link_tokens WHERE user_id = ? AND id = ?", userID, id) |
| 639 | if err != nil { |
| 640 | return false, err |
| 641 | } |
| 642 | n, _ := res.RowsAffected() |
| 643 | return n > 0, nil |
| 644 | } |
| 645 | |
amadan