Puzzleshot #022 - Querying remote redacted data

raw data→sed→socat→duckdb→result

A colleague on another team has an access log you need for analysis — but company policy says client IPs can never leave their machine unredacted, even for an internal request.

Rather than asking them to manually clean the file and send it over, you want the data redacted at the source, streamed directly to you, and queried immediately — with the unredacted version never leaving their machine at all.

Sample log format (comma separated, no header):

2026-04-09,10:15:32,client=192.168.1.45,status=200,path=/api/orders
2026-04-09,10:15:33,client=10.0.0.12,status=200,path=/api/dealers
2026-04-09,10:15:34,client=203.0.113.7,status=500,path=/api/orders
2026-04-09,10:15:35,client=192.168.1.46,status=200,path=/api/orders
2026-04-09,10:15:36,client=192.168.1.45,status=404,path=/api/missing

The challenge:

Using socat, sed, and DuckDB together, build a pipeline that:

On the sending side, redacts every client IP with sed before the data touches the network at all
Streams the already-redacted data over a network connection with socat
On the receiving side, feeds the incoming stream directly into DuckDB to answer: how many requests fall into each status code?

At no point should the unredacted log leave the sender’s machine.

Things to consider:

Should sed run inside socat’s connection setup, or outside it in a regular shell pipeline? Does it matter?
How does DuckDB read a CSV stream that never touches disk?
Where might quoting or escaping cause problems if you’re not careful about how the pieces are wired together?

Reveal solution

This challenge was redacting data at the source before it ever left the sending machine, streaming it over the network, and querying it directly on arrival.

The sender’s side:

sed -E "s/client=([0-9]{1,3}\.){3}[0-9]{1,3}/client=REDACTED_IP/g" access.log | socat - TCP-LISTEN:9999,reuseaddr

sed runs first, redacting every client IP as the file is read. Only the redacted output is piped into socat, which listens on port 9999 and streams whatever it receives on stdin to any client that connects. The unredacted log never reaches the network layer at all — it’s filtered before socat ever sees it.

The receiving side:

socat TCP:sender-host:9999 - | duckdb -c "
  SELECT status, count(*) as count
  FROM read_csv('/dev/stdin', header=false, columns={'date':'VARCHAR','time':'VARCHAR','client':'VARCHAR','status':'VARCHAR','path':'VARCHAR'})
  GROUP BY status
  ORDER BY count DESC
"

socat connects to the sender and streams the incoming (already redacted) data to stdout, which feeds directly into DuckDB via /dev/stdin. No file is ever written on either end.

Why sed belongs outside socat’s address syntax:

It’s tempting to try embedding the redaction directly into socat’s own connection setup — something like socat TCP-LISTEN:9999,fork EXEC:“sed … file” or using SYSTEM: to invoke a shell inline. In testing, this runs into a real problem: socat performs its own quote-stripping on address strings before handing them to the shell, since socat uses characters like double quotes for its own address parsing. That conflicts directly with quotes meant for the inner sed command, and the regex ends up mangled or dropped entirely by the time it reaches sed.

The fix is to keep sed completely outside socat’s address syntax — run it as a normal pipeline stage, and let socat’s only job be moving bytes over the network. Simpler, and it avoids a genuinely confusing failure mode.

On the “never touches disk” guarantee:

Both sides use /dev/stdin and stdout throughout — no intermediate files are written. The one thing worth being honest about: DuckDB and socat both may make small temporary buffers in memory as they process the stream, but nothing is written to persistent storage at any stage. The guarantee holds for disk, not for memory — which is the right distinction for most redaction/compliance requirements, but worth knowing precisely what you’re claiming.

The broader point:

When you need data redacted before it crosses a boundary — network, disk, team, whatever the boundary is — do the redaction as early in the pipeline as possible, and keep it as a distinct, simple stage rather than trying to fold it into a more complex tool’s own configuration syntax. Simpler pipelines are also easier to reason about when something breaks.