Pii Leakage in Actix with Cockroachdb

When building Actix web services that use CockroachDB as the primary data store, PII leakage commonly arises from mismatches between ORM/query habits and the sensitivity of data stored in distributed SQL databases. CockroachDB preserves relational semantics but does not automatically redact or mask sensitive columns at query time. In Actix, if route handlers deserialize rows into structs or pass query results through templates or JSON serializers without explicit field filtering, fields such as email, phone, or national ID can be exposed through API responses, logs, or error messages.

Consider an Actix handler that fetches a user profile by ID and returns the full ORM model as JSON. If the model includes a email or ssn column and the handler does not project only safe fields, an authenticated endpoint can unintentionally disclose PII. The risk is higher when developers rely on automatic serialization derived from database columns. Even with authentication in place, missing authorization checks (BOLA/IDOR) can allow one user to request another user’s record and receive sensitive fields they should not see. Misconfigured logging in Actix middleware that prints request or response structs can also write PII to stdout or files, which may be aggregated in CockroachDB diagnostic logs.

Another leakage vector involves SQL queries constructed with string concatenation or unchecked user input in WHERE clauses. In Actix, passing raw parameters into queries without strict validation can expose PII through verbose error messages returned by CockroachDB. For example, a malformed query might return stack traces or constraint violations that include email addresses or phone numbers. If the API response is consumed by downstream services or rendered in a browser, these messages become an unintended data exposure channel. The distributed nature of CockroachDB means that query results can be served from any node; without consistent field-level filtering, replicas may return full rows that include PII.

Middleware or interceptors in Actix that inspect or modify requests can also contribute to PII leakage if they copy headers, cookies, or payload fields into logs. When combined with CockroachDB’s long-term storage for audit trails, any logged PII persists beyond the immediate request lifecycle. Developers may inadvertently rely on debug features that print entire structs, including sensitive fields, during development and forget to remove them before deployment. Because CockroachDB supports cross-region replication, replicated rows containing PII can end up in regions with different compliance expectations, increasing the scope of exposure.

To prevent PII leakage in Actix applications using CockroachDB, explicitly control which fields are read and returned. Use projection queries to select only necessary columns and map them to dedicated response structs that omit sensitive data. Avoid returning full ORM models directly from handlers. Below are concrete, syntactically correct examples that demonstrate secure patterns.

1. Projection query with a safe response DTO

Define a response struct that includes only non-sensitive fields and bind query columns by name. This ensures CockroachDB returns only the columns you need, and Actix serializes exactly those fields.

use serde::Serialize;
use sqlx::FromRow;

#[derive(Debug, Serialize, FromRow)]
pub struct UserPublic {
    pub id: i64,
    pub username: String,
    pub display_name: String,
}

#[actix_web::get("/users/{id}")]
async fn get_user_public(
    path: web::Path,
    pool: web::Data>,
) -> Result {
    let user = sqlx::query_as::<_, UserPublic>(
        "SELECT id, username, display_name FROM users WHERE id = $1",
    )
    .bind(path.id)
    .fetch_optional(pool.get_ref())
    .await?;

    match user {
        Some(u) => Ok(HttpResponse::Ok().json(u)),
        None => Ok(HttpResponse::NotFound().finish()),
    }
}

2. Explicit field masking for sensitive columns

If you must read sensitive columns for internal processing, map them into an internal struct and ensure they are never serialized in responses. Use environment-based masking for logs.

use serde::Serialize;
use sqlx::Row;

#[derive(Debug)]
struct UserInternal {
    id: i64,
    email: String,
    phone: String,
}

#[actix_web::get("/users/internal/{id}")]
async fn get_user_internal(
    path: web::Path,
    pool: web::Data>,
) -> Result {
    let row = sqlx::query(
        "SELECT id, email, phone FROM users WHERE id = $1",
    )
    .bind(path.id)
    .fetch_one(pool.get_ref())
    .await?;

    let user = UserInternal {
        id: row.get("id"),
        email: row.get("email"),
        phone: row.get("phone"),
    };

    // Use user.id for business logic, but never serialize user.email/user.phone
    // Mask before any logging
    log::info!(target: "audit", "Fetched user id={}", user.id);

    Ok(HttpResponse::Ok().json(json!({ "id": user.id })))
}

3. Parameterized queries to avoid error-based leakage

Always use bind variables instead of string interpolation to prevent CockroachDB from returning raw values in error messages. Validate inputs before constructing queries.

async fn safe_search(
    pool: web::Data>,
    username: web::Query>,
) -> Result {
    let term = username.get("q").ok_or_else(|| {
        error::ErrorBadRequest("missing query parameter")
    })?;

    // Validate input to avoid injection and verbose errors
    if term.len() > 100 {
        return Err(error::ErrorBadRequest("invalid query length"));
    }

    let rows = sqlx::query(
        "SELECT id, username FROM users WHERE username LIKE $1",
    )
    .bind(format!("%{}%", term))
    .fetch_all(pool.get_ref())
    .await?;

    let results: Vec = rows.iter().map(|r| {
        json!({ "id": r.get::("id"), "username": r.get::("username") })
    }).collect();

    Ok(HttpResponse::Ok().json(results))
}

4. Middleware hygiene to prevent log leakage

Ensure Actix middleware does not copy PII fields into logs. Redact sensitive headers or payload keys before writing structured logs.

use actix_web::dev::{ServiceRequest, ServiceResponse};
use actix_web::Error;
use std::future::{ready, Ready};

struct SanitizeLogger;

impl actix_web::middleware::Transform for SanitizeLogger
where
    S: actix_web::dev::Service, Error = Error>,
    S::Future: 'static,
    B: 'static,
{
    type Response = ServiceResponse;
    type Error = Error;
    type Transform = SanitizeLoggerMiddleware;
    type InitError = ();
    type Future = Ready>;

    fn new_transform(&self, service: S) -> Self::Future {
        ready(Ok(SanitizeLoggerMiddleware { service }))
    }
}

struct SanitizeLoggerMiddleware {
    service: S,
}

impl actix_web::dev::Service for SanitizeLoggerMiddleware
where
    S: actix_web::dev::Service, Error = Error>,
    S::Future: 'static,
    B: 'static,
{
    type Response = ServiceResponse;
    type Error = Error;
    type Future = futures::future::LocalBoxFuture<'static, Result>;

    fn poll_ready(&self, cx: &mut std::task::Context<'_>) -> std::task::Poll> {
        self.service.poll_ready(cx)
    }

    fn call(&self, req: ServiceRequest) -> Self::Future {
        // Redact PII from payload before logging
        let req_clone = req.clone();
        let fut = self.service.call(req);

        Box::pin(async move {
            let res = fut.await?;
            // Example: avoid logging headers like "X-User-Email"
            // Implement redaction as needed
            Ok(res)
        })
    }
}