max / makenotwork

983 B · 19 lines History Blame Raw

1	-- An SSH key fingerprint must map to exactly one identity. The original table
2	-- only enforced UNIQUE (user_id, fingerprint), so the same public key could be
3	-- registered to multiple accounts. Because SSH public keys are non-secret (they
4	-- are published, and MNW itself renders them into authorized_keys), an attacker
5	-- could register a victim's key on their own account; the CLI SSH identity
6	-- lookup then returns >1 row and fails closed, breaking the victim's CLI auth
7	-- (Run 15 Auth C1). Add a global UNIQUE (fingerprint).
8
9	-- Dedup first: if any fingerprint is registered to more than one account, keep
10	-- the earliest registration (the legitimate owner; a cross-account duplicate is
11	-- always added later) and drop the rest, so the constraint can be added.
12	DELETE FROM ssh_keys a
13	USING ssh_keys b
14	WHERE a.fingerprint = b.fingerprint
15	AND (a.created_at, a.id) > (b.created_at, b.id);
16
17	ALTER TABLE ssh_keys
18	ADD CONSTRAINT ssh_keys_fingerprint_key UNIQUE (fingerprint);
19