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 =
52 sqlx::query("DELETE FROM ssh_keys WHERE id = $1 AND user_id = $2")
53 .bind(key_id)
54 .bind(user_id)
55 .execute(pool)
56 .await?;
57
58 Ok(result.rows_affected() > 0)
59 }
60
61 /// Delete an SSH key by fingerprint. Returns false if not found or not owned by the user.
62 #[tracing::instrument(skip_all)]
63 pub async fn delete_key_by_fingerprint(
64 pool: &PgPool,
65 user_id: UserId,
66 fingerprint: &str,
67 ) -> Result<bool> {
68 let result =
69 sqlx::query("DELETE FROM ssh_keys WHERE user_id = $1 AND fingerprint = $2")
70 .bind(user_id)
71 .bind(fingerprint)
72 .execute(pool)
73 .await?;
74
75 Ok(result.rows_affected() > 0)
76 }
77
78 /// Get all SSH keys with their owner's username, for authorized_keys rebuild.
79 #[tracing::instrument(skip_all)]
80 pub async fn get_all_keys_with_username(pool: &PgPool) -> Result<Vec<SshKeyWithUsername>> {
81 let rows = sqlx::query_as::<_, SshKeyWithUsername>(
82 r#"
83 SELECT sk.id, sk.public_key, u.username::TEXT as username
84 FROM ssh_keys sk
85 JOIN users u ON u.id = sk.user_id
86 ORDER BY sk.created_at
87 "#,
88 )
89 .fetch_all(pool)
90 .await?;
91
92 Ok(rows)
93 }
94
95 /// Look up a user by their SSH key fingerprint. Used by the CLI SSH server
96 /// to authenticate connections.
97 #[tracing::instrument(skip_all)]
98 pub async fn lookup_user_by_fingerprint(
99 pool: &PgPool,
100 fingerprint: &str,
101 ) -> Result<Option<SshKeyUserLookup>> {
102 let row = sqlx::query_as::<_, SshKeyUserLookup>(
103 r#"
104 SELECT u.id AS user_id, u.username, u.display_name, u.email,
105 u.creator_tier, u.can_create_projects,
106 (u.suspended_at IS NOT NULL) AS suspended
107 FROM ssh_keys sk
108 JOIN users u ON u.id = sk.user_id
109 WHERE sk.fingerprint = $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