Session Fixation in Cockroachdb

Session fixation is an attack where an adversary forces a user to adopt a known session identifier, allowing the attacker to hijack the authenticated session. In CockroachDB, this vulnerability typically emerges in applications that use CockroachDB-backed authentication systems or session stores, particularly when session tokens are generated before authentication and not regenerated afterward.

CockroachDB itself does not manage application-layer sessions; it is a distributed SQL database. The risk arises in how applications use CockroachDB to store or validate session data. A common flawed pattern:

1. Application generates a session token (e.g., UUID) upon initial visit.
2. This token is stored in a CockroachDB table (e.g., sessions) with a user_id of NULL.
3. User authenticates; application updates the same row with user_id but does not change the session token.
4. An attacker who knew the pre-auth token can now use it to access the authenticated session.

CockroachDB-specific code paths that enable this include:

• Using INSERT ... ON CONFLICT for session upserts without token rotation: If an application uses a single query to create or update a session record without regenerating the token on privilege elevation, the original token persists.
• Connection pooling with session-level settings: CockroachDB supports session variables (SET SESSION ...). If an application uses a connection pool and reuses a physical connection that had session variables set by a previous user, a new user on that connection could inherit those settings—a form of fixation at the protocol level.
• Storing tokens in plaintext without expiration checks: A query like SELECT * FROM sessions WHERE token = $1 that doesn't also verify expires_at > now() and user_id IS NOT NULL will accept a pre-auth token post-authentication.

Example vulnerable Go code using the cockroachdb/cockroach-go driver:

// VULNERABLE: Token generated before login, reused after
func (s *Service) Login(ctx context.Context, username, password string) (string, error) {
    // 1. Generate token on first visit (e.g., via a separate endpoint)
    token := uuid.New().String()
    // 2. Store pre-auth session
    _, err := s.db.ExecContext(ctx,
        `INSERT INTO sessions (token, created_at) VALUES ($1, now()) ON CONFLICT (token) DO UPDATE SET created_at = EXCLUDED.created_at`,
        token,
    )
    if err != nil { return "", err }

    // 3. Authenticate user
    var userID int
    err = s.db.QueryRowContext(ctx,
        `SELECT id FROM users WHERE username=$1 AND password_hash=hash($2)`, username, password).Scan(&userID)
    if err != nil { return "", err }

    // 4. FLAW: Update same token with user_id, DO NOT ROTATE TOKEN
    _, err = s.db.ExecContext(ctx,
        `UPDATE sessions SET user_id=$1, updated_at=now() WHERE token=$2`,
        userID, token,
    )
    return token, err // Attacker with pre-auth token now has access
}

Detecting session fixation in a CockroachDB-backed API requires testing the lifecycle of a session token across authentication. middleBrick's Authentication and Property Authorization checks actively probe for this by:

1. Obtaining a pre-authentication session token (e.g., from a /session/init endpoint that creates a session record).
2. Using that token to authenticate (e.g., via /login).
3. Re-presenting the same token in subsequent authenticated requests to test if it remains valid.
4. Querying the CockroachDB-backed session store (if exposed via an API endpoint) to inspect token rotation behavior.

middleBrick's scanner simulates this attack pattern automatically. It flags a vulnerability if:

• The same session token is accepted before and after authentication.
• The session record's user_id field transitions from NULL to a valid user ID without a token change.
• Session endpoints lack proper token regeneration responses (e.g., no new Set-Cookie header or JSON token field post-login).

You can also manually verify using middlebrick CLI against an API endpoint that interfaces with CockroachDB:

# Scan the API; middleBrick will test session fixation as part of its 12 checks
middlebrick scan https://api.example.com/v1 --output json | jq '.findings[] | select(.category=="Authentication")'

The report will include a finding like: "Session token not regenerated upon authentication (CWE-384)" with the specific request/response evidence and a severity rating based on the exploitability and impact.

Remediation requires ensuring a new, cryptographically random session token is generated after any privilege escalation (like login). With CockroachDB, implement this at the application layer using its transactional strengths to guarantee atomic updates.

Pattern 1: Token Regeneration with Transaction

Use a CockroachDB transaction to invalidate the old session and create a new one in a single atomic operation. This prevents race conditions where the old token might still be used.

func (s *Service) Login(ctx context.Context, username, password, oldToken string) (string, error) {
    // Authenticate first
    var userID int
    err := s.db.QueryRowContext(ctx,
        `SELECT id FROM users WHERE username=$1 AND password_hash=hash($2)`, username, password).Scan(&userID)
    if err != nil { return "", err }

    // Begin transaction for atomic session update
    txn, err := s.db.BeginTx(ctx, nil)
    if err != nil { return "", err }
    defer txn.Rollback() // Safe to call even if Commit succeeds

    // 1. Invalidate old session (if exists and is unauthenticated)
    _, err = txn.ExecContext(ctx,
        `DELETE FROM sessions WHERE token=$1 AND user_id IS NULL`,
        oldToken,
    )
    if err != nil { return "", err }

    // 2. Generate NEW token
    newToken := uuid.New().String()
    _, err = txn.ExecContext(ctx,
        `INSERT INTO sessions (token, user_id, created_at, expires_at) VALUES ($1, $2, now(), now() + INTERVAL '24 hours')`,
        newToken, userID,
    )
    if err != nil { return "", err }

    err = txn.Commit()
    return newToken, err
}

Pattern 2: Using CockroachDB's RETURNING clause for immediate token use

When creating a new session, use INSERT ... RETURNING to get the token directly from the database, ensuring the token stored is the one issued.

func createSession(ctx context.Context, userID int) (string, error) {
    var token string
    err := s.db.QueryRowContext(ctx,
        `INSERT INTO sessions (token, user_id, created_at) VALUES (gen_random_uuid(), $1, now()) RETURNING token`,
        userID,
    ).Scan(&token)
    return token, err // This token was generated by the DB and is guaranteed fresh
}

Additional Hardening:

• Always include expires_at and enforce it in every query: SELECT ... WHERE token=$1 AND expires_at > now().
• Use CockroachDB's ROW_SECURITY (RLS) policies if your session table is queried via a shared service account, to ensure a session can only be accessed by its owning user ID.
• Set idle_in_transaction_session_timeout at the database or session level to clean up stale connections that might retain session state.

After applying fixes, re-scan with middleBrick's CLI or GitHub Action to verify the session fixation finding is resolved.