flamenet-relay/docs/DATABASE.md

The relay's database role

The relay connects to Postgres as relayapp: a role with no superuser attribute, which owns its own tables and nothing else.

Why this is not about the operator

Whoever runs the relay owns the machine; that is assumed everywhere in this design, and the key log's integrity does not depend on it. Each leaf is a hash of fixed fields, the head is a signed Merkle root, clients pin that head on first sight and demand inclusion proofs. Two validly-signed heads of the same size with different roots are evidence the operator cannot retract. Tampering is designed to be detected, not prevented at the server.

The exposure this closes is blast radius from a flaw in the relay itself. Without it, any SQL injection or path that reaches a query with attacker-influenced input becomes full cluster control, and superuser can run COPY … TO PROGRAM, which turns a SQL flaw into remote code execution on the database host. That single fact is why this was worth doing regardless of how much confidence anyone has in the query layer.

Why RLS is not the answer

A superuser bypasses RLS unconditionally, and the old role had rolbypassrls set as well. More fundamentally the relay is the only client of this database, there is no second tenant for a policy to separate it from.

What was actually done

The bootstrap superuser cannot be demoted. ALTER ROLE flamenet NOSUPERUSER is refused by Postgres itself:

ERROR:  permission denied to alter role
DETAIL:  The bootstrap superuser must have the SUPERUSER attribute.

So this is a new role and a switchover, not a demotion. flamenet still exists and is still the superuser; the relay simply stopped being it.

CREATE ROLE relayapp LOGIN PASSWORD '…'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS;
GRANT CONNECT ON DATABASE flamenet TO relayapp;
GRANT USAGE, CREATE ON SCHEMA public TO relayapp;
-- every table, sequence and view reassigned to relayapp

Ownership, not just grants, and that is deliberate. Fluent runs migrations at boot. A role holding only SELECT/INSERT/UPDATE/DELETE cannot add a table or an index, so the next deploy carrying a migration would fail at start-up, and a start-up failure from a missing grant reads as an application bug, not a permissions one. Owning its own tables lets the relay do DDL on those and nothing else.

CONNECT on the other database had to be revoked from PUBLIC separately. Postgres grants it to PUBLIC by default, so the new role could still open postgres and read pg_catalog, role names, database names, until:

REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

Check the result with has_database_privilege, not by reading datacl: a revoke from PUBLIC leaves any direct role grant in place, and the two are easy to confuse.

What is closed, measured on production

	before	after
COPY … TO PROGRAM (RCE on the DB host)	allowed	refused
CREATE ROLE / privilege escalation	allowed	refused
Connect to the other database	allowed	refused
Bypass RLS	yes	no
CREATE DATABASE	yes	no
DDL on its own tables (migrations)	yes	yes, preserved

Not done, and why

key_log is not INSERT-only. The issue suggested withholding UPDATE and DELETE on it, which is right in principle, the table is append-only by design. It is not done here because ownership carries those rights implicitly, and splitting the relay into an owner role for migrations and a restricted role for runtime is a second change with its own failure mode. Worth doing; it is defence in depth either way, never a replacement for the Merkle proof that clients actually verify.

Rehearsal

This was rehearsed before it was applied, against a throwaway Postgres holding the real production schema and the real _fluent_migrations rows: schema and migration bookkeeping only, no user data. The relay booted healthy, ran its migrations, and answered signup, signin and both pages.

The first rehearsal failed with relation "devices" already exists, which looks like a permissions problem and is not: pg_dump --schema-only carries the _fluent_migrations table but none of its rows, so Fluent believed nothing had been migrated and tried to create everything again. A rehearsal that omits the bookkeeping is not a rehearsal.

Indexes built CONCURRENTLY

AddScanIndexes (relay review 2026-10-14) adds roster_tags_digest on roster_tags (digest) and prekeys_used_created on prekeys (created_at) WHERE used. On Postgres it runs CREATE INDEX CONCURRENTLY, which refuses to run inside a transaction. FluentKit's migrator opens none around prepare, so the migration builds them at boot, and ScanAndScaleTests proves it against Postgres 17.

A concurrent build waits for every open transaction on the table to finish, so boot waits with it. Both tables are small today. To keep the build out of the boot path on a large table, run these as relayapp before deploying; the migration then finds them and does nothing:

CREATE INDEX CONCURRENTLY IF NOT EXISTS roster_tags_digest
  ON roster_tags (digest);
CREATE INDEX CONCURRENTLY IF NOT EXISTS prekeys_used_created
  ON prekeys (created_at) WHERE used;

A build that fails halfway leaves an index marked INVALID. The migration drops and rebuilds one of those; by hand, DROP INDEX CONCURRENTLY it first.