1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
package main
// calweb's own state.
//
// There is no mirror of the calendar here: every read asks Google, whose API
// already answers the questions a mirror would exist for (a time window,
// sorted, with recurrences expanded; a full-text search). What remains is the
// state that is calweb's rather than Google's — the OAuth grant, and the
// proposals waiting for a person to confirm them. See calweb(7), "No mirror".
import (
"database/sql"
"errors"
"fmt"
"os"
"path/filepath"
"time"
_ "modernc.org/sqlite"
)
// migrations are applied in order, each exactly once, recorded in
// schema_version. Append only: a migration that has run somewhere must never
// change, because nothing would re-run it there.
var migrations = []string{
// 1: the OAuth grant. One row, because calweb serves one account; the
// CHECK makes a second one an error rather than an ambiguity about which
// token is current.
`CREATE TABLE oauth (
id INTEGER PRIMARY KEY CHECK (id = 1),
refresh_token TEXT NOT NULL,
scope TEXT NOT NULL,
obtained_at INTEGER NOT NULL,
access_token TEXT,
access_expires_at INTEGER
);
CREATE TABLE proposals (
token TEXT PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN ('create','edit','delete')),
event_id TEXT,
etag TEXT,
before_json TEXT,
after_json TEXT,
created_at INTEGER NOT NULL,
applied_at INTEGER,
result_json TEXT,
discarded_at INTEGER
);`,
// 2: a pending login. The PKCE verifier and state must survive between
// /login and the callback; they are kept here rather than in memory so
// that a restart in between fails visibly ("unknown state") rather than
// with a verifier silently gone.
`CREATE TABLE oauth_pending (
state TEXT PRIMARY KEY,
verifier TEXT NOT NULL,
redirect TEXT NOT NULL,
created_at INTEGER NOT NULL
);`,
}
func openDB(path string) (*sql.DB, error) {
if err := os.MkdirAll(filepath.Dir(path), 0o700); err != nil {
return nil, fmt.Errorf("create db dir: %w", err)
}
// Create the file 0600 before SQLite does, so the refresh token is never
// in a file anybody else on the machine could read, not even briefly.
f, err := os.OpenFile(path, os.O_RDWR|os.O_CREATE, 0o600)
if err != nil {
return nil, fmt.Errorf("create db: %w", err)
}
f.Close()
if err := os.Chmod(path, 0o600); err != nil {
return nil, fmt.Errorf("chmod db: %w", err)
}
db, err := sql.Open("sqlite",
"file:"+path+"?_pragma=busy_timeout(30000)&_pragma=journal_mode(WAL)")
if err != nil {
return nil, fmt.Errorf("open db: %w", err)
}
db.SetMaxOpenConns(1)
if err := migrate(db); err != nil {
db.Close()
return nil, err
}
return db, nil
}
func migrate(db *sql.DB) error {
if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)`); err != nil {
return fmt.Errorf("schema_version: %w", err)
}
var v int
err := db.QueryRow(`SELECT version FROM schema_version`).Scan(&v)
if errors.Is(err, sql.ErrNoRows) {
if _, err := db.Exec(`INSERT INTO schema_version (version) VALUES (0)`); err != nil {
return err
}
} else if err != nil {
return err
}
for i := v; i < len(migrations); i++ {
tx, err := db.Begin()
if err != nil {
return err
}
if _, err := tx.Exec(migrations[i]); err != nil {
tx.Rollback()
return fmt.Errorf("migration %d: %w", i+1, err)
}
if _, err := tx.Exec(`UPDATE schema_version SET version = ?`, i+1); err != nil {
tx.Rollback()
return err
}
if err := tx.Commit(); err != nil {
return err
}
}
return nil
}
// ============================================================================
// OAuth grant
// ============================================================================
type grant struct {
RefreshToken string
Scope string
ObtainedAt time.Time
AccessToken string
AccessExpiresAt time.Time
}
var errNoGrant = errors.New("not logged in")
func loadGrant(db *sql.DB) (grant, error) {
var g grant
var obtained int64
var access sql.NullString
var expires sql.NullInt64
err := db.QueryRow(`SELECT refresh_token, scope, obtained_at, access_token, access_expires_at
FROM oauth WHERE id = 1`).Scan(&g.RefreshToken, &g.Scope, &obtained, &access, &expires)
if errors.Is(err, sql.ErrNoRows) {
return grant{}, errNoGrant
}
if err != nil {
return grant{}, err
}
g.ObtainedAt = time.Unix(obtained, 0)
g.AccessToken = access.String
if expires.Valid {
g.AccessExpiresAt = time.Unix(expires.Int64, 0)
}
return g, nil
}
func saveGrant(db *sql.DB, g grant) error {
_, err := db.Exec(`INSERT INTO oauth (id, refresh_token, scope, obtained_at, access_token, access_expires_at)
VALUES (1, ?, ?, ?, ?, ?)
ON CONFLICT (id) DO UPDATE SET refresh_token = excluded.refresh_token,
scope = excluded.scope, obtained_at = excluded.obtained_at,
access_token = excluded.access_token, access_expires_at = excluded.access_expires_at`,
g.RefreshToken, g.Scope, g.ObtainedAt.Unix(), g.AccessToken, g.AccessExpiresAt.Unix())
return err
}
func saveAccessToken(db *sql.DB, token string, expires time.Time) error {
_, err := db.Exec(`UPDATE oauth SET access_token = ?, access_expires_at = ? WHERE id = 1`,
token, expires.Unix())
return err
}
// dropGrant forgets the grant, used when Google says it is no longer valid, so
// that every page then says "log in" rather than repeating the same failure.
func dropGrant(db *sql.DB) error {
_, err := db.Exec(`DELETE FROM oauth`)
return err
}
func savePending(db *sql.DB, state, verifier, redirect string) error {
// A pending login older than ten minutes was abandoned; clearing them here
// keeps the table from growing without needing a timer.
db.Exec(`DELETE FROM oauth_pending WHERE created_at < ?`, time.Now().Add(-10*time.Minute).Unix())
_, err := db.Exec(`INSERT INTO oauth_pending (state, verifier, redirect, created_at) VALUES (?, ?, ?, ?)`,
state, verifier, redirect, time.Now().Unix())
return err
}
// takePending returns and deletes a pending login, so a state is good for one
// callback only.
func takePending(db *sql.DB, state string) (verifier, redirect string, err error) {
err = db.QueryRow(`DELETE FROM oauth_pending WHERE state = ? AND created_at >= ?
RETURNING verifier, redirect`, state, time.Now().Add(-10*time.Minute).Unix()).Scan(&verifier, &redirect)
if errors.Is(err, sql.ErrNoRows) {
return "", "", errors.New("unknown or expired login state; start again at /login")
}
return verifier, redirect, err
}
|