Fix bugs from audit: Postgres placeholders, cascades, auth, XSS

#46 in Riparion/riparion-cms — merged 2026-06-02

Summary

A multi-agent bug audit (DB layer, auth/security, server logic, frontend/SSR, KISS/DRY) surfaced a set of issues; findings were de-duplicated and each verified against the code to rule out false positives. This PR fixes the real ones.

Fixes

Critical

• create_post Postgres breakage — the Owner-membership upsert used ? placeholders while the whole codebase standardized on $N. Postgres rejects ?, so post creation errored on that backend (after the post row was already committed → ownerless post). The sqlite test suite couldn't catch it. (src/server/admin/posts.rs)

High / Medium

• delete_post_db cascade — added reactions to the hand-rolled delete so databases created before the REFERENCES … ON DELETE CASCADE clauses don't orphan reaction rows. (src/db/posts.rs)
• Duplicate live comment — moderate_comment now only fans a comment to readers on the actual pending → approved transition, not on a re-approve (double-click / editing an already-public comment), which previously appended a duplicate over SSE. (src/server/admin/comments.rs)
• Unauthenticated live-data push — push_data_point was anonymous; any visitor could spoof chart points into any post for all viewers. Now gated behind can_edit_post. (src/server/live_data.rs)
• Splash embed XSS — embed props bypass the markdown sanitizer, so the author-supplied CTA href could be a javascript: URL. Routed through a new escape::safe_url that allows only relative refs + http(s)/mailto/tel. (src/embeds/splash.rs, src/escape.rs)

Low

• escape_xml drops XML-1.0-illegal control chars (a stray one in a title/excerpt makes strict feed/sitemap parsers reject the whole document). (src/escape.rs)
• looks_like_email rejects control chars (CR/LF), closing the mail-header-injection surface. (src/server/mod.rs)
• top_referrers groups on the (direct) bucket expression so NULL and '' don't split into two rows. (src/db/analytics.rs)

DRY / KISS

• Shared one total_pages() ceiling-div between PostFeed and QueryLoopResult. (src/model/content.rs)
• Draft-visibility gates use STATUS_PUBLISHED instead of bare "published" literals. (src/server/posts.rs, src/server/pages.rs)

Testing

• cargo fmt --all --check ✅
• cargo clippy server+sqlite ✅, server+postgres ✅, wasm web (CI lint config) ✅
• cargo test server+sqlite (38 passed) ✅
• cargo test server+postgres against a live PG 16 container (14 passed, migrations apply) ✅

Items reviewed and intentionally left unchanged (with rationale) are noted in the local TODO_BUGS.md: the localhost canonical-URL fallback (by design; production sets SITE_URL), pages having no per-page ownership (documented intended model), and LiveHub mutex poisoning (trivial critical sections, broad change for low value).

🤖 Generated with Claude Code