Repository: jackc/pgx
Pull request #1887 (2024-01-27 14:45:42)

Remove openssl from TLS test setup
TLS setup and tests were rather finicky. It seems that openssl 3 encrypts certificates differently than older openssl and it does it in a way Go and/or pgx ssl handling code can't handle. It appears that this related to the use of a deprecated client certificate encryption system.

This caused CI to be stuck on Ubuntu 20.04 and recently caused the contributing guide to fail to work on MacOS.

Remove openssl from the test setup and replace it with a Go program that generates the certificates.


Patch:
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index c1bf91c3..f45c3045 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -9,10 +9,7 @@ on:
 jobs:
   test:
     name: Test
-    # Note: The TLS tests are rather finicky. It seems that openssl 3 encrypts certificates differently than older
-    # openssl and it does it in a way Go and/or pgx ssl handling code can't handle. So stick with Ubuntu 20.04 until
-    # that is figured out.
-    runs-on: ubuntu-20.04
+    runs-on: ubuntu-22.04
 
     strategy:
       matrix:
diff --git a/CONTRIBUTING.md b/CONTRIBUTING.md
index 3eb0da5b..6ed3205c 100644
--- a/CONTRIBUTING.md
+++ b/CONTRIBUTING.md
@@ -79,20 +79,11 @@ echo "listen_addresses = '127.0.0.1'" >> .testdb/$POSTGRESQL_DATA_DIR/postgresql
 echo "port = $PGPORT" >> .testdb/$POSTGRESQL_DATA_DIR/postgresql.conf
 cat testsetup/postgresql_ssl.conf >> .testdb/$POSTGRESQL_DATA_DIR/postgresql.conf
 cp testsetup/pg_hba.conf .testdb/$POSTGRESQL_DATA_DIR/pg_hba.conf
-cp testsetup/ca.cnf .testdb
-cp testsetup/localhost.cnf .testdb
-cp testsetup/pgx_sslcert.cnf .testdb
 
 cd .testdb
 
-# Generate a CA public / private key pair.
-openssl genrsa -out ca.key 4096
-openssl req -x509 -config ca.cnf -new -nodes -key ca.key -sha256 -days 365 -subj '/O=pgx-test-root' -out ca.pem
-
-# Generate the certificate for localhost (the server).
-openssl genrsa -out localhost.key 2048
-openssl req -new -config localhost.cnf -key localhost.key -out localhost.csr
-openssl x509 -req -in localhost.csr -CA ca.pem -CAkey ca.key -CAcreateserial -out localhost.crt -days 364 -sha256 -extfile localhost.cnf -extensions v3_req
+# Generate CA, server, and encrypted client certificates.
+go run ../testsetup/generate_certs.go
 
 # Copy certificates to server directory and set permissions.
 cp ca.pem $POSTGRESQL_DATA_DIR/root.crt
@@ -100,11 +91,6 @@ cp localhost.key $POSTGRESQL_DATA_DIR/server.key
 chmod 600 $POSTGRESQL_DATA_DIR/server.key
 cp localhost.crt $POSTGRESQL_DATA_DIR/server.crt
 
