dohertj2/ScadaBridge
1
0
Fork 0
Code Issues 15 Pull Requests Actions Packages Projects Releases Wiki Activity

Audit hash chain: signing/anchoring decision, enablement plan, chainer login, migration deploy gate (#41) #107

New Issue
Open
opened 2026-11-09 16:37:30 -05:00 by dohertj2 · 5 comments
dohertj2 commented 2026-11-09 16:37:30 -05:00
Owner
Copy Link
Copy Source

Decisions + deploy gate for the audit hash chain (#41, off by default):

  1. Signing / external anchoring — the chain is unsigned and anchored only in the same database; someone able to rewrite rows, recompute later links and update the head can forge a consistent chain. Decide: sign the per-month head (who holds the key? Secrets store?) and/or anchor it outside the DB (e.g. periodic head export to a separate store/syslog).
  2. Enablement — when to set AuditLog:HashChain:Enabled on DEV/QA, and whether to chain the historical backlog (~100k rows/min). Run the SourceNode backfill first.
  3. Permissions — the migration denies the writer role writes to the chain tables, so in a hardened two-login setup the chainer and purge paths must not run as the writer login.
  4. Deploy gate — migration 20261109211317_AddAuditLogHashChain must ship with the binaries, both centrals stopped (old binaries can't partition-purge once the columns exist; new binaries' per-channel purge fails without the migration). Pair with the other pending migrations (#102 item 1, #50 PrefixSearchIndexes).
Decisions + deploy gate for the audit hash chain (#41, off by default): 1. **Signing / external anchoring** — the chain is unsigned and anchored only in the same database; someone able to rewrite rows, recompute later links and update the head can forge a consistent chain. Decide: sign the per-month head (who holds the key? Secrets store?) and/or anchor it outside the DB (e.g. periodic head export to a separate store/syslog). 2. **Enablement** — when to set `AuditLog:HashChain:Enabled` on DEV/QA, and whether to chain the historical backlog (~100k rows/min). Run the SourceNode backfill first. 3. **Permissions** — the migration denies the writer role writes to the chain tables, so in a hardened two-login setup the chainer and purge paths must not run as the writer login. 4. **Deploy gate** — migration `20261109211317_AddAuditLogHashChain` must ship with the binaries, both centrals stopped (old binaries can't partition-purge once the columns exist; new binaries' per-channel purge fails without the migration). Pair with the other pending migrations (#102 item 1, #50 PrefixSearchIndexes).
dohertj2 added the type:deploy-gatepriority:p2 labels 2026-11-09 16:37:30 -05:00
dohertj2 commented 2026-11-09 22:13:26 -05:00
Author
Owner
Copy Link
Copy Source

Owner decision 2026-11-09: item 1: no signing; periodically export the per-month chain head (hash + row count) off-box to a store/syslog the DB login can't write. Item 3: chainer and purge run as a separate maintenance login, not the writer. Items 2/4 (enablement, deploy gate) unchanged — they ride the coordinated migration window.

**Owner decision 2026-11-09:** item 1: **no signing; periodically export the per-month chain head (hash + row count) off-box** to a store/syslog the DB login can't write. Item 3: chainer and purge run as a **separate maintenance login**, not the writer. Items 2/4 (enablement, deploy gate) unchanged — they ride the coordinated migration window.
dohertj2 commented 2026-11-09 22:39:26 -05:00
Author
Owner
Copy Link
Copy Source

Item 4: another migration joins the coordinated central-pair window: 20261110032839_AddConfigGraphRowVersions (ScadaBridge#62, a4806576, script docs/plans/sql/AddConfigGraphRowVersions.sql). Apply it after AddAuditLogHashChain. It is purely additive: a non-concurrency rowversion column plus index on 18 configuration tables. SQL Server fills the existing rows as each column is added, a size-of-data operation per table on small tables. Old binaries ignore the column, so it adds no ordering constraint beyond going out in the same window.

Item 4: another migration joins the coordinated central-pair window: **`20261110032839_AddConfigGraphRowVersions`** (ScadaBridge#62, `a4806576`, script `docs/plans/sql/AddConfigGraphRowVersions.sql`). Apply it after `AddAuditLogHashChain`. It is purely additive: a non-concurrency `rowversion` column plus index on 18 configuration tables. SQL Server fills the existing rows as each column is added, a size-of-data operation per table on small tables. Old binaries ignore the column, so it adds no ordering constraint beyond going out in the same window.
dohertj2 commented 2026-11-09 23:33:24 -05:00
Author
Owner
Copy Link
Copy Source

Items 1 and 3 implemented — c4d7ec9b (main). Kept open for items 2 (enablement) and 4 (deploy gate).

Item 1 — no signing; off-box head export (AuditLog:HashChain:HeadExport, default off)

  • Both centrals, every IntervalMinutes (60), read every month's head in one snapshot under the chain lock SHARED — month, LastSeq, LastHash, purge anchor, live chained rows, tombstones — and append the heads that changed since that node's last export (all on the first tick; ExportUnchanged for every tick). A failed write is logged and retried next tick.
  • Pluggable sink (IAuditChainHeadSink): File — append-only JSONL, {Directory}/{yyyy}/audit-chain-heads-{yyyy-MM-dd}-{node}.jsonl, local or UNC, opened FileMode.Append only (grant the service account create + append data, nothing else); Syslog — RFC 5424 (<109>1 … chainhead - {json}, facility 13, severity Notice) over UDP or TCP (RFC 6587 octet counting).
  • Verify: scadabridge audit verify-heads --path <file|dir> reads the records (file-sink lines or any syslog-collector log — JSON taken from first { to last }), collapses duplicates, sends the new VerifyAuditChainHeads command (OperationalAudit, unscoped). Per record: Matches / Extended / Purged (link removed by a partition switch → check the archive), or failures Mismatch / Regressed / MissingHead / MissingLink / CountMismatch → exit 3.
  • Proof: AuditChainHeadStoreTests rewrites a chained row and re-links it, every later row and the head with the production hasher — verify-chain reports Intact, verify-heads reports Mismatch. Each other verdict has its own tampering case; sanctioned per-channel and partition purges stay non-failures. Mutation: removing the hash check or the count check fails the forgery / tamper tests. Sink tests use real UDP/TCP loopback listeners.

Item 3 — maintenance login (AuditLog:MaintenanceConnectionString, ${secret:}-capable, unset = today's single login)

  • The chain job, verify-chain, head export/verify-heads, partition switch-out + archive and per-channel purge run on AuditMaintenanceContext (keyed audit-maintenance repository/archive; the purge actor prefers them).
  • AuditMaintenanceLoginTests (MSSQL) runs the hardened setup: writer login = db_datareader + db_datawriter + scadabridge_audit_writer ingests and is refused the chain UPDATE and the per-channel DELETE; maintenance login = scadabridge_audit_chainer + scadabridge_audit_purger only chains, verifies, reads heads, purges per channel, enumerates partitions, archives and switches out. No new grants were needed. Mutation: putting the chain back on the runtime context fails with UPDATE permission was denied.
  • Two-login setup + grant table documented in Component-AuditLog.md (Security & Tamper-Evidence). Not moved (follow-up #113): monthly partition roll-forward and audit backfill-source-node. Export failures are log-only so far (follow-up #115).

Tests: AuditLog 462, ConfigurationDatabase 526, ManagementService 785, CLI 590, Host 671 — all green; build 0 warnings.

**Items 1 and 3 implemented — `c4d7ec9b` (main).** Kept open for items 2 (enablement) and 4 (deploy gate). **Item 1 — no signing; off-box head export** (`AuditLog:HashChain:HeadExport`, default off) - Both centrals, every `IntervalMinutes` (60), read every month's head in one snapshot under the chain lock SHARED — month, `LastSeq`, `LastHash`, purge anchor, live chained rows, tombstones — and append the heads that changed since that node's last export (all on the first tick; `ExportUnchanged` for every tick). A failed write is logged and retried next tick. - Pluggable sink (`IAuditChainHeadSink`): `File` — append-only JSONL, `{Directory}/{yyyy}/audit-chain-heads-{yyyy-MM-dd}-{node}.jsonl`, local or UNC, opened `FileMode.Append` only (grant the service account create + append data, nothing else); `Syslog` — RFC 5424 (`<109>1 … chainhead - {json}`, facility 13, severity Notice) over UDP or TCP (RFC 6587 octet counting). - Verify: `scadabridge audit verify-heads --path <file|dir>` reads the records (file-sink lines or any syslog-collector log — JSON taken from first `{` to last `}`), collapses duplicates, sends the new `VerifyAuditChainHeads` command (OperationalAudit, unscoped). Per record: `Matches` / `Extended` / `Purged` (link removed by a partition switch → check the archive), or failures `Mismatch` / `Regressed` / `MissingHead` / `MissingLink` / `CountMismatch` → exit 3. - Proof: `AuditChainHeadStoreTests` rewrites a chained row and re-links it, every later row and the head with the production hasher — `verify-chain` reports **Intact**, `verify-heads` reports **Mismatch**. Each other verdict has its own tampering case; sanctioned per-channel and partition purges stay non-failures. Mutation: removing the hash check or the count check fails the forgery / tamper tests. Sink tests use real UDP/TCP loopback listeners. **Item 3 — maintenance login** (`AuditLog:MaintenanceConnectionString`, `${secret:}`-capable, unset = today's single login) - The chain job, verify-chain, head export/verify-heads, partition switch-out + archive and per-channel purge run on `AuditMaintenanceContext` (keyed `audit-maintenance` repository/archive; the purge actor prefers them). - `AuditMaintenanceLoginTests` (MSSQL) runs the hardened setup: writer login = `db_datareader` + `db_datawriter` + `scadabridge_audit_writer` ingests and is **refused** the chain UPDATE and the per-channel DELETE; maintenance login = `scadabridge_audit_chainer` + `scadabridge_audit_purger` only chains, verifies, reads heads, purges per channel, enumerates partitions, archives and switches out. No new grants were needed. Mutation: putting the chain back on the runtime context fails with `UPDATE permission was denied`. - Two-login setup + grant table documented in Component-AuditLog.md (Security & Tamper-Evidence). Not moved (follow-up #113): monthly partition roll-forward and `audit backfill-source-node`. Export failures are log-only so far (follow-up #115). Tests: AuditLog 462, ConfigurationDatabase 526, ManagementService 785, CLI 590, Host 671 — all green; build 0 warnings.
dohertj2 commented 2026-11-09 23:33:24 -05:00
Author
Owner
Copy Link
Copy Source

Item 4 — deploy gate: migrations added since this issue (2026-11-10). The coordinated central-pair window now carries, in order:

  1. 20261109203753_PrefixSearchIndexes (#50)
  2. 20261109211317_AddAuditLogHashChain (#41)
  3. 20261110032839_AddConfigGraphRowVersions (#62)
  4. 20261110033732_FlipAuditLogClusteredKey (#111) — offline whole-table rebuild of dbo.AuditLog (size the window from DEV/QA first; log ~2× table size). Script docs/plans/sql/FlipAuditLogClusteredKey.sql, rollback FlipAuditLogClusteredKey.rollback.sql. Gate: binaries from 5e7d5389 on purge either key shape; older binaries cannot partition-switch a flipped table (their staging table hard-codes the old key) — apply with both centrals stopped, together with the new binaries.

Plus #102 item 1. #107 (c4d7ec9b) and #109 add no migration: the head export, maintenance login and archive retention/cleanup are config only, default off (AuditLog:HashChain:HeadExport, AuditLog:MaintenanceConnectionString, AuditLog:Archive:RetentionDays / DeleteSupersededStaleArchives). Enabling the maintenance login needs the DBA steps in Component-AuditLog.md (chainer + purger roles on a second login, writer role on the runtime login).

**Item 4 — deploy gate: migrations added since this issue (2026-11-10).** The coordinated central-pair window now carries, in order: 1. `20261109203753_PrefixSearchIndexes` (#50) 2. `20261109211317_AddAuditLogHashChain` (#41) 3. `20261110032839_AddConfigGraphRowVersions` (#62) 4. **`20261110033732_FlipAuditLogClusteredKey` (#111)** — offline whole-table rebuild of `dbo.AuditLog` (size the window from DEV/QA first; log ~2× table size). Script `docs/plans/sql/FlipAuditLogClusteredKey.sql`, rollback `FlipAuditLogClusteredKey.rollback.sql`. **Gate:** binaries from `5e7d5389` on purge either key shape; older binaries cannot partition-switch a flipped table (their staging table hard-codes the old key) — apply with both centrals stopped, together with the new binaries. Plus #102 item 1. #107 (`c4d7ec9b`) and #109 add **no** migration: the head export, maintenance login and archive retention/cleanup are config only, default off (`AuditLog:HashChain:HeadExport`, `AuditLog:MaintenanceConnectionString`, `AuditLog:Archive:RetentionDays` / `DeleteSupersededStaleArchives`). Enabling the maintenance login needs the DBA steps in Component-AuditLog.md (chainer + purger roles on a second login, writer role on the runtime login).
dohertj2 commented 2026-11-09 23:48:19 -05:00
Author
Owner
Copy Link
Copy Source

Deploy-gate list addition (item 4): migration AuditMaintenanceGrants (533414a1, #113) grants scadabridge_audit_purger ALTER ANY DATASPACE + UPDATE(SourceNode) on dbo.AuditLog — ships in the same coordinated central-pair window as AddAuditLogHashChain, AddConfigGraphRowVersions (#62) and FlipAuditLogClusteredKey (#111; requires new binaries, both centrals stopped).

Deploy-gate list addition (item 4): migration `AuditMaintenanceGrants` (`533414a1`, #113) grants `scadabridge_audit_purger` ALTER ANY DATASPACE + UPDATE(SourceNode) on dbo.AuditLog — ships in the same coordinated central-pair window as `AddAuditLogHashChain`, `AddConfigGraphRowVersions` (#62) and `FlipAuditLogClusteredKey` (#111; requires new binaries, both centrals stopped).
dohertj2 added the blocked label 2026-11-10 08:41:39 -05:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
blocked
Blocked by dependency or external hold
 cross-repo
Needs coordinated change in another family repo (wire contract etc.)
 migrated
Migrated from a markdown backlog (2026-11 standardization)
 priority:p0
Must-have / blocking
 priority:p1
High priority
 priority:p2
Normal priority
 priority:p3
Low priority / nice to have
 type:bug
Defect in shipped behaviour
 type:debt
Technical debt / hygiene
 type:deploy-gate
Rollout/deploy/migration step not yet done on an environment
 type:docs
Documentation work
 type:feature
Feature implementation
 type:followup
Follow-up deferred from a plan, PR or gate
 type:infra
Infrastructure and scaffolding
 type:review-finding
Unfixed finding from an architecture/code review
 type:test
Test work
No Label blocked priority:p2 type:deploy-gate
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
dohertj2 (dohertj2)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: dohertj2/ScadaBridge#107
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?