logaritmisk/biggus-dickus
1
0
Fork 0
Code Issues 17 Pull Requests Actions Packages Projects Releases Wiki Activity

Run the app under a non-owner DB role (INSERT/SELECT only on audit_log) #5

New Issue
Open
opened 2026-06-02 06:09:58 +00:00 by logaritmisk · 0 comments
logaritmisk commented 2026-06-02 06:09:58 +00:00
Owner
Copy Link
Copy Source

audit_log immutability is enforced by triggers rejecting UPDATE/DELETE/TRUNCATE (crates/db/migrations/0001_audit_log.sql). Triggers are robust against the application's normal DML, but a table owner / superuser can bypass them via DDL — ALTER TABLE audit_log DISABLE TRIGGER, DROP TRIGGER, or SET session_replication_role = replica.

To make the append-only guarantee hold even if the app connection is compromised, the application's runtime DB role should be a non-owner with only INSERT and SELECT on audit_log (and appropriate grants elsewhere) — so it cannot disable or drop the triggers. Fits naturally into the per-org provisioning model (a migration/owner role separate from the app role).

  • Define an app role distinct from the migration/owner role.
  • Grant the app role INSERT, SELECT on audit_log (no UPDATE/DELETE/TRUNCATE, not owner).
  • Document/automate this in provisioning.

Source: Plan 1 (audit spine) final review — non-blocking, security hardening.

`audit_log` immutability is enforced by triggers rejecting UPDATE/DELETE/TRUNCATE (`crates/db/migrations/0001_audit_log.sql`). Triggers are robust against the application's normal DML, but a **table owner / superuser can bypass them** via DDL — `ALTER TABLE audit_log DISABLE TRIGGER`, `DROP TRIGGER`, or `SET session_replication_role = replica`. To make the append-only guarantee hold even if the app connection is compromised, the application's runtime DB role should be a **non-owner with only `INSERT` and `SELECT`** on `audit_log` (and appropriate grants elsewhere) — so it cannot disable or drop the triggers. Fits naturally into the per-org provisioning model (a migration/owner role separate from the app role). - [ ] Define an app role distinct from the migration/owner role. - [ ] Grant the app role `INSERT, SELECT` on `audit_log` (no `UPDATE`/`DELETE`/`TRUNCATE`, not owner). - [ ] Document/automate this in provisioning. _Source: Plan 1 (audit spine) final review — non-blocking, security hardening._
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
logaritmisk
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: logaritmisk/biggus-dickus#5