1use crate::{
2 crypt::EncryptedString,
3 models::{
4 InsertQueryBuilder, UpdateQueryBuilder,
5 database_host::{DatabaseTransaction, DatabaseType},
6 },
7 prelude::*,
8};
9use garde::Validate;
10use rand::{RngExt, distr::SampleString};
11use serde::{Deserialize, Serialize};
12use sqlx::{Row, postgres::PgRow};
13use std::{
14 collections::BTreeMap,
15 sync::{Arc, LazyLock},
16};
17use utoipa::ToSchema;
18
19mod explorer;
20pub use explorer::{
21 BROWSE_DEFAULT_ROWS, BROWSE_MAX_FILTERS, BROWSE_MAX_ROWS, BrowseFilter, BrowseOptions,
22 CREATE_TABLE_MAX_COLUMNS, ColumnDefinition, DatabaseSchema, FilterOperator, MUTATE_MAX_ROWS,
23 QUERY_ACTIVITY_LENGTH, QUERY_DEFAULT_ROWS, QUERY_MAX_LENGTH, QUERY_MAX_ROWS, QueryColumn,
24 QueryResultSet, QueryValue, RowDelete, RowInsert, RowOperation, RowUpdate, RowValue,
25 SCHEMA_MAX_TABLES, SchemaColumn, SchemaTable, TenantConnection,
26};
27
28#[derive(Serialize, Deserialize, Clone)]
29pub struct ServerDatabase {
30 pub uuid: uuid::Uuid,
31 pub server: Fetchable<super::server::Server>,
32 pub database_host: super::database_host::DatabaseHost,
33
34 pub name: compact_str::CompactString,
35 pub locked: bool,
36
37 pub username: compact_str::CompactString,
38 pub password: EncryptedString,
39
40 pub created: chrono::NaiveDateTime,
41
42 extension_data: super::ModelExtensionData,
43}
44
45impl BaseModel for ServerDatabase {
46 const NAME: &'static str = "server_database";
47
48 fn get_extension_list() -> &'static super::ModelExtensionList {
49 static EXTENSIONS: LazyLock<super::ModelExtensionList> =
50 LazyLock::new(|| parking_lot::RwLock::new(Vec::new()));
51
52 &EXTENSIONS
53 }
54
55 fn get_extension_data(&self) -> &super::ModelExtensionData {
56 &self.extension_data
57 }
58
59 #[inline]
60 fn base_columns(prefix: Option<&str>) -> BTreeMap<&'static str, compact_str::CompactString> {
61 let prefix = prefix.unwrap_or_default();
62
63 let mut columns = BTreeMap::from([
64 (
65 "server_databases.uuid",
66 compact_str::format_compact!("{prefix}uuid"),
67 ),
68 (
69 "server_databases.server_uuid",
70 compact_str::format_compact!("{prefix}server_uuid"),
71 ),
72 (
73 "server_databases.name",
74 compact_str::format_compact!("{prefix}name"),
75 ),
76 (
77 "server_databases.locked",
78 compact_str::format_compact!("{prefix}locked"),
79 ),
80 (
81 "server_databases.username",
82 compact_str::format_compact!("{prefix}username"),
83 ),
84 (
85 "server_databases.password",
86 compact_str::format_compact!("{prefix}password"),
87 ),
88 (
89 "server_databases.created",
90 compact_str::format_compact!("{prefix}created"),
91 ),
92 ]);
93
94 columns.extend(super::database_host::DatabaseHost::base_columns(Some(
95 "database_host_",
96 )));
97
98 columns
99 }
100
101 #[inline]
102 fn map(prefix: Option<&str>, row: &PgRow) -> Result<Self, crate::database::DatabaseError> {
103 let prefix = prefix.unwrap_or_default();
104
105 Ok(Self {
106 uuid: row.try_get(compact_str::format_compact!("{prefix}uuid").as_str())?,
107 server: super::server::Server::get_fetchable(
108 row.try_get(compact_str::format_compact!("{prefix}server_uuid").as_str())?,
109 ),
110 database_host: super::database_host::DatabaseHost::map(Some("database_host_"), row)?,
111 name: row.try_get(compact_str::format_compact!("{prefix}name").as_str())?,
112 locked: row.try_get(compact_str::format_compact!("{prefix}locked").as_str())?,
113 username: row.try_get(compact_str::format_compact!("{prefix}username").as_str())?,
114 password: row.try_get(compact_str::format_compact!("{prefix}password").as_str())?,
115 created: row.try_get(compact_str::format_compact!("{prefix}created").as_str())?,
116 extension_data: Self::map_extensions(prefix, row)?,
117 })
118 }
119
120 fn cache_invalidation_keys(&self) -> Vec<compact_str::CompactString> {
121 vec![compact_str::format_compact!(
122 "{}::{}",
123 Self::NAME,
124 self.uuid
125 )]
126 }
127}
128
129impl ServerDatabase {
130 pub async fn by_server_uuid_uuid(
131 database: &crate::database::Database,
132 server_uuid: uuid::Uuid,
133 uuid: uuid::Uuid,
134 ) -> Result<Option<Self>, crate::database::DatabaseError> {
135 let row = sqlx::query(sqlx::AssertSqlSafe(format!(
136 r#"
137 SELECT {}
138 FROM server_databases
139 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
140 WHERE server_databases.server_uuid = $1 AND server_databases.uuid = $2
141 "#,
142 Self::columns_sql(None)
143 )))
144 .bind(server_uuid)
145 .bind(uuid)
146 .fetch_optional(database.read())
147 .await?;
148
149 row.try_map(|row| Self::map(None, &row))
150 }
151
152 pub async fn by_database_host_uuid_uuid(
153 database: &crate::database::Database,
154 database_host_uuid: uuid::Uuid,
155 uuid: uuid::Uuid,
156 ) -> Result<Option<Self>, crate::database::DatabaseError> {
157 let row = sqlx::query(sqlx::AssertSqlSafe(format!(
158 r#"
159 SELECT {}
160 FROM server_databases
161 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
162 WHERE server_databases.database_host_uuid = $1 AND server_databases.uuid = $2
163 "#,
164 Self::columns_sql(None)
165 )))
166 .bind(database_host_uuid)
167 .bind(uuid)
168 .fetch_optional(database.read())
169 .await?;
170
171 row.try_map(|row| Self::map(None, &row))
172 }
173
174 pub async fn by_database_host_uuid_with_pagination(
175 database: &crate::database::Database,
176 database_host_uuid: uuid::Uuid,
177 page: i64,
178 per_page: i64,
179 search: Option<&str>,
180 ) -> Result<super::Pagination<Self>, crate::database::DatabaseError> {
181 super::PaginatedQueryBuilder::new(
182 Self::columns_sql(None),
183 format!(
184 r#"
185 FROM server_databases
186 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
187 WHERE server_databases.database_host_uuid = $1 AND {search}
188 "#,
189 search =
190 super::search_sql(2, &["server_databases.name"], &["server_databases.uuid"]),
191 ),
192 "server_databases.created, server_databases.uuid",
193 )
194 .bind(database_host_uuid)
195 .bind(search)
196 .fetch(database, page, per_page)
197 .await
198 }
199
200 pub async fn by_server_uuid_with_pagination(
201 database: &crate::database::Database,
202 server_uuid: uuid::Uuid,
203 page: i64,
204 per_page: i64,
205 search: Option<&str>,
206 ) -> Result<super::Pagination<Self>, crate::database::DatabaseError> {
207 super::PaginatedQueryBuilder::new(
208 Self::columns_sql(None),
209 format!(
210 r#"
211 FROM server_databases
212 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
213 WHERE server_databases.server_uuid = $1 AND {search}
214 "#,
215 search =
216 super::search_sql(2, &["server_databases.name"], &["server_databases.uuid"]),
217 ),
218 "server_databases.created, server_databases.uuid",
219 )
220 .bind(server_uuid)
221 .bind(search)
222 .fetch(database, page, per_page)
223 .await
224 }
225
226 pub async fn all_by_server_uuid(
227 database: &crate::database::Database,
228 server_uuid: uuid::Uuid,
229 ) -> Result<Vec<Self>, crate::database::DatabaseError> {
230 let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
231 r#"
232 SELECT {}
233 FROM server_databases
234 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
235 WHERE server_databases.server_uuid = $1
236 "#,
237 Self::columns_sql(None)
238 )))
239 .bind(server_uuid)
240 .fetch_all(database.read())
241 .await?;
242
243 rows.into_iter()
244 .map(|row| Self::map(None, &row))
245 .try_collect_vec()
246 }
247
248 pub async fn count_by_server_uuid(
249 database: &crate::database::Database,
250 server_uuid: uuid::Uuid,
251 ) -> Result<i64, sqlx::Error> {
252 sqlx::query_scalar(
253 r#"
254 SELECT COUNT(*)
255 FROM server_databases
256 WHERE server_databases.server_uuid = $1
257 "#,
258 )
259 .bind(server_uuid)
260 .fetch_one(database.read())
261 .await
262 }
263
264 pub async fn all_by_database_host_uuid(
265 database: &crate::database::Database,
266 database_host_uuid: uuid::Uuid,
267 ) -> Result<Vec<Self>, crate::database::DatabaseError> {
268 let rows = sqlx::query(sqlx::AssertSqlSafe(format!(
269 r#"
270 SELECT {}
271 FROM server_databases
272 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
273 WHERE server_databases.database_host_uuid = $1
274 "#,
275 Self::columns_sql(None)
276 )))
277 .bind(database_host_uuid)
278 .fetch_all(database.read())
279 .await?;
280
281 rows.into_iter()
282 .map(|row| Self::map(None, &row))
283 .try_collect_vec()
284 }
285
286 pub async fn count_by_database_host_uuid(
287 database: &crate::database::Database,
288 database_host_uuid: uuid::Uuid,
289 ) -> Result<i64, sqlx::Error> {
290 sqlx::query_scalar(
291 r#"
292 SELECT COUNT(*)
293 FROM server_databases
294 WHERE server_databases.database_host_uuid = $1
295 "#,
296 )
297 .bind(database_host_uuid)
298 .fetch_one(database.read())
299 .await
300 }
301
302 #[inline]
303 pub fn generate_database_name(server_uuid: uuid::Uuid, name: &str) -> String {
304 let server_id = format!("{:08x}", server_uuid.as_u128() >> 96);
305 format!("s{}_{}", server_id, name)
306 }
307
308 #[inline]
309 pub fn generate_username(server_uuid: uuid::Uuid) -> String {
310 let server_id = format!("{:08x}", server_uuid.as_u128() >> 96);
311 format!(
312 "u{}_{}",
313 server_id,
314 rand::distr::Alphanumeric.sample_string(&mut rand::rng(), 10)
315 )
316 }
317
318 #[inline]
319 pub fn generate_password() -> String {
320 const PASSWORD_SPECIAL_CHARS: &[u8] = b"!@#$%^&*()<>-_";
321
322 let mut rng = rand::rng();
323 let mut password = rand::distr::Alphanumeric
324 .sample_string(&mut rng, 24)
325 .into_bytes();
326
327 for _ in 0..rng.random_range(1..=5) {
328 let pos = rng.random_range(0..password.len());
329 password[pos] =
330 PASSWORD_SPECIAL_CHARS[rng.random_range(0..PASSWORD_SPECIAL_CHARS.len())];
331 }
332
333 String::from_utf8(password).unwrap()
334 }
335
336 pub async fn rotate_password(
337 &mut self,
338 database: &crate::database::Database,
339 ) -> Result<String, anyhow::Error> {
340 let new_password = Self::generate_password();
341
342 match self.database_host.get_connection(database).await? {
343 crate::models::database_host::DatabasePool::Mysql(pool) => {
344 sqlx::query(sqlx::AssertSqlSafe(format!(
345 "ALTER USER '{}'@'%' IDENTIFIED BY '{}'",
346 self.username, new_password
347 )))
348 .execute(&pool)
349 .await?;
350 }
351 crate::models::database_host::DatabasePool::Postgres(pool) => {
352 sqlx::query(sqlx::AssertSqlSafe(format!(
353 "ALTER USER \"{}\" WITH PASSWORD '{}'",
354 self.username, new_password
355 )))
356 .execute(&pool)
357 .await?;
358 }
359 crate::models::database_host::DatabasePool::Mongodb(client) => {
360 let cmd = mongodb::bson::doc! {
361 "updateUser": self.username.to_string(),
362 "pwd": &new_password
363 };
364 client.database(&self.name).run_command(cmd).await?;
365 }
366 }
367
368 let (new_password, encrypted_password) =
369 EncryptedString::from_plaintext_with_input(new_password, database).await?;
370
371 sqlx::query(
372 r#"
373 UPDATE server_databases
374 SET password = $1
375 WHERE server_databases.uuid = $2
376 "#,
377 )
378 .bind(encrypted_password)
379 .bind(self.uuid)
380 .execute(database.write())
381 .await?;
382
383 Ok(new_password)
384 }
385
386 pub async fn get_size(
387 &mut self,
388 database: &crate::database::Database,
389 ) -> Result<i64, crate::database::DatabaseError> {
390 match self.database_host.get_connection(database).await? {
391 crate::models::database_host::DatabasePool::Mysql(pool) => {
392 let row = sqlx::query(
393 "SELECT CAST(SUM(data_length + index_length) AS SIGNED) FROM information_schema.tables WHERE table_schema = ?",
394 )
395 .bind(&self.name)
396 .fetch_one(&pool)
397 .await?;
398
399 Ok(row.get::<Option<i64>, _>(0).unwrap_or(0))
400 }
401 crate::models::database_host::DatabasePool::Postgres(pool) => {
402 let row = sqlx::query("SELECT pg_database_size($1)")
403 .bind(&self.name)
404 .fetch_one(&pool)
405 .await?;
406
407 Ok(row.get::<Option<i64>, _>(0).unwrap_or(0))
408 }
409 crate::models::database_host::DatabasePool::Mongodb(client) => {
410 let cmd = mongodb::bson::doc! { "dbStats": 1, "scale": 1 };
411 let stats = client.database(&self.name).run_command(cmd).await?;
412
413 let size = match stats.get("dataSize") {
414 Some(mongodb::bson::Bson::Int32(i)) => *i as i64,
415 Some(mongodb::bson::Bson::Int64(i)) => *i,
416 Some(mongodb::bson::Bson::Double(f)) => *f as i64,
417 _ => 0,
418 };
419
420 Ok(size)
421 }
422 }
423 }
424
425 pub async fn recreate(
426 &mut self,
427 database: &crate::database::Database,
428 ) -> Result<(), anyhow::Error> {
429 let mut run_recreate = async || {
430 match self.database_host.get_connection(database).await? {
431 crate::models::database_host::DatabasePool::Mysql(pool) => {
432 sqlx::query(sqlx::AssertSqlSafe(format!(
433 "DROP DATABASE IF EXISTS `{}`",
434 self.name
435 )))
436 .execute(&pool)
437 .await?;
438 sqlx::query(sqlx::AssertSqlSafe(format!("CREATE DATABASE `{}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;", self.name)))
439 .execute(&pool)
440 .await?;
441 }
442 crate::models::database_host::DatabasePool::Postgres(pool) => {
443 sqlx::query(sqlx::AssertSqlSafe(format!(
444 "DROP DATABASE IF EXISTS \"{}\" WITH (FORCE)",
445 self.name
446 )))
447 .execute(&pool)
448 .await?;
449 sqlx::query(sqlx::AssertSqlSafe(format!(
450 "CREATE DATABASE \"{}\" WITH OWNER \"{}\" ENCODING 'UTF8'",
451 self.name, self.username
452 )))
453 .execute(&pool)
454 .await?;
455 sqlx::query(sqlx::AssertSqlSafe(format!(
456 "REVOKE ALL ON DATABASE \"{}\" FROM PUBLIC",
457 self.name
458 )))
459 .execute(&pool)
460 .await?;
461 }
462 crate::models::database_host::DatabasePool::Mongodb(client) => {
463 let db = client.database(&self.name);
464
465 let drop_user_cmd =
466 mongodb::bson::doc! { "dropUser": self.username.to_string() };
467 let _ = db.run_command(drop_user_cmd).await;
468
469 db.drop().await?;
470
471 let password = self.password.decrypt(database).await?;
472 let cmd = mongodb::bson::doc! {
473 "createUser": self.username.to_string(),
474 "pwd": password.into_string(),
475 "roles": [
476 { "role": "readWrite", "db": self.name.to_string() }
477 ]
478 };
479 db.run_command(cmd).await?;
480 }
481 }
482
483 Ok::<(), anyhow::Error>(())
484 };
485
486 if let Err(err) = run_recreate().await {
487 if err
488 .downcast_ref::<sqlx::Error>()
489 .and_then(|e| e.as_database_error())
490 .is_some_and(|e| e.message().contains("is being accessed"))
491 {
492 return Err(crate::response::DisplayError::new(
493 "this database is being accessed, unable to recreate.",
494 )
495 .into());
496 }
497
498 return Err(err);
499 }
500
501 Ok(())
502 }
503}
504
505impl ServerDatabase {
506 pub async fn into_admin_server_api_object(
507 self,
508 state: &crate::State,
509 ) -> Result<AdminApiServerServerDatabase, crate::database::DatabaseError> {
510 let api_object = AdminApiServerServerDatabase::init_hooks(&self, state).await?;
511
512 let details = self
513 .database_host
514 .credentials
515 .parse_connection_details(&state.database)
516 .await?;
517 let host = self
518 .database_host
519 .public_host
520 .clone()
521 .unwrap_or(details.host);
522 let port = self
523 .database_host
524 .public_port
525 .unwrap_or(details.port as i32);
526
527 let api_object = finish_extendible!(
528 AdminApiServerServerDatabase {
529 uuid: self.uuid,
530 r#type: self.database_host.r#type,
531 database_host: self.database_host.into_admin_api_object(state, ()).await?,
532 host,
533 port,
534 name: self.name,
535 is_locked: self.locked,
536 username: self.username,
537 password: self.password.decrypt(&state.database).await?,
538 created: self.created.and_utc(),
539 },
540 api_object,
541 state
542 )?;
543
544 Ok(api_object)
545 }
546}
547
548#[async_trait::async_trait]
549impl IntoAdminApiObject for ServerDatabase {
550 type AdminApiObject = AdminApiServerDatabase;
551 type ExtraArgs<'a> = &'a crate::storage::StorageUrlRetriever<'a>;
552
553 async fn into_admin_api_object<'a>(
554 self,
555 state: &crate::State,
556 storage_url_retriever: Self::ExtraArgs<'a>,
557 ) -> Result<Self::AdminApiObject, crate::database::DatabaseError> {
558 let api_object = AdminApiServerDatabase::init_hooks(&self, state).await?;
559
560 let details = self
561 .database_host
562 .credentials
563 .parse_connection_details(&state.database)
564 .await?;
565
566 let api_object = finish_extendible!(
567 AdminApiServerDatabase {
568 uuid: self.uuid,
569 server: self
570 .server
571 .fetch_cached(&state.database)
572 .await?
573 .into_admin_api_object(state, storage_url_retriever)
574 .await?,
575 r#type: self.database_host.r#type,
576 host: self.database_host.public_host.unwrap_or(details.host),
577 port: self
578 .database_host
579 .public_port
580 .unwrap_or(details.port as i32),
581 name: self.name,
582 is_locked: self.locked,
583 username: self.username,
584 password: self.password.decrypt(&state.database).await?,
585 created: self.created.and_utc(),
586 },
587 api_object,
588 state
589 )?;
590
591 Ok(api_object)
592 }
593}
594
595#[async_trait::async_trait]
596impl IntoApiObject for ServerDatabase {
597 type ApiObject = ApiServerDatabase;
598 type ExtraArgs<'a> = bool;
599
600 async fn into_api_object<'a>(
601 self,
602 state: &crate::State,
603 show_password: Self::ExtraArgs<'a>,
604 ) -> Result<Self::ApiObject, crate::database::DatabaseError> {
605 let api_object = ApiServerDatabase::init_hooks(&self, state).await?;
606
607 let details = self
608 .database_host
609 .credentials
610 .parse_connection_details(&state.database)
611 .await?;
612
613 let api_object = finish_extendible!(
614 ApiServerDatabase {
615 uuid: self.uuid,
616 r#type: self.database_host.r#type,
617 host: self.database_host.public_host.unwrap_or(details.host),
618 port: self
619 .database_host
620 .public_port
621 .unwrap_or(details.port as i32),
622 name: self.name,
623 is_locked: self.locked,
624 username: self.username,
625 password: if show_password {
626 Some(self.password.decrypt(&state.database).await?)
627 } else {
628 None
629 },
630 created: self.created.and_utc(),
631 },
632 api_object,
633 state
634 )?;
635
636 Ok(api_object)
637 }
638}
639
640#[async_trait::async_trait]
641impl ByUuid for ServerDatabase {
642 async fn by_uuid(
643 database: &crate::database::Database,
644 uuid: uuid::Uuid,
645 ) -> Result<Self, crate::database::DatabaseError> {
646 let row = sqlx::query(sqlx::AssertSqlSafe(format!(
647 r#"
648 SELECT {}
649 FROM server_databases
650 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
651 WHERE server_databases.uuid = $1
652 "#,
653 Self::columns_sql(None)
654 )))
655 .bind(uuid)
656 .fetch_one(database.read())
657 .await?;
658
659 Self::map(None, &row)
660 }
661
662 async fn by_uuid_with_transaction(
663 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
664 uuid: uuid::Uuid,
665 ) -> Result<Self, crate::database::DatabaseError> {
666 let row = sqlx::query(sqlx::AssertSqlSafe(format!(
667 r#"
668 SELECT {}
669 FROM server_databases
670 JOIN database_hosts ON database_hosts.uuid = server_databases.database_host_uuid
671 WHERE server_databases.uuid = $1
672 "#,
673 Self::columns_sql(None)
674 )))
675 .bind(uuid)
676 .fetch_one(&mut **transaction)
677 .await?;
678
679 Self::map(None, &row)
680 }
681}
682
683#[derive(Validate)]
684pub struct CreateServerDatabaseOptions<'a> {
685 #[garde(skip)]
686 pub server: &'a super::server::Server,
687 #[garde(skip)]
688 pub database_host: &'a super::database_host::DatabaseHost,
689
690 #[garde(length(chars, min = 3, max = 31), pattern("^[a-zA-Z0-9_]+$"))]
691 pub name: compact_str::CompactString,
692}
693
694#[async_trait::async_trait]
695impl CreatableModel for ServerDatabase {
696 type CreateOptions<'a> = CreateServerDatabaseOptions<'a>;
697 type CreateResult = Self;
698
699 fn get_create_handlers() -> &'static LazyLock<CreateListenerList<Self>> {
700 static CREATE_LISTENERS: LazyLock<CreateListenerList<ServerDatabase>> =
701 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
702
703 &CREATE_LISTENERS
704 }
705
706 async fn create_with_transaction(
707 state: &crate::State,
708 mut options: Self::CreateOptions<'_>,
709 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
710 ) -> Result<Self, crate::database::DatabaseError> {
711 options.validate()?;
712
713 let name = Self::generate_database_name(options.server.uuid, &options.name);
714 let username = Self::generate_username(options.server.uuid);
715 let password = Self::generate_password();
716
717 let db_transaction: DatabaseTransaction = match options
718 .database_host
719 .clone()
720 .get_connection(&state.database)
721 .await?
722 {
723 crate::models::database_host::DatabasePool::Mysql(pool) => {
724 let mut transaction = pool.begin().await?;
725
726 sqlx::query(sqlx::AssertSqlSafe(format!(
727 "CREATE USER IF NOT EXISTS '{username}'@'%' IDENTIFIED BY '{password}'"
728 )))
729 .execute(&mut *transaction)
730 .await?;
731 sqlx::query(sqlx::AssertSqlSafe(format!("CREATE DATABASE IF NOT EXISTS `{name}` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")))
732 .execute(&mut *transaction)
733 .await?;
734 sqlx::query(sqlx::AssertSqlSafe(format!(
735 "GRANT ALL PRIVILEGES ON `{name}`.* TO '{username}'@'%' WITH GRANT OPTION"
736 )))
737 .execute(&mut *transaction)
738 .await?;
739
740 DatabaseTransaction::Mysql(transaction, pool)
741 }
742 crate::models::database_host::DatabasePool::Postgres(pool) => {
743 let transaction = pool.begin().await?;
744
745 sqlx::query(sqlx::AssertSqlSafe(format!(
746 "CREATE USER \"{username}\" WITH PASSWORD '{password}'"
747 )))
748 .execute(&pool)
749 .await?;
750 sqlx::query(sqlx::AssertSqlSafe(format!(
751 "CREATE DATABASE \"{name}\" WITH OWNER \"{username}\" ENCODING 'UTF8'"
752 )))
753 .execute(&pool)
754 .await?;
755 sqlx::query(sqlx::AssertSqlSafe(format!(
756 "REVOKE ALL ON DATABASE \"{name}\" FROM PUBLIC"
757 )))
758 .execute(&pool)
759 .await?;
760
761 DatabaseTransaction::Postgres(transaction, pool)
762 }
763 crate::models::database_host::DatabasePool::Mongodb(client) => {
764 let db = client.database(&name);
765 let cmd = mongodb::bson::doc! {
766 "createUser": &username,
767 "pwd": &password,
768 "roles": [
769 { "role": "readWrite", "db": &name }
770 ]
771 };
772 db.run_command(cmd).await?;
773
774 DatabaseTransaction::Mongodb(client)
775 }
776 };
777
778 let mut query_builder = InsertQueryBuilder::new("server_databases");
779
780 Self::run_create_handlers(&mut options, &mut query_builder, state, transaction).await?;
781
782 query_builder
783 .set("server_uuid", options.server.uuid)
784 .set("database_host_uuid", options.database_host.uuid)
785 .set("name", &name)
786 .set("username", &username)
787 .set(
788 "password",
789 EncryptedString::from_plaintext(password, &state.database).await?,
790 );
791
792 let row = match query_builder
793 .returning("uuid")
794 .fetch_one(&mut **transaction)
795 .await
796 {
797 Ok(row) => row,
798 Err(err) => {
799 match db_transaction {
800 DatabaseTransaction::Mysql(db_tx, pool) => {
801 db_tx.rollback().await?;
802
803 let drop_database = format!("DROP DATABASE IF EXISTS `{name}`");
804 let drop_user = format!("DROP USER IF EXISTS '{username}'@'%'");
805
806 let (_, _) = tokio::join!(
807 sqlx::query(sqlx::AssertSqlSafe(drop_database)).execute(&pool),
808 sqlx::query(sqlx::AssertSqlSafe(drop_user)).execute(&pool)
809 );
810 }
811 DatabaseTransaction::Postgres(db_tx, pool) => {
812 db_tx.rollback().await?;
813
814 let drop_database = format!("DROP DATABASE IF EXISTS \"{name}\"");
815 let drop_user = format!("DROP USER IF EXISTS \"{username}\"");
816
817 let (_, _) = tokio::join!(
818 sqlx::query(sqlx::AssertSqlSafe(drop_database)).execute(&pool),
819 sqlx::query(sqlx::AssertSqlSafe(drop_user)).execute(&pool)
820 );
821 }
822 DatabaseTransaction::Mongodb(client) => {
823 let _ = client.database(&name).drop().await;
824 }
825 }
826
827 return Err(err.into());
828 }
829 };
830
831 let uuid: uuid::Uuid = row.try_get("uuid")?;
832
833 match match db_transaction {
834 DatabaseTransaction::Mysql(db_tx, _) => db_tx.commit().await,
835 DatabaseTransaction::Postgres(db_tx, _) => db_tx.commit().await,
836 DatabaseTransaction::Mongodb(_) => Ok(()),
837 } {
838 Ok(_) => {}
839 Err(err) => {
840 sqlx::query(
841 r#"
842 DELETE FROM server_databases
843 WHERE server_databases.uuid = $1
844 "#,
845 )
846 .bind(uuid)
847 .execute(&mut **transaction)
848 .await
849 .ok();
850
851 return Err(err.into());
852 }
853 }
854
855 let mut result = Self::by_uuid_with_transaction(transaction, uuid).await?;
856
857 Self::run_after_create_handlers(&mut result, &options, state, transaction).await?;
858
859 Ok(result)
860 }
861}
862
863#[derive(ToSchema, Serialize, Deserialize, Validate, Default)]
864pub struct UpdateServerDatabaseOptions {
865 #[garde(skip)]
866 pub locked: Option<bool>,
867}
868
869#[async_trait::async_trait]
870impl UpdatableModel for ServerDatabase {
871 type UpdateOptions = UpdateServerDatabaseOptions;
872
873 fn get_update_handlers() -> &'static LazyLock<UpdateHandlerList<Self>> {
874 static UPDATE_LISTENERS: LazyLock<UpdateHandlerList<ServerDatabase>> =
875 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
876
877 &UPDATE_LISTENERS
878 }
879
880 async fn update_with_transaction(
881 &mut self,
882 state: &crate::State,
883 mut options: Self::UpdateOptions,
884 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
885 ) -> Result<(), crate::database::DatabaseError> {
886 options.validate()?;
887
888 let mut query_builder = UpdateQueryBuilder::new("server_databases");
889
890 self.run_update_handlers(&mut options, &mut query_builder, state, transaction)
891 .await?;
892
893 query_builder
894 .set("locked", options.locked)
895 .where_eq("uuid", self.uuid);
896
897 query_builder.execute(&mut **transaction).await?;
898
899 if let Some(locked) = options.locked {
900 self.locked = locked;
901 }
902
903 self.run_after_update_handlers(state, transaction).await?;
904
905 Ok(())
906 }
907}
908
909#[derive(Clone, Default)]
910pub struct DeleteServerDatabaseOptions {
911 pub force: bool,
912}
913
914#[async_trait::async_trait]
915impl DeletableModel for ServerDatabase {
916 type DeleteOptions = DeleteServerDatabaseOptions;
917
918 fn get_delete_handlers() -> &'static LazyLock<DeleteHandlerList<Self>> {
919 static DELETE_LISTENERS: LazyLock<DeleteHandlerList<ServerDatabase>> =
920 LazyLock::new(|| Arc::new(ModelHandlerList::default()));
921
922 &DELETE_LISTENERS
923 }
924
925 async fn delete_with_transaction(
926 &self,
927 state: &crate::State,
928 options: Self::DeleteOptions,
929 transaction: &mut sqlx::Transaction<'_, sqlx::Postgres>,
930 ) -> Result<(), anyhow::Error> {
931 self.run_delete_handlers(&options, state, transaction)
932 .await?;
933
934 sqlx::query(
935 r#"
936 DELETE FROM server_databases
937 WHERE server_databases.uuid = $1
938 "#,
939 )
940 .bind(self.uuid)
941 .execute(&mut **transaction)
942 .await?;
943
944 self.run_after_delete_handlers(&options, state, transaction)
945 .await?;
946
947 Ok(())
948 }
949
950 async fn delete(
951 &self,
952 state: &crate::State,
953 options: Self::DeleteOptions,
954 ) -> Result<(), anyhow::Error> {
955 let database_name = self.name.clone();
956 let database_username = self.username.clone();
957 let mut database_host = self.database_host.clone();
958 let self_clone = self.clone();
959 let state_clone = state.clone();
960
961 tokio::spawn(async move {
962 let mut run_delete = async || {
963 if database_name.contains(|c| ['"', '\'', '`'].contains(&c))
964 || database_username.contains(|c| ['"', '\'', '`'].contains(&c))
965 {
966 return Err(anyhow::anyhow!(
967 "unable to delete database with escape characters"
968 ));
969 }
970
971 match database_host.get_connection(&state_clone.database).await? {
972 crate::models::database_host::DatabasePool::Mysql(pool) => {
973 let database = sqlx::query(sqlx::AssertSqlSafe(format!(
974 "DROP DATABASE IF EXISTS `{}`",
975 database_name
976 )))
977 .execute(&pool)
978 .await;
979 let user = sqlx::query(sqlx::AssertSqlSafe(format!(
980 "DROP USER IF EXISTS '{}'@'%'",
981 database_username
982 )))
983 .execute(&pool)
984 .await;
985
986 database?;
987 user?;
988 }
989 crate::models::database_host::DatabasePool::Postgres(pool) => {
990 let database = sqlx::query(sqlx::AssertSqlSafe(format!(
991 "DROP DATABASE IF EXISTS \"{}\" WITH (FORCE)",
992 database_name
993 )))
994 .execute(&pool)
995 .await;
996 let user = sqlx::query(sqlx::AssertSqlSafe(format!(
997 "DROP USER IF EXISTS \"{}\"",
998 database_username
999 )))
1000 .execute(&pool)
1001 .await;
1002
1003 database?;
1004 user?;
1005 }
1006 crate::models::database_host::DatabasePool::Mongodb(client) => {
1007 let db = client.database(&database_name);
1008
1009 let user = db
1010 .run_command(
1011 mongodb::bson::doc! { "dropUser": database_username.as_str() },
1012 )
1013 .await;
1014 let database = db.drop().await;
1015
1016 user?;
1017 database?;
1018 }
1019 }
1020
1021 Ok::<_, anyhow::Error>(())
1022 };
1023
1024 if let Err(err) = run_delete().await
1025 && !options.force
1026 {
1027 if err
1028 .downcast_ref::<sqlx::Error>()
1029 .and_then(|e| e.as_database_error())
1030 .is_some_and(|e| e.message().contains("is being accessed"))
1031 {
1032 return Err(crate::response::DisplayError::new(
1033 "this database is being accessed, unable to delete.",
1034 )
1035 .into());
1036 }
1037
1038 return Err(err);
1039 }
1040
1041 let mut transaction = state_clone.database.write().begin().await?;
1042 self_clone
1043 .delete_with_transaction(&state_clone, options, &mut transaction)
1044 .await?;
1045 transaction.commit().await?;
1046
1047 super::invalidate_cache_keys(
1048 &state_clone.database,
1049 &self_clone.cache_invalidation_keys(),
1050 )
1051 .await;
1052
1053 Ok(())
1054 })
1055 .await?
1056 }
1057}
1058
1059#[schema_extension_derive::extendible]
1060#[init_args(ServerDatabase, crate::State)]
1061#[hook_args(crate::State)]
1062#[derive(ToSchema, Serialize)]
1063#[schema(title = "AdminServerServerDatabase")]
1064pub struct AdminApiServerServerDatabase {
1065 pub uuid: uuid::Uuid,
1066 pub database_host: super::database_host::AdminApiDatabaseHost,
1067
1068 pub r#type: DatabaseType,
1069 pub host: compact_str::CompactString,
1070 pub port: i32,
1071
1072 pub name: compact_str::CompactString,
1073 pub is_locked: bool,
1074
1075 pub username: compact_str::CompactString,
1076 pub password: compact_str::CompactString,
1077
1078 pub created: chrono::DateTime<chrono::Utc>,
1079}
1080
1081#[schema_extension_derive::extendible]
1082#[init_args(ServerDatabase, crate::State)]
1083#[hook_args(crate::State)]
1084#[derive(ToSchema, Serialize)]
1085#[schema(title = "AdminServerDatabase")]
1086pub struct AdminApiServerDatabase {
1087 pub uuid: uuid::Uuid,
1088 pub server: super::server::AdminApiServer,
1089
1090 pub r#type: DatabaseType,
1091 pub host: compact_str::CompactString,
1092 pub port: i32,
1093
1094 pub name: compact_str::CompactString,
1095 pub is_locked: bool,
1096
1097 pub username: compact_str::CompactString,
1098 pub password: compact_str::CompactString,
1099
1100 pub created: chrono::DateTime<chrono::Utc>,
1101}
1102
1103#[schema_extension_derive::extendible]
1104#[init_args(ServerDatabase, crate::State)]
1105#[hook_args(crate::State)]
1106#[derive(ToSchema, Serialize)]
1107#[schema(title = "ServerDatabase")]
1108pub struct ApiServerDatabase {
1109 pub uuid: uuid::Uuid,
1110
1111 pub r#type: DatabaseType,
1112 pub host: compact_str::CompactString,
1113 pub port: i32,
1114
1115 pub name: compact_str::CompactString,
1116 pub is_locked: bool,
1117
1118 pub username: compact_str::CompactString,
1119 pub password: Option<compact_str::CompactString>,
1120
1121 pub created: chrono::DateTime<chrono::Utc>,
1122}