server/

provision.rs

//! Per-user database provisioning.
//!
//! Each user owns a dedicated Postgres database, reached via `userpool` from
//! the *name* in `users.database_name`. This module creates that database,
//! brings it up to the (DDL-only) schema with the shared migration set, and
//! returns its name for the caller to persist. The DSN is composed at
//! connection time by [`dsn_for`]; a DSN in that column would put a live
//! credential in a row (ADR 0032).
//!
//! Provisioning reuses the admin `DATABASE_URL` connection — Postgres allows a
//! connection to create a *different* database — so no separate maintenance DSN
//! is required. The only prerequisite is that the admin role holds `CREATEDB`.

use crate::db::{DBError, admin_database_url, execute_raw, migrate_url};
use rust_i18n::t;
use sqlx::types::Uuid;

/// Postgres SQLSTATE for `duplicate_database`.
const DUPLICATE_DATABASE: &str = "42P04";

/// Creates and migrates a dedicated database for `user_id`, returning its NAME.
///
/// The name — never a DSN — is what the caller stores in
/// `users.database_name`. A DSN in that column would put a live credential in
/// a row that any `SELECT *` prints; see ADR 0032.
///
/// Idempotent on the `CREATE DATABASE` step: a pre-existing database (SQLSTATE
/// `42P04`) is treated as success so a retried provision after a mid-way
/// failure reuses the same deterministically-named database rather than
/// erroring.
///
/// # Errors
///
/// - [`DBError::MissingUrl`] if `DATABASE_URL` is unset.
/// - [`DBError::NoCreateDb`] if the role lacks `CREATEDB`.
/// - [`DBError::Sqlx`] / [`DBError::Migration`] on other failures.
pub async fn create_user_database(user_id: Uuid) -> Result<String, DBError> {
    provision_user_database(user_id)
        .await
        .map(|provisioned| provisioned.database_name)
}

/// A provisioned tenant database, and whether this call is what created it.
pub struct Provisioned {
    pub database_name: String,
    /// `false` when an existing database was reused (SQLSTATE `42P04`). A
    /// caller compensating for a later failure must not drop in that case: the
    /// data is not ours to destroy.
    pub created: bool,
}

/// [`create_user_database`], reporting whether the database is newly created.
///
/// # Errors
/// As [`create_user_database`].
pub async fn provision_user_database(user_id: Uuid) -> Result<Provisioned, DBError> {
    let database_name = database_name(user_id);
    let dsn = dsn_for(&database_name)?;

    let created = create_database(&database_name).await?;

    // If migration fails after *this call* created the database, drop it so a
    // transient failure doesn't leak an orphaned (un-migrated) per-user
    // database. We must NOT drop when we reused a pre-existing database
    // (42P04): a retry whose migration fails would otherwise destroy data we
    // didn't create.
    if let Err(err) = migrate_url(&dsn).await {
        if created && let Err(drop_err) = drop_user_database(user_id).await {
            log::error!(
                "{}",
                t!("Failed to drop partially-provisioned database after migration error: %{e}",
                       e = drop_err : {})
            );
        }
        return Err(err);
    }

    Ok(Provisioned {
        database_name,
        created,
    })
}

/// Stores a user's RSA private signing key into their (already-provisioned)
/// per-user database, named by `database_name`. The private key never leaves that database.
///
/// # Errors
/// [`DBError::Sqlx`] on connect or insert failure.
pub async fn store_user_private_key(
    database_name: &str,
    private_pem_b64: &str,
) -> Result<(), DBError> {
    // Takes the database name, not a DSN: composing it here keeps connection
    // strings — and the credentials in them — inside this module.
    let pool = sqlx::postgres::PgPoolOptions::new()
        .max_connections(1)
        .acquire_timeout(std::time::Duration::from_secs(10))
        .connect(&dsn_for(database_name)?)
        .await?;
    // Upsert on the singleton constraint: the per-user DB holds exactly one
    // signing key for its owner, enforced at the DB level (see 0005), so
    // concurrent calls collide on `singleton` rather than creating duplicates.
    let result = sqlx::query(
        "INSERT INTO user_auth_keys (singleton, private_key) VALUES (TRUE, $1) \
         ON CONFLICT (singleton) DO UPDATE SET private_key = excluded.private_key",
    )
    .bind(private_pem_b64)
    .execute(&pool)
    .await;
    pool.close().await;
    result.map(|_| ()).map_err(DBError::Sqlx)
}

/// Drops a user's database, used as compensating cleanup when a step *after*
/// provisioning (e.g. the `users` row insert) fails and would otherwise orphan
/// the just-created database. Best-effort and idempotent (`IF EXISTS`).
///
/// # Errors
/// [`DBError::Sqlx`] if the `DROP DATABASE` itself errors (e.g. the database is
/// still in use); callers typically log and continue rather than failing the
/// original error path.
pub async fn drop_user_database(user_id: Uuid) -> Result<(), DBError> {
    let database_name = database_name(user_id);
    // `database_name` is `nomi_user_<hex>` (only `[a-z0-9_]`), so the quoted
    // identifier is injection-safe.
    execute_raw(&format!("DROP DATABASE IF EXISTS \"{database_name}\"")).await
}

/// Deterministic, injection-safe database name for a user. Derived purely from
/// the UUID's hex (no hyphens), so a retry targets the same database.
fn database_name(user_id: Uuid) -> String {
    format!("nomi_user_{}", user_id.simple())
}

/// Postgres SQLSTATE for `insufficient_privilege`.
const INSUFFICIENT_PRIVILEGE: &str = "42501";

