| 1 | -- Deploy keys: SSH keys that reach one repository, read-only unless whoever |
| 2 | -- added them allowed write access. See src/deploy_keys.rs. |
| 3 | -- |
| 4 | -- `repo_id` is the repository's id, so a rename or a transfer keeps its |
| 5 | -- keys; the path is looked up when a key is used. `workspace_id` is the |
| 6 | -- workspace it was added in. `created_by` is the user (or, for a |
| 7 | -- workspace's token, the workspace) that added it. `read_only` is 1 or 0. |
| 8 | CREATE TABLE deploy_keys ( |
| 9 | id TEXT PRIMARY KEY, |
| 10 | repo_id TEXT NOT NULL, |
| 11 | workspace_id TEXT NOT NULL, |
| 12 | title TEXT NOT NULL, |
| 13 | public_key TEXT NOT NULL, |
| 14 | fingerprint TEXT NOT NULL UNIQUE, |
| 15 | read_only INTEGER NOT NULL DEFAULT 1, |
| 16 | created_by TEXT, |
| 17 | created_at TEXT NOT NULL, |
| 18 | last_used_at TEXT |
| 19 | ); |
| 20 | CREATE INDEX deploy_keys_repo ON deploy_keys (repo_id); |
| 21 | |
| 22 | -- When a person's SSH key was last used to sign in over SSH, to within 5 |
| 23 | -- minutes. Null until it is. |
| 24 | ALTER TABLE ssh_keys ADD COLUMN last_used_at TEXT; |
| 25 | |
| 26 | -- A public key means one thing: it is someone's SSH key or one |
| 27 | -- repository's deploy key, never both. The services check first and say |
| 28 | -- "Key is already in use."; these keep two adds at once from both landing. |
| 29 | CREATE TRIGGER deploy_keys_not_ssh_keys BEFORE INSERT ON deploy_keys |
| 30 | WHEN EXISTS (SELECT 1 FROM ssh_keys WHERE fingerprint = NEW.fingerprint) |
| 31 | BEGIN |
| 32 | SELECT RAISE(ABORT, 'Key is already in use.'); |
| 33 | END; |
| 34 | |
| 35 | CREATE TRIGGER ssh_keys_not_deploy_keys BEFORE INSERT ON ssh_keys |
| 36 | WHEN EXISTS (SELECT 1 FROM deploy_keys WHERE fingerprint = NEW.fingerprint) |
| 37 | BEGIN |
| 38 | SELECT RAISE(ABORT, 'Key is already in use.'); |
| 39 | END; |