-# Generate the certificate for client authentication.
-openssl genrsa -des3 -out pgx_sslcert.key -passout pass:certpw 2048
-openssl req -new -config pgx_sslcert.cnf -key pgx_sslcert.key -passin pass:certpw -out pgx_sslcert.csr
-openssl x509 -req -in pgx_sslcert.csr -CA ca.pem -CAkey ca.key -CAcreateserial -out pgx_sslcert.crt -days 363 -sha256 -extfile pgx_sslcert.cnf -extensions v3_req
-
 cd ..
 ```
 
diff --git a/pgtype/ltree.go b/pgtype/ltree.go
deleted file mode 100644
index 6af31779..00000000
--- a/pgtype/ltree.go
+++ /dev/null
@@ -1,122 +0,0 @@
-package pgtype
-
-import (
-	"database/sql/driver"
-	"fmt"
-)
-
-type LtreeCodec struct{}
-
-func (l LtreeCodec) FormatSupported(format int16) bool {
-	return format == TextFormatCode || format == BinaryFormatCode
-}
-
-// PreferredFormat returns the preferred format.
-func (l LtreeCodec) PreferredFormat() int16 {
-	return TextFormatCode
-}
-
-// PlanEncode returns an EncodePlan for encoding value into PostgreSQL format for oid and format. If no plan can be
-// found then nil is returned.
-func (l LtreeCodec) PlanEncode(m *Map, oid uint32, format int16, value any) EncodePlan {
-	switch format {
-	case TextFormatCode:
-		return (TextCodec)(l).PlanEncode(m, oid, format, value)
-	case BinaryFormatCode:
-		switch value.(type) {
-		case string:
-			return encodeLtreeCodecBinaryString{}
-		case []byte:
-			return encodeLtreeCodecBinaryByteSlice{}
-		case TextValuer:
-			return encodeLtreeCodecBinaryTextValuer{}
-		}
-	}
-
-	return nil
-}
-
-type encodeLtreeCodecBinaryString struct{}
-
-func (encodeLtreeCodecBinaryString) Encode(value any, buf []byte) (newBuf []byte, err error) {
-	ltree := value.(string)
-	buf = append(buf, 1)
-	return append(buf, ltree...), nil
-}
-
-type encodeLtreeCodecBinaryByteSlice struct{}
-
-func (encodeLtreeCodecBinaryByteSlice) Encode(value any, buf []byte) (newBuf []byte, err error) {
-	ltree := value.([]byte)
-	buf = append(buf, 1)
-	return append(buf, ltree...), nil
-}
-
-type encodeLtreeCodecBinaryTextValuer struct{}
-
-func (encodeLtreeCodecBinaryTextValuer) Encode(value any, buf []byte) (newBuf []byte, err error) {
-	t, err := value.(TextValuer).TextValue()
-	if err != nil {
-		return nil, err
-	}
-	if !t.Valid {
-		return nil, nil
-	}
-
-	buf = append(buf, 1)
-	return append(buf, t.String...), nil
-}
-
-// PlanScan returns a ScanPlan for scanning a PostgreSQL value into a destination with the same type as target. If
-// no plan can be found then nil is returned.
-func (l LtreeCodec) PlanScan(m *Map, oid uint32, format int16, target any) ScanPlan {
-	switch format {
-	case TextFormatCode:
-		return (TextCodec)(l).PlanScan(m, oid, format, target)
-	case BinaryFormatCode:
-		switch target.(type) {
-		case *string:
-			return scanPlanBinaryLtreeToString{}
-		case TextScanner:
-			return scanPlanBinaryLtreeToTextScanner{}
-		}
-	}
-
-	return nil
-}
-
-type scanPlanBinaryLtreeToString struct{}
-
-func (scanPlanBinaryLtreeToString) Scan(src []byte, target any) error {
-	version := src[0]
-	if version != 1 {
-		return fmt.Errorf("unsupported ltree version %d", version)
-	}
-
-	p := (target).(*string)
-	*p = string(src[1:])
-
-	return nil
-}
-
-type scanPlanBinaryLtreeToTextScanner struct{}
-
-func (scanPlanBinaryLtreeToTextScanner) Scan(src []byte, target any) error {
-	version := src[0]
-	if version != 1 {
-		return fmt.Errorf("unsupported ltree version %d", version)
-	}
-
-	scanner := (target).(TextScanner)
-	return scanner.ScanText(Text{String: string(src[1:]), Valid: true})
-}
-
-// DecodeDatabaseSQLValue returns src decoded into a value compatible with the sql.Scanner interface.
-func (l LtreeCodec) DecodeDatabaseSQLValue(m *Map, oid uint32, format int16, src []byte) (driver.Value, error) {
-	return (TextCodec)(l).DecodeDatabaseSQLValue(m, oid, format, src)
-}
-
-// DecodeValue returns src decoded into its default format.
-func (l LtreeCodec) DecodeValue(m *Map, oid uint32, format int16, src []byte) (any, error) {
-	return (TextCodec)(l).DecodeValue(m, oid, format, src)
-}


Test patch:
diff --git a/ci/setup_test.bash b/ci/setup_test.bash
index f96d2b72..66ba07d4 100755
--- a/ci/setup_test.bash
+++ b/ci/setup_test.bash
@@ -16,14 +16,8 @@ then
 
   cd testsetup
 
-  # Generate a CA public / private key pair.
-  openssl genrsa -out ca.key 4096
-  openssl req -x509 -config ca.cnf -new -nodes -key ca.key -sha256 -days 365 -subj '/O=pgx-test-root' -out ca.pem
-
-  # Generate the certificate for localhost (the server).
-  openssl genrsa -out localhost.key 2048
-  openssl req -new -config localhost.cnf -key localhost.key -out localhost.csr
-  openssl x509 -req -in localhost.csr -CA ca.pem -CAkey ca.key -CAcreateserial -out localhost.crt -days 364 -sha256 -extfile localhost.cnf -extensions v3_req
+  # Generate CA, server, and encrypted client certificates.
+  go run generate_certs.go
 
   # Copy certificates to server directory and set permissions.
   sudo cp ca.pem /var/lib/postgresql/$PGVERSION/main/root.crt
@@ -34,11 +28,6 @@ then
   sudo cp localhost.crt /var/lib/postgresql/$PGVERSION/main/server.crt
   sudo chown postgres:postgres /var/lib/postgresql/$PGVERSION/main/server.crt
 
-  # Generate the certificate for client authentication.
-  openssl genrsa -des3 -out pgx_sslcert.key -passout pass:certpw 2048
-  openssl req -new -config pgx_sslcert.cnf -key pgx_sslcert.key -passin pass:certpw -out pgx_sslcert.csr
-  openssl x509 -req -in pgx_sslcert.csr -CA ca.pem -CAkey ca.key -CAcreateserial -out pgx_sslcert.crt -days 363 -sha256 -extfile pgx_sslcert.cnf -extensions v3_req
-
   cp ca.pem /tmp
   cp pgx_sslcert.key /tmp
   cp pgx_sslcert.crt /tmp
diff --git a/pgtype/ltree_test.go b/pgtype/ltree_test.go
deleted file mode 100644
index 2ec850f5..00000000
--- a/pgtype/ltree_test.go
+++ /dev/null
@@ -1,26 +0,0 @@
-package pgtype_test
-
-import (
-	"context"
-	"testing"
-
-	"github.com/jackc/pgx/v5/pgtype"
-	"github.com/jackc/pgx/v5/pgxtest"
-)
-
-func TestLtreeCodec(t *testing.T) {
-	skipCockroachDB(t, "Server does not support type ltree")
-
-	pgxtest.RunValueRoundTripTests(context.Background(), t, defaultConnTestRunner, pgxtest.KnownOIDQueryExecModes, "ltree", []pgxtest.ValueRoundTripTest{
-		{
-			Param:  "A.B.C",
-			Result: new(string),
-			Test:   isExpectedEq("A.B.C"),
-		},
-		{
-			Param:  pgtype.Text{String: "", Valid: true},
-			Result: new(pgtype.Text),
-			Test:   isExpectedEq(pgtype.Text{String: "", Valid: true}),
-		},
-	})
-}
diff --git a/testsetup/ca.cnf b/testsetup/ca.cnf
deleted file mode 100644
index bd018037..00000000
--- a/testsetup/ca.cnf
+++ /dev/null
@@ -1,6 +0,0 @@
-[ req ]
-distinguished_name = dn
-[ dn ]
-commonName         = ca
-[ ext ]
-basicConstraints   =CA:TRUE,pathlen:0
diff --git a/testsetup/generate_certs.go b/testsetup/generate_certs.go
new file mode 100644
index 00000000..945c6c5e
--- /dev/null
+++ b/testsetup/generate_certs.go
@@ -0,0 +1,187 @@
+// Generates a CA, server certificate, and encrypted client certificate for testing pgx.
+
+package main
+
+import (
+	"crypto/rand"
+	"crypto/rsa"
+	"crypto/x509"
+	"crypto/x509/pkix"
+	"encoding/pem"
+	"fmt"
+	"math/big"
+	"net"
+	"os"
+	"time"
+)
+
+func main() {
+	// Create the CA
+	ca := &x509.Certificate{
+		SerialNumber: big.NewInt(1),
+		Subject: pkix.Name{
+			CommonName: "pgx-root-ca",
+		},
+		NotBefore:             time.Now(),
+		NotAfter:              time.Now().AddDate(20, 0, 0),
+		IsCA:                  true,
+		ExtKeyUsage:           []x509.ExtKeyUsage{x509.ExtKeyUsageClientAuth, x509.ExtKeyUsageServerAuth},
+		KeyUsage:              x509.KeyUsageDigitalSignature | x509.KeyUsageCertSign,
+		BasicConstraintsValid: true,
+	}
+
+	caKey, err := rsa.GenerateKey(rand.Reader, 4096)
+	if err != nil {
+		panic(err)
+	}
+
+	caBytes, err := x509.CreateCertificate(rand.Reader, ca, ca, &caKey.PublicKey, caKey)
+	if err != nil {
+		panic(err)
+	}
+
+	err = writePrivateKey("ca.key", caKey)
+	if err != nil {
+		panic(err)
+	}
+
+	err = writeCertificate("ca.pem", caBytes)
+	if err != nil {
+		panic(err)
+	}
+
+	// Create a server certificate signed by the CA for localhost.
+	serverCert := &x509.Certificate{
+		SerialNumber: big.NewInt(2),
+		Subject: pkix.Name{
+			CommonName: "localhost",
+		},
+		DNSNames:    []string{"localhost"},
+		IPAddresses: []net.IP{net.IPv4(127, 0, 0, 1), net.IPv6loopback},
+		NotBefore:   time.Now(),
+		NotAfter:    time.Now().AddDate(20, 0, 0),
+		ExtKeyUsage: []x509.ExtKeyUsage{x509.ExtKeyUsageClientAuth, x509.ExtKeyUsageServerAuth},
+		KeyUsage:    x509.KeyUsageDigitalSignature,
+	}
+
+	serverCertPrivKey, err := rsa.GenerateKey(rand.Reader, 2048)
+	if err != nil {
+		panic(err)
+	}
+
+	serverBytes, err := x509.CreateCertificate(rand.Reader, serverCert, ca, &serverCertPrivKey.PublicKey, caKey)
+	if err != nil {
+		panic(err)
+	}
+
+	err = writePrivateKey("localhost.key", serverCertPrivKey)
+	if err != nil {
+		panic(err)
+	}
+
+	err = writeCertificate("localhost.crt", serverBytes)
+	if err != nil {
+		panic(err)
+	}
+
+	// Create a client certificate signed by the CA and encrypted.
+	clientCert := &x509.Certificate{
+		SerialNumber: big.NewInt(3),
+		Subject: pkix.Name{
+			CommonName: "pgx_sslcert",
+		},
+		NotBefore:   time.Now(),
+		NotAfter:    time.Now().AddDate(20, 0, 0),
+		ExtKeyUsage: []x509.ExtKeyUsage{x509.ExtKeyUsageClientAuth},
+		KeyUsage:    x509.KeyUsageDigitalSignature,
+	}
+
+	clientCertPrivKey, err := rsa.GenerateKey(rand.Reader, 2048)
+	if err != nil {
+		panic(err)
+	}
+
+	clientBytes, err := x509.CreateCertificate(rand.Reader, clientCert, ca, &clientCertPrivKey.PublicKey, caKey)
+	if err != nil {
+		panic(err)
+	}
+
+	writeEncryptedPrivateKey("pgx_sslcert.key", clientCertPrivKey, "certpw")
+	if err != nil {
+		panic(err)
+	}
+
+	writeCertificate("pgx_sslcert.crt", clientBytes)
+	if err != nil {
+		panic(err)
+	}
+}
+
+func writePrivateKey(path string, privateKey *rsa.PrivateKey) error {
+	file, err := os.Create(path)
+	if err != nil {
+		return fmt.Errorf("writePrivateKey: %w", err)
+	}
+
+	err = pem.Encode(file, &pem.Block{
+		Type:  "RSA PRIVATE KEY",
+		Bytes: x509.MarshalPKCS1PrivateKey(privateKey),
+	})
+	if err != nil {
+		return fmt.Errorf("writePrivateKey: %w", err)
+	}
+
+	err = file.Close()
+	if err != nil {
+		return fmt.Errorf("writePrivateKey: %w", err)
+	}
+
+	return nil
+}
+
+func writeEncryptedPrivateKey(path string, privateKey *rsa.PrivateKey, password string) error {
+	file, err := os.Create(path)
+	if err != nil {
+		return fmt.Errorf("writeEncryptedPrivateKey: %w", err)
+	}
+
+	block, err := x509.EncryptPEMBlock(rand.Reader, "CERTIFICATE", x509.MarshalPKCS1PrivateKey(privateKey), []byte(password), x509.PEMCipher3DES)
+	if err != nil {
+		return fmt.Errorf("writeEncryptedPrivateKey: %w", err)
+	}
+
+	err = pem.Encode(file, block)
+	if err != nil {
+		return fmt.Errorf("writeEncryptedPrivateKey: %w", err)
+	}
+
+	err = file.Close()
+	if err != nil {
+		return fmt.Errorf("writeEncryptedPrivateKey: %w", err)
+	}
+
+	return nil
+
+}
+
+func writeCertificate(path string, certBytes []byte) error {
+	file, err := os.Create(path)
+	if err != nil {
+		return fmt.Errorf("writeCertificate: %w", err)
+	}
+
+	err = pem.Encode(file, &pem.Block{
+		Type:  "CERTIFICATE",
+		Bytes: certBytes,
+	})
+	if err != nil {
+		return fmt.Errorf("writeCertificate: %w", err)
+	}
+
+	err = file.Close()
+	if err != nil {
+		return fmt.Errorf("writeCertificate: %w", err)
+	}
+
+	return nil
+}
diff --git a/testsetup/localhost.cnf b/testsetup/localhost.cnf
deleted file mode 100644
index 14dcd57f..00000000
--- a/testsetup/localhost.cnf
+++ /dev/null
@@ -1,13 +0,0 @@
-[ req ]
-default_bits       = 2048
-distinguished_name = dn
-req_extensions     = v3_req
-prompt             = no
-[ dn ]
-commonName         = localhost
-[ v3_req ]
-subjectAltName     = @alt_names
-keyUsage           = digitalSignature
-extendedKeyUsage   = serverAuth
-[alt_names]
-DNS.1              = localhost
diff --git a/testsetup/pgx_sslcert.cnf b/testsetup/pgx_sslcert.cnf
deleted file mode 100644
index 2d5d0ff7..00000000
--- a/testsetup/pgx_sslcert.cnf
+++ /dev/null
@@ -1,9 +0,0 @@
-[ req ]
-default_bits       = 2048
-distinguished_name = dn
-req_extensions     = v3_req
-prompt             = no
-[ dn ]
-commonName         = pgx_sslcert
-[ v3_req ]
-keyUsage           = digitalSignature
diff --git a/testsetup/postgresql_setup.sql b/testsetup/postgresql_setup.sql
index 837c978a..51414863 100644
--- a/testsetup/postgresql_setup.sql
+++ b/testsetup/postgresql_setup.sql
@@ -1,6 +1,5 @@
 -- Create extensions and types.
 create extension hstore;
-create extension ltree;
 create domain uint64 as numeric(20,0);
 
 -- Create users for different types of connections and authentication.