Skip to content

A native macOS / windows / Linux database IDE built with Swift and AppKit. Connect to PostgreSQL, MySQL, SQLite, and Redis from a single app with a fast, keyboard-driven interface.

C++
20.0 KB
Raw Blame
// PostgreSQLAdapterIntegrationTests.swift
//
// Integration tests for PR #43 against two local Postgres instances:
//   :55434  ssl=off  (no-SSL)
//   :55435  ssl=on   (cert CN=localhost, signed by /tmp/gridex-pg-certs/ca.crt)
//
// Skipped automatically when those instances aren't reachable (CI / dev machines
// without test PG running). To bring them up locally, see scripts/test-pg-setup.sh
// (added in this PR) or the inline shell in PR #43's review thread.

import XCTest
@testable import Gridex

final class PostgreSQLAdapterIntegrationTests: XCTestCase {

    // MARK: - Endpoints

    private let noSSLPort = 5432  // :55434 in shell, but Swift tests open it via 127.0.0.1
    private let sslPort   = 5432  // overridden below
    private let caPath    = "/tmp/gridex-pg-certs/ca.crt"

    private func makeBaseConfig(host: String, port: Int, sslMode: SSLMode,
                                caPath: String? = nil) -> ConnectionConfig {
        ConnectionConfig(
            id: UUID(),
            name: "test",
            databaseType: .postgresql,
            host: host,
            port: port,
            database: "postgres",
            username: "postgres",
            sslEnabled: sslMode != .disabled,
            sslMode: sslMode,
            sslCACertPath: caPath
        )
    }

    private func skipIfNoServer(port: Int) throws {
        // Fast TCP probe — skip the test if the test PG isn't running.
        let s = socket(AF_INET, SOCK_STREAM, 0)
        defer { close(s) }
        var addr = sockaddr_in()
        addr.sin_family = sa_family_t(AF_INET)
        addr.sin_port = in_port_t(port).bigEndian
        addr.sin_addr.s_addr = inet_addr("127.0.0.1")
        let rc = withUnsafePointer(to: &addr) { ptr -> Int32 in
            ptr.withMemoryRebound(to: sockaddr.self, capacity: 1) { sa in
                connect(s, sa, socklen_t(MemoryLayout<sockaddr_in>.size))
            }
        }
        try XCTSkipIf(rc != 0,
                      "PG not reachable on 127.0.0.1:\(port) — skipping integration test")
    }

    // MARK: - Mode × server matrix

    // ---- vs no-SSL server (:55434) ----

