A throwaway Postgres per attempt: running untrusted student SQL

The requirement

Students submit arbitrary SQL. It has to be safe. And it has to allow real DDL — because "design this schema and populate it" is most of what teaching databases consists of, and an exercise limited to SELECT against a fixed fixture teaches a fraction of the subject.

Two obvious designs, both rejected:

An in-process SQL engine in the backend. Fast, no containers. Also means evaluating untrusted input inside the application process — a shape that has burned this codebase before, in a different feature, with a real RCE as the outcome. Not again.

A shared teaching database with restricted grants. Cheap. Also means shared mutable state between students, and shared mutable state plus thirty people learning DELETE ends exactly one way.

What we built

One throwaway postgres:16-alpine container per attempt, executed through the same sandbox mechanism (epicbox) that already runs student Python, Java and Go.

Per-attempt sequence:

1. Start Postgres
2. Run the exercise author's setup.sql as superuser
3. CREATE a restricted role
4. Run the student's SQL as that role
5. Run the author's verification queries as superuser
6. Emit JSON on stdout, pg_ctl stop

Everything the student did dies with the container.

Making a database-per-attempt fast enough

The naive version — start a Postgres container, initdb, then use it — costs about ten seconds. That's the difference between a feature students use and one they avoid.

initdb is baked at image build time. The image ships with an initialized data directory; the container starts an already-initialized cluster. This is the single change that made the approach viable.

Durability is turned off. fsync, synchronous_commit, full_page_writes — all off. The entire state is discarded seconds later, so every guarantee Postgres offers about surviving a crash is pure cost here. This is one of the rare cases where turning off fsync is not reckless but obviously correct.

Unix socket only, no TCP. The sandbox runs with networking disabled, so there's nothing to listen for.

Resource floors have to be raised. Postgres forks helper processes, so limits tuned for a single-process interpreter fail immediately. We floor it at 384 MB and 64 PIDs.

The part I'd defend hardest: don't filter the SQL

We do not inspect the student's SQL. At all. No allowlist of statements, no regex for DROP, no parsing.

Instead, the student connects as a role created per attempt:

NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS
CONNECTION LIMIT 5
statement_timeout = 5s
search_path = public

When a student tries CREATE ROLE or DROP DATABASE, PostgreSQL refuses them. Not our filter — the engine's own privilege system, which is the only authoritative answer to "is this connection allowed to do that".

Text-level filtering of a programming language is a losing game; there is always another spelling. That's true of JavaScript (this codebase learned it the hard way, with a bypassable regex allowlist over vm.runInContext) and it's true of SQL. The database already contains a complete, battle-tested authorization system. Use that one.

We verified it the boring way: wrote tests that attempt CREATE ROLE and DROP DATABASE and asserted the engine rejects them.

Two timeouts, because one doesn't cover both cases

statement_timeout = 5s on the student role kills a runaway student query. It does not protect you from a runaway setup.sql, because setup runs as superuser — and superuser can raise or ignore that setting.

So there's a second, outer limit: the sandbox's hard wall-clock kill at ~20 seconds, which doesn't care who you are or what you set. Two failure modes, two mechanisms, one of them outside anything the workload can influence.

If that sounds familiar, it's the same shape as enforcing sandbox session TTLs in two places — the inner mechanism does the graceful thing, the outer one exists because the inner one can be subverted or die.

Test it on real containers or don't claim it works

I want to be specific about this because it's the part that generalizes best.

Seven scenarios were run against live containers, not reasoned about: happy path, student syntax error, error in the author's setup, CREATE ROLE rejected by the engine, DROP DATABASE rejected by the engine, statement_timeout killing a hung student query, and the outer limit killing a hung setup.

That process found three bugs code review had not — the most instructive being that the sandbox resets /sandbox ownership to root on every file upload, which meant runtime-writable state had to move under a path baked into the image instead. There is no amount of reading the code that surfaces that.

For anything where the failure mode is "untrusted code does something you didn't anticipate", the test that counts is the one that actually runs it.

Honest limits

• It's a container. This inherits whatever your container isolation is worth — for us, the same layer our other language executors run in, with no network.
• setup.sql runs as superuser. That's a deliberate trust boundary: exercise authors are trusted, students aren't. If your authors aren't trusted, this design doesn't transfer.
• A database engine per attempt costs real compute, which is why this sits on our paid tier. A shared instance with per-student schemas is dramatically cheaper; it's just a weaker isolation story.