UmbraTrans/umbratrans_web/migrations/0004_node_pairing.sql

-- Pairing codes: a node proves it is the machine a person authorised, without that
-- person ever typing a 32-byte credential into a terminal on the far end.
--
-- The problem this solves. Issuing a node credential the way it was done for
-- umbraaudio-m4 meant writing the key into a 0600 file on the controller and having a
-- human copy it onto another machine by hand. That key landed in shell history, in a
-- terminal buffer, and in whatever it was pasted into. It also made the controller a
-- courier: nothing could be enrolled without me in the middle, which is the opposite of
-- what a fleet of heterogeneous nodes needs.
--
-- The flow this supports:
--
--   1. An administrator requests a pairing code for a *named* node.
--   2. The code is shown once, expires, and is single-use.
--   3. The node calls POST /api/node/pair with that code and its own name.
--   4. The controller verifies the code, creates the node, and issues a credential
--      once, in the response.
--
-- Two properties make this worth having over "send the operator a secret":
--
--   * **The code is bound to a node name.** A code read out of a terminal buffer
--     cannot pair a machine called something else, so it cannot register a node under
--     a name no administrator approved.
--   * **The code never becomes a credential.** It is exchanged once, for a credential
--     shown once and stored only as an Argon2id hash. There is no path by which the
--     code itself is usable against the node surface afterwards.
--
-- ## Why there is no SECURITY DEFINER function here
--
-- The obvious shape for this is a plpgsql function that verifies and redeems, so the
-- node surface never touches the tables. It cannot be done in SQL here: the codebase's
-- hasher is Argon2id, in Python, and the only hashing PostgreSQL offers in this
-- database is `crypt()` (bcrypt and MD5). Writing pairing against bcrypt would be
-- claiming an algorithm choice the rest of the system does not make, and MD5 would be
-- indefensible. So the verification happens in Python, in the repository, alongside
-- `issue_node_credential` - which means the node endpoint authenticates as whatever
-- role the application uses and needs that role to hold EXECUTE on nothing and direct
-- table rights on these two tables.
--
-- That is a deliberate consequence of G2 still being open: the application connects as
-- the table owner, so a paired node's credential is issued with owner-level authority
-- rather than a scoped one. The tables below are additive and hold no secrets other
-- than hashed codes, so nothing sensitive is exposed by that gap today. It is recorded
-- here because when G2 closes, these tables need grants for the application role, and
-- the pairing path needs re-testing as a non-owner role - the same treatment
-- test_rls_tenancy.py gives everything else.

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. Pairing codes
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.node_pairing_codes (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    -- The node this code may pair. An administrator names the machine they are
    -- standing at; the node claims that name or nothing.
    node_name text NOT NULL CHECK (btrim(node_name) <> '' AND length(node_name) <= 120),
    organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
    locality text,
    trust_domain text NOT NULL DEFAULT 'local'
        CHECK (trust_domain IN ('local', 'remote', 'cloud')),
    -- Argon2id, never the code. A database read must not yield something replayable
    -- against the node surface, and a support transcript must not hand over a live
    -- credential.
    code_hash text NOT NULL,
    -- Shown to the administrator so two codes are distinguishable in a conversation
    -- ("the one ending 41"). Deliberately not a lookup key: verifying by hash over
    -- every live code avoids a prefix lookup that would make a truncated or misread
    -- code valid.
    code_hint text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL,
    -- Set on successful redemption, and the single-use guard: redemption is one
    -- UPDATE ... WHERE used_at IS NULL, so two nodes racing on a single code produce
    -- one winner rather than two nodes.
    used_at timestamptz,
    used_by_node_id uuid REFERENCES public.compute_nodes(id) ON DELETE SET NULL,
    attempts smallint NOT NULL DEFAULT 0 CHECK (attempts >= 0),
    CHECK (expires_at > created_at)
);

CREATE INDEX IF NOT EXISTS node_pairing_codes_live_idx
    ON public.node_pairing_codes (organization_id, node_name)
    WHERE used_at IS NULL;

COMMENT ON TABLE public.node_pairing_codes IS
    'Single-use, expiring, node-name-bound codes that exchange for a node credential. '
    'A code is never itself usable against the node surface.';