/// Issues `CREATE DATABASE` on the admin connection. Returns `true` if this call
/// created the database, `false` if it already existed (SQLSTATE `42P04`, the
/// retry-safe reuse path — the caller still migrates, which is idempotent). An
/// insufficient-privilege error maps to [`DBError::NoCreateDb`]. The boolean
/// lets the caller compensate-drop ONLY databases it actually created.
async fn create_database(database_name: &str) -> Result<bool, DBError> {
    // `database_name` is `nomi_user_<hex>` — only `[a-z0-9_]`, so the quoted
    // identifier cannot be abused. CREATE DATABASE forbids bind parameters.
    let stmt = format!("CREATE DATABASE \"{database_name}\"");
    let Err(DBError::Sqlx(err)) = execute_raw(&stmt).await else {
        return Ok(true);
    };
    match err.as_database_error().and_then(|db| db.code()).as_deref() {
        Some(DUPLICATE_DATABASE) => {
            log::debug!("{}", t!("Per-user database already exists, reusing"));
            Ok(false)
        }
        Some(INSUFFICIENT_PRIVILEGE) => Err(DBError::NoCreateDb),
        _ => Err(DBError::Sqlx(err)),
    }
}

/// Replaces the database (path) component of a Postgres DSN, preserving scheme,
/// credentials, host, port, and query parameters.
///
/// Parses `scheme://authority[/path][?query]`. The authority ends at the first
/// `/` or `?` (whichever comes first), so a DSN with no path but a query
/// (`postgres://user@host?sslmode=require`) is handled correctly — the query is
/// preserved and the new db name is inserted as the path.
/// Composes the connection string for a per-user database.
///
/// The single place a tenant DSN is built. Callers hold names; only this
/// function turns one into something connectable, from the process's own
/// `DATABASE_URL`.
///
/// # Errors
/// [`DBError::MissingUrl`] if `DATABASE_URL` is unset or malformed.
pub(crate) fn dsn_for(database_name: &str) -> Result<String, DBError> {
    check_database_name(database_name)?;
    swap_database(&admin_database_url()?, database_name)
}

/// The names `dsn_for` will accept.
///
/// # Errors
/// [`DBError::UnsafeDatabaseName`] for anything outside `[a-z0-9_]{1,63}`.
pub(crate) fn check_database_name(database_name: &str) -> Result<(), DBError> {
    if is_plain_database_name(database_name) {
        Ok(())
    } else {
        Err(DBError::UnsafeDatabaseName(database_name.to_owned()))
    }
}

/// Whether a name is safe to interpolate into a DSN.
///
/// The value comes from `users.database_name`, and only `database_name()` (hex
/// from a uuid) and `current_database()` ever write it — but this composes a
/// connection string, and sqlx honours parameters like `?host=` inside one. A
/// row reading `x?host=elsewhere` would redirect the admin credential, so the
/// name is checked where it is *used* rather than trusted because of where it
/// is supposed to come from.
fn is_plain_database_name(name: &str) -> bool {
    !name.is_empty()
        && name.len() <= 63
        && name
            .chars()
            .all(|c| c.is_ascii_lowercase() || c.is_ascii_digit() || c == '_')
}

fn swap_database(base: &str, database_name: &str) -> Result<String, DBError> {
    let (scheme, rest) = base.split_once("://").ok_or(DBError::MissingUrl)?;

    // Split off the query first so a query-only DSN (no path) doesn't fold the
    // query into the authority.
    let (before_query, query) = match rest.split_once('?') {
        Some((head, q)) => (head, format!("?{q}")),
        None => (rest, String::new()),
    };

    // Whatever path the original DSN had is dropped; the authority is the part
    // before the first '/'.
    let authority = before_query.split('/').next().unwrap_or(before_query);

    Ok(format!("{scheme}://{authority}/{database_name}{query}"))
}

#[cfg(test)]
mod tests {
    use super::{is_plain_database_name, swap_database};

    #[test]
    fn a_provisioned_name_is_plain() {
        assert!(is_plain_database_name(
            "nomi_user_0e78f0a8b2c74df1bc4eb463faa44e91"
        ));
        assert!(is_plain_database_name("nomisync"));
    }

    #[test]
    fn a_name_that_could_redirect_the_connection_is_refused() {
        // sqlx honours parameters inside a connection string, so a directory row
        // carrying one would send the admin credential somewhere else.
        assert!(!is_plain_database_name("x?host=elsewhere"));
        assert!(!is_plain_database_name("x&user=postgres"));
        assert!(!is_plain_database_name("a/b"));
        assert!(!is_plain_database_name("Mixed_Case"));
        assert!(!is_plain_database_name(""));
        assert!(!is_plain_database_name(&"a".repeat(64)));
    }

    #[test]
    fn swap_database_replaces_path_component() {
        let out = swap_database("postgres://ray@localhost/maindb", "nomi_user_abc").unwrap();
        assert_eq!(out, "postgres://ray@localhost/nomi_user_abc");
    }

    #[test]
    fn swap_database_preserves_credentials_port_and_query() {
        let out = swap_database(
            "postgres://u:p@host:5432/maindb?sslmode=require",
            "nomi_user_abc",
        )
        .unwrap();
        assert_eq!(
            out,
            "postgres://u:p@host:5432/nomi_user_abc?sslmode=require"
        );
    }

    #[test]
    fn swap_database_handles_dsn_without_path_but_with_query() {
        let out = swap_database("postgres://user@host?sslmode=require", "nomi_user_abc").unwrap();
        assert_eq!(out, "postgres://user@host/nomi_user_abc?sslmode=require");
    }

    #[test]
    fn swap_database_handles_dsn_without_path_or_query() {
        let out = swap_database("postgres://user@host:5432", "nomi_user_abc").unwrap();
        assert_eq!(out, "postgres://user@host:5432/nomi_user_abc");
    }
}