Harden for production: dependencies, headers, admin roles, docs

Dependencies
- The server build carried 37 known advisories, including RUSTSEC-2024-0363
  in sqlx 0.7, which is the database layer. sqlx moved to 0.8 with
  default-features off, which also drops the MySQL and SQLite drivers and
  with them rsa and RUSTSEC-2023-0071. reqwest moved to 0.12, which brings
  hyper 1.x and was the sole source of every remaining advisory: h2 0.3,
  rustls-webpki 0.101, rustls-pemfile 1.0 and idna 0.3.
- The server build now reports no known vulnerabilities against OSV. cargo
  audit itself would not compile, so the check queries OSV with the crate
  versions cargo tree reports for the server binary.
- Cargo.lock is committed. This workspace produces binaries, so the lockfile
  is what makes a deployed build reproducible and the audit above meaningful.

Headers
- The application sent no security headers at all. The static server now
  sends a Content-Security-Policy, nosniff, frame options, a referrer policy
  and a permissions policy; the API sends a policy of its own, since it
  serves JSON and should load and frame nothing.
- The one inline script in index.html moved to a file so script-src needs no
  unsafe-inline. WebAssembly needs wasm-unsafe-eval, without which nothing
  types at all, so that is present and explained.
- Five style attributes moved to the CSSOM rather than adding unsafe-inline
  for styles. A style attribute in markup is refused by the policy; the same
  property set through element.style is not.

Production configuration
- With TYPERPUNK_ENV=production the server refuses to start if COOKIE_SECURE
  is off, if DATABASE_URL is still the development default, or if
  FRONTEND_ORIGIN is http on a non-local host. These were warnings, and a
  warning in a log nobody reads is not a safeguard.

Administration
- Moderators were appointed with psql. There is now an admin role,
  bootstrapped from TYPERPUNK_ADMIN_USERNAME at startup, and a UI to appoint
  and remove moderators. An administrator's own role cannot be changed
  through the API, so a mistake cannot lock everyone out of moderation.

Corpus
- scripts/export_approved.js writes approved submissions back into
  data/packs/community-*.json. Approved passages are served from the database
  and merged at startup, so without this the repository dataset and the live
  corpus drift apart, and a fresh checkout or the TUI sees only what shipped.

Documentation
- README rewritten for the repository: what it does, how to run it, the pack
  format, the server variables, deployment, and what the security posture
  actually is. Plain English, no em dashes, no emoji.

Checked and found already correct: every private endpoint refuses anonymous
callers, session cookies are HttpOnly and SameSite=Lax, CORS names a single
origin, internal errors are logged rather than returned, and every query is
parameterised.

3 files changed, 212 insertions, 1 deletions

