1use serde_json::{Value, json};
10use sqlx::Row;
11use sqlx::sqlite::SqliteRow;
12use tracing::debug;
13
14use crate::audit::{Actor, ActorKind, AuditEvent, AuditRecord, ClientContext};
15use crate::sqlite::db::Database;
16use crate::sqlite::nonce::now_secs;
17use crate::sqlite::order::rfc3339;
18
19#[derive(Debug, Clone)]
28pub struct AuditEntry {
29 pub id: i64,
30 pub created_at: i64,
31 pub event: String,
32 pub outcome: String,
33 pub profile: String,
34 pub actor_kind: String,
35 pub actor_id: Option<String>,
36 pub account_id: Option<String>,
37 pub order_id: Option<String>,
38 pub cert_serial: Option<String>,
39 pub identifiers: Vec<String>,
40 pub client_ip: Option<String>,
41 pub client_ptr: Option<String>,
42 pub user_agent: Option<String>,
43 pub request_id: Option<String>,
44 pub reason: Option<String>,
45 pub detail: Option<String>,
46}
47
48const COLUMNS: &str = "id, created_at, event, outcome, profile, actor_kind, actor_id, \
51 account_id, order_id, cert_serial, identifiers, client_ip, client_ptr, \
52 user_agent, request_id, reason, detail";
53
54#[derive(Debug, Clone, Default)]
59pub struct AuditQuery {
60 pub profile: Option<String>,
61 pub account_id: Option<String>,
62 pub order_id: Option<String>,
63 pub cert_serial: Option<String>,
64 pub event: Option<String>,
65 pub outcome: Option<String>,
66 pub since: Option<i64>,
69 pub limit: i64,
70 pub offset: i64,
71}
72
73impl AuditQuery {
74 fn push_predicates(&self, builder: &mut sqlx::QueryBuilder<sqlx::Sqlite>) {
79 let mut separator = " WHERE ";
80 for (column, value) in [
81 ("profile = ", self.profile.as_ref()),
82 ("account_id = ", self.account_id.as_ref()),
83 ("order_id = ", self.order_id.as_ref()),
84 ("cert_serial = ", self.cert_serial.as_ref()),
85 ("event = ", self.event.as_ref()),
86 ("outcome = ", self.outcome.as_ref()),
87 ] {
88 if let Some(value) = value {
89 builder
90 .push(separator)
91 .push(column)
92 .push_bind(value.clone());
93 separator = " AND ";
94 }
95 }
96 if let Some(since) = self.since {
97 builder
98 .push(separator)
99 .push("created_at >= ")
100 .push_bind(since);
101 }
102 }
103}
104
105impl AuditEntry {
106 fn from_row(row: SqliteRow) -> Result<Self, sqlx::Error> {
107 let identifiers_json: String = row.try_get("identifiers")?;
108 let identifiers: Vec<String> = serde_json::from_str(&identifiers_json)
109 .map_err(|error| sqlx::Error::Decode(Box::new(error)))?;
110
111 Ok(Self {
112 id: row.try_get("id")?,
113 created_at: row.try_get("created_at")?,
114 event: row.try_get("event")?,
115 outcome: row.try_get("outcome")?,
116 profile: row.try_get("profile")?,
117 actor_kind: row.try_get("actor_kind")?,
118 actor_id: row.try_get("actor_id")?,
119 account_id: row.try_get("account_id")?,
120 order_id: row.try_get("order_id")?,
121 cert_serial: row.try_get("cert_serial")?,
122 identifiers,
123 client_ip: row.try_get("client_ip")?,
124 client_ptr: row.try_get("client_ptr")?,
125 user_agent: row.try_get("user_agent")?,
126 request_id: row.try_get("request_id")?,
127 reason: row.try_get("reason")?,
128 detail: row.try_get("detail")?,
129 })
130 }
131
132 #[must_use]
135 pub fn event(&self) -> Option<AuditEvent> {
136 AuditEvent::parse(&self.event)
137 }
138
139 pub async fn insert(record: AuditRecord, database: &Database) -> Result<i64, sqlx::Error> {
145 let AuditRecord {
146 event,
147 profile,
148 actor,
149 account_id,
150 order_id,
151 cert_serial,
152 identifiers,
153 client,
154 reason,
155 detail,
156 } = record;
157 let Actor { kind, id: actor_id } = actor;
158 let ClientContext {
159 ip: client_ip,
160 ptr: client_ptr,
161 user_agent,
162 request_id,
163 } = client;
164
165 let identifiers_json = Value::from(identifiers).to_string();
167 let created_at = now_secs();
168
169 let id = sqlx::query(
170 "INSERT INTO audit_log (created_at, event, outcome, profile, actor_kind, actor_id, \
171 account_id, order_id, cert_serial, identifiers, client_ip, client_ptr, user_agent, \
172 request_id, reason, detail) \
173 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id;",
174 )
175 .bind(created_at)
176 .bind(event.as_str())
177 .bind(event.outcome())
178 .bind(&profile)
179 .bind(kind.as_str())
180 .bind(&actor_id)
181 .bind(&account_id)
182 .bind(&order_id)
183 .bind(&cert_serial)
184 .bind(identifiers_json)
185 .bind(&client_ip)
186 .bind(&client_ptr)
187 .bind(&user_agent)
188 .bind(&request_id)
189 .bind(&reason)
190 .bind(&detail)
191 .fetch_one(&database.pool)
192 .await?
193 .try_get::<i64, _>("id")?;
194
195 debug!(
196 event = "db_audit_row_written",
197 outcome = "success",
198 audit_id = id,
199 audit_event = event.as_str(),
200 profile = %profile,
201 actor_kind = kind.as_str(),
202 );
203 Ok(id)
204 }
205
206 pub async fn find_by_id(id: i64, database: &Database) -> Result<Option<Self>, sqlx::Error> {
208 let mut query =
212 sqlx::QueryBuilder::new(format!("SELECT {COLUMNS} FROM audit_log WHERE id = "));
213 query.push_bind(id);
214 let row = query.build().fetch_optional(&database.pool).await?;
215 row.map(Self::from_row).transpose()
216 }
217
218 pub async fn search(
221 query: &AuditQuery,
222 database: &Database,
223 ) -> Result<(Vec<Self>, i64), sqlx::Error> {
224 debug!(
225 event = "db_audit_search_started",
226 outcome = "progress",
227 profile = ?query.profile,
228 account_id = ?query.account_id,
229 audit_event = ?query.event,
230 outcome = ?query.outcome,
231 limit = query.limit,
232 offset = query.offset,
233 );
234
235 let mut page = sqlx::QueryBuilder::new(format!("SELECT {COLUMNS} FROM audit_log"));
236 query.push_predicates(&mut page);
237 page.push(" ORDER BY created_at DESC, id DESC LIMIT ");
242 page.push_bind(query.limit);
243 page.push(" OFFSET ");
244 page.push_bind(query.offset);
245
246 let rows = page.build().fetch_all(&database.pool).await?;
247 let entries: Vec<Self> = rows
248 .into_iter()
249 .map(Self::from_row)
250 .collect::<Result<_, _>>()?;
251
252 let mut count = sqlx::QueryBuilder::new("SELECT COUNT(*) FROM audit_log");
253 query.push_predicates(&mut count);
254 let total: i64 = count
255 .build()
256 .fetch_one(&database.pool)
257 .await?
258 .try_get::<i64, _>(0)?;
259
260 Ok((entries, total))
261 }
262
263 pub async fn count_older_than(cutoff: i64, database: &Database) -> Result<i64, sqlx::Error> {
266 sqlx::query("SELECT COUNT(*) FROM audit_log WHERE created_at < ?;")
267 .bind(cutoff)
268 .fetch_one(&database.pool)
269 .await?
270 .try_get::<i64, _>(0)
271 }
272
273 pub async fn cleanup(cutoff: i64, database: &Database) -> Result<u64, sqlx::Error> {
279 let deleted = sqlx::query("DELETE FROM audit_log WHERE created_at < ?;")
280 .bind(cutoff)
281 .execute(&database.pool)
282 .await?
283 .rows_affected();
284 debug!(
285 event = "db_audit_cleanup",
286 outcome = "success",
287 rows_removed = deleted,
288 cutoff = cutoff
289 );
290 Ok(deleted)
291 }
292
293 #[must_use]
300 pub fn to_json(&self) -> Value {
301 let mut value = json!({
302 "id": self.id,
303 "createdAt": rfc3339(self.created_at),
304 "event": self.event,
305 "outcome": self.outcome,
306 "profile": self.profile,
307 "actorKind": self.actor_kind,
308 "identifiers": self.identifiers,
309 });
310 let map = value
311 .as_object_mut()
312 .expect("the literal above is an object");
313 for (key, field) in [
314 ("actorId", self.actor_id.as_ref()),
315 ("accountId", self.account_id.as_ref()),
316 ("orderId", self.order_id.as_ref()),
317 ("certSerial", self.cert_serial.as_ref()),
318 ("clientIp", self.client_ip.as_ref()),
319 ("clientPtr", self.client_ptr.as_ref()),
320 ("userAgent", self.user_agent.as_ref()),
321 ("requestId", self.request_id.as_ref()),
322 ("reason", self.reason.as_ref()),
323 ("detail", self.detail.as_ref()),
324 ] {
325 if let Some(field) = field {
326 map.insert(key.to_string(), Value::from(field.clone()));
327 }
328 }
329 value
330 }
331}
332
333#[must_use]
336pub fn actor_kinds() -> [&'static str; 4] {
337 [
338 ActorKind::Acme.as_str(),
339 ActorKind::Admin.as_str(),
340 ActorKind::Cli.as_str(),
341 ActorKind::System.as_str(),
342 ]
343}
344
345#[cfg(test)]
346mod tests {
347 use super::*;
348 use crate::audit::{Actor, AuditEvent, ClientContext};
349 use std::sync::Arc;
350
351 async fn db() -> Arc<Database> {
352 Arc::new(Database::connect_in_memory().await.unwrap())
353 }
354
355 fn client() -> ClientContext {
356 ClientContext {
357 ip: Some("203.0.113.7".to_string()),
358 ptr: Some("host.example.com".to_string()),
359 user_agent: Some("certbot/2.9.0".to_string()),
360 request_id: Some("req-1".to_string()),
361 }
362 }
363
364 fn record(event: AuditEvent, profile: &str) -> AuditRecord {
365 AuditRecord::new(event, profile, Actor::acme("acct-1"))
366 }
367
368 fn with_subject(mut record: AuditRecord, order_id: &str, identifiers: &[&str]) -> AuditRecord {
373 record.order_id = Some(order_id.to_string());
374 record.identifiers = identifiers.iter().map(|v| (*v).to_string()).collect();
375 record
376 }
377
378 #[tokio::test]
381 async fn a_row_round_trips_and_derives_its_own_outcome() {
382 let db = db().await;
383 let id = AuditEntry::insert(
384 with_subject(
385 record(AuditEvent::CertificateIssued, "le"),
386 "order-1",
387 &["a.example.com"],
388 )
389 .with_serial("0a0b")
390 .with_client(client()),
391 &db,
392 )
393 .await
394 .unwrap();
395
396 let entry = AuditEntry::find_by_id(id, &db).await.unwrap().unwrap();
397 assert_eq!(entry.event, "certificate_issued");
398 assert_eq!(entry.outcome, "success");
399 assert_eq!(entry.event(), Some(AuditEvent::CertificateIssued));
400 assert_eq!(entry.profile, "le");
401 assert_eq!(entry.actor_kind, "acme");
402 assert_eq!(entry.actor_id.as_deref(), Some("acct-1"));
403 assert_eq!(entry.order_id.as_deref(), Some("order-1"));
404 assert_eq!(entry.cert_serial.as_deref(), Some("0a0b"));
405 assert_eq!(entry.identifiers, vec!["a.example.com"]);
406 assert_eq!(entry.client_ip.as_deref(), Some("203.0.113.7"));
407 assert_eq!(entry.client_ptr.as_deref(), Some("host.example.com"));
408 assert_eq!(entry.user_agent.as_deref(), Some("certbot/2.9.0"));
409 assert_eq!(entry.request_id.as_deref(), Some("req-1"));
410
411 let failed = AuditEntry::insert(
412 record(AuditEvent::CertificateRevokeFailed, "le").with_reason("unauthorized"),
413 &db,
414 )
415 .await
416 .unwrap();
417 let entry = AuditEntry::find_by_id(failed, &db).await.unwrap().unwrap();
418 assert_eq!(entry.outcome, "failure");
419 assert!(entry.identifiers.is_empty());
420 assert_eq!(entry.client_ip, None);
421
422 assert!(AuditEntry::find_by_id(9_999, &db).await.unwrap().is_none());
423 }
424
425 #[tokio::test]
429 async fn a_row_survives_the_account_and_order_it_names_being_deleted() {
430 let db = db().await;
431 let account_id = crate::testutil::account_id(&db).await;
432 let order = crate::sqlite::order::Order::new(
433 "default",
434 &account_id,
435 vec![crate::sqlite::order::Identifier::dns("a.example.com")],
436 0,
437 None,
438 None,
439 );
440 order.insert(&db.pool).await.unwrap();
441
442 let id = AuditEntry::insert(
443 AuditRecord::new(
444 AuditEvent::CertificateIssued,
445 "default",
446 Actor::acme(&account_id),
447 )
448 .with_order(&order),
449 &db,
450 )
451 .await
452 .unwrap();
453
454 crate::sqlite::account::Account::delete(&account_id, &db)
456 .await
457 .unwrap();
458 assert!(
459 crate::sqlite::order::Order::find_by_id(&order.id, &db)
460 .await
461 .unwrap()
462 .is_none()
463 );
464
465 let entry = AuditEntry::find_by_id(id, &db).await.unwrap().unwrap();
466 assert_eq!(entry.account_id.as_deref(), Some(account_id.as_str()));
467 assert_eq!(entry.order_id.as_deref(), Some(order.id.as_str()));
468 assert_eq!(entry.identifiers, vec!["a.example.com"]);
469 }
470
471 #[tokio::test]
474 async fn every_filter_narrows_the_page_and_the_total_together() {
475 let db = db().await;
476 for (event, profile, account, serial) in [
477 (AuditEvent::CertificateIssued, "le", "acct-1", "aa"),
478 (AuditEvent::CertificateIssued, "le", "acct-2", "bb"),
479 (AuditEvent::CertificateIssueFailed, "le", "acct-1", "cc"),
480 (AuditEvent::CertificateRevoked, "internal", "acct-1", "dd"),
481 ] {
482 AuditEntry::insert(
483 AuditRecord::new(event, profile, Actor::acme(account))
484 .with_account(account)
489 .with_serial(serial),
490 &db,
491 )
492 .await
493 .unwrap();
494 }
495
496 let count = async |query: AuditQuery| {
497 let (rows, total) = AuditEntry::search(&query, &db).await.unwrap();
498 assert_eq!(rows.len() as i64, total, "page and total must agree");
499 total
500 };
501 let base = AuditQuery {
502 limit: 50,
503 ..AuditQuery::default()
504 };
505
506 assert_eq!(count(base.clone()).await, 4);
507 assert_eq!(
508 count(AuditQuery {
509 profile: Some("le".to_string()),
510 ..base.clone()
511 })
512 .await,
513 3
514 );
515 assert_eq!(
516 count(AuditQuery {
517 account_id: Some("acct-1".to_string()),
518 ..base.clone()
519 })
520 .await,
521 3
522 );
523 assert_eq!(
524 count(AuditQuery {
525 cert_serial: Some("dd".to_string()),
526 ..base.clone()
527 })
528 .await,
529 1
530 );
531 assert_eq!(
532 count(AuditQuery {
533 event: Some("certificate_issued".to_string()),
534 ..base.clone()
535 })
536 .await,
537 2
538 );
539 assert_eq!(
540 count(AuditQuery {
541 outcome: Some("failure".to_string()),
542 ..base.clone()
543 })
544 .await,
545 1
546 );
547 assert_eq!(
549 count(AuditQuery {
550 profile: Some("le".to_string()),
551 outcome: Some("success".to_string()),
552 account_id: Some("acct-1".to_string()),
553 ..base.clone()
554 })
555 .await,
556 1
557 );
558 assert_eq!(
559 count(AuditQuery {
560 event: Some("certificate_renewed".to_string()),
561 ..base
562 })
563 .await,
564 0
565 );
566 }
567
568 #[tokio::test]
572 async fn paging_one_row_at_a_time_sees_every_row_exactly_once_newest_first() {
573 let db = db().await;
574 let mut inserted = Vec::new();
575 for index in 0..5 {
576 inserted.push(
577 AuditEntry::insert(
578 record(AuditEvent::CertificateIssued, "le")
579 .with_serial(format!("serial-{index}")),
580 &db,
581 )
582 .await
583 .unwrap(),
584 );
585 }
586
587 let mut seen = Vec::new();
588 for offset in 0..5 {
589 let (rows, total) = AuditEntry::search(
590 &AuditQuery {
591 limit: 1,
592 offset,
593 ..AuditQuery::default()
594 },
595 &db,
596 )
597 .await
598 .unwrap();
599 assert_eq!(total, 5);
600 seen.push(rows[0].id);
601 }
602
603 inserted.reverse();
604 assert_eq!(seen, inserted);
605 }
606
607 #[tokio::test]
610 async fn cleanup_removes_only_what_is_strictly_older_than_the_cutoff() {
611 let db = db().await;
612 for _ in 0..3 {
613 AuditEntry::insert(record(AuditEvent::CertificateIssued, "le"), &db)
614 .await
615 .unwrap();
616 }
617 let now = crate::sqlite::nonce::now_secs();
618
619 let past = now - 3600;
621 assert_eq!(AuditEntry::count_older_than(past, &db).await.unwrap(), 0);
622 assert_eq!(AuditEntry::cleanup(past, &db).await.unwrap(), 0);
623
624 assert_eq!(AuditEntry::count_older_than(now, &db).await.unwrap(), 0);
626
627 let future = now + 3600;
628 assert_eq!(AuditEntry::count_older_than(future, &db).await.unwrap(), 3);
629 assert_eq!(AuditEntry::cleanup(future, &db).await.unwrap(), 3);
630 assert_eq!(AuditEntry::count_older_than(future, &db).await.unwrap(), 0);
631 }
632
633 #[tokio::test]
636 async fn to_json_omits_the_columns_that_have_no_value() {
637 let db = db().await;
638 let bare = AuditEntry::insert(
639 AuditRecord::new(AuditEvent::CertificateRevoked, "le", Actor::system()),
640 &db,
641 )
642 .await
643 .unwrap();
644 let json = AuditEntry::find_by_id(bare, &db)
645 .await
646 .unwrap()
647 .unwrap()
648 .to_json();
649 let object = json.as_object().unwrap();
650
651 assert_eq!(object["event"], "certificate_revoked");
652 assert_eq!(object["outcome"], "success");
653 assert_eq!(object["actorKind"], "system");
654 assert_eq!(object["identifiers"], serde_json::json!([]));
655 assert!(object["createdAt"].as_str().unwrap().contains('T'));
656 for absent in [
657 "actorId",
658 "accountId",
659 "orderId",
660 "certSerial",
661 "clientIp",
662 "clientPtr",
663 "userAgent",
664 "requestId",
665 "reason",
666 "detail",
667 ] {
668 assert!(!object.contains_key(absent), "{absent} should be absent");
669 }
670
671 let full = AuditEntry::insert(
672 record(AuditEvent::CertificateIssueFailed, "le")
673 .with_client(client())
674 .with_reason("badCSR")
675 .with_detail("nope"),
676 &db,
677 )
678 .await
679 .unwrap();
680 let json = AuditEntry::find_by_id(full, &db)
681 .await
682 .unwrap()
683 .unwrap()
684 .to_json();
685 assert_eq!(json["clientIp"], "203.0.113.7");
686 assert_eq!(json["clientPtr"], "host.example.com");
687 assert_eq!(json["reason"], "badCSR");
688 assert_eq!(json["detail"], "nope");
689 }
690
691 #[tokio::test]
695 async fn an_unknown_event_string_still_loads_and_simply_does_not_parse() {
696 let db = db().await;
697 sqlx::query(
698 "INSERT INTO audit_log (created_at, event, outcome, profile, actor_kind) \
699 VALUES (0, 'certificate_issued', 'success', 'le', 'acme');",
700 )
701 .execute(&db.pool)
702 .await
703 .unwrap();
704 let mut entry = AuditEntry::search(
705 &AuditQuery {
706 limit: 1,
707 ..AuditQuery::default()
708 },
709 &db,
710 )
711 .await
712 .unwrap()
713 .0
714 .remove(0);
715 entry.event = "certificate_renewed".to_string();
716 assert_eq!(entry.event(), None);
717 }
718
719 #[tokio::test]
721 async fn the_schema_refuses_an_event_outcome_or_actor_it_does_not_know() {
722 let db = db().await;
723 for (event, outcome, actor) in [
724 ("certificate_renewed", "success", "acme"),
725 ("certificate_issued", "maybe", "acme"),
726 ("certificate_issued", "success", "robot"),
727 ] {
728 let error = sqlx::query(
729 "INSERT INTO audit_log (created_at, event, outcome, profile, actor_kind) \
730 VALUES (0, ?, ?, 'le', ?);",
731 )
732 .bind(event)
733 .bind(outcome)
734 .bind(actor)
735 .execute(&db.pool)
736 .await
737 .unwrap_err();
738 assert!(
739 error.to_string().contains("CHECK constraint failed"),
740 "{event}/{outcome}/{actor} was accepted: {error}"
741 );
742 }
743 }
744
745 #[test]
748 fn the_actor_kinds_helper_lists_every_variant() {
749 assert_eq!(actor_kinds(), ["acme", "admin", "cli", "system"]);
750 }
751}