Replay Attack in Gin with Cockroachdb

A replay attack in a Gin service that uses Cockroachdb occurs when an attacker intercepts a valid request or token and re‑issues it to the API to gain unauthorized access or cause duplicate side effects. Because Cockroachdb is a distributed SQL database that you typically reach over the network, the risk is less about the database itself being tricked into replaying a statement and more about the API layer (Gin) not ensuring that each operation is unique and idempotent-safe.

In Gin, common triggers include endpoints that perform money transfers, state changes, or record creation based solely on a client-supplied identifier or timestamp. If the request lacks a strong nonce or replay window, an attacker can capture a signed HTTP request (for example, a POST to /transfer with a JWT and a transaction ID) and replay it. Cockroachdb’s serializable isolation and distributed nature do not prevent duplicate writes unless the application enforces uniqueness; without a server-side guard, the same transaction may be applied more than once, leading to double charges or inconsistent state.

The vulnerability is exposed when:

• The API accepts operations without a server‑generated, single‑use nonce or replay cache.
• The API relies only on client timestamps or request IDs that can be reused.
• Gin routes directly execute SQL like INSERT or UPDATE against Cockroachdb without verifying whether the operation was already performed.

Consider a Gin handler that creates a payment record by inserting into a Cockroachdb table using a client-provided transaction ID. If the client retries the request (e.g., due to a network timeout) and the server does not check for existing transaction IDs, Cockroachdb will insert a second row, potentially causing double processing. The database will accept both inserts because it sees them as valid serializable transactions; the responsibility to deduplicate lies with the Gin application.

Additionally, if the service uses JWTs with long lifetimes or allows clock skew without replay protection, an intercepted token can be reused. Cockroachdb does not manage token lifetimes; therefore, any replay prevention must be implemented in the Gin layer, for example by validating a nonce or a short‑lived, one‑time token before executing sensitive SQL statements.

To mitigate replay attacks in Gin when interacting with Cockroachdb, enforce uniqueness and idempotency at the API layer. Use server‑generated nonces, idempotency keys, and conditional SQL writes. Below are concrete, realistic examples using Cockroachdb with the pq driver in Gin.

1. Enforce idempotency keys on the server

Require clients to send an Idempotency-Key header for mutating operations. Store the key with the result in Cockroachdb and reject duplicate keys.

// main.go
package main

import (
    "context"
    "net/http"
    "time"

    "github.com/gin-gonic/gin"
    "github.com/lib/pq"
    "database/sql"
)

type IdempotencyRecord struct {
    Key         string `json:"idempotency_key"`
    Status      string `json:"status"`
    Response    string `json:"response,omitempty"`
    InsertedAt  time.Time
}

