Skip to main content

max / makenotwork

8.1 KB · 246 lines History Blame Raw
1 //! DB-layer contract tests for `db::synckit::security`, the audit trail and the
2 //! sync-token revocation line.
3 //!
4 //! An audit log that dedupes reports a repeated attack as one attempt, and a
5 //! revocation that misses is a stolen token that keeps working. Pinned here:
6 //! the trail is append-only under an argument-for-argument repeat, it is scoped
7 //! to one app, a row outlives the user it names (the subject is nulled rather
8 //! than the row cascaded away with the account, which is what would erase the
9 //! evidence), revoking sync tokens leaves the website session's own stamp
10 //! alone, and a second revocation moves the line forward so a token minted
11 //! between the two cannot survive it.
12 //!
13 //! `db_synckit_accounts_layer` pins the audit log's basic append and the
14 //! cross-user token revocation, and is not repeated here.
15 //!
16 //! Delete this file and the trail could start deduping, cascade away with the
17 //! account it describes, or stop advancing, none of it visible to a route
18 //! test.
19
20 use crate::harness::db::TestDb;
21 use crate::harness::seed_user;
22
23 use makenotwork::db::synckit;
24 use makenotwork::db::{SyncAppId, UserId};
25
26 /// Seed a sync app owned by `user`.
27 async fn seed_app(pool: &sqlx::PgPool, user: UserId, name: &str) -> SyncAppId {
28 sqlx::query_scalar::<_, SyncAppId>(
29 "INSERT INTO sync_apps (creator_id, name, api_key_hash, api_key_prefix)
30 VALUES ($1, $2, $3, $4) RETURNING id",
31 )
32 .bind(user)
33 .bind(name)
34 .bind(format!("hash_{name}"))
35 .bind(&name[..name.len().min(8)])
36 .fetch_one(pool)
37 .await
38 .expect("seed sync app")
39 }
40 // ── security ────────────────────────────────────────────────────────────────
41
42 #[tokio::test]
43 async fn the_audit_log_appends_an_identical_event_twice_and_scopes_it_to_one_app() {
44 let db = TestDb::new().await;
45 let user = seed_user(&db.pool, "sksecp_append").await;
46 let app = seed_app(&db.pool, user, "secappend").await;
47 let other_app = seed_app(&db.pool, user, "secother").await;
48
49 // The same event twice, argument for argument. An audit log that deduped
50 // would hide a repeated attack as one attempt.
51 for _ in 0..2 {
52 synckit::record_security_event(
53 &db.pool,
54 app,
55 Some(user),
56 synckit::sync_security_event::AUTH_FAILURE,
57 Some(serde_json::json!({ "attempt": "same" })),
58 Some("198.51.100.7"),
59 )
60 .await
61 .unwrap();
62 }
63 synckit::record_security_event(
64 &db.pool,
65 other_app,
66 Some(user),
67 synckit::sync_security_event::KEY_ROTATION_COMPLETED,
68 None,
69 None,
70 )
71 .await
72 .unwrap();
73
74 let here: Vec<(String, Option<String>)> = sqlx::query_as(
75 "SELECT event_type, ip FROM sync_security_events WHERE app_id = $1 ORDER BY id",
76 )
77 .bind(app)
78 .fetch_all(&db.pool)
79 .await
80 .unwrap();
81 assert_eq!(
82 here.len(),
83 2,
84 "append-only: two identical events are two rows: {here:?}"
85 );
86 assert!(
87 here.iter()
88 .all(|(e, ip)| e == "auth_failure" && ip.as_deref() == Some("198.51.100.7")),
89 "both rows keep what was recorded: {here:?}"
90 );
91
92 let there: Vec<(String, Option<String>)> = sqlx::query_as(
93 "SELECT event_type, ip FROM sync_security_events WHERE app_id = $1 ORDER BY id",
94 )
95 .bind(other_app)
96 .fetch_all(&db.pool)
97 .await
98 .unwrap();
99 assert_eq!(
100 there.len(),
101 1,
102 "another app's audit trail is its own: {there:?}"
103 );
104 // The literal strings are the contract with whoever queries this table by
105 // hand, so they are asserted as literals rather than against the constant.
106 assert_eq!(there[0].0, "key_rotation_completed", "{there:?}");
107 }
108
109 #[tokio::test]
110 async fn an_audit_row_outlives_the_user_it_names() {
111 let db = TestDb::new().await;
112 let owner = seed_user(&db.pool, "sksecp_owner").await;
113 let subject = seed_user(&db.pool, "sksecp_subject").await;
114 // The app belongs to someone else, so removing the subject cannot take the
115 // app (and its events) down by cascade.
116 let app = seed_app(&db.pool, owner, "secoutlive").await;
117
118 synckit::record_security_event(
119 &db.pool,
120 app,
121 Some(subject),
122 synckit::sync_security_event::DEVICE_REMOVED,
123 Some(serde_json::json!({ "device": "old-laptop" })),
124 Some("203.0.113.4"),
125 )
126 .await
127 .unwrap();
128
129 sqlx::query("DELETE FROM users WHERE id = $1")
130 .bind(subject)
131 .execute(&db.pool)
132 .await
133 .expect("remove the audited user");
134
135 let rows: Vec<(String, Option<UserId>, Option<serde_json::Value>)> = sqlx::query_as(
136 "SELECT event_type, user_id, detail FROM sync_security_events WHERE app_id = $1",
137 )
138 .bind(app)
139 .fetch_all(&db.pool)
140 .await
141 .unwrap();
142 assert_eq!(
143 rows.len(),
144 1,
145 "the trail survives the account it describes: {rows:?}"
146 );
147 assert_eq!(rows[0].0, "device_removed");
148 assert_eq!(
149 rows[0].1, None,
150 "the subject is nulled rather than the row deleted: {rows:?}"
151 );
152 assert_eq!(
153 rows[0].2,
154 Some(serde_json::json!({ "device": "old-laptop" })),
155 "and the detail an operator would investigate is still there: {rows:?}"
156 );
157 }
158
159 #[tokio::test]
160 async fn revoking_sync_tokens_leaves_the_website_session_alone() {
161 let db = TestDb::new().await;
162 let user = seed_user(&db.pool, "sksecp_split").await;
163
164 let before: (
165 Option<chrono::DateTime<chrono::Utc>>,
166 Option<chrono::DateTime<chrono::Utc>>,
167 ) = sqlx::query_as(
168 "SELECT sync_jwt_invalidated_at, jwt_invalidated_at FROM users WHERE id = $1",
169 )
170 .bind(user)
171 .fetch_one(&db.pool)
172 .await
173 .unwrap();
174 assert_eq!(
175 (before.0, before.1),
176 (None, None),
177 "a fresh user has neither stamp set"
178 );
179
180 synckit::invalidate_user_sync_tokens(&db.pool, user)
181 .await
182 .unwrap();
183
184 let after: (
185 Option<chrono::DateTime<chrono::Utc>>,
186 Option<chrono::DateTime<chrono::Utc>>,
187 ) = sqlx::query_as(
188 "SELECT sync_jwt_invalidated_at, jwt_invalidated_at FROM users WHERE id = $1",
189 )
190 .bind(user)
191 .fetch_one(&db.pool)
192 .await
193 .unwrap();
194 assert!(
195 after.0.is_some(),
196 "the sync sessions must be forced to re-authenticate: {after:?}"
197 );
198 // The two stamps are deliberately separate: removing a sync device must
199 // not log the creator out of the website they are working in.
200 assert_eq!(
201 after.1, None,
202 "and the website session stamp must not be touched: {after:?}"
203 );
204 }
205
206 #[tokio::test]
207 async fn revoking_sync_tokens_again_moves_the_stamp_forward() {
208 let db = TestDb::new().await;
209 let user = seed_user(&db.pool, "sksecp_forward").await;
210
211 // Backdated by a day so the comparison below cannot turn on clock
212 // resolution: the second stamp has to be later by roughly that day.
213 sqlx::query(
214 "UPDATE users SET sync_jwt_invalidated_at = NOW() - INTERVAL '1 day' WHERE id = $1",
215 )
216 .bind(user)
217 .execute(&db.pool)
218 .await
219 .unwrap();
220 let old: Option<chrono::DateTime<chrono::Utc>> =
221 sqlx::query_scalar("SELECT sync_jwt_invalidated_at FROM users WHERE id = $1")
222 .bind(user)
223 .fetch_one(&db.pool)
224 .await
225 .unwrap();
226 let old = old.expect("the backdated stamp is set");
227
228 synckit::invalidate_user_sync_tokens(&db.pool, user)
229 .await
230 .unwrap();
231
232 let new: Option<chrono::DateTime<chrono::Utc>> =
233 sqlx::query_scalar("SELECT sync_jwt_invalidated_at FROM users WHERE id = $1")
234 .bind(user)
235 .fetch_one(&db.pool)
236 .await
237 .unwrap();
238 let new = new.expect("the stamp is still set");
239 // A second revocation has to move the line forward, or a token issued
240 // between the two revocations would survive the second one.
241 assert!(
242 new > old,
243 "the revocation line must advance: {new} is not after {old}"
244 );
245 }
246