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/// Sets up and locks down a database role.
109///
110/// This database role is granted rds_iam to allow access to this
111/// role through AWS IAM
112///
113/// Running this requires using an account with a higher level of access
114/// than the standard db user.
115///
116/// `db` - Should be the tenant database
117/// `db_name` - Name of the tenant database
118/// `role_name` - Name of the user role to create and setup
119pub async fn create_restricted_role_aws_iam(
120    db: &DbPool,
121    db_name: &str,
122    role_name: &str,
123) -> DbResult<()> {
124    let sql = format!(
125        r#"
126-- Create database user/role per tenant for docbox api to use
127CREATE ROLE {role_name}
128LOGIN;
129
130-- Allow IAM authentication
131GRANT rds_iam TO {role_name};
132
133-- 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)
134REVOKE ALL ON DATABASE "{db_name}" FROM PUBLIC;
135
136--grant all privileges on our schema to our api user;
137GRANT ALL ON ALL TABLES IN SCHEMA public TO {role_name};
138GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO {role_name};
139GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO {role_name};
140
141-- ensure our api user can access any new objects created in future
142ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO {role_name};
143ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO {role_name};
144ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO {role_name};
145
146-- ensure our api user can connect to the db
147GRANT CONNECT ON DATABASE "{db_name}" TO {role_name};
148    "#
149    );
150
151    sqlx::raw_sql(&sql).execute(db).await?;
152
153    Ok(())
154}
155
156/// Modifies a role to make it accessible through IAM only
157/// removes the password from the role
158///
159/// `db` - Should be the root database
160/// `role_name` - Name of the user role to update
161pub async fn make_role_iam_only(db: &DbPool, role_name: &str) -> DbResult<()> {
162    let sql = format!(
163        r#"
164-- Grant IAM access to the role
165GRANT rds_iam TO {role_name};
166
167-- Remove the password from the role
168ALTER ROLE {role_name} WITH PASSWORD NULL;
169    "#
170    );
171
172    sqlx::raw_sql(&sql).execute(db).await?;
173
174    Ok(())
175}
176
177/// Delete a database role.
178///
179/// Running this requires using an account with a higher level of access
180/// than the standard db user
181pub async fn delete_role(db: &DbPool, role_name: &str) -> DbResult<()> {
182    let sql = format!(r#"DROP ROLE IF EXISTS "{role_name}";"#);
183    sqlx::raw_sql(&sql).execute(db).await?;
184
185    Ok(())
186}
docbox_database/create.rs

docbox_database/
create.rs
