Pii Leakage in Chi with Cockroachdb

Chi is a lightweight HTTP router for Go that encourages structured routing and clear handler boundaries. When integrating CockroachDB, a distributed SQL database, developers often focus on correctness and performance while inadvertently exposing personally identifiable information (PII) through database interactions and HTTP responses. PII leakage in this context occurs when sensitive data—such as email addresses, phone numbers, government IDs, or location data—is returned to unauthenticated or insufficiently authorized clients, logged in plaintext, or transmitted without encryption.

The combination of Chi and CockroachDB can expose PII through several common patterns. If a handler queries CockroachDB using raw SQL or an ORM and directly serializes database rows into JSON responses, fields that should be redacted or omitted may be included. For example, a /users/{id} endpoint might return a user record that includes email, phone, and ssn without applying field-level filtering. Another vector is insufficient authorization checks: a handler may load a tenant or organization ID from the request context and use it in a CockroachDB query, but if the query does not enforce row-level security or tenant scoping, one user could access another user’s PII by manipulating identifiers.

CockroachDB’s PostgreSQL wire protocol compatibility means that standard SQL behaviors apply, and developers must be cautious about how queries are constructed in Chi handlers. Dynamic query building with string concatenation or insufficient parameterization can lead to information disclosure beyond PII, but the primary concern here is the exposure of sensitive data fields. Logging queries or errors that include user data—such as writing a full struct to logs—also contributes to PII leakage. Because Chi does not enforce data handling policies by itself, it is the developer’s responsibility to ensure that only necessary, anonymized, or tokenized data is returned to clients and that CockroachDB queries respect privacy boundaries.

Additionally, if the API uses unencrypted connections between the Chi service and CockroachDB (for example, during development or in misconfigured deployments), data in transit can be intercepted, leading to PII leakage. Even with TLS enabled, failing to validate certificates or using weak cipher suites can undermine confidentiality. The scanner checks for encryption in transit and proper configuration, but developers must ensure that connection strings and ORM settings do not inadvertently disable security features.

Remediation focuses on strict data selection, tenant-aware queries, and secure transport. In Chi, define explicit response structs that exclude sensitive fields rather than serializing database models directly. Use context values for tenant identification and enforce row-level filtering in every CockroachDB query.

1. Use selective serialization

Instead of returning the full database row, create a dedicated response type:

// models/user.go
package models

type UserPublic struct {
    ID   int64  `json:"id"`
    Name string `json:"name"`
}

type UserPrivate struct {
    ID        int64  `json:"id"`
    Name      string `json:"name"`
    Email     string `json:"-"` // excluded from JSON
    Phone     string `json:"-"`
    SSN       string `json:"-"`
}

In your Chi handler, map the database row to the public struct:

// handlers/user.go
package handlers

import ("net/http"
        "yourapp/models")

func GetUserPublic(w http.ResponseWriter, r *http.Request) {
    id := chi.URLParam(r, "id")
    var user models.UserPublic
    row := db.QueryRow("SELECT id, name FROM users WHERE id = $1", id)
    if err := row.Scan(&user.ID, &user.Name); err != nil {
        http.Error(w, "not found", http.StatusNotFound)
        return
    }
    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(user)
}

2. Enforce tenant scoping with context

Store tenant ID in the request context and use it in all CockroachDB queries:

// middleware/tenant.go
package middleware

import "net/http"

func TenantMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        tenantID := r.Header.Get("X-Tenant-ID")
        ctx := context.WithValue(r.Context(), "tenant_id", tenantID)
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

// handlers/data.go
package handlers

import ("database/sql"
        "net/http"
        "context")

func GetTenantData(w http.ResponseWriter, r *http.Request) {
    tenantID, ok := r.Context().Value("tenant_id").(string)
    if !ok || tenantID == "" {
        http.Error(w, "missing tenant", http.StatusBadRequest)
        return
    }
    var value string
    err := db.QueryRow("SELECT data FROM tenant_data WHERE tenant_id = $1", tenantID).Scan(&value)
    if err != nil {
        http.Error(w, "not found", http.StatusNotFound)
        return
    }
    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(map[string]string{"data": value})
}

3. Use parameterized queries and avoid logging sensitive fields

Always use placeholders to prevent accidental exposure in logs:

// bad: string concatenation risk
// good: parameterized query
stmt := "SELECT id, email FROM profiles WHERE user_id = $1"
err := db.QueryRow(stmt, userID).Scan(&id, &email)

Ensure that logging middleware does not dump request or response bodies that may contain PII. Configure structured logging to exclude fields named email, phone, or ssn.

4. Enforce encryption in transit

When initializing the CockroachDB connection in Chi, use a TLS-enabled connection string and set sslmode=verify-full:

connStr := "postgresql://user:password@host:26257/dbname?sslmode=verify-full&sslrootcert=ca.pem"
db, err = sql.Open("postgres", connStr)
if err != nil {
    log.Fatalf("failed to connect: %v", err)
}
defer db.Close()