fix: auth for admin

apps/chattyness-app/src/main.rs

@@ -185,10 +185,15 @@ mod server {
             leptos_options: leptos_options.clone(),
         };
 
+        // Create admin connection layer for RLS context on admin API routes
+        let admin_conn_layer =
+            chattyness_admin_ui::auth::AdminConnLayer::new(pool.clone());
+
         // Build nested API routers with their own state
         let user_api_router = chattyness_user_ui::api::api_router().with_state(user_api_state);
-        let admin_api_router =
+        let admin_api_router = chattyness_admin_ui::api::admin_api_router()
-            chattyness_admin_ui::api::admin_api_router().with_state(admin_api_state);
+            .layer(admin_conn_layer)
+            .with_state(admin_api_state);
 
         // Create RLS layer for row-level security
         let rls_layer = chattyness_user_ui::auth::RlsLayer::new(pool.clone());

apps/chattyness-owner/src/main.rs

@@ -108,6 +108,10 @@ mod server {
         // Shared assets directory for uploaded files (realm images, etc.)
         let assets_dir = Path::new("/srv/chattyness/assets");
 
+        // Create admin connection layer for RLS context
+        let admin_conn_layer =
+            chattyness_admin_ui::auth::AdminConnLayer::new(pool.clone());
+
         // Build the app
         let app = Router::new()
             // Redirect root to admin
@@ -115,7 +119,9 @@ mod server {
             // Nest API routes under /api/admin (matches frontend expectations when UI is at /admin)
             .nest(
                 "/api/admin",
-                chattyness_admin_ui::api::admin_api_router().with_state(app_state.clone()),
+                chattyness_admin_ui::api::admin_api_router()
+                    .layer(admin_conn_layer)
+                    .with_state(app_state.clone()),
             )
             // Uploaded assets (realm backgrounds, props, etc.) - must come before /static
             .nest_service("/assets/server", ServeDir::new(assets_dir.join("server")))

crates/chattyness-admin-ui/Cargo.toml

@@ -24,6 +24,7 @@ leptos_router = { workspace = true }
 axum = { workspace = true, optional = true }
 axum-extra = { workspace = true, optional = true }
 sqlx = { workspace = true, optional = true }
+tower = { workspace = true, optional = true }
 tower-sessions = { workspace = true, optional = true }
 tower-sessions-sqlx-store = { workspace = true, optional = true }
 argon2 = { workspace = true, optional = true }
@@ -52,6 +53,7 @@ ssr = [
     "dep:axum",
     "dep:axum-extra",
     "dep:sqlx",
+    "dep:tower",
     "dep:tracing",
     "dep:tower-sessions",
     "dep:tower-sessions-sqlx-store",

crates/chattyness-admin-ui/src/api/avatars.rs

@@ -11,6 +11,8 @@ use serde::Serialize;
 use sqlx::PgPool;
 use uuid::Uuid;
 
+use crate::auth::AdminConn;
+
 // =============================================================================
 // API Types
 // =============================================================================
@@ -51,15 +53,18 @@ pub async fn list_avatars(
 
 /// Create a new server avatar.
 pub async fn create_avatar(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Json(req): Json<CreateServerAvatarRequest>,
 ) -> Result<Json<CreateAvatarResponse>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Validate the request
     req.validate()?;
 
     // Check slug availability
     let slug = req.slug_or_generate();
-    let available = server_avatars::is_avatar_slug_available(&pool, &slug).await?;
+    let available = server_avatars::is_avatar_slug_available(&mut *guard, &slug).await?;
     if !available {
         return Err(AppError::Conflict(format!(
             "Avatar slug '{}' is already taken",
@@ -68,7 +73,7 @@ pub async fn create_avatar(
     }
 
     // Create the avatar
-    let avatar = server_avatars::create_server_avatar(&pool, &req, None).await?;
+    let avatar = server_avatars::create_server_avatar(&mut *guard, &req, None).await?;
 
     tracing::info!("Created server avatar: {} ({})", avatar.name, avatar.id);
 
@@ -88,20 +93,23 @@ pub async fn get_avatar(
 
 /// Update a server avatar.
 pub async fn update_avatar(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     axum::extract::Path(avatar_id): axum::extract::Path<Uuid>,
     Json(req): Json<UpdateServerAvatarRequest>,
 ) -> Result<Json<ServerAvatar>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Validate the request
     req.validate()?;
 
     // Check avatar exists
-    let existing = server_avatars::get_server_avatar_by_id(&pool, avatar_id)
+    let existing = server_avatars::get_server_avatar_by_id(&mut *guard, avatar_id)
         .await?
         .ok_or_else(|| AppError::NotFound("Avatar not found".to_string()))?;
 
     // Update the avatar
-    let avatar = server_avatars::update_server_avatar(&pool, avatar_id, &req).await?;
+    let avatar = server_avatars::update_server_avatar(&mut *guard, avatar_id, &req).await?;
 
     tracing::info!("Updated server avatar: {} ({})", existing.name, avatar_id);
 
@@ -110,16 +118,19 @@ pub async fn update_avatar(
 
 /// Delete a server avatar.
 pub async fn delete_avatar(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     axum::extract::Path(avatar_id): axum::extract::Path<Uuid>,
 ) -> Result<Json<()>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Get the avatar first to log its name
-    let avatar = server_avatars::get_server_avatar_by_id(&pool, avatar_id)
+    let avatar = server_avatars::get_server_avatar_by_id(&mut *guard, avatar_id)
         .await?
         .ok_or_else(|| AppError::NotFound("Avatar not found".to_string()))?;
 
     // Delete from database
-    server_avatars::delete_server_avatar(&pool, avatar_id).await?;
+    server_avatars::delete_server_avatar(&mut *guard, avatar_id).await?;
 
     tracing::info!("Deleted server avatar: {} ({})", avatar.name, avatar_id);
 

crates/chattyness-admin-ui/src/api/props.rs

@@ -15,6 +15,8 @@ use sqlx::PgPool;
 use std::path::PathBuf;
 use uuid::Uuid;
 
+use crate::auth::AdminConn;
+
 // =============================================================================
 // API Types
 // =============================================================================
@@ -115,10 +117,12 @@ pub async fn list_props(
 /// Query parameters:
 /// - `force`: If true, update existing prop with same slug instead of returning 409 Conflict.
 pub async fn create_prop(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Query(query): Query<CreatePropQuery>,
     mut multipart: Multipart,
 ) -> Result<Json<CreatePropResponse>, AppError> {
+    let conn = admin_conn.0;
+
     let mut metadata: Option<CreateServerPropRequest> = None;
     let mut file_data: Option<(Vec<u8>, String)> = None; // (bytes, extension)
 
@@ -182,20 +186,23 @@ pub async fn create_prop(
     // Store the file first (SHA256-based, safe to run even if prop exists)
     let asset_path = store_prop_file(&file_bytes, &extension).await?;
 
+    // Acquire the connection for database operations
+    let mut guard = conn.acquire().await;
+
     let prop = if query.force {
         // Force mode: upsert (insert or update)
-        props::upsert_server_prop(&pool, &metadata, &asset_path, None).await?
+        props::upsert_server_prop(&mut *guard, &metadata, &asset_path, None).await?
     } else {
         // Normal mode: check availability first
         let slug = metadata.slug_or_generate();
-        let available = props::is_prop_slug_available(&pool, &slug).await?;
+        let available = props::is_prop_slug_available(&mut *guard, &slug).await?;
         if !available {
             return Err(AppError::Conflict(format!(
                 "Prop slug '{}' is already taken",
                 slug
             )));
         }
-        props::create_server_prop(&pool, &metadata, &asset_path, None).await?
+        props::create_server_prop(&mut *guard, &metadata, &asset_path, None).await?
     };
 
     let action = if query.force { "Upserted" } else { "Created" };
@@ -217,16 +224,19 @@ pub async fn get_prop(
 
 /// Delete a server prop.
 pub async fn delete_prop(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     axum::extract::Path(prop_id): axum::extract::Path<Uuid>,
 ) -> Result<Json<()>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Get the prop first to get the asset path
-    let prop = props::get_server_prop_by_id(&pool, prop_id)
+    let prop = props::get_server_prop_by_id(&mut *guard, prop_id)
         .await?
         .ok_or_else(|| AppError::NotFound("Prop not found".to_string()))?;
 
     // Delete from database
-    props::delete_server_prop(&pool, prop_id).await?;
+    props::delete_server_prop(&mut *guard, prop_id).await?;
 
     // Try to delete the file (don't fail if file doesn't exist)
     let file_path = PathBuf::from("/srv/chattyness/assets").join(&prop.asset_path);

crates/chattyness-admin-ui/src/api/realms.rs

@@ -16,6 +16,8 @@ use serde::{Deserialize, Serialize};
 use sqlx::PgPool;
 use uuid::Uuid;
 
+use crate::auth::AdminConn;
+
 /// Create realm response.
 #[derive(Debug, Serialize)]
 pub struct CreateRealmResponse {
@@ -66,6 +68,9 @@ pub async fn get_realm(
 }
 
 /// Create a new realm.
+///
+/// Note: Uses State(pool) instead of AdminConn because create_realm_with_new_owner
+/// uses transactions internally that require pool access.
 pub async fn create_realm(
     State(pool): State<PgPool>,
     Json(req): Json<OwnerCreateRealmRequest>,
@@ -110,6 +115,8 @@ pub async fn create_realm(
 }
 
 /// Update a realm.
+///
+/// Note: Uses State(pool) because owner queries use &PgPool directly.
 pub async fn update_realm(
     State(pool): State<PgPool>,
     Path(slug): Path<String>,
@@ -124,6 +131,9 @@ pub async fn update_realm(
 }
 
 /// Transfer realm ownership.
+///
+/// Note: Uses State(pool) instead of AdminConn because transfer_realm_ownership
+/// uses transactions internally that require pool access.
 pub async fn transfer_ownership(
     State(pool): State<PgPool>,
     Path(slug): Path<String>,
@@ -174,18 +184,23 @@ pub async fn list_realm_avatars(
 /// Create a new realm avatar.
 pub async fn create_realm_avatar(
     State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path(slug): Path<String>,
     Json(req): Json<CreateRealmAvatarRequest>,
 ) -> Result<Json<CreateRealmAvatarResponse>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Validate the request
     req.validate()?;
 
-    // Get realm ID
+    // Get realm ID (uses pool because owner queries require it)
     let realm = queries::get_realm_by_slug(&pool, &slug).await?;
 
     // Check slug availability
     let avatar_slug = req.slug_or_generate();
-    let available = realm_avatars::is_avatar_slug_available(&pool, realm.id, &avatar_slug).await?;
+    let available =
+        realm_avatars::is_avatar_slug_available(&mut *guard, realm.id, &avatar_slug).await?;
     if !available {
         return Err(AppError::Conflict(format!(
             "Avatar slug '{}' is already taken in this realm",
@@ -194,7 +209,7 @@ pub async fn create_realm_avatar(
     }
 
     // Create the avatar
-    let avatar = realm_avatars::create_realm_avatar(&pool, realm.id, &req, None).await?;
+    let avatar = realm_avatars::create_realm_avatar(&mut *guard, realm.id, &req, None).await?;
 
     tracing::info!(
         "Created realm avatar: {} ({}) in realm {}",
@@ -223,22 +238,26 @@ pub async fn get_realm_avatar(
 /// Update a realm avatar.
 pub async fn update_realm_avatar(
     State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path((slug, avatar_id)): Path<(String, Uuid)>,
     Json(req): Json<UpdateRealmAvatarRequest>,
 ) -> Result<Json<RealmAvatar>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Validate the request
     req.validate()?;
 
-    // Verify realm exists
+    // Verify realm exists (uses pool because owner queries require it)
     let _realm = queries::get_realm_by_slug(&pool, &slug).await?;
 
     // Check avatar exists
-    let existing = realm_avatars::get_realm_avatar_by_id(&pool, avatar_id)
+    let existing = realm_avatars::get_realm_avatar_by_id(&mut *guard, avatar_id)
         .await?
         .ok_or_else(|| AppError::NotFound("Avatar not found".to_string()))?;
 
     // Update the avatar
-    let avatar = realm_avatars::update_realm_avatar(&pool, avatar_id, &req).await?;
+    let avatar = realm_avatars::update_realm_avatar(&mut *guard, avatar_id, &req).await?;
 
     tracing::info!(
         "Updated realm avatar: {} ({}) in realm {}",
@@ -253,18 +272,22 @@ pub async fn update_realm_avatar(
 /// Delete a realm avatar.
 pub async fn delete_realm_avatar(
     State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path((slug, avatar_id)): Path<(String, Uuid)>,
 ) -> Result<Json<()>, AppError> {
-    // Verify realm exists
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
+    // Verify realm exists (uses pool because owner queries require it)
     let _realm = queries::get_realm_by_slug(&pool, &slug).await?;
 
     // Get the avatar first to log its name
-    let avatar = realm_avatars::get_realm_avatar_by_id(&pool, avatar_id)
+    let avatar = realm_avatars::get_realm_avatar_by_id(&mut *guard, avatar_id)
         .await?
         .ok_or_else(|| AppError::NotFound("Avatar not found".to_string()))?;
 
     // Delete from database
-    realm_avatars::delete_realm_avatar(&pool, avatar_id).await?;
+    realm_avatars::delete_realm_avatar(&mut *guard, avatar_id).await?;
 
     tracing::info!(
         "Deleted realm avatar: {} ({}) from realm {}",

crates/chattyness-admin-ui/src/api/scenes.rs

@@ -14,6 +14,8 @@ use sqlx::PgPool;
 use std::path::PathBuf;
 use uuid::Uuid;
 
+use crate::auth::AdminConn;
+
 // =============================================================================
 // Image Processing Helpers
 // =============================================================================
@@ -210,17 +212,20 @@ pub struct CreateSceneResponse {
 
 /// Create a new scene in a realm.
 pub async fn create_scene(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path(slug): Path<String>,
     Json(mut req): Json<CreateSceneRequest>,
 ) -> Result<Json<CreateSceneResponse>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Get the realm
-    let realm = realms::get_realm_by_slug(&pool, &slug)
+    let realm = realms::get_realm_by_slug(&mut *guard, &slug)
         .await?
         .ok_or_else(|| AppError::NotFound(format!("Realm '{}' not found", slug)))?;
 
     // Check if slug is available
-    let available = scenes::is_scene_slug_available(&pool, realm.id, &req.slug).await?;
+    let available = scenes::is_scene_slug_available(&mut *guard, realm.id, &req.slug).await?;
     if !available {
         return Err(AppError::Conflict(format!(
             "Scene slug '{}' is already taken in this realm",
@@ -249,7 +254,7 @@ pub async fn create_scene(
         }
     }
 
-    let scene = scenes::create_scene_with_id(&pool, scene_id, realm.id, &req).await?;
+    let scene = scenes::create_scene_with_id(&mut *guard, scene_id, realm.id, &req).await?;
     Ok(Json(CreateSceneResponse {
         id: scene.id,
         slug: scene.slug,
@@ -258,12 +263,15 @@ pub async fn create_scene(
 
 /// Update a scene.
 pub async fn update_scene(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path(scene_id): Path<Uuid>,
     Json(mut req): Json<UpdateSceneRequest>,
 ) -> Result<Json<Scene>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Get the existing scene to get realm_id
-    let existing_scene = scenes::get_scene_by_id(&pool, scene_id)
+    let existing_scene = scenes::get_scene_by_id(&mut *guard, scene_id)
         .await?
         .ok_or_else(|| AppError::NotFound("Scene not found".to_string()))?;
 
@@ -296,15 +304,17 @@ pub async fn update_scene(
         }
     }
 
-    let scene = scenes::update_scene(&pool, scene_id, &req).await?;
+    let scene = scenes::update_scene(&mut *guard, scene_id, &req).await?;
     Ok(Json(scene))
 }
 
 /// Delete a scene.
 pub async fn delete_scene(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path(scene_id): Path<Uuid>,
 ) -> Result<Json<()>, AppError> {
-    scenes::delete_scene(&pool, scene_id).await?;
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+    scenes::delete_scene(&mut *guard, scene_id).await?;
     Ok(Json(()))
 }

crates/chattyness-admin-ui/src/api/spots.rs

@@ -13,6 +13,8 @@ use serde::Serialize;
 use sqlx::PgPool;
 use uuid::Uuid;
 
+use crate::auth::AdminConn;
+
 /// List all spots for a scene.
 pub async fn list_spots(
     State(pool): State<PgPool>,
@@ -41,13 +43,16 @@ pub struct CreateSpotResponse {
 
 /// Create a new spot in a scene.
 pub async fn create_spot(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path(scene_id): Path<Uuid>,
     Json(req): Json<CreateSpotRequest>,
 ) -> Result<Json<CreateSpotResponse>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // Check if slug is available (if provided)
     if let Some(ref slug) = req.slug {
-        let available = spots::is_spot_slug_available(&pool, scene_id, slug).await?;
+        let available = spots::is_spot_slug_available(&mut *guard, scene_id, slug).await?;
         if !available {
             return Err(AppError::Conflict(format!(
                 "Spot slug '{}' is already taken in this scene",
@@ -56,25 +61,28 @@ pub async fn create_spot(
         }
     }
 
-    let spot = spots::create_spot(&pool, scene_id, &req).await?;
+    let spot = spots::create_spot(&mut *guard, scene_id, &req).await?;
     Ok(Json(CreateSpotResponse { id: spot.id }))
 }
 
 /// Update a spot.
 pub async fn update_spot(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path(spot_id): Path<Uuid>,
     Json(req): Json<UpdateSpotRequest>,
 ) -> Result<Json<Spot>, AppError> {
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+
     // If updating slug, check availability
     if let Some(ref new_slug) = req.slug {
-        let existing = spots::get_spot_by_id(&pool, spot_id)
+        let existing = spots::get_spot_by_id(&mut *guard, spot_id)
             .await?
             .ok_or_else(|| AppError::NotFound("Spot not found".to_string()))?;
 
         if Some(new_slug.clone()) != existing.slug {
             let available =
-                spots::is_spot_slug_available(&pool, existing.scene_id, new_slug).await?;
+                spots::is_spot_slug_available(&mut *guard, existing.scene_id, new_slug).await?;
             if !available {
                 return Err(AppError::Conflict(format!(
                     "Spot slug '{}' is already taken in this scene",
@@ -84,15 +92,17 @@ pub async fn update_spot(
         }
     }
 
-    let spot = spots::update_spot(&pool, spot_id, &req).await?;
+    let spot = spots::update_spot(&mut *guard, spot_id, &req).await?;
     Ok(Json(spot))
 }
 
 /// Delete a spot.
 pub async fn delete_spot(
-    State(pool): State<PgPool>,
+    admin_conn: AdminConn,
     Path(spot_id): Path<Uuid>,
 ) -> Result<Json<()>, AppError> {
-    spots::delete_spot(&pool, spot_id).await?;
+    let conn = admin_conn.0;
+    let mut guard = conn.acquire().await;
+    spots::delete_spot(&mut *guard, spot_id).await?;
     Ok(Json(()))
 }

crates/chattyness-admin-ui/src/auth.rs

@@ -4,6 +4,12 @@
 //! - Server staff: Uses chattyness_owner pool (bypasses RLS, full access)
 //! - Realm admins: Uses chattyness_app pool (RLS enforces permissions)
 
+#[cfg(feature = "ssr")]
+mod admin_conn;
+
+#[cfg(feature = "ssr")]
+pub use admin_conn::{AdminConn, AdminConnError, AdminConnGuard, AdminConnLayer, AdminConnection};
+
 #[cfg(feature = "ssr")]
 use axum::{
     http::StatusCode,