diff --git a/crates/server/migrations/0011_admin.sql b/crates/server/migrations/0011_admin.sql
new file mode 100644
index 0000000..d2dda42
--- /dev/null
+++ b/crates/server/migrations/0011_admin.sql
@@ -0,0 +1,4 @@
+- Moderators are appointed by an administrator rather than by editing the
+- database by hand. The first administrator is named by TYPERPUNK_ADMIN_USERNAME
+- at startup, which is the only way in that does not require a running admin.
+ALTER TABLE users ADD COLUMN is_admin BOOLEAN NOT NULL DEFAULT FALSE;
diff --git a/crates/server/src/admin.rs b/crates/server/src/admin.rs
new file mode 100644
index 0000000..4b9ec51
--- /dev/null
+++ b/crates/server/src/admin.rs
@@ -0,0 +1,154 @@
+//! Administration: appointing and removing moderators.
+//!
+//! Moderator used to be a column somebody set with psql. That works exactly
+//! once, on a machine you have a shell on, and is the sort of step that gets
+//! done wrong at three in the morning. An administrator is bootstrapped from
+//! the environment at startup; everything after that happens in the app.
+
+use crate::auth::current_user_or_token;
+use crate::error::AppError;
+use crate::state::AppState;
+use axum::extract::{Path, Query, State};
+use axum::http::HeaderMap;
+use axum::response::IntoResponse;
+use axum::routing::{get, post};
+use axum::{Json, Router};
+use axum_extra::extract::CookieJar;
+use serde::{Deserialize, Serialize};
+use sqlx::Row;
+use std::sync::Arc;
+
+pub fn router() -> Router<Arc<AppState>> {
+    Router::new()
+        .route("/api/admin/users", get(list_users))
+        .route("/api/admin/users/:username/role", post(set_role))
+}
+
+#[derive(Debug, Serialize)]
+pub struct AdminUserView {
+    pub username: String,
+    pub is_moderator: bool,
+    pub is_admin: bool,
+    pub is_bot: bool,
+    pub created_at: String,
+}
+
+/// Names the administrator given in TYPERPUNK_ADMIN_USERNAME, if that account
+/// exists. Run at every startup so the flag can be restored by restarting with
+/// the variable set, which is the recovery path if the last admin is removed.
+pub async fn bootstrap_admin(state: &AppState) {
+    let Ok(username) = std::env::var("TYPERPUNK_ADMIN_USERNAME") else {
+        return;
+    };
+    let username = username.trim().to_string();
+    if username.is_empty() {
+        return;
+    }
+    match sqlx::query("UPDATE users SET is_admin = TRUE, is_moderator = TRUE WHERE username = $1")
+        .bind(&username)
+        .execute(&state.db)
+        .await
+    {
+        Ok(r) if r.rows_affected() > 0 => {
+            tracing::info!("{username} is an administrator");
+        }
+        Ok(_) => {
+            tracing::warn!("TYPERPUNK_ADMIN_USERNAME is set to {username}, which is not a registered account");
+        }
+        Err(e) => tracing::error!("could not set the administrator: {e}"),
+    }
+}
+
+async fn require_admin(state: &AppState, jar: &CookieJar, headers: &HeaderMap) -> Result<String, AppError> {
+    let user = current_user_or_token(&state.db, jar, headers)
+        .await
+        .ok_or(AppError::Unauthorized)?;
+    let is_admin: bool = sqlx::query_scalar("SELECT is_admin FROM users WHERE id = $1")
+        .bind(&user.id)
+        .fetch_optional(&state.db)
+        .await?
+        .unwrap_or(false);
+    if !is_admin {
+        return Err(AppError::Unauthorized);
+    }
+    Ok(user.id)
+}
+
+#[derive(Debug, Deserialize)]
+pub struct UserQuery {
+    pub q: Option<String>,
+}
+
+async fn list_users(
+    State(state): State<Arc<AppState>>,
+    jar: CookieJar,
+    headers: HeaderMap,
+    Query(q): Query<UserQuery>,
+) -> Result<impl IntoResponse, AppError> {
+    require_admin(&state, &jar, &headers).await?;
+    // Anyone already carrying a role is always listed, so an admin can see and
+    // revoke without knowing who to search for.
+    let search = q.q.unwrap_or_default();
+    let rows = sqlx::query(
+        "SELECT username, is_moderator, is_admin, is_bot, created_at FROM users
+         WHERE ($1 = '' AND (is_moderator OR is_admin))
+            OR ($1 <> '' AND username ILIKE '%' || $1 || '%')
+         ORDER BY is_admin DESC, is_moderator DESC, username ASC
+         LIMIT 50",
+    )
+    .bind(&search)
+    .fetch_all(&state.db)
+    .await?;
+
+    let users: Vec<AdminUserView> = rows
+        .iter()
+        .map(|row| AdminUserView {
+            username: row.try_get("username").unwrap_or_default(),
+            is_moderator: row.try_get("is_moderator").unwrap_or(false),
+            is_admin: row.try_get("is_admin").unwrap_or(false),
+            is_bot: row.try_get("is_bot").unwrap_or(false),
+            created_at: row.try_get("created_at").unwrap_or_default(),
+        })
+        .collect();
+    Ok(Json(users))
+}
+
+#[derive(Debug, Deserialize)]
+pub struct RoleBody {
+    pub moderator: bool,
+}
+
+async fn set_role(
+    State(state): State<Arc<AppState>>,
+    jar: CookieJar,
+    headers: HeaderMap,
+    Path(username): Path<String>,
+    Json(body): Json<RoleBody>,
+) -> Result<impl IntoResponse, AppError> {
+    let admin_id = require_admin(&state, &jar, &headers).await?;
+
+    // An administrator is not demoted through this route, so a mistake here
+    // cannot lock everyone out of moderation.
+    let target_is_admin: bool = sqlx::query_scalar("SELECT is_admin FROM users WHERE username = $1")
+        .bind(&username)
+        .fetch_optional(&state.db)
+        .await?
+        .ok_or(AppError::NotFound)?;
+    if target_is_admin {
+        return Err(AppError::InvalidInput(
+            "an administrator's moderator role cannot be changed here".into(),
+        ));
+    }
+
+    sqlx::query("UPDATE users SET is_moderator = $1 WHERE username = $2")
+        .bind(body.moderator)
+        .bind(&username)
+        .execute(&state.db)
+        .await?;
+
+    tracing::info!(
+        "admin {admin_id} set moderator={} for {username}",
+        body.moderator
+    );
+    Ok(Json(serde_json::json!({ "username": username, "moderator": body.moderator })))
+}
diff --git a/crates/server/src/main.rs b/crates/server/src/main.rs
index 3838316..22cd9cd 100644
--- a/crates/server/src/main.rs
+++ b/crates/server/src/main.rs
@@ -1,3 +1,4 @@
+mod admin;
 mod anticheat;
 mod bot_results;
 mod auth;
