package db import ( "context" "database/sql" "errors" "fmt" "net/url" "os" "strings" "sync" "testing" "time" "github.com/google/uuid" "github.com/jackc/pgx/v5/pgconn" ) func TestPostgreSQLConfigurationAndRedaction(t *testing.T) { for _, options := range []PostgreSQLOptions{ {}, {DSN: "postgres://test:secret@localhost/db", MaxOpenConns: -1}, {DSN: "postgres://test:secret@localhost/db?sslmode=disable"}, {DSN: "postgres://test:secret@localhost/db?sslmode=require"}, {DSN: "postgres://test:secret@localhost/db?sslmode=prefer"}, {DSN: "postgres://test:secret@localhost/db?sslmode=invalid"}, } { database, err := OpenPostgreSQL(context.Background(), options) if database != nil || err == nil { t.Fatal("invalid configuration accepted") } if strings.Contains(err.Error(), "secret") { t.Fatal("password disclosed") } } err := postgresError("write", &pgconn.PgError{Code: "23505", Message: "secret", Detail: "private row"}) var safe *PostgreSQLError if !errors.As(err, &safe) || safe.SQLState != "23505" || strings.Contains(err.Error(), "secret") { t.Fatal("unsafe error classification") } if !errors.Is(postgresError("read", context.Canceled), context.Canceled) { t.Fatal("cancellation lost") } ctx, cancel := context.WithCancel(context.Background()) cancel() database, err := OpenPostgreSQL(ctx, PostgreSQLOptions{DSN: "postgres://test:secret@127.0.0.1:1/db?sslmode=disable", AllowInsecure: true}) if database != nil || !errors.Is(err, context.Canceled) { t.Fatal("connection cancellation lost") } } // These tests create and drop only a random schema. Use a disposable database // and URL DSN, never a production connection. No fixtures contain real events. func postgresTestDatabase(t *testing.T) *sql.DB { t.Helper() dsn := os.Getenv("PAGESWIKI_TEST_POSTGRES_DSN") if dsn == "" { t.Skip("set PAGESWIKI_TEST_POSTGRES_DSN to a disposable PostgreSQL URL") } u, err := url.Parse(dsn) if err != nil || (u.Scheme != "postgres" && u.Scheme != "postgresql") { t.Fatal("test DSN must be a PostgreSQL URL") } ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second) defer cancel() root, err := OpenPostgreSQL(ctx, PostgreSQLOptions{DSN: dsn, AllowInsecure: true}) if err != nil { t.Fatal(err) } schema := "pageswiki_test_" + strings.ReplaceAll(uuid.NewString(), "-", "") if _, err := root.ExecContext(ctx, `CREATE SCHEMA "`+schema+`"`); err != nil { root.Close() t.Fatal(postgresError("test schema", err)) } t.Cleanup(func() { cleanup, cancel := context.WithTimeout(context.Background(), 10*time.Second) defer cancel() if _, err := root.ExecContext(cleanup, `DROP SCHEMA "`+schema+`" CASCADE`); err != nil { t.Error(postgresError("test cleanup", err)) } root.Close() }) query := u.Query() query.Set("search_path", schema) u.RawQuery = query.Encode() database, err := OpenPostgreSQL(ctx, PostgreSQLOptions{DSN: u.String(), AllowInsecure: true, MaxOpenConns: 6}) if err != nil { t.Fatal(err) } t.Cleanup(func() { database.Close() }) return database } func TestPostgreSQLMigrationConcurrencyAndHistory(t *testing.T) { database := postgresTestDatabase(t) ctx, cancel := context.WithTimeout(context.Background(), 20*time.Second) defer cancel() steps := []PostgreSQLMigration{{Version: 1, Name: "initial", Statements: []string{ `CREATE TABLE sample (uuid TEXT PRIMARY KEY, created_at_ms BIGINT NOT NULL)`, `INSERT INTO sample VALUES ('synthetic', 1800000000000)`, }, Assertions: []string{`SELECT 1 WHERE NOT EXISTS (SELECT 1 FROM sample WHERE created_at_ms=1800000000000)`}}} var wg sync.WaitGroup errs := make(chan error, 6) for i := 0; i < 6; i++ { wg.Add(1) go func() { defer wg.Done(); errs <- ApplyPostgreSQLMigrations(ctx, database, "test", steps) }() } wg.Wait() close(errs) for err := range errs { if err != nil { t.Fatal(err) } } var count int if err := database.QueryRowContext(ctx, `SELECT count(*) FROM sample`).Scan(&count); err != nil || count != 1 { t.Fatalf("rows=%d error=%v", count, err) } if err := ApplyPostgreSQLMigrations(ctx, database, "test", nil); err == nil { t.Fatal("downgrade accepted") } changed := append([]PostgreSQLMigration(nil), steps...) changed[0].Name = "changed" if err := ApplyPostgreSQLMigrations(ctx, database, "test", changed); err == nil { t.Fatal("changed history accepted") } changed = append([]PostgreSQLMigration(nil), steps...) changed[0].Statements = []string{`SELECT 1`} if err := ApplyPostgreSQLMigrations(ctx, database, "test", changed); err == nil { t.Fatal("changed SQL checksum accepted") } if err := ApplyPostgreSQLMigrations(ctx, database, "other", []PostgreSQLMigration{{Version: 2, Name: "gap", Statements: []string{`SELECT 1`}}}); err == nil { t.Fatal("version gap accepted") } // Assertions are checked again even when no migrations remain to apply. if _, err := database.ExecContext(ctx, `DELETE FROM sample`); err != nil { t.Fatal(err) } if err := ApplyPostgreSQLMigrations(ctx, database, "test", steps); err == nil { t.Fatal("schema assertion skipped") } } func TestPostgreSQLMigrationRollbackAndLockCancellation(t *testing.T) { database := postgresTestDatabase(t) ctx, cancel := context.WithTimeout(context.Background(), 20*time.Second) defer cancel() broken := []PostgreSQLMigration{{Version: 1, Name: "broken", Statements: []string{`CREATE TABLE partial (id INTEGER)`, `NOT VALID SQL`}}} if err := ApplyPostgreSQLMigrations(ctx, database, "test", broken); err == nil { t.Fatal("SQL error ignored") } var name sql.NullString if err := database.QueryRowContext(ctx, `SELECT to_regclass('partial')::text`).Scan(&name); err != nil || name.Valid { t.Fatal("DDL not rolled back", err) } if err := database.QueryRowContext(ctx, `SELECT to_regclass('schema_migration_steps')::text`).Scan(&name); err != nil || name.Valid { t.Fatal("ledger not rolled back", err) } broken[0].Statements = broken[0].Statements[:1] if err := ApplyPostgreSQLMigrations(ctx, database, "test", broken); err != nil { t.Fatal("retry failed", err) } assertionFailure := append(append([]PostgreSQLMigration(nil), broken...), PostgreSQLMigration{ Version: 2, Name: "assertion failure", Statements: []string{`CREATE TABLE should_rollback (id INTEGER)`}, Assertions: []string{`SELECT 1`}, }) if err := ApplyPostgreSQLMigrations(ctx, database, "test", assertionFailure); err == nil { t.Fatal("assertion failure ignored") } if err := database.QueryRowContext(ctx, `SELECT to_regclass('should_rollback')::text`).Scan(&name); err != nil || name.Valid { t.Fatal("assertion DDL not rolled back", err) } var version int if err := database.QueryRowContext(ctx, `SELECT max(version) FROM schema_migration_steps WHERE component=$1`, "test").Scan(&version); err != nil || version != 1 { t.Fatal("assertion ledger not rolled back", err) } tx, err := database.BeginTx(ctx, nil) if err != nil { t.Fatal(err) } defer tx.Rollback() if _, err = tx.ExecContext(ctx, `SELECT pg_advisory_xact_lock(1885431653, 1835624306)`); err != nil { t.Fatal(err) } waitCtx, stop := context.WithTimeout(ctx, 100*time.Millisecond) defer stop() if err := ApplyPostgreSQLMigrations(waitCtx, database, "test", broken); err == nil { t.Fatal("blocked migration ignored deadline") } if err := tx.Rollback(); err != nil { t.Fatal(err) } if err := ApplyPostgreSQLMigrations(ctx, database, "test", broken); err != nil { t.Fatal(fmt.Sprintf("lock not released: %v", err)) } }