Pii Leakage in Fiber with Cockroachdb

When building APIs with Fiber and CockroachDB, PII leakage often occurs at the intersection of application-layer routing and database-level data handling. Because CockroachDB is a distributed SQL database that enforces strong consistency and supports JSONB columns, developers may inadvertently expose sensitive fields through insecure query patterns or serialization logic. For example, a Fiber route that constructs SQL queries by string concatenation can lead to excessive data retrieval, including columns containing personally identifiable information such as email, phone, or national ID values.

Consider a typical user profile endpoint that queries CockroachDB using a raw SQL string without column restriction:

// Insecure: selects all columns, potentially exposing PII
app.Get("/user/:id", func(c *fiber.Ctx) error {
    var user map[string]interface{}
    rows, err := db.Query("SELECT * FROM users WHERE id = $1", c.Params("id"))
    if err != nil {
        return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": err.Error()})
    }
    defer rows.Close()
    for rows.Next() {
        // Risk: sensitive columns like ssn, salary, or contact_info may be included
        cols, _ := rows.Columns()
        values := make([]interface{}, len(cols))
        // simplified handling
        user[cols[0]] = values[0]
    }
    return c.JSON(user)
})

In this pattern, even if the client expects only a subset of fields, the backend returns all columns stored in the table. If columns such as ssn, contact_info, or password_hash exist, they are transmitted to the client, resulting in PII leakage. This becomes more pronounced when combined with CockroachDB’s JSONB columns, where nested objects may contain sensitive attributes that are not validated or redacted before response serialization.

Additionally, improper use of ORM or query builders can exacerbate the issue. For instance, scanning query results into a struct that embeds sensitive fields without explicit omission increases exposure risk. If logging or error messages inadvertently include query results or database errors that contain PII, the data may be exposed through server logs or client error responses. The distributed nature of CockroachDB does not inherently mitigate these risks; without strict column selection and output filtering, PII can be exposed across nodes and during replication.

Furthermore, developers might rely on automatic struct mapping that includes all database columns. If the struct definition contains fields like Email or PhoneNumber without appropriate access controls, the API can unintentionally serve PII to unauthorized clients. This is especially critical in multi-tenant deployments where tenant isolation is not enforced at the query level, allowing one client to request data that belongs to another if identifiers are not properly validated.

To prevent PII leakage in Fiber applications using CockroachDB, explicitly select only required columns and avoid mapping unrestricted database rows directly to responses. Use prepared statements and structured scanning to control which fields are exposed. Below are concrete, secure examples tailored for CockroachDB.

First, define a minimal user struct that includes only non-sensitive fields:

type PublicUser struct {
    ID   int64  `json:"id"`
    Name string `json:"name"`
    Role string `json:"role"`
}

Then, query with explicit column selection and use parameterized statements to avoid injection and data overexposure:

app.Get("/user/:id", func(c *fiber.Ctx) error {
    var user PublicUser
    // Explicit column list prevents PII leakage
    row := db.QueryRow("SELECT id, name, role FROM users WHERE id = $1", c.Params("id"))
    if err := row.Scan(&user.ID, &user.Name, &user.Role); err != nil {
        return c.Status(fiber.StatusNotFound).JSON(fiber.Map{"error": "user not found"})
    }
    return c.JSON(user)
})

For JSONB columns that may contain nested PII, deserialize selectively and redact sensitive keys before sending the response:

app.Get("/profile/:id", func(c *fiber.Ctx) error {
    var result struct {
        ID    int64          `json:"id"`
        Traits json.RawMessage `json:"traits"` // JSONB column
    }
    if err := db.QueryRow("SELECT id, traits FROM profiles WHERE id = $1", c.Params("id")).Scan(&result.ID, &result.Traits); err != nil {
        return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": err.Error()})
    }

    // Redact sensitive keys from JSONB before returning
    var traitsMap map[string]interface{}
    if err := json.Unmarshal(result.Traits, &traitsMap); err != nil {
        return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": "invalid traits"})
    }
    delete(traitsMap, "ssn")
    delete(traitsMap, "contact_info")
    result.Traits, _ = json.Marshal(traitsMap)
    return c.JSON(result)
})

When using ORM-like patterns, ensure that queries are scoped to exclude sensitive columns and that struct tags do not inadvertently expose fields. With these practices, the API maintains data utility while reducing the risk of PII leakage specific to CockroachDB’s schema and JSONB capabilities.