scadaproj/ZB.MOM.WW.Secrets/docs/operations/clustered-secrets.md

# Operating clustered secrets

How to run `ZB.MOM.WW.Secrets` across more than one node. Covers the shared SQL-Server store, the
SQL-Server hub, and Akka peer-to-peer replication.

Companion: [`kek-rotation.md`](kek-rotation.md).

---

## 0. The one rule that breaks everything if you get it wrong

**Every node must resolve the same master KEK.**

Rows are ciphertext everywhere — in the database, in a backup, on the wire. Only the KEK decrypts
them, and it never leaves the node. So if node B has a different master key than node A:

- replication still "works" — rows arrive and are stored;
- every resolve on B of a secret written on A **fails closed** with a `kek_id` mismatch.

The symptom looks like data corruption and is not. Before enabling any topology below, confirm the
same key material is present on every node:

```bash
# On each node — compare the digests, never print the key itself.
sha256sum /run/secrets/zb-kek
# or, for the environment source:
printf '%s' "$ZB_SECRETS_MASTER_KEY" | sha256sum
```

---

## 1. Shared SQL-Server store

One database, one copy of each row. Prefer this unless a node must keep resolving secrets while
partitioned from the database.

### Provision

The schema is created automatically by the first node that starts (idempotent, and safe if several
start at once). You only need a database and a login:

```sql
CREATE DATABASE ZbSecrets;
GO
USE ZbSecrets;
CREATE LOGIN zbsecrets WITH PASSWORD = '<strong password>';
CREATE USER  zbsecrets FOR LOGIN zbsecrets;
GO
-- The app creates and owns the zbsecrets schema; it needs permission to do so.
GRANT CREATE SCHEMA, CREATE TABLE TO zbsecrets;
ALTER ROLE db_owner ADD MEMBER zbsecrets;   -- or grant on the schema once it exists
GO
```

To pre-create the schema instead (so the app can run with narrower rights), run the DDL from
`SqlServerSecretsSchema.CreateSchemaDdl("zbsecrets")` and then grant only
`SELECT, INSERT, UPDATE` on `[zbsecrets].[secret]` and `[zbsecrets].[schema_version]`.

> `DELETE` is deliberately **not** required. Deletes are tombstones — an `UPDATE` — so a compromised
> app credential cannot destroy secret history.

### Configure

```jsonc
"Secrets": {
  "MasterKey": { "Source": "File", "FilePath": "/run/secrets/zb-kek" },
  "SqlServer": {
    "ConnectionString": "Server=sql01;Database=ZbSecrets;User Id=zbsecrets;Password=${secret:sql/zbsecrets-password};TrustServerCertificate=False",
    "SchemaName": "zbsecrets",
    "CommandTimeout": "00:00:30"
  }
}
```

The connection string is itself a credential — deliver it by `${secret:}` reference, an orchestrator
secret, or an environment variable. Never commit it.

### Verify

```bash
secret set smoke/test hello --description "delete me"   # on node A
secret get smoke/test                                    # on node B — expect: hello
secret rm  smoke/test
```

### Rotation

Run `rewrap-all` **once**, against the shared store, with resolve traffic quiesced.

---

## 2. SQL-Server hub replication

Each node keeps its local SQLite store and syncs against a shared hub.

Same provisioning as topology 1. Configure with `AddZbSecretsSqlServerReplication` and:

```jsonc
"Secrets": {
  "SqlitePath": "/var/lib/zb/secrets.db",
  "MasterKey": { "Source": "File", "FilePath": "/run/secrets/zb-kek" },
  "SqlServer": {
    "ConnectionString": "...",
    "SyncInterval": "00:00:30",
    "SyncOnStartup": true
  }
}
```

`SyncInterval` is the convergence bound, not the propagation latency — a live write reaches the hub
in milliseconds; the interval governs how fast a *dropped* write or a rejoining node is repaired.

### Rotation

Run `rewrap-all` against the hub **and** against every node's local SQLite store. A re-wrap does not
bump the revision (by design — otherwise every node's independent re-wrap would churn replication),
so it does **not** propagate.

### Verifying convergence

```
Secret hub sync converged: pulled N row(s) from the hub, pushed M row(s) to it.
```

Logged at Information only when something actually moved. A healthy steady-state cluster logs
nothing. Continuous non-zero pushes on every tick means two nodes are fighting over the same secret
— look for a script writing the same name on a loop.

---

## 3. Akka cluster peer-to-peer

No shared database. Requires the application to register its `ActorSystem` in DI.

```jsonc
"Secrets": {
  "SqlitePath": "/var/lib/zb/secrets.db",
  "MasterKey": { "Source": "File", "FilePath": "/run/secrets/zb-kek" },
  "Replication": {
    "AnnounceInterval": "00:00:30",
    "ActorName": "zb-secret-replication"
  }
}
```

Merge the serializer config when creating the actor system:

```csharp
Config config = AkkaSecretsReplication.SerializationConfig.WithFallback(myAppConfig);
ActorSystem.Create("my-system", config);
```

Optional but recommended: without it the protocol still round-trips under Akka's default JSON
serializer, but the serializer carrying secret ciphertext becomes an inherited default rather than
a deliberate choice.

### Securing the transport

Rows on the wire are ciphertext and the KEK never travels, so a passive eavesdropper learns
nothing but secret *names*, revisions, and timestamps. That is still metadata worth protecting, and
an unauthenticated cluster port is a much larger problem than secret replication — configure
Akka.Remote TLS and cluster authentication as you would for any other cluster traffic.

### Rotation

Run `rewrap-all` on **each node's** local store. Re-wraps do not replicate (see above).

### Announce traffic

One manifest per node per interval: names, revisions, timestamps, tombstone flags — no ciphertext.
Cost grows as O(nodes × secrets), so a large cluster with a large secret set should lengthen
`AnnounceInterval` rather than shorten it.

---

## Choosing between them

| | Shared store | SQL hub | Akka peer-to-peer |
|---|---|---|---|
| Resolves while cut off from the DB | ✗ (cache TTL only) | ✓ | ✓ (no DB at all) |
| Distributed failure modes | none | LWW, sweep lag | LWW, sweep lag |
| Rotation runs | once | hub + each node | each node |
| Needs a shared database | ✓ | ✓ | ✗ |
| Needs an Akka cluster | ✗ | ✗ | ✓ |

Start at the left and move right only when a stated requirement forces it.

---

## Troubleshooting

**`SecretDecryptionException` / `kek_id` mismatch after enabling replication.**
The KEKs differ between nodes. Compare digests as in §0. Do not "fix" it by re-writing the secret on
the failing node — that hides the mismatch until the next secret.

**A secret written on node A never appears on node B.**
Check, in order: (1) are both nodes actually in the cluster / pointed at the same hub database and
schema; (2) has one `AnnounceInterval` / `SyncInterval` elapsed; (3) is there a warning-level log
about a failed publish or a failed sweep. A dropped publish is expected and self-repairing — a
sweep that fails every interval is not.

**A deleted secret came back.**
Deletes are tombstones and propagate as newer rows, so this should not happen through replication.
It does happen if a node's local store was restored from a backup taken before the delete: the
restore reintroduces the row with an old timestamp, loses to LWW, and the tombstone re-wins. If
instead the row is *live* again, someone re-created it — check the audit log for the write.

**The migration refuses to run.** `SecretStoreMigrationException` naming a version newer than
supported means another node is running a newer build against the same store. Upgrade this node;
do not downgrade the store.