CosmicClash/server/store/session_sql.go

package store

import (
	"context"
	"crypto/rand"
	"crypto/sha256"
	"crypto/subtle"
	"database/sql"
	"encoding/hex"
	"time"

	"github.com/cosmic-clash/cosmic-clash/server/domain"
)

const (
	// SessionInsertSQL refuses to mint a session for an actively banned
	// identity. Enforcing it here rather than in the login adapter makes the
	// ban part of the durable authentication transaction, so it holds for any
	// present or future adapter rather than depending on each one to
	// re-implement the policy.
	SessionInsertSQL = `INSERT INTO sessions (session_id, player_id, token_digest, expires_at, created_at)
SELECT $1, $2, $3, $4, $5
FROM identities i
WHERE i.player_id = $2
  AND (i.banned_until IS NULL OR i.banned_until <= $5)`
	// SessionSelectSQL joins the identity so an existing session stops working
	// the moment a ban lands. Without this a banned player kept full access
	// through every already-issued session until it expired.
	SessionSelectSQL = `SELECT s.session_id, s.player_id, s.token_digest, s.expires_at, s.revoked_at,
    i.banned_until
FROM sessions s
JOIN identities i ON i.player_id = s.player_id
WHERE s.session_id = $1`
	SessionRevokeSQL = `UPDATE sessions SET revoked_at = COALESCE(revoked_at, $2)
WHERE session_id = $1`
	// SessionRevokeAllForPlayerSQL is applied in the same transaction as a ban
	// so there is no window in which the ban is durable but the player's
	// existing sessions still authenticate on another replica.
	SessionRevokeAllForPlayerSQL = `UPDATE sessions SET revoked_at = COALESCE(revoked_at, $2)
WHERE player_id = $1 AND revoked_at IS NULL`
	IdentityBanSQL = `UPDATE identities SET banned_until = $2, ban_reason = $3
WHERE player_id = $1`
	IdentityBanClearSQL = `UPDATE identities SET banned_until = NULL, ban_reason = NULL
WHERE player_id = $1`
)

// PostgresSessions persists only a SHA-256 token digest. The plaintext token
// is returned once by Issue and is never sent to SQL or logged by this layer.
type PostgresSessions struct{ DB *sql.DB }

func (s PostgresSessions) Issue(ctx context.Context, playerID string, lifetime time.Duration, now time.Time) (domain.Session, string, error) {
	if s.DB == nil || playerID == "" || lifetime <= 0 || now.IsZero() {
		return domain.Session{}, "", domain.ErrSessionRejected
	}
	sessionID, err := opaqueSessionValue()
	if err != nil {
		return domain.Session{}, "", err
	}
	token, err := opaqueSessionValue()
	if err != nil {
		return domain.Session{}, "", err
	}
	session := domain.Session{SessionID: sessionID, PlayerID: playerID, ExpiresAt: now.Add(lifetime)}
	digest := sha256.Sum256([]byte(token))
	result, err := s.DB.ExecContext(ctx, SessionInsertSQL, session.SessionID, session.PlayerID, digest[:], session.ExpiresAt, now)
	if err != nil {
		return domain.Session{}, "", err
	}
	inserted, err := result.RowsAffected()
	if err != nil {
		return domain.Session{}, "", err
	}
	if inserted != 1 {
		// Either no such identity or an active ban; both refuse issuance.
		return domain.Session{}, "", domain.ErrSessionRejected
	}
	return session, token, nil
}

func (s PostgresSessions) Authenticate(ctx context.Context, sessionID, token string, now time.Time) (domain.Session, error) {
	if s.DB == nil || sessionID == "" || token == "" || now.IsZero() {
		return domain.Session{}, domain.ErrSessionRejected
	}
	var session domain.Session
	var digestBytes []byte
	var revokedAt sql.NullTime
	var bannedUntil sql.NullTime
	if err := s.DB.QueryRowContext(ctx, SessionSelectSQL, sessionID).Scan(&session.SessionID, &session.PlayerID, &digestBytes, &session.ExpiresAt, &revokedAt, &bannedUntil); err != nil {
		return domain.Session{}, domain.ErrSessionRejected
	}
	provided := sha256.Sum256([]byte(token))
	if len(digestBytes) != sha256.Size || subtle.ConstantTimeCompare(digestBytes, provided[:]) != 1 || (revokedAt.Valid && !revokedAt.Time.IsZero()) || !now.Before(session.ExpiresAt) {
		return domain.Session{}, domain.ErrSessionRejected
	}
	// Checked on every authenticated request, not only at issuance, so a ban
	// takes effect immediately across every replica rather than at expiry.
	if bannedUntil.Valid && now.Before(bannedUntil.Time) {
		return domain.Session{}, domain.ErrIdentityBanned
	}
	return session, nil
}

func (s PostgresSessions) Revoke(ctx context.Context, sessionID string, now time.Time) error {
	if s.DB == nil || sessionID == "" || now.IsZero() {
		return domain.ErrSessionRejected
	}
	result, err := s.DB.ExecContext(ctx, SessionRevokeSQL, sessionID, now)
	if err != nil {
		return err
	}
	changed, err := result.RowsAffected()
	if err != nil || changed != 1 {
		return domain.ErrSessionRejected
	}
	return nil
}

func opaqueSessionValue() (string, error) {
	value := make([]byte, 32)
	if _, err := rand.Read(value); err != nil {
		return "", err
	}
	return hex.EncodeToString(value), nil
}

// ApplyIdentityBan makes a ban and the revocation of that identity's existing
// sessions one atomic change. Applying the ban alone would leave a window in
// which another control-plane replica still authenticates an already-issued
// session, which is exactly the gap that made banned_until dead schema.
//
// bannedUntil is the instant the ban lifts; a zero value clears the ban.
func ApplyIdentityBan(ctx context.Context, db *sql.DB, playerID, reason string, bannedUntil, now time.Time) error {
	if db == nil || playerID == "" || now.IsZero() {
		return domain.ErrSessionRejected
	}
	if !bannedUntil.IsZero() && !bannedUntil.After(now) {
		return domain.ErrSessionRejected
	}
	return RunSerializable(ctx, db, DefaultSerializableAttempts, func(ctx context.Context, tx *sql.Tx) error {
		var result sql.Result
		var err error
		if bannedUntil.IsZero() {
			result, err = tx.ExecContext(ctx, IdentityBanClearSQL, playerID)
		} else {
			result, err = tx.ExecContext(ctx, IdentityBanSQL, playerID, bannedUntil, reason)
		}
		if err != nil {
			return err
		}
		changed, err := result.RowsAffected()
		if err != nil {
			return err
		}
		if changed != 1 {
			return domain.ErrSessionRejected
		}
		if bannedUntil.IsZero() {
			// Unbanning does not resurrect revoked sessions; the player signs
			// in again and receives a fresh one.
			return nil
		}
		_, err = tx.ExecContext(ctx, SessionRevokeAllForPlayerSQL, playerID, now)
		return err
	})
}

// IdentityUpsertSQL resolves a verified Steam ID to a durable player ID,
// creating the identity on first sign-in. The player ID is derived by the
// backend and never supplied by the client.
const IdentityUpsertSQL = `INSERT INTO identities (player_id, steam_id)
VALUES ($1, $2)
ON CONFLICT (steam_id) DO UPDATE SET steam_id = EXCLUDED.steam_id
RETURNING player_id`

// ResolveSteamIdentity returns the player ID for a verified Steam ID. The
// proposed ID is used only when this Steam ID has never signed in before; an
// existing identity keeps the player ID it already had, so a returning player
// keeps their ratings and penalties.
func ResolveSteamIdentity(ctx context.Context, db *sql.DB, steamID, proposedPlayerID string) (string, error) {
	if db == nil || steamID == "" || proposedPlayerID == "" {
		return "", domain.ErrTicketRejected
	}
	var playerID string
	if err := db.QueryRowContext(ctx, IdentityUpsertSQL, proposedPlayerID, steamID).Scan(&playerID); err != nil {
		return "", err
	}
	return playerID, nil
}