accounts/store.go (7415 bytes)
1 package main 2 3 import ( 4 "crypto/rand" 5 "crypto/sha256" 6 "database/sql" 7 "encoding/hex" 8 "errors" 9 "time" 10 11 _ "modernc.org/sqlite" 12 ) 13 14 // The users table holds only addresses that a sign-in link verified. 15 // login_tokens and sessions hold SHA-256 hashes of their tokens, so a copy 16 // of the database does not let a person sign in. feed_token and 17 // unsub_token are stored as plain text: the site must show them again, 18 // and they give access only to public posts and to the unsubscribe step. 19 const schema = ` 20 CREATE TABLE IF NOT EXISTS users ( 21 id INTEGER PRIMARY KEY, 22 email TEXT NOT NULL UNIQUE, 23 created_at INTEGER NOT NULL, 24 email_on INTEGER NOT NULL DEFAULT 1, 25 feed_token TEXT NOT NULL UNIQUE, 26 unsub_token TEXT NOT NULL UNIQUE 27 ); 28 CREATE TABLE IF NOT EXISTS muted_tags ( 29 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, 30 tag TEXT NOT NULL, 31 PRIMARY KEY (user_id, tag) 32 ); 33 CREATE TABLE IF NOT EXISTS login_tokens ( 34 hash TEXT PRIMARY KEY, 35 email TEXT NOT NULL, 36 created_at INTEGER NOT NULL, 37 expires_at INTEGER NOT NULL 38 ); 39 CREATE TABLE IF NOT EXISTS sessions ( 40 hash TEXT PRIMARY KEY, 41 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, 42 expires_at INTEGER NOT NULL 43 ); 44 CREATE TABLE IF NOT EXISTS posts ( 45 slug TEXT PRIMARY KEY, 46 first_seen INTEGER NOT NULL 47 ); 48 CREATE TABLE IF NOT EXISTS deliveries ( 49 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, 50 slug TEXT NOT NULL, 51 PRIMARY KEY (user_id, slug) 52 ); 53 ` 54 55 const ( 56 loginTTL = 15 * time.Minute 57 loginSpacing = 2 * time.Minute // minimum time between two links to one address 58 sessionTTL = 30 * 24 * time.Hour 59 ) 60 61 var errNotFound = errors.New("not found") 62 63 func openDB(path string) (*sql.DB, error) { 64 db, err := sql.Open("sqlite", path+"?_pragma=foreign_keys(1)&_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)") 65 if err != nil { 66 return nil, err 67 } 68 // One connection serializes all writes. The site is small, and this 69 // removes the need to handle SQLITE_BUSY. It also keeps a :memory: 70 // database the same for all queries in tests. 71 db.SetMaxOpenConns(1) 72 if _, err := db.Exec(schema); err != nil { 73 db.Close() 74 return nil, err 75 } 76 return db, nil 77 } 78 79 // newToken returns a random token with 128 bits of entropy. 80 func newToken() string { return rand.Text() } 81 82 func hashToken(t string) string { 83 s := sha256.Sum256([]byte(t)) 84 return hex.EncodeToString(s[:]) 85 } 86 87 type user struct { 88 ID int64 89 Email string 90 CreatedAt int64 91 EmailOn bool 92 FeedToken string 93 UnsubToken string 94 } 95 96 const userColumns = `id, email, created_at, email_on, feed_token, unsub_token` 97 98 func scanUser(row *sql.Row) (user, error) { 99 var u user 100 err := row.Scan(&u.ID, &u.Email, &u.CreatedAt, &u.EmailOn, &u.FeedToken, &u.UnsubToken) 101 if errors.Is(err, sql.ErrNoRows) { 102 err = errNotFound 103 } 104 return u, err 105 } 106 107 // createLoginToken makes a sign-in token for email. It returns an empty 108 // token and no error if a token for email was made less than loginSpacing 109 // ago. This stops a person who uses the form to send many emails to one 110 // address. 111 func createLoginToken(db *sql.DB, email string, now time.Time) (string, error) { 112 if _, err := db.Exec(`DELETE FROM login_tokens WHERE expires_at <= ?`, now.Unix()); err != nil { 113 return "", err 114 } 115 var recent int 116 err := db.QueryRow(`SELECT count(*) FROM login_tokens WHERE email = ? AND created_at > ?`, 117 email, now.Add(-loginSpacing).Unix()).Scan(&recent) 118 if err != nil || recent > 0 { 119 return "", err 120 } 121 t := newToken() 122 _, err = db.Exec(`INSERT INTO login_tokens (hash, email, created_at, expires_at) VALUES (?, ?, ?, ?)`, 123 hashToken(t), email, now.Unix(), now.Add(loginTTL).Unix()) 124 if err != nil { 125 return "", err 126 } 127 return t, nil 128 } 129 130 // consumeLoginToken removes the token and returns the user for its 131 // address. It makes the user if the address has no account. A token works 132 // one time only. 133 func consumeLoginToken(db *sql.DB, token string, now time.Time) (user, error) { 134 var email string 135 err := db.QueryRow(`DELETE FROM login_tokens WHERE hash = ? AND expires_at > ? RETURNING email`, 136 hashToken(token), now.Unix()).Scan(&email) 137 if errors.Is(err, sql.ErrNoRows) { 138 return user{}, errNotFound 139 } 140 if err != nil { 141 return user{}, err 142 } 143 _, err = db.Exec(`INSERT INTO users (email, created_at, feed_token, unsub_token) VALUES (?, ?, ?, ?) 144 ON CONFLICT (email) DO NOTHING`, email, now.Unix(), newToken(), newToken()) 145 if err != nil { 146 return user{}, err 147 } 148 return scanUser(db.QueryRow(`SELECT `+userColumns+` FROM users WHERE email = ?`, email)) 149 } 150 151 func createSession(db *sql.DB, userID int64, now time.Time) (string, error) { 152 if _, err := db.Exec(`DELETE FROM sessions WHERE expires_at <= ?`, now.Unix()); err != nil { 153 return "", err 154 } 155 t := newToken() 156 _, err := db.Exec(`INSERT INTO sessions (hash, user_id, expires_at) VALUES (?, ?, ?)`, 157 hashToken(t), userID, now.Add(sessionTTL).Unix()) 158 return t, err 159 } 160 161 func sessionUser(db *sql.DB, token string, now time.Time) (user, error) { 162 return scanUser(db.QueryRow(`SELECT u.id, u.email, u.created_at, u.email_on, u.feed_token, u.unsub_token 163 FROM sessions s JOIN users u ON u.id = s.user_id 164 WHERE s.hash = ? AND s.expires_at > ?`, hashToken(token), now.Unix())) 165 } 166 167 func deleteSession(db *sql.DB, token string) error { 168 _, err := db.Exec(`DELETE FROM sessions WHERE hash = ?`, hashToken(token)) 169 return err 170 } 171 172 func userByFeedToken(db *sql.DB, token string) (user, error) { 173 return scanUser(db.QueryRow(`SELECT `+userColumns+` FROM users WHERE feed_token = ?`, token)) 174 } 175 176 func mutedTags(db *sql.DB, userID int64) (map[string]bool, error) { 177 rows, err := db.Query(`SELECT tag FROM muted_tags WHERE user_id = ?`, userID) 178 if err != nil { 179 return nil, err 180 } 181 defer rows.Close() 182 muted := map[string]bool{} 183 for rows.Next() { 184 var t string 185 if err := rows.Scan(&t); err != nil { 186 return nil, err 187 } 188 muted[t] = true 189 } 190 return muted, rows.Err() 191 } 192 193 // saveSettings sets the email switch and replaces the muted tags. 194 func saveSettings(db *sql.DB, userID int64, emailOn bool, muted []string) error { 195 tx, err := db.Begin() 196 if err != nil { 197 return err 198 } 199 defer tx.Rollback() 200 if _, err := tx.Exec(`UPDATE users SET email_on = ? WHERE id = ?`, emailOn, userID); err != nil { 201 return err 202 } 203 if _, err := tx.Exec(`DELETE FROM muted_tags WHERE user_id = ?`, userID); err != nil { 204 return err 205 } 206 for _, t := range muted { 207 if _, err := tx.Exec(`INSERT OR IGNORE INTO muted_tags (user_id, tag) VALUES (?, ?)`, userID, t); err != nil { 208 return err 209 } 210 } 211 return tx.Commit() 212 } 213 214 // rotateFeedToken gives the user a new feed token. The old feed URL then 215 // stops working. 216 func rotateFeedToken(db *sql.DB, userID int64) error { 217 _, err := db.Exec(`UPDATE users SET feed_token = ? WHERE id = ?`, newToken(), userID) 218 return err 219 } 220 221 // unsubscribe turns off email for the user with the unsubscribe token. It 222 // returns errNotFound for an unknown token. 223 func unsubscribe(db *sql.DB, token string) error { 224 res, err := db.Exec(`UPDATE users SET email_on = 0 WHERE unsub_token = ?`, token) 225 if err != nil { 226 return err 227 } 228 if n, _ := res.RowsAffected(); n == 0 { 229 return errNotFound 230 } 231 return nil 232 } 233 234 // deleteUser removes the user. The foreign keys remove the sessions, 235 // muted tags and delivery records of the user. 236 func deleteUser(db *sql.DB, userID int64) error { 237 _, err := db.Exec(`DELETE FROM users WHERE id = ?`, userID) 238 return err 239 }