Skip to main content

max / makenotwork

3.7 KB · 139 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 FROM ssh_keys sk
106 JOIN users u ON u.id = sk.user_id
107 WHERE sk.fingerprint = $1
108 ORDER BY sk.created_at ASC
109 LIMIT 1
110 ",
111 )
112 .bind(fingerprint)
113 .fetch_optional(pool)
114 .await?;
115
116 Ok(row)
117 }
118
119 /// Look up an SSH key by ID, returning the key and its owner. For git-auth.
120 #[tracing::instrument(skip_all)]
121 pub async fn get_key_with_user(
122 pool: &PgPool,
123 key_id: SshKeyId,
124 ) -> Result<Option<(SshKeyId, UserId, String)>> {
125 let row = sqlx::query_as::<_, (SshKeyId, UserId, String)>(
126 r"
127 SELECT sk.id, sk.user_id, u.username::TEXT as username
128 FROM ssh_keys sk
129 JOIN users u ON u.id = sk.user_id
130 WHERE sk.id = $1
131 ",
132 )
133 .bind(key_id)
134 .fetch_optional(pool)
135 .await?;
136
137 Ok(row)
138 }
139