Solid crypto on signed SQL uploads, but a dry run could rewrite the ledger

Flow

Each check, what it stops, and how to test it

1. 1Execute permission⌄

   Keeps most admins out. Test with a view-only user.

2. 2Second password⌄

   Stops a stolen session. Test that a wrong one fails before the file is parsed.

3. 3Signature⌄

   Stops edited or forged envelopes. Test by changing one manifest field.

4. 4Encryption and fingerprints⌄

   Stops tampering. Test by flipping one byte.

5. 5Applied status⌄

   Stops replays. Test apply, dry run, apply.

6. 6Lock⌄

   Stops two runs at once. Test that the loser does not mark the version failed.

Uploading a file to one of my admin apps now runs the SQL inside it on the live database, through an endpoint I added. I put a lot of crypto around it, and the crypto nearly hid the weak part.

I'm going off the diff here. The commit had no tests and I have no logs from it.

Why an upload door is risky

If an endpoint runs SQL, anyone who gets past it can change your data with the app's full rights. The safer habit is a container that applies migrations when you deploy and takes no requests. The same commit made that path better too. It doesn't say why I wanted the upload door as well.

The checks on the way in

The upload is a sealed envelope. It holds the SQL, encrypted, plus two fingerprints of it and a signature. The server checks it in a fixed order:

1. The user needs a special execute permission, and the file can't be too big.
2. They type a second password that is not their login. A stolen session isn't enough.
3. The signature must match a public key. The server can check signatures but can't make them.
4. The SQL is decrypted, and both fingerprints must match the signed ones.

The encryption keeps the SQL private. Only the signature says who wrote it, and nothing says the SQL is a good idea.

Where each version gets recorded

Every envelope has a version. A table records each one with a status, and another records each run. If a version is marked applied, a real run is refused. A lock stops two runs at once. That table is where the trouble is.

A dry run can undo "applied"

A dry run checks an envelope without running it, so it skips the "already applied" check. That's fair. The final write is blunt.

The write marks the version verified even if it had already been applied.

So dry-run something that already ran, and the next real upload runs it again. The status write should never move backward. A single test of apply, dry run, apply would catch it.

The version isn't always signed

The table trusts a version field that the signature doesn't cover. It's only tied to a signed version if the manifest has one. Without it, someone with the right access could rename an old envelope and run it twice.

Smaller problems

If a second upload loses the race for the lock, it marks the version failed, even while the first run is still applying it. Failed versions can be retried. My code doesn't wrap the script in a transaction, so a script that stopped halfway could run part of itself twice.

And I'm not sure the lock gets released. That kind of lock only lets go on the same database connection that took it, and my code goes through a pool of connections. If the release lands on a different one, the lock hangs around and blocks the next upload.