package db import ( "context" "database/sql" "errors" "fmt" "time" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgconn" "github.com/jackc/pgx/v5/stdlib" ) // PostgreSQLOptions is an instance-level secret-bearing configuration. Do not // log it. Application startup additionally requires a verified full-import // receipt; opening a connection alone does not authorize backend cutover. type PostgreSQLOptions struct { DSN string MaxOpenConns int MaxIdleConns int ConnectTimeout time.Duration // AllowInsecure permits plaintext or unverified TLS for isolated development // only. Production defaults require verified TLS and no plaintext fallback. AllowInsecure bool } // PostgreSQLError deliberately excludes server messages, SQL, connection // strings and constraint details, all of which can contain credentials or data. // SQLState remains available for retry/conflict classification. type PostgreSQLError struct { Operation string SQLState string } func (e *PostgreSQLError) Error() string { if e.SQLState != "" { return fmt.Sprintf("PostgreSQL %s failed (SQLSTATE %s)", e.Operation, e.SQLState) } return "PostgreSQL " + e.Operation + " failed" } func postgresError(operation string, err error) error { if errors.Is(err, context.Canceled) { return context.Canceled } if errors.Is(err, context.DeadlineExceeded) { return context.DeadlineExceeded } var pgErr *pgconn.PgError result := &PostgreSQLError{Operation: operation} if errors.As(err, &pgErr) { result.SQLState = pgErr.Code } return result } func OpenPostgreSQL(ctx context.Context, options PostgreSQLOptions) (*sql.DB, error) { if options.DSN == "" { return nil, fmt.Errorf("PostgreSQL DSN is required") } if options.MaxOpenConns < 0 || options.MaxIdleConns < 0 || options.ConnectTimeout < 0 { return nil, fmt.Errorf("invalid PostgreSQL pool or timeout configuration") } config, err := pgx.ParseConfig(options.DSN) if err != nil { return nil, postgresError("configuration", err) } if !options.AllowInsecure { if config.TLSConfig == nil || config.TLSConfig.InsecureSkipVerify { return nil, fmt.Errorf("PostgreSQL requires verified TLS; use sslmode=verify-full") } for _, fallback := range config.Fallbacks { if fallback.TLSConfig == nil || fallback.TLSConfig.InsecureSkipVerify { return nil, fmt.Errorf("PostgreSQL insecure connection fallback is not allowed") } } } timeout := options.ConnectTimeout if timeout == 0 { timeout = 10 * time.Second } config.ConnectTimeout = timeout config.RuntimeParams["application_name"] = "pageswiki" database := stdlib.OpenDB(*config) maxOpen := options.MaxOpenConns if maxOpen == 0 { maxOpen = 10 } maxIdle := options.MaxIdleConns if maxIdle == 0 { maxIdle = 2 } if maxIdle > maxOpen { maxIdle = maxOpen } database.SetMaxOpenConns(maxOpen) database.SetMaxIdleConns(maxIdle) database.SetConnMaxLifetime(30 * time.Minute) database.SetConnMaxIdleTime(5 * time.Minute) pingCtx, cancel := context.WithTimeout(ctx, timeout) defer cancel() if err := database.PingContext(pingCtx); err != nil { _ = database.Close() return nil, postgresError("connect", err) } return database, nil }