What causes "create desired-schema scratch db" in block/buzz?

Grant CREATEDB to the admin role or use the postgres superuser in the admin URL

create desired-schema scratch db

Error message

create desired-schema scratch db

What it means

The test creates a uniquely named scratch database with CREATE DATABASE {scratch_name} on the admin pool. The expect panics when the server rejects the statement: insufficient privilege (not CREATEDB/superuser), name collision, or CREATE DATABASE cannot run inside a transaction (sqlx runs single statements so this is rarely it) or while a template DB is in use.

Source

Thrown at crates/buzz-db/src/store/channel_members.rs:3133

        drop_scratch_db(&admin, pool, &scratch_name).await;
    }

    #[tokio::test]
    #[ignore = "requires Postgres"]
    async fn desired_schema_rejects_stale_legacy_roster_role() {
        use nostr::{EventBuilder, Keys, Kind, Tag, Timestamp};

        let admin = PgPool::connect(&admin_url().await)
            .await
            .expect("connect admin");
        let scratch_name = format!("schema_roster_role_{}", Uuid::new_v4().simple());
        sqlx::query(sqlx::AssertSqlSafe(format!(
            "CREATE DATABASE {scratch_name}"
        )))
        .execute(&admin)
        .await
        .expect("create desired-schema scratch db");
        let base_url = admin_url().await;
        let slash = base_url.rfind('/').expect("database URL has path segment");
        let scratch_url = format!("{}/{}", &base_url[..slash], scratch_name);
        let pool = PgPoolOptions::new()
            .max_connections(1)
            .connect(&scratch_url)
            .await
            .expect("connect desired-schema scratch db");
        sqlx::raw_sql(include_str!("../../../../schema/schema.sql"))
            .execute(&pool)
            .await
            .expect("apply desired-state schema");

        let db = Db::from_pool(pool.clone());
        let community_uuid = Uuid::new_v4();
        let community = CommunityId::from_uuid(community_uuid);
        let channel = Uuid::new_v4();
        let relay_keys = Keys::generate();

View on GitHub (pinned to dad5a33865)

Solutions

Grant CREATEDB to the admin role or use the postgres superuser in the admin URL
Check for the existing database: DROP DATABASE IF EXISTS {scratch_name} or pick a new name
If managed Postgres blocks CREATE DATABASE, point the test at a local Postgres that allows it
Confirm the server is writable (not in recovery / read_only=off)

Example fix

// before
sqlx::query(sqlx::AssertSqlSafe(format!("CREATE DATABASE {scratch_name}")))
    .execute(&admin).await.expect("create desired-schema scratch db");
// after
sqlx::query(sqlx::AssertSqlSafe(format!("DROP DATABASE IF EXISTS {scratch_name}"))).execute(&admin).await.expect("drop stale scratch db");
sqlx::query(sqlx::AssertSqlSafe(format!("CREATE DATABASE {scratch_name}")))
    .execute(&admin).await.expect("create desired-schema scratch db: does the admin role have CREATEDB?");

Defensive patterns

Strategy: validation

Validate before calling

let createdb: bool = sqlx::query_scalar("SELECT rolcreatedb FROM pg_roles WHERE rolname = current_user").fetch_one(&admin).await?;
assert!(createdb, "admin role lacks CREATEDB");

Type guard

async fn can_create_db(pool: &PgPool) -> bool {
    sqlx::query_scalar::<_, bool>("SELECT rolcreatedb FROM pg_roles WHERE rolname = current_user")
        .fetch_one(pool).await.unwrap_or(false)
}

Try / catch

if let Err(e) = create_db.execute(&admin).await {
    if e.as_database_error().map_or(false, |d| d.is_unique_violation()) { /* drop & retry */ }
    else { panic!("create scratch db failed: {e} — CREATEDB granted?"); }
}

Prevention

Grant CREATEDB to the test admin role
Use local Postgres for schema tests; managed clouds often restrict CREATE DATABASE
Use UUID names (as the test does) to avoid collisions

When it happens

Trigger: Executing CREATE DATABASE when the admin role lacks CREATEDB, a database with the generated name already exists (collision, though Uuid::new_v4 makes this unlikely), or the server is in recovery/read-only mode.

Common situations: Using a limited application role instead of a superuser as the admin URL; managed Postgres (RDS/Cloud SQL) that forbids CREATE DATABASE on the master user; disk-full or template1 locked states.

Related errors

AI-assisted analysis of block/buzz@dad5a33865 (2026-08-30). Data as JSON: /api/errors/9e90502c66bd5053. Report an issue: GitHub.