func main() {
    connStr := "postgresql://root@localhost:26257/defaultdb?sslmode=disable"
    db, _ := sql.Open("postgres", connStr)
    // Ensure the idempotency table exists (Cockroachdb compatible DDL)
    _, _ = db.Exec(`CREATE TABLE IF NOT EXISTS idempotency_keys (
        idempotency_key STRING PRIMARY KEY,
        status STRING NOT NULL,
        response JSONB,
        inserted_at TIMESTAMPTZ DEFAULT now()
    )`)

    r := gin.Default()
    r.POST("/transfer", func(c *gin.Context) {
        key := c.GetHeader("Idempotency-Key")
        if key == "" {
            c.JSON(http.StatusBadRequest, gin.H{"error": "Idempotency-Key header required"})
            return
        }

        var from, to string
        var amount float64
        if c.BindJSON(&gin.H{"from": &from, "to": &to, "amount": &amount}) != nil {
            c.JSON(http.StatusBadRequest, gin.H{"error": "invalid body"})
            return
        }

        ctx := context.Background()
        // Try to insert the idempotency key; if it already exists, we assume a prior response.
        var rec IdempotencyRecord
        err := db.QueryRowContext(ctx,
            `INSERT INTO idempotency_keys (idempotency_key, status, response) VALUES ($1, $2, $3) ON CONFLICT (idempotency_key) DO NOTHING RETURNING idempotency_key, status, response, inserted_at`,
            key, "processing", `{"message":"pending"}`).Scan(&rec.Key, &rec.Status, &rec.Response, &rec.InsertedAt)

        if err == sql.ErrNoRows {
            // Conflict means we already processed this key; fetch the stored response.
            var resp string
            var status string
            var insertedAt time.Time
            row := db.QueryRowContext(ctx, `SELECT status, response, inserted_at FROM idempotency_keys WHERE idempotency_key = $1`, key)
            if row.Err() != nil {
                c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to retrieve prior response"})
                return
            }
            _ = row.Scan(&status, &resp, &insertedAt)
            c.Data(http.StatusOK, "application/json", []byte(resp))
            return
        } else if err != nil {
            c.JSON(http.StatusInternalServerError, gin.H{"error": "database error"})
            return
        }

        // At this point, the key was inserted and we proceed with the business operation.
        // Use a conditional write to avoid duplicates at the SQL level.
        res, execErr := db.ExecContext(ctx,
            `INSERT INTO transfers (from_account, to_account, amount, created_at) VALUES ($1, $2, $3, now())`+
                ` ON CONFLICT DO NOTHING`, from, to, amount)
        if execErr != nil {
            // Rollback idempotency key on failure (optional: use a transaction for stronger guarantees).
            _ = db.ExecContext(ctx, `DELETE FROM idempotency_keys WHERE idempotency_key = $1`, key)
            c.JSON(http.StatusInternalServerError, gin.H{"error": "transfer failed"})
            return
        }
        if res.RowsAffected() == 0 {
            // This can happen if a duplicate request slipped through; treat as already processed.
            _ = db.ExecContext(ctx, `UPDATE idempotency_keys SET status=$1, response=$2 WHERE idempotency_key = $3`,
                "duplicate", `{"message":"already processed"}`, key)
            c.JSON(http.StatusOK, gin.H{"message": "already processed"})
            return
        }

        // Store success response and return.
        _ = db.ExecContext(ctx, `UPDATE idempotency_keys SET status=$1, response=$2 WHERE idempotency_key = $3`,
            "success", `{"message":"ok"}`, key)
        c.JSON(http.StatusOK, gin.H{"message": "ok"})
    })

    _ = r.Run(":8080")
}

2. Use conditional SQL with unique request identifiers

Embed a client-supplied unique request ID in your SQL upsert so Cockroachdb can reject duplicates via a uniqueness constraint.

// In a handler, ensure the request includes X-Request-Id.
_, err := db.ExecContext(ctx,
    `INSERT INTO payments (request_id, user_id, amount, created_at) VALUES ($1, $2, $3, now())`+
        ` ON CONFLICT (request_id) DO NOTHING`, reqID, userID, amount)
if err != nil {
    // handle error
}

3. Short-lived tokens and replay windows

Issue short-lived tokens or nonces and maintain a small server-side window (e.g., last 5 minutes) to reject out-of-window replays. Store processed nonce timestamps in Cockroachdb with TTL or periodic cleanup.

// Simple nonce check with a time window.
const replayWindow = 5 * time.Minute
var seenNonces = make(map[string]time.Time) // in production, use Cockroachdb or Redis.

func isReplay(nonce string, ts time.Time) bool {
    if t, ok := seenNonces[nonce]; ok {
        return ts.Sub(t) <= replayWindow
    }
    seenNonces[nonce] = ts
    return false
}

4. Secure token handling in Gin

Do not rely on client timestamps alone. Validate JWTs with short expirations and bind them to a server-side nonce store before executing Cockroachdb writes.

// Example JWT validation with a replay-aware claim check.
func validateAndStore(c *gin.Context, db *sql.DB) (bool, error) {
    tokenString := c.GetHeader("Authorization")
    // parse and validate token, extract nonce and exp
    nonce, exp, err := extractNonceAndExp(tokenString)
    if err != nil || time.Until(exp) < 0 {
        return false, err
    }
    // Check replay window using Cockroachdb.
    var count int
    err = db.QueryRowContext(c, `SELECT COUNT(*) FROM processed_nonces WHERE nonce=$1 AND created_at > now() - interval '5 minutes'`, nonce).Scan(&count)
    if err != nil || count > 0 {
        return false, nil // replay or error
    }
    _, _ = db.ExecContext(c, `INSERT INTO processed_nonces (nonce, created_at) VALUES ($1, now())`, nonce)
    return true, nil
}

These patterns ensure that even if a request is captured and re‑sent, Cockroachdb will not apply duplicate business effects, and Gin will reject the replay before reaching the database.