docbox_database/
create.rs

1//! # Create
2//!
3//! Tenant creation and database setup logic
4
5use crate::{DbPool, DbResult};
6
7/// Creates a new database.
8///
9/// Running this requires using an account with a higher level of access
10/// than the standard db user
11pub async fn create_database(db: &DbPool, db_name: &str) -> DbResult<()> {
12    let sql = format!(r#"CREATE DATABASE "{db_name}";"#);
13    sqlx::raw_sql(&sql).execute(db).await?;
14
15    Ok(())
16}
17
18/// Check if a database with the provided `db_name` exists
19pub async fn check_database_exists(db: &DbPool, db_name: &str) -> DbResult<bool> {
20    let result = sqlx::query("SELECT 1 FROM pg_database WHERE datname = $1")
21        .bind(db_name)
22        .fetch_optional(db)
23        .await?;
24
25    Ok(result.is_some())
26}
27
28/// Check if a table with the provided `table_name` exists
29pub async fn check_database_table_exists(db: &DbPool, table_name: &str) -> DbResult<bool> {
30    let result = sqlx::query(
31        "SELECT 1 FROM pg_catalog.pg_tables
32        WHERE schemaname = 'public'
33          AND tablename  = $1",
34    )
35    .bind(table_name)
36    .fetch_optional(db)
37    .await?;
38
39    Ok(result.is_some())
40}
41
42/// Check if a database role with the provided `role_name` exists
43pub async fn check_database_role_exists(db: &DbPool, role_name: &str) -> DbResult<bool> {
44    let result = sqlx::query("SELECT 1 FROM pg_roles WHERE rolname = $1")
45        .bind(role_name)
46        .fetch_optional(db)
47        .await?;
48
49    Ok(result.is_some())
50}
51
52/// Delete a database.
53///
54/// Running this requires using an account with a higher level of access
55/// than the standard db user
56pub async fn delete_database(db: &DbPool, db_name: &str) -> DbResult<()> {
57    let sql = format!(r#"DROP DATABASE "{db_name}";"#);
58    sqlx::raw_sql(&sql).execute(db).await?;
59
60    Ok(())
61}
62
63/// Sets up and locks down a database role.
64///
65/// Running this requires using an account with a higher level of access
66/// than the standard db user
67///
68/// `db` - Should be the tenant database
69/// `db_name` - Name of the tenant database
70/// `role_name` - Name of the user role to create and setup
71/// `password` - Password to assign the user role
72pub async fn create_restricted_role(
73    db: &DbPool,
74    db_name: &str,
75    role_name: &str,
76    password: &str,
77) -> DbResult<()> {
78    let sql = format!(
79        r#"
80-- Create database user/role per tenant for docbox api to use
81CREATE ROLE {role_name}
82LOGIN
83PASSWORD '{password}';
84
85-- prevent other pg users with 'public' role from being able to access this database (should have already been done when db was created, but just in case)
86REVOKE ALL ON DATABASE "{db_name}" FROM PUBLIC;
87
88--grant all privileges on our schema to our api user;
89GRANT ALL ON ALL TABLES IN SCHEMA public TO {role_name};
90GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO {role_name};
91GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO {role_name};
92
93-- ensure our api user can access any new objects created in future
94ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO {role_name};
95ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO {role_name};
96ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO {role_name};
97
98-- ensure our api user can connect to the db
99GRANT CONNECT ON DATABASE "{db_name}" TO {role_name};
100    "#
101    );
102
103    sqlx::raw_sql(&sql).execute(db).await?;
104
105    Ok(())
106}
107
108/// Delete a database role.
109///
110/// Running this requires using an account with a higher level of access
111/// than the standard db user
112pub async fn delete_role(db: &DbPool, role_name: &str) -> DbResult<()> {
113    let sql = format!(r#"DROP ROLE IF EXISTS "{role_name}";"#);
114    sqlx::raw_sql(&sql).execute(db).await?;
115
116    Ok(())
117}
docbox_database/create.rs

docbox_database/
create.rs
