Skip to main content

max / makenotwork

3.7 KB · 140 lines History Blame Raw
1 //! SSH key CRUD and lookup queries.
2
3 use sqlx::PgPool;
4
5 use super::models::{DbSshKey, SshKeyUserLookup, SshKeyWithUsername};
6 use super::{SshKeyId, UserId};
7 use crate::error::Result;
8
9 /// Add an SSH public key for a user.
10 #[tracing::instrument(skip_all)]
11 pub async fn add_key(
12 pool: &PgPool,
13 user_id: UserId,
14 public_key: &str,
15 fingerprint: &str,
16 label: &str,
17 ) -> Result<DbSshKey> {
18 let key = sqlx::query_as::<_, DbSshKey>(
19 r"
20 INSERT INTO ssh_keys (user_id, public_key, fingerprint, label)
21 VALUES ($1, $2, $3, $4)
22 RETURNING *
23 ",
24 )
25 .bind(user_id)
26 .bind(public_key)
27 .bind(fingerprint)
28 .bind(label)
29 .fetch_one(pool)
30 .await?;
31
32 Ok(key)
33 }
34
35 /// List all SSH keys for a user, newest first.
36 #[tracing::instrument(skip_all)]
37 pub async fn list_keys_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbSshKey>> {
38 let keys = sqlx::query_as::<_, DbSshKey>(
39 "SELECT * FROM ssh_keys WHERE user_id = $1 ORDER BY created_at DESC LIMIT 100",
40 )
41 .bind(user_id)
42 .fetch_all(pool)
43 .await?;
44
45 Ok(keys)
46 }
47
48 /// Delete an SSH key. Returns false if not found or not owned by the user.
49 #[tracing::instrument(skip_all)]
50 pub async fn delete_key(pool: &PgPool, key_id: SshKeyId, user_id: UserId) -> Result<bool> {
51 let result = sqlx::query("DELETE FROM ssh_keys WHERE id = $1 AND user_id = $2")
52 .bind(key_id)
53 .bind(user_id)
54 .execute(pool)
55 .await?;
56
57 Ok(result.rows_affected() > 0)
58 }
59
60 /// Delete an SSH key by fingerprint. Returns false if not found or not owned by the user.
61 #[tracing::instrument(skip_all)]
62 pub async fn delete_key_by_fingerprint(
63 pool: &PgPool,
64 user_id: UserId,
65 fingerprint: &str,
66 ) -> Result<bool> {
67 let result = sqlx::query("DELETE FROM ssh_keys WHERE user_id = $1 AND fingerprint = $2")
68 .bind(user_id)
69 .bind(fingerprint)
70 .execute(pool)
71 .await?;
72
73 Ok(result.rows_affected() > 0)
74 }
75
76 /// Get all SSH keys with their owner's username, for authorized_keys rebuild.
77 #[tracing::instrument(skip_all)]
78 pub async fn get_all_keys_with_username(pool: &PgPool) -> Result<Vec<SshKeyWithUsername>> {
79 let rows = sqlx::query_as::<_, SshKeyWithUsername>(
80 r"
81 SELECT sk.id, sk.public_key, u.username::TEXT as username
82 FROM ssh_keys sk
83 JOIN users u ON u.id = sk.user_id
84 ORDER BY sk.created_at
85 ",
86 )
87 .fetch_all(pool)
88 .await?;
89
90 Ok(rows)
91 }
92
93 /// Look up a user by their SSH key fingerprint. Used by the CLI SSH server
94 /// to authenticate connections.
95 #[tracing::instrument(skip_all)]
96 pub async fn lookup_user_by_fingerprint(
97 pool: &PgPool,
98 fingerprint: &str,
99 ) -> Result<Option<SshKeyUserLookup>> {
100 let row = sqlx::query_as::<_, SshKeyUserLookup>(
101 r"
102 SELECT u.id AS user_id, u.username, u.display_name, u.email,
103 u.creator_tier, u.can_create_projects,
104 (u.suspended_at IS NOT NULL) AS suspended,
105 u.settlement_currency
106 FROM ssh_keys sk
107 JOIN users u ON u.id = sk.user_id
108 WHERE sk.fingerprint = $1
109 ORDER BY sk.created_at ASC
110 LIMIT 1
111 ",
112 )
113 .bind(fingerprint)
114 .fetch_optional(pool)
115 .await?;
116
117 Ok(row)
118 }
119
120 /// Look up an SSH key by ID, returning the key and its owner. For git-auth.
121 #[tracing::instrument(skip_all)]
122 pub async fn get_key_with_user(
123 pool: &PgPool,
124 key_id: SshKeyId,
125 ) -> Result<Option<(SshKeyId, UserId, String)>> {
126 let row = sqlx::query_as::<_, (SshKeyId, UserId, String)>(
127 r"
128 SELECT sk.id, sk.user_id, u.username::TEXT as username
129 FROM ssh_keys sk
130 JOIN users u ON u.id = sk.user_id
131 WHERE sk.id = $1
132 ",
133 )
134 .bind(key_id)
135 .fetch_optional(pool)
136 .await?;
137
138 Ok(row)
139 }
140