-- ---------------------------------------------------------------------------
-- 2. Rate limiting
-- ---------------------------------------------------------------------------
-- A per-code attempt counter alone is not enough. An attacker who never generated a
-- code can still spray: pick a code, guess until it is right. Without a per-address
-- limit the six-digit space is reachable at whatever rate the network allows, and the
-- per-code counter only helps once a guess happens to hit a real code.
--
-- The counter is per address over a window, so guessing across many codes costs the
-- same as guessing repeatedly at one. This is the control that makes a six-digit code
-- defensible; without it the code length would not be.
CREATE TABLE IF NOT EXISTS public.node_pairing_attempts (
    source_address text PRIMARY KEY,
    window_started_at timestamptz NOT NULL DEFAULT now(),
    attempts integer NOT NULL DEFAULT 0 CHECK (attempts >= 0)
);

COMMENT ON TABLE public.node_pairing_attempts IS
    'Rolling per-address attempt counter for node pairing. Separate from the per-code '
    'counter: one bounds guessing at a known code, this bounds guessing across all of '
    'them.';

-- ---------------------------------------------------------------------------
-- 3. Row-level security
-- ---------------------------------------------------------------------------
-- Enabled now so the tables cannot be reached by a role that is not the owner once G2
-- lands, and so `test_rls_coverage.py` sees them. No policy is created: pairing is an
-- administrative operation on a table that holds no tenant data, and the read path is
-- the repository's. A permissive policy here would widen the surface for no gain.
ALTER TABLE public.node_pairing_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.node_pairing_attempts ENABLE ROW LEVEL SECURITY;

-- ---------------------------------------------------------------------------
-- 4. Immutability of the redemption record
-- ---------------------------------------------------------------------------
-- `used_at` and `used_by_node_id` are the evidence that a code was spent and on what.
-- An UPDATE that clears them would make a single-use code reusable, and nothing else
-- would notice: the redemption itself is already committed.
CREATE OR REPLACE FUNCTION app.prevent_pairing_reuse_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $body$
BEGIN
    -- The redemption columns are immutable; the attempt counter is not, because the
    -- rate limiter updates it. Guarding both would break the limiter, and guarding
    -- nothing would let a spent code be revived.
    IF OLD.used_at IS NOT NULL THEN
        -- Spent. Nothing about this row may change afterwards, or a code could be
        -- revived by clearing the redemption.
        RAISE EXCEPTION
            'a pairing code is single-use; a redeemed code cannot be changed';
    END IF;

    -- While a code is still live, its *identity* is fixed: the hash it was minted
    -- under, and the organization and node name it was authorised for. Rewriting any
    -- of those would let an unspent code be re-pointed at another tenant's node,
    -- which is the one thing the name binding exists to prevent.
    --
    -- `expires_at` is deliberately NOT guarded. Expiry is how a code is revoked before
    -- it is used - an operator who issued a code to the wrong person shortens or ages
    -- it out - so it must be adjustable. It can only ever move *earlier*, never later:
    -- extending a live code is not revocation, it is a second lease on a secret that
    -- was issued for ten minutes.
    IF NEW.code_hash IS DISTINCT FROM OLD.code_hash
       OR NEW.node_name IS DISTINCT FROM OLD.node_name
       OR NEW.organization_id IS DISTINCT FROM OLD.organization_id
       OR NEW.expires_at > OLD.expires_at
       THEN
        RAISE EXCEPTION
            'a pairing code''s identity is fixed and its expiry may only be brought forward';
    END IF;
    RETURN NEW;
END
$body$;

DO $$
BEGIN
    EXECUTE 'DROP TRIGGER IF EXISTS node_pairing_codes_redeem_once ON public.node_pairing_codes';
    EXECUTE 'CREATE TRIGGER node_pairing_codes_redeem_once BEFORE UPDATE ON public.node_pairing_codes '
            'FOR EACH ROW EXECUTE FUNCTION app.prevent_pairing_reuse_mutation()';
END
$$;

-- Rollback, in dependency order:
-- DROP TRIGGER IF EXISTS node_pairing_codes_redeem_once ON public.node_pairing_codes;
-- DROP FUNCTION IF EXISTS app.prevent_pairing_reuse_mutation();
-- DROP TABLE IF EXISTS public.node_pairing_attempts;
-- DROP TABLE IF EXISTS public.node_pairing_codes;

COMMIT;