| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- SSH public keys associated with users. Used by sshd's |
| 4 | -- AuthorizedKeysCommand to map an inbound SSH connection to a shithub user. |
| 5 | -- |
| 6 | -- fingerprint_sha256 is unique across ALL users, not just per-user — two |
| 7 | -- users registering the same key would produce ambiguous AKC results. |
| 8 | -- Stored without the "SHA256:" prefix so the column carries only the |
| 9 | -- canonical b64-no-padding payload; the prefix is reattached at display time. |
| 10 | -- |
| 11 | -- public_key holds the canonical authorized_keys line WITHOUT the comment |
| 12 | -- (we store the title separately). It is the exact blob we re-emit from AKC. |
| 13 | |
| 14 | -- +goose Up |
| 15 | CREATE TABLE user_ssh_keys ( |
| 16 | id bigserial PRIMARY KEY, |
| 17 | user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 18 | title text NOT NULL, |
| 19 | fingerprint_sha256 text NOT NULL UNIQUE, |
| 20 | key_type text NOT NULL, |
| 21 | key_bits integer NOT NULL DEFAULT 0, |
| 22 | public_key text NOT NULL, |
| 23 | last_used_at timestamptz, |
| 24 | last_used_ip inet, |
| 25 | created_at timestamptz NOT NULL DEFAULT now(), |
| 26 | |
| 27 | CONSTRAINT user_ssh_keys_title_length CHECK (char_length(title) BETWEEN 1 AND 80), |
| 28 | CONSTRAINT user_ssh_keys_type_known CHECK (key_type IN ( |
| 29 | 'ssh-ed25519', |
| 30 | 'sk-ssh-ed25519@openssh.com', |
| 31 | 'ecdsa-sha2-nistp256', |
| 32 | 'ecdsa-sha2-nistp384', |
| 33 | 'ecdsa-sha2-nistp521', |
| 34 | 'sk-ecdsa-sha2-nistp256@openssh.com', |
| 35 | 'ssh-rsa' |
| 36 | )) |
| 37 | ); |
| 38 | |
| 39 | CREATE INDEX user_ssh_keys_user_id_idx ON user_ssh_keys (user_id, created_at DESC); |
| 40 | |
| 41 | -- +goose Down |
| 42 | DROP TABLE IF EXISTS user_ssh_keys; |
| 43 |