irdb/api/tests/Integration/Auth/SchemaSecretsAtRestTest.php

<?php

declare(strict_types=1);

namespace App\Tests\Integration\Auth;

use App\Tests\Integration\Support\AppTestCase;

/**
 * SPEC §M14.8: a best-effort sanity scan of the schema for columns whose
 * name suggests they could store unhashed credentials.
 *
 * The architectural rule is: **the api never stores passwords or raw
 * secrets**. Local-admin passwords live as Argon2id hashes in the UI's
 * env (not in the DB at all). API tokens are stored as their SHA-256
 * digest plus an 8-char prefix. OIDC, MaxMind, and IPinfo credentials
 * never touch the DB — they live in `.env` for the relevant container.
 *
 * This test catches accidental schema drift where someone adds a column
 * called `password`, `client_secret`, etc. — anything that smells like a
 * credential.
 *
 * Implementation note: DBAL's SQLite SchemaManager can't always parse
 * tables defined with raw CREATE TABLE statements that include CHECK
 * constraints (the api_tokens table does this for kind-vs-id mutual
 * exclusion). We sidestep that by reading sqlite_master + pragma
 * directly, which works for the SQLite test bootstrap regardless of
 * how the table was created.
 */
final class SchemaSecretsAtRestTest extends AppTestCase
{
    /**
     * Columns whose name matches one of these substrings is suspect.
     * Lower-cased; we match a substring inside `lower(column_name)`.
     */
    private const SUSPECT_NEEDLES = [
        'password',
        '_secret',
        'secret_',
        'license_key',
        'api_key',
        'private_key',
        'client_secret',
        'oauth_secret',
        'plaintext',
        'cleartext',
    ];

    /**
     * `<table>.<column>` (lower-case) names known to be safe under the
     * rule. Add justification comments here if a future migration needs
     * an exception. Currently empty — none of the suspect names match a
     * legitimate column.
     *
     * @var list<string>
     */
    private const ALLOWLIST_COLUMNS = [];

    public function testNoSchemaColumnLooksLikeUnhashedSecret(): void
    {
        $offenders = [];
        foreach ($this->listUserTables() as $table) {
            foreach ($this->columnsOf($table) as $column) {
                $name = strtolower((string) $column);
                $key = $table . '.' . $name;
                if (in_array($key, self::ALLOWLIST_COLUMNS, true)) {
                    continue;
                }
                if (self::matchesSuspect($name)) {
                    $offenders[] = $key;
                }
            }
        }

        self::assertSame(
            [],
            $offenders,
            "Schema contains columns whose name suggests they store unhashed secrets:\n  - "
                . implode("\n  - ", $offenders)
                . "\n\nIf any of these are intentional and store hashed/derived data, add to ALLOWLIST_COLUMNS with justification."
        );
    }

    public function testApiTokensTableStoresHashesNotRawValues(): void
    {
        // Positive assertion: the `api_tokens` table — the one place we
        // legitimately have to keep "credential-shaped" data — has the
        // expected hash + prefix columns (and no plaintext column).
        $names = array_map('strtolower', $this->columnsOf('api_tokens'));

        self::assertContains('token_hash', $names, 'api_tokens must have token_hash column');
        self::assertContains('token_prefix', $names, 'api_tokens must have token_prefix column');
        self::assertNotContains('token', $names, 'api_tokens must NOT have a raw token column');
        self::assertNotContains('token_plain', $names);
        self::assertNotContains('raw_token', $names);
    }

    /**
     * @return list<string>
     */
    private function listUserTables(): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->db->fetchAllAssociative(
            "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE 'phinxlog%'"
        );

        return array_map(static fn (array $r): string => (string) $r['name'], $rows);
    }

    /**
     * @return list<string>
     */
    private function columnsOf(string $table): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->db->fetchAllAssociative(
            sprintf('PRAGMA table_info(%s)', $this->db->quoteIdentifier($table))
        );

        return array_map(static fn (array $r): string => (string) $r['name'], $rows);
    }

    private static function matchesSuspect(string $name): bool
    {
        foreach (self::SUSPECT_NEEDLES as $needle) {
            if (str_contains($name, $needle)) {
                return true;
            }
        }

        return false;
    }
}