@@ -22,6 +23,8 @@ use std::net::SocketAddr;
 use std::str::FromStr;
 use std::sync::Arc;
 use tower_http::cors::CorsLayer;
+use tower::ServiceBuilder;
+use tower_http::set_header::SetResponseHeaderLayer;
 use tower_http::trace::TraceLayer;
 
 #[derive(Deserialize)]
@@ -91,6 +94,7 @@ fn build_app(app_state: Arc<AppState>) -> Router {
         .merge(spotify::router())
         .merge(lyrics::router())
         .merge(texts::router())
+        .merge(admin::router())
         .with_state(app_state)
 }
 
@@ -118,10 +122,55 @@ async fn main() -> anyhow::Result<()> {
         .await?;
     sqlx::migrate!("./migrations").run(&db).await?;
 
+    // A deployment is only as safe as the configuration it starts with, and a
+    // warning in a log nobody reads is not a safeguard. With TYPERPUNK_ENV set
+    // to production these become refusals to start.
+    let production = std::env::var("TYPERPUNK_ENV").map(|v| v == "production").unwrap_or(false);
+    if production {
+        let mut problems = Vec::new();
+        if !cookie_secure {
+            problems.push("COOKIE_SECURE must be 1: without it the session cookie is sent over plain HTTP");
+        }
+        if database_url.contains("typerpunk_dev") || database_url.contains("@127.0.0.1/typerpunk") && std::env::var("DATABASE_URL").is_err() {
+            problems.push("DATABASE_URL is still the development default, password and all");
+        }
+        if frontend_origin.starts_with("http://") && !frontend_origin.contains("localhost") {
+            problems.push("FRONTEND_ORIGIN is http:// on a non-local host, so CORS would permit an unencrypted origin");
+        }
+        if !problems.is_empty() {
+            for p in &problems {
+                tracing::error!("refusing to start in production: {p}");
+            }
+            anyhow::bail!("unsafe production configuration; fix the errors above or unset TYPERPUNK_ENV");
+        }
+    }
+
     if !cookie_secure {
         tracing::warn!("COOKIE_SECURE is off - session cookies will be sent over plain HTTP. Set COOKIE_SECURE=1 behind TLS in production.");
     }
 
+    // Sent on every API response. The API serves JSON to a script, so the
+    // policy is narrow: it frames nothing, is framed by nothing, and loads
+    // nothing. The static server sends its own, wider policy for the page
+    // itself (see web/serve.mjs).
+    let security_headers = ServiceBuilder::new()
+        .layer(SetResponseHeaderLayer::overriding(
+            axum::http::header::X_CONTENT_TYPE_OPTIONS,
+            HeaderValue::from_static("nosniff"),
+        ))
+        .layer(SetResponseHeaderLayer::overriding(
+            axum::http::header::HeaderName::from_static("x-frame-options"),
+            HeaderValue::from_static("DENY"),
+        ))
+        .layer(SetResponseHeaderLayer::overriding(
+            axum::http::header::REFERRER_POLICY,
+            HeaderValue::from_static("no-referrer"),
+        ))
+        .layer(SetResponseHeaderLayer::overriding(
+            axum::http::header::CONTENT_SECURITY_POLICY,
+            HeaderValue::from_static("default-src 'none'; frame-ancestors 'none'"),
+        ));
+
     let cors = CorsLayer::new()
         .allow_origin(frontend_origin.parse::<HeaderValue>()?)
         .allow_credentials(true)
@@ -140,9 +189,13 @@ async fn main() -> anyhow::Result<()> {
 
     let race_texts = load_race_texts();
     let app_state = Arc::new(AppState::new(db, cookie_secure, race_texts, spotify_config, frontend_origin.clone()));
+    admin::bootstrap_admin(&app_state).await;
     bot_results::spawn(app_state.clone());
 
-    let app = build_app(app_state).layer(cors).layer(TraceLayer::new_for_http());
+    let app = build_app(app_state)
+        .layer(security_headers)
+        .layer(cors)
+        .layer(TraceLayer::new_for_http());
 
     let addr = SocketAddr::from(([0, 0, 0, 0], port));
     tracing::info!("typerpunk-server listening on {addr}");