Skip to main content

rok_db_core/
audit.rs

1//! A trigger-based audit log (feature `json`): every insert, update and
2//! delete of an audited table — through rok-db, bulk queries or raw SQL —
3//! is recorded in `rok_audit_log` with the old and new row as JSONB.
4//!
5//! ```ignore
6//! rok_db::audit::install(&db).await?;                       // once: table + trigger function
7//! rok_db::audit::enable::<User>(&db, &[User::PASSWORD_HASH]).await?;  // per table, minus secrets
8//!
9//! // Attribute changes to an actor: every transaction started inside
10//! // `with_actor` records it (or call `tx.set_actor(..)` yourself).
11//! rok_db::audit::with_actor("user:42", async {
12//!     db.transaction(|tx| Box::pin(async move { user.save(&mut *tx).await })).await
13//! }).await?;
14//!
15//! for entry in rok_db::audit::history::<User>(&db, user.id).await? {
16//!     println!("{} {:?} by {:?}: {:?}", entry.op, entry.changed, entry.actor, entry.new_data);
17//! }
18//! ```
19//!
20//! Updates that change nothing are not recorded. Statements outside a
21//! transaction have no actor unless the database role's `rok.actor` setting
22//! is set.
23
24use std::future::Future;
25use std::time::{Duration, SystemTime};
26
27use sqlx::postgres::PgRow;
28use sqlx::{FromRow, Row};
29
30use crate::{Column, Db, Executor, Expr, Hooks, Model, Result, Value};
31
32/// The audit log table.
33pub const TABLE: &str = "rok_audit_log";
34
35tokio::task_local! {
36    static ACTOR: String;
37}
38
39/// Run `future` with `actor` recorded on every transaction it begins via
40/// [`Db::begin`] / [`Db::transaction`].
41pub async fn with_actor<F: Future>(actor: impl Into<String>, future: F) -> F::Output {
42    ACTOR.scope(actor.into(), future).await
43}
44
45/// The current actor, if inside [`with_actor`].
46pub fn current_actor() -> Option<String> {
47    ACTOR.try_with(String::clone).ok()
48}
49
50const INSTALL: &str = r#"
51CREATE TABLE IF NOT EXISTS rok_audit_log (
52    id BIGSERIAL PRIMARY KEY,
53    table_name TEXT NOT NULL,
54    record_key TEXT,
55    op TEXT NOT NULL,
56    actor TEXT,
57    old_data JSONB,
58    new_data JSONB,
59    changed TEXT[] NOT NULL DEFAULT '{}',
60    at_ms BIGINT NOT NULL DEFAULT (extract(epoch FROM clock_timestamp()) * 1000)::BIGINT
61);
62CREATE INDEX IF NOT EXISTS rok_audit_log_record ON rok_audit_log (table_name, record_key, id);
63CREATE OR REPLACE FUNCTION rok_db_audit() RETURNS trigger LANGUAGE plpgsql AS $rok$
64DECLARE
65    old_j jsonb;
66    new_j jsonb;
67    j jsonb;
68    keys text[] := string_to_array(TG_ARGV[0], ',');
69    excluded text[] := TG_ARGV[2:];
70    changed text[] := '{}';
71BEGIN
72    IF TG_OP <> 'INSERT' THEN old_j := to_jsonb(OLD) - excluded; END IF;
73    IF TG_OP <> 'DELETE' THEN new_j := to_jsonb(NEW) - excluded; END IF;
74    IF TG_OP = 'UPDATE' THEN
75        SELECT coalesce(array_agg(k ORDER BY k), '{}') INTO changed
76        FROM jsonb_object_keys(new_j) AS k
77        WHERE new_j -> k IS DISTINCT FROM old_j -> k;
78        IF cardinality(changed) = 0 THEN RETURN NULL; END IF;
79    END IF;
80    IF TG_OP = 'DELETE' THEN j := to_jsonb(OLD); ELSE j := to_jsonb(NEW); END IF;
81    INSERT INTO rok_audit_log (table_name, record_key, op, actor, old_data, new_data, changed)
82    VALUES (
83        TG_ARGV[1],
84        __KEY__,
85        TG_OP,
86        nullif(current_setting('rok.actor', true), ''),
87        old_j,
88        new_j,
89        changed
90    );
91    RETURN NULL;
92END
93$rok$;
94"#;
95
96fn quoted(ident: &str) -> String {
97    let mut s = String::new();
98    crate::sql::push_ident(&mut s, ident);
99    s
100}
101
102fn literal(s: &str) -> String {
103    format!("'{}'", s.replace('\'', "''"))
104}
105
106/// Create the `rok_audit_log` table and the trigger function (idempotent).
107pub async fn install(db: &Db) -> Result<()> {
108    db.execute(&INSTALL.replace("__KEY__", crate::key::TRIGGER_KEY_SQL))
109        .await?;
110    Ok(())
111}
112
113/// Start auditing `M`'s table, leaving the `exclude`d columns (secrets,
114/// large blobs) out of the recorded data. Re-running replaces the trigger.
115pub async fn enable<M: Model>(db: &Db, exclude: &[Column<M>]) -> Result<()> {
116    let mut args = vec![
117        literal(&crate::key::key_columns_arg::<M>()),
118        literal(M::TABLE),
119    ];
120    args.extend(exclude.iter().map(|c| literal(c.name())));
121    let table = quoted(M::TABLE);
122    db.execute(&format!(
123        "DROP TRIGGER IF EXISTS rok_db_audit ON {table};
124         CREATE TRIGGER rok_db_audit AFTER INSERT OR UPDATE OR DELETE ON {table}
125             FOR EACH ROW EXECUTE FUNCTION rok_db_audit({});",
126        args.join(", ")
127    ))
128    .await?;
129    Ok(())
130}
131
132/// Stop auditing `M`'s table (recorded entries are kept).
133pub async fn disable<M: Model>(db: &Db) -> Result<()> {
134    db.execute(&format!(
135        "DROP TRIGGER IF EXISTS rok_db_audit ON {}",
136        quoted(M::TABLE)
137    ))
138    .await?;
139    Ok(())
140}
141
142/// Every audit entry of one `M` record, oldest first. Pass a tuple for
143/// composite keys.
144pub async fn history<'e, M: Model>(
145    executor: impl Executor<'e>,
146    key: impl crate::IntoKey,
147) -> Result<Vec<AuditEntry>> {
148    let values = key.into_key();
149    AuditEntry::for_table::<M>()
150        .filter(Expr::raw(
151            format!(
152                "{} = {}",
153                quoted("record_key"),
154                crate::key::key_param_sql(values.len())
155            ),
156            values,
157        ))
158        .order_by(AuditEntry::ID)
159        .all(executor)
160        .await
161}
162
163/// One recorded change. Query the log like any model:
164/// `AuditEntry::for_table::<User>().filter(AuditEntry::ACTOR.eq("user:42"))`.
165#[derive(Debug, Clone, PartialEq)]
166pub struct AuditEntry {
167    /// Sequential id.
168    pub id: i64,
169    /// The audited table (as named by the model).
170    pub table_name: String,
171    /// The record's primary key, as text (a JSON array of texts for
172    /// composite keys).
173    pub record_key: Option<String>,
174    /// `INSERT`, `UPDATE` or `DELETE`.
175    pub op: String,
176    /// Who made the change, if known.
177    pub actor: Option<String>,
178    /// The row before the change (`None` for inserts).
179    pub old_data: Option<serde_json::Value>,
180    /// The row after the change (`None` for deletes).
181    pub new_data: Option<serde_json::Value>,
182    /// Columns that changed (updates only).
183    pub changed: Vec<String>,
184    /// When the change happened, in milliseconds since the Unix epoch.
185    pub at_ms: i64,
186}
187
188impl AuditEntry {
189    /// The `id` column.
190    pub const ID: Column<Self> = Column::new("id");
191    /// The `table_name` column.
192    pub const TABLE_NAME: Column<Self> = Column::new("table_name");
193    /// The `record_key` column.
194    pub const RECORD_KEY: Column<Self> = Column::new("record_key");
195    /// The `op` column.
196    pub const OP: Column<Self> = Column::new("op");
197    /// The `actor` column.
198    pub const ACTOR: Column<Self> = Column::new("actor");
199    /// The `at_ms` column.
200    pub const AT_MS: Column<Self> = Column::new("at_ms");
201
202    /// Entries of `M`'s table.
203    pub fn for_table<M: Model>() -> crate::Select<Self> {
204        Self::filter(Self::TABLE_NAME.eq(M::TABLE))
205    }
206
207    /// When the change happened.
208    pub fn at(&self) -> SystemTime {
209        SystemTime::UNIX_EPOCH + Duration::from_millis(self.at_ms.max(0) as u64)
210    }
211}
212
213impl<'r> FromRow<'r, PgRow> for AuditEntry {
214    fn from_row(row: &'r PgRow) -> std::result::Result<Self, sqlx::Error> {
215        Ok(Self {
216            id: row.try_get("id")?,
217            table_name: row.try_get("table_name")?,
218            record_key: row.try_get("record_key")?,
219            op: row.try_get("op")?,
220            actor: row.try_get("actor")?,
221            old_data: row.try_get("old_data")?,
222            new_data: row.try_get("new_data")?,
223            changed: row.try_get("changed")?,
224            at_ms: row.try_get("at_ms")?,
225        })
226    }
227}
228
229impl Hooks for AuditEntry {}
230
231impl Model for AuditEntry {
232    const TABLE: &'static str = TABLE;
233    const PRIMARY_KEY: &'static str = "id";
234    const COLUMNS: &'static [&'static str] = &[
235        "id",
236        "table_name",
237        "record_key",
238        "op",
239        "actor",
240        "old_data",
241        "new_data",
242        "changed",
243        "at_ms",
244    ];
245    const GENERATED: &'static [&'static str] = &["id", "at_ms"];
246
247    fn primary_key(&self) -> Value {
248        Value::from(self.id)
249    }
250
251    fn values(&self) -> Vec<(&'static str, Value)> {
252        vec![
253            ("id", Value::from(self.id)),
254            ("table_name", Value::from(&self.table_name)),
255            ("record_key", Value::from(&self.record_key)),
256            ("op", Value::from(&self.op)),
257            ("actor", Value::from(&self.actor)),
258            ("old_data", Value::from(&self.old_data)),
259            ("new_data", Value::from(&self.new_data)),
260            ("changed", Value::from(&self.changed)),
261            ("at_ms", Value::from(self.at_ms)),
262        ]
263    }
264}