    func test_disabled_vs_noSSLServer_succeeds() async throws {
        try skipIfNoServer(port: 55434)
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .disabled)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)
        XCTAssertTrue(adapter.isConnected)
        try await adapter.disconnect()
    }

    func test_preferred_vs_noSSLServer_succeeds_overPlaintext() async throws {
        try skipIfNoServer(port: 55434)
        // libpq prefer: try TLS, fall back to plaintext when server refuses.
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .preferred)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)
        XCTAssertTrue(adapter.isConnected, "preferred must fall back to plaintext")
        try await adapter.disconnect()
    }

    func test_required_vs_noSSLServer_FAILS() async throws {
        try skipIfNoServer(port: 55434)
        // libpq REQUIRED must reject a server that can't TLS.
        // This is the security regression PR #43 is fixing — pre-PR it silently
        // succeeded over plaintext because adapter used .prefer(tls).
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .required)
        let adapter = PostgreSQLAdapter()
        do {
            try await adapter.connect(config: cfg, password: nil)
            try? await adapter.disconnect()
            XCTFail("REQUIRED must reject a server with ssl=off; pre-PR this silently succeeded over plaintext")
        } catch {
            // expected
        }
    }

    func test_verifyCA_vs_noSSLServer_FAILS() async throws {
        try skipIfNoServer(port: 55434)
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434,
                                 sslMode: .verifyCA, caPath: caPath)
        let adapter = PostgreSQLAdapter()
        do {
            try await adapter.connect(config: cfg, password: nil)
            try? await adapter.disconnect()
            XCTFail("VERIFY_CA must reject a server with ssl=off")
        } catch { /* expected */ }
    }

    func test_verifyIdentity_vs_noSSLServer_FAILS() async throws {
        try skipIfNoServer(port: 55434)
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434,
                                 sslMode: .verifyIdentity, caPath: caPath)
        let adapter = PostgreSQLAdapter()
        do {
            try await adapter.connect(config: cfg, password: nil)
            try? await adapter.disconnect()
            XCTFail("VERIFY_IDENTITY must reject a server with ssl=off")
        } catch { /* expected */ }
    }

    // ---- vs SSL server (:55435) ----

    func test_disabled_vs_SSLServer_works() async throws {
        try skipIfNoServer(port: 55435)
        // PG with ssl=on still accepts plaintext clients by default (pg_hba allows both).
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55435, sslMode: .disabled)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)
        XCTAssertTrue(adapter.isConnected)
        try await adapter.disconnect()
    }

    func test_required_vs_SSLServer_succeeds() async throws {
        try skipIfNoServer(port: 55435)
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55435, sslMode: .required)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)
        XCTAssertTrue(adapter.isConnected)
        try await adapter.disconnect()
    }

    func test_verifyCA_withCorrectCA_succeeds_evenAtIPHost() async throws {
        try skipIfNoServer(port: 55435)
        // verifyCA: chain must validate, but hostname mismatch is OK
        // (cert is for "localhost", connecting via "127.0.0.1").
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55435,
                                 sslMode: .verifyCA, caPath: caPath)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)
        XCTAssertTrue(adapter.isConnected)
        try await adapter.disconnect()
    }

    func test_verifyIdentity_withMismatchedHostname_FAILS() async throws {
        try skipIfNoServer(port: 55435)
        // Cert CN=localhost, connect via 127.0.0.1 → must reject (matches psql verify-full).
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55435,
                                 sslMode: .verifyIdentity, caPath: caPath)
        let adapter = PostgreSQLAdapter()
        do {
            try await adapter.connect(config: cfg, password: nil)
            try? await adapter.disconnect()
            XCTFail("VERIFY_IDENTITY must reject when host doesn't match cert SAN/CN")
        } catch { /* expected */ }
    }

    func test_verifyIdentity_withMatchingHostname_succeeds() async throws {
        try skipIfNoServer(port: 55435)
        let cfg = makeBaseConfig(host: "localhost", port: 55435,
                                 sslMode: .verifyIdentity, caPath: caPath)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)
        XCTAssertTrue(adapter.isConnected)
        try await adapter.disconnect()
    }

    func test_verifyCA_withWrongCA_FAILS() async throws {
        try skipIfNoServer(port: 55435)
        // Provide an unrelated cert as the CA — must reject.
        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55435,
                                 sslMode: .verifyCA,
                                 caPath: "/tmp/gridex-pg-certs/server-wrongcn.crt")
        let adapter = PostgreSQLAdapter()
        do {
            try await adapter.connect(config: cfg, password: nil)
            try? await adapter.disconnect()
            XCTFail("VERIFY_CA must reject when supplied CA does not sign the server cert")
        } catch { /* expected */ }
    }

    // MARK: - Bug #1 — race fix

    func test_connect_doesNotRaiseConnectionPoolError_under_repeatedRapidConnects() async throws {
        try skipIfNoServer(port: 55434)
        // Pre-PR: spawning Task { run() } and immediately querying could surface
        // _ConnectionPoolModule.ConnectionPoolError on slow handshakes.
        // Repeat 20 times; if even one fails for a reason other than expected
        // server-side error, the gate didn't hold.
        for i in 1...20 {
            let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .disabled)
            let adapter = PostgreSQLAdapter()
            do {
                try await adapter.connect(config: cfg, password: nil)
                try await adapter.disconnect()
            } catch {
                XCTFail("iteration \(i) raised: \(error)")
                return
            }
        }
    }

    // MARK: - HighGo Secure regressions

    // Regression test for HighGo Secure 4.5+: information_schema.tables can return
    // duplicate rows for the same physical table, which made the macOS sidebar
    // render repeated names. listTables must dedupe by reading pg_class directly.
    //
    // Target instance requirements:
    //   - Must allow non-SSL connections (test connects with sslMode = .disabled).
    //   - Schema `public` must contain at least one user-visible table whose name
    //     starts with `hg_` (e.g. the HighGo audit module's `hg_t_audit_log`),
    //     i.e. the HighGo audit module must be enabled.
    //
    // Run with: GRIDEX_HIGHGO_HOST=... GRIDEX_HIGHGO_PORT=... GRIDEX_HIGHGO_DATABASE=...
    //            GRIDEX_HIGHGO_USER=... GRIDEX_HIGHGO_PASSWORD=... swift test ...
    func test_listTables_usesPgCatalogOnHighGoSecure() async throws {
        let env = ProcessInfo.processInfo.environment
        guard let host = env["GRIDEX_HIGHGO_HOST"],
              let port = Int(env["GRIDEX_HIGHGO_PORT"] ?? ""),
              let database = env["GRIDEX_HIGHGO_DATABASE"],
              let username = env["GRIDEX_HIGHGO_USER"],
              let password = env["GRIDEX_HIGHGO_PASSWORD"] else {
            throw XCTSkip("Set GRIDEX_HIGHGO_* to run the HighGo Secure catalog regression test")
        }

        let cfg = ConnectionConfig(
            id: UUID(),
            name: "highgo-catalog-regression",
            databaseType: .postgresql,
            host: host,
            port: port,
            database: database,
            username: username,
            sslEnabled: false,
            sslMode: .disabled
        )
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: password)

        let tables: [TableInfo]
        do {
            tables = try await adapter.listTables(schema: "public")
            try await adapter.disconnect()
        } catch {
            try? await adapter.disconnect()
            throw error
        }

        let names = tables.map(\.name)
        XCTAssertEqual(names.count, Set(names).count, "HighGo information_schema.tables can duplicate rows; listTables must return unique physical tables")
        XCTAssertTrue(
            names.contains(where: { $0.hasPrefix("hg_") }),
            "pg_class-backed listTables should include HighGo-managed physical tables (hg_*) that information_schema.tables can hide"
        )
    }

    // MARK: - Vanilla PG regression — partition children must not surface as siblings

    // Declarative partitioning (PG 11+): a partition child has relkind='r' and
    // relispartition=true. The parent (relkind='p') already represents its
    // children's data in the UI, so leaking children would double-count rows
    // and clutter the sidebar. The original information_schema.tables query
    // returned only 'BASE TABLE' parents; switching to pg_class introduced
    // the leak unless we also filter `NOT relispartition`.
    func test_listTables_excludesPartitionChildren_onVanillaPG() async throws {
        try skipIfNoServer(port: 55434)

        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .disabled)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)

        // Clean previous fixtures, then create a partitioned table + one partition.
        let prefix = "gridex_part_test_\(UUID().uuidString.prefix(8).lowercased())"
        let plain = "\(prefix)_plain"
        let parent = "\(prefix)_parent"
        let child = "\(prefix)_p1"
        do {
            _ = try await adapter.executeRaw(sql: "CREATE TABLE \(plain) (id int)")
            _ = try await adapter.executeRaw(sql: "CREATE TABLE \(parent) (id int) PARTITION BY RANGE (id)")
            _ = try await adapter.executeRaw(sql: "CREATE TABLE \(child) PARTITION OF \(parent) FOR VALUES FROM (0) TO (100)")

            let names = try await adapter.listTables(schema: "public").map(\.name)

            XCTAssertTrue(names.contains(plain),
                "ordinary table \(plain) must appear")
            XCTAssertTrue(names.contains(parent),
                "partitioned-table parent \(parent) must appear")
            XCTAssertFalse(names.contains(child),
                "partition child \(child) must NOT appear as a sibling — it would double-count the parent's data")
        } catch {
            // Best-effort cleanup, then rethrow.
            _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(parent) CASCADE")
            _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(plain)")
            try? await adapter.disconnect()
            throw error
        }

        _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(parent) CASCADE")
        _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(plain)")
        try await adapter.disconnect()
    }

    // MARK: - Issue #75 - PostgreSQL array cells must not render as NULL

    func test_execute_decodesTextArrayCells() async throws {
        try skipIfNoServer(port: 55434)

        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .disabled)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)

        let table = "gridex_array_test_\(UUID().uuidString.prefix(8).lowercased())"
        do {
            _ = try await adapter.executeRaw(sql: "CREATE TEMP TABLE \(table) (id int, reasons text[])")
            _ = try await adapter.executeRaw(sql: """
                INSERT INTO \(table) VALUES
                (1, ARRAY['because', 'why']::text[]),
                (2, NULL),
                (3, ARRAY[]::text[])
                """)

            let result = try await adapter.executeRaw(sql: "SELECT reasons FROM \(table) ORDER BY id")

            XCTAssertEqual(result.rows.count, 3)
            XCTAssertEqual(result.columns.first?.dataType, "TEXT[]")

            guard case .array(let reasons) = result.rows[0][0] else {
                XCTFail("Expected non-null text[] to decode as RowValue.array")
                try? await adapter.disconnect()
                return
            }
            XCTAssertEqual(reasons, [.string("because"), .string("why")])
            XCTAssertEqual(result.rows[1][0], .null)

            guard case .array(let emptyReasons) = result.rows[2][0] else {
                XCTFail("Expected empty text[] to decode as an empty RowValue.array")
                try? await adapter.disconnect()
                return
            }
            XCTAssertTrue(emptyReasons.isEmpty)
        } catch {
            try? await adapter.disconnect()
            throw error
        }

        try await adapter.disconnect()
    }

    // MARK: - Issue #49 — Explain button server-side regression

    // The query editor button used to send "EXPLAIN QUERY PLAN <sql>" to every
    // engine — that's SQLite syntax, Postgres rejects it with SQLSTATE 42601
    // ("syntax error at or near \"QUERY\""). After the fix, the editor goes
    // through DatabaseType.explainSQL(for:) which emits the engine-correct form.
    // This test asserts the PG-specific shape actually parses and executes.
    func test_explainSQL_postgresShape_runsWithoutSyntaxError() async throws {
        try skipIfNoServer(port: 55434)

        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .disabled)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)

        // Make sure there's something to plan against.
        let table = "gridex_explain_test_\(UUID().uuidString.prefix(8).lowercased())"
        do {
            _ = try await adapter.executeRaw(sql: "CREATE TABLE \(table) (id int)")

            guard let explainSQL = DatabaseType.postgresql.explainSQL(for: "SELECT * FROM \(table)") else {
                XCTFail("PG must produce a non-nil EXPLAIN string")
                return
            }
            // The literal regression: the SQL we send must NOT be the SQLite
            // form. (Belt-and-braces against a future contributor accidentally
            // re-introducing the same hardcode.)
            XCTAssertFalse(explainSQL.uppercased().contains("QUERY PLAN"))

            let result = try await adapter.executeRaw(sql: explainSQL)
            XCTAssertFalse(result.rows.isEmpty, "EXPLAIN must return at least one plan row")
        } catch {
            _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(table)")
            try? await adapter.disconnect()
            throw error
        }

        _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(table)")
        try await adapter.disconnect()
    }

    // MARK: - ExplainOptions — server accepts the option lists we build

    /// Sanity check that `EXPLAIN (BUFFERS true, COSTS true, FORMAT JSON, ...)`
    /// — the shape produced when a user toggles options in the new dropdown —
    /// is something a real PG server actually parses. The pure-logic
    /// ExplainOptionsTests cover SQL shape; this one covers wire compatibility.
    func test_explainOptions_pgAcceptsBuiltOptionLists() async throws {
        try skipIfNoServer(port: 55434)

        let cfg = makeBaseConfig(host: "127.0.0.1", port: 55434, sslMode: .disabled)
        let adapter = PostgreSQLAdapter()
        try await adapter.connect(config: cfg, password: nil)

        let table = "gridex_explain_opts_\(UUID().uuidString.prefix(8).lowercased())"
        do {
            _ = try await adapter.executeRaw(sql: "CREATE TABLE \(table) (id int)")
            _ = try await adapter.executeRaw(sql: "INSERT INTO \(table) VALUES (1), (2), (3)")

            // Plan-only with extra detail — should never modify data.
            var planOnly = ExplainOptions.default
            planOnly.buffers = true
            planOnly.verbose = true
            planOnly.summary = true
            planOnly.format  = .json
            let planSQL = DatabaseType.postgresql.explainSQL(
                for: "SELECT * FROM \(table)",
                options: planOnly
            )!
            let planRes = try await adapter.executeRaw(sql: planSQL)
            XCTAssertFalse(planRes.rows.isEmpty,
                "EXPLAIN with bool toggles + JSON format must return plan rows")

            // Actual execution with timing — should run the SELECT, not modify
            // the table. Asserts the cross-disable rules in `sanitized()` don't
            // strip Timing when ANALYZE is on.
            var withAnalyze = ExplainOptions.default
            withAnalyze.analyze = true
            withAnalyze.timing  = true
            withAnalyze.buffers = true
            let analyzeSQL = DatabaseType.postgresql.explainSQL(
                for: "SELECT count(*) FROM \(table)",
                options: withAnalyze
            )!
            XCTAssertTrue(analyzeSQL.contains("TIMING true"),
                "Timing must survive sanitize when Analyze is on")
            let analyzeRes = try await adapter.executeRaw(sql: analyzeSQL)
            XCTAssertFalse(analyzeRes.rows.isEmpty,
                "EXPLAIN ANALYZE must return plan rows including actual times")
        } catch {
            _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(table)")
            try? await adapter.disconnect()
            throw error
        }

        _ = try? await adapter.executeRaw(sql: "DROP TABLE IF EXISTS \(table)")
        try await adapter.disconnect()
    }
}