Pii Leakage in Gin with Cockroachdb

Pii Leakage occurs when personally identifiable information is returned to an unauthenticated or insufficiently authorized caller. In a Gin application that uses Cockroachdb as the primary datastore, the risk typically arises from a mismatch between database permissions, query construction, and HTTP handler behavior. Cockroachdb’s PostgreSQL wire protocol and SQL semantics are compatible with many Go drivers, but the way rows are scanned and mapped into structs can inadvertently expose sensitive columns if the code does not explicitly limit them.

When a Gin handler queries Cockroachdb using broad SELECT * or omits column-level filtering in views and joins, fields such as email, phone, national_id, or internal identifiers can be returned directly into the JSON response. This becomes more likely when application-level authorization is applied at the route level only, rather than at the data-access layer. For example, an endpoint like /users/:id may rely on a simple primary-key lookup without verifying that the requesting context is allowed to view sensitive columns owned by another user or role.

The combination of Cockroachdb’s strong consistency and automatic secondary index usage can also encourage developers to assume row-level security is enforced by the database alone. However, unless tenant-aware filters or explicit policies are enforced in the Gin handler, a direct SQL query can still return rows belonging to other tenants or users. Sensitive columns included in the SELECT list, or exposed through related JOINs, will be serialized into the HTTP response if the handler does not explicitly prune them before calling c.JSON.

Another common pattern is the use of Cockroachdb’s UUID primary keys and the inadvertent inclusion of internal reference fields in API responses. Even when the database role is restricted, a developer might map all columns from a row into a struct with json tags that accidentally expose internal fields. Because Gin uses reflection to marshal structs into JSON, exported fields will be included unless they are explicitly omitted or transformed. This can result in Pii Leakage without any database-side error, as the query itself is syntactically valid and returns rows within expected constraints.

Middleware and logging practices can further compound the issue. If Gin logs request parameters, headers, or full SQL queries with bound values, Pii such as email addresses or phone numbers may be persisted in log files or exposed through debug endpoints. Cockroachdb’s execution details, including index usage and plan summaries, can also reveal schema information when combined with error messages returned to the client. Proper sanitization of Gin’s default logger and careful control of error response content are essential to prevent indirect Pii leakage through operational channels.

To mitigate Pii Leakage in Gin applications using Cockroachdb, adopt explicit column selection, row-level filtering, and strict struct scoping. Avoid SELECT * and instead list only the columns required for the operation. Use context-aware authorization checks inside handlers before executing queries, and ensure that sensitive fields are omitted from struct fields or omitted via database aliases.

Below are concrete, working examples that demonstrate secure patterns for querying Cockroachdb from Gin. These examples assume a users table with columns id, email, phone, full_name, created_at, and tenant_id, where tenant_id enforces isolation between customers or organizations.

1. Parameterized query with explicit columns and tenant isolation

// secure_user_handler.go
package handlers

import (
    "context
    "net/http
    "github.com/gin-gonic/gin
    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/google/uuid"
)

type UserPublic struct {
    ID        uuid.UUID  `json:"id"`
    FullName string     `json:"full_name"`
    CreatedAt string     `json:"created_at"`
}

func GetUserPublic(pool *pgxpool.Pool) gin.HandlerFunc {
    return func(c *gin.Context) {
        userID, err := uuid.Parse(c.Param("id"))
        if err != nil {
            c.JSON(400, gin.H{"error": "invalid_id"})
            return
        }
        tenantID, exists := c.Get("tenant_id")
        if !exists {
            c.JSON(400, gin.H{"error": "missing_tenant"})
            return
        }

        var user UserPublic
        err = pool.QueryRow(c, 
            `SELECT id, full_name, created_at
             FROM users
             WHERE id = $1 AND tenant_id = $2`,
            userID, tenantID,
        ).Scan(
            &user.ID,
            &user.FullName,
            &user.CreatedAt,
        )
        if err != nil {
            c.JSON(500, gin.H{"error": "internal_error"})
            return
        }
        c.JSON(200, user)
    }
}

2. Row-level security with JOINs and column whitelisting

// secure_profile_handler.go
package handlers

import (
    "context"
    "net/http"
    "github.com/gin-gonic/gin"
    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/google/uuid"
)

type ProfileResponse struct {
    UserID        uuid.UUID `json:"user_id"`
;    DisplayName   string    `json:"display_name"`
;    AvatarURL     string    `json:"avatar_url"`
    LastActiveAt  string    `json:"last_active_at"`
}

func GetProfile(pool *pgxpool.Pool) gin.HandlerFunc {
    return func(c *gin.Context) {
        userID, err := uuid.Parse(c.Param("user_id"))
        if err != nil {
            c.JSON(400, gin.H{"error": "invalid_user_id"})
            return
        }
        tenantID, _ := c.Get("tenant_id")

        var resp ProfileResponse
        err = pool.QueryRow(c, 
            `SELECT u.id, p.display_name, p.avatar_url, p.last_active_at
             FROM profiles p
             JOIN users u ON u.id = p.user_id
             WHERE u.id = $1 AND u.tenant_id = $2`,
            userID, tenantID,
        ).Scan(
            &resp.UserID,
            &resp.DisplayName,
            &resp.AvatarURL,
            &resp.LastActiveAt,
        )
        if err != nil {
            c.JSON(500, gin.H{"error": "internal_error"})
            return
        }
        c.JSON(200, resp)
    }
}

3. Using database views to enforce column-level exposure

// secure_view_query.go
package main

import (
    "context"
    "log"
    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/google/uuid"
)

type UserAudit struct {
    ID        uuid.UUID `json:"id"`
    FullName  string    `json:"full_name"`
    CreatedAt string    `json:"created_at"`
}

func main() {
    pool, err := pgxpool.New("postgres://user:pass@localhost/db")
    if err != nil {
        log.Fatalf("unable to connect: %v", err)
    }
    defer pool.Close()

    var audit UserAudit
    err = pool.QueryRow(context.Background(), 
        `SELECT id, full_name, created_at FROM user_audit_vw WHERE id = $1`,
        uuid.MustParse("..."),
    ).Scan(&audit.ID, &audit.FullName, &audit.CreatedAt)
    // The view user_audit_vw should include only non-sensitive columns
}

In these examples, explicit column lists prevent the inclusion of email, phone, or other sensitive fields. Tenant filtering is applied at the query level to enforce isolation. Avoid logging query parameters or raw SQL with Pii values, and ensure struct tags align precisely with the intended public schema. These Cockroachdb-aware practices reduce the likelihood of Pii Leakage in Gin services.