internal/server/store/evolve.go

Ref: Size: 6.7 KiB History
package store

import (
	"database/sql"
	"fmt"
	"log/slog"
)

// ensureColumn adds a column to an existing table if it is not already
// present. The schema const only creates tables; columns added after a
// table shipped must go through here so old databases pick them up on Open.
//
// table, column, and decl are interpolated verbatim into the ALTER TABLE
// statement (SQLite cannot bind identifiers): pass trusted compile-time
// constants only, never caller- or request-derived strings.
func ensureColumn(db *sql.DB, table, column, decl string) error {
	var n int
	err := db.QueryRow(
		`SELECT count(*) FROM pragma_table_info(?) WHERE name = ?`, table, column,
	).Scan(&n)
	if err != nil {
		return fmt.Errorf("ensure %s.%s: %w", table, column, err)
	}
	if n > 0 {
		return nil
	}
	if _, err := db.Exec(fmt.Sprintf(`ALTER TABLE %s ADD COLUMN %s %s`, table, column, decl)); err != nil {
		return fmt.Errorf("add %s.%s: %w", table, column, err)
	}
	return nil
}

// dropTable removes a table that is no longer part of the schema, so an
// existing database stops carrying it. The schema const only creates tables;
// removing one has to go through here for the same reason adding a column does.
//
// table is interpolated verbatim (SQLite cannot bind identifiers): pass trusted
// compile-time constants only, never caller- or request-derived strings — the
// same rule ensureColumn states, and for the same reason.
func dropTable(db *sql.DB, table string) error {
	if _, err := db.Exec(fmt.Sprintf(`DROP TABLE IF EXISTS %s`, table)); err != nil {
		return fmt.Errorf("drop %s: %w", table, err)
	}
	return nil
}

// dropIndex removes an index the schema no longer declares. An index whose
// columns change is a new index under a new name plus this: SQLite's CREATE
// UNIQUE INDEX IF NOT EXISTS leaves an existing index of that name exactly as
// it was, so a redefinition under the old name would be silently ignored on
// every database that already had one.
//
// index is interpolated verbatim (SQLite cannot bind identifiers): pass trusted
// compile-time constants only, the same rule ensureColumn states.
func dropIndex(db *sql.DB, index string) error {
	if _, err := db.Exec(fmt.Sprintf(`DROP INDEX IF EXISTS %s`, index)); err != nil {
		return fmt.Errorf("drop index %s: %w", index, err)
	}
	return nil
}

// dropColumn removes a column that is no longer part of the schema, so an
// existing database stops carrying it — and stops carrying whatever was in it.
// Idempotent: a database that never had the column, or has already dropped it,
// is left alone.
//
// SQLite has dropped columns in place since 3.35, which is why this is one
// statement rather than the rebuild-and-swap dance. That matters for more than
// brevity: vms is referenced by exposures ON DELETE CASCADE, so a rebuild that
// dropped the old table would take every exposure with it, and it would have to
// restate every column added by ensureColumn or silently lose those too.
//
// table and column are interpolated verbatim (SQLite cannot bind identifiers):
// pass trusted compile-time constants only, the same rule ensureColumn states.
func dropColumn(db *sql.DB, table, column string) error {
	var n int
	err := db.QueryRow(
		`SELECT count(*) FROM pragma_table_info(?) WHERE name = ?`, table, column,
	).Scan(&n)
	if err != nil {
		return fmt.Errorf("check %s.%s: %w", table, column, err)
	}
	if n == 0 {
		return nil
	}
	if _, err := db.Exec(fmt.Sprintf(`ALTER TABLE %s DROP COLUMN %s`, table, column)); err != nil {
		return fmt.Errorf("drop %s.%s: %w", table, column, err)
	}
	slog.Info("dropped retired column", "table", table, "column", column)
	return nil
}

// rekeyRevokedSSHCerts rebuilds revoked_ssh_certs with a PRIMARY KEY over
// (tenant, serial) instead of serial alone.
//
// The table shipped keyed on serial because revocation was enforced fleet-wide:
// one row denied a serial for everyone. Scoping enforcement to the revoking
// tenant makes that key wrong in a way that FAILS OPEN — with one row per
// serial globally, the first tenant to revoke a serial takes the row, and a
// second tenant revoking the same serial is silently dropped by the ON CONFLICT
// and its certificate keeps working. Per-tenant rows remove that possibility
// rather than making it unlikely.
//
// Idempotent: it inspects the existing key and returns immediately once the
// table is already in the new shape. Every row is carried across.
func rekeyRevokedSSHCerts(db *sql.DB) error {
	keyed, err := pkColumns(db, "revoked_ssh_certs")
	if err != nil {
		return err
	}
	if len(keyed) == 0 || (len(keyed) == 2 && keyed[0] == "tenant" && keyed[1] == "serial") {
		return nil // absent (fresh schema builds it right) or already rekeyed
	}
	// Rows a pre-tenant-column binary wrote were backfilled to 'default', and the
	// default tenant is retired: no CA resolves to it, so once enforcement is
	// scoped those rows deny nothing. Say so at the moment of the migration,
	// where an operator rolling the release will see it, rather than leaving it
	// to be discovered as a certificate that started working again.
	var orphaned int
	if err := db.QueryRow(`SELECT COUNT(*) FROM revoked_ssh_certs WHERE tenant='default'`).Scan(&orphaned); err == nil && orphaned > 0 {
		slog.Warn("revocations filed under the retired 'default' tenant will no longer deny anything "+
			"now that revocation is tenant-scoped; re-revoke them under the owning tenant",
			"rows", orphaned)
	}

	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	for _, stmt := range []string{
		`CREATE TABLE revoked_ssh_certs_new (
			tenant     TEXT NOT NULL DEFAULT 'default',
			serial     INTEGER NOT NULL,
			revoked_at DATETIME NOT NULL,
			reason     TEXT NOT NULL DEFAULT '',
			PRIMARY KEY (tenant, serial)
		)`,
		`INSERT INTO revoked_ssh_certs_new(tenant, serial, revoked_at, reason)
		   SELECT tenant, serial, revoked_at, reason FROM revoked_ssh_certs`,
		`DROP TABLE revoked_ssh_certs`,
		`ALTER TABLE revoked_ssh_certs_new RENAME TO revoked_ssh_certs`,
	} {
		if _, err := tx.Exec(stmt); err != nil {
			return fmt.Errorf("rekey revoked_ssh_certs: %w", err)
		}
	}
	return tx.Commit()
}

// pkColumns returns the table's primary-key columns in key order, or nil when
// the table does not exist. table is a compile-time constant, the same rule
// ensureColumn states.
func pkColumns(db *sql.DB, table string) ([]string, error) {
	rows, err := db.Query(`SELECT name, pk FROM pragma_table_info(?) WHERE pk > 0 ORDER BY pk`, table)
	if err != nil {
		return nil, fmt.Errorf("read primary key of %s: %w", table, err)
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var name string
		var pk int
		if err := rows.Scan(&name, &pk); err != nil {
			return nil, err
		}
		out = append(out, name)
	}
	return out, rows.Err()
}