Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

9.4 KB
Raw Blame
package cockroachdb

import (
	"bytes"
	"compress/zlib"
	"crypto/rand"
	"crypto/rsa"
	"crypto/x509"
	"crypto/x509/pkix"
	"encoding/base64"
	"encoding/pem"
	"math/big"
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/plugin/db"
)

func TestGetDatabaseInCreateDatabaseStatement(t *testing.T) {
	tests := []struct {
		createDatabaseStatement string
		want                    string
		wantErr                 bool
	}{
		{
			`CREATE DATABASE "hello" ENCODING "UTF8";`,
			"hello",
			false,
		},
		{
			`CREATE DATABASE "hello";`,
			"hello",
			false,
		},
		{
			`CREATE DATABASE hello;`,
			"hello",
			false,
		},
		{
			`CREATE DATABASE hello ENCODING "UTF8";`,
			"hello",
			false,
		},
		{
			`CREATE DATABASE;`,
			"",
			true,
		},
	}

	for _, test := range tests {
		got, err := getDatabaseInCreateDatabaseStatement(test.createDatabaseStatement)
		if test.wantErr {
			require.Error(t, err)
		} else {
			require.NoError(t, err)
		}
		require.Equal(t, test.want, got)
	}
}

func TestGetRoutingIDFromCockroachCloudURL(t *testing.T) {
	tests := []struct {
		host     string
		expected string
	}{
		{
			host:     "routing-id.cockroachlabs.cloud",
			expected: "routing-id",
		},
		{
			host:     "bytebase-cluster-7749.6xw.aws-ap-southeast-1.cockroachlabs.cloud",
			expected: "bytebase-cluster-7749",
		},
		{
			host:     "subdomain.routing-id.cockroachlabs.cloud",
			expected: "subdomain",
		},
		{
			host:     "subdomain.routing-id.cockroachlabs.cloud",
			expected: "subdomain",
		},
		{
			host:     "cockroachlabs.cloud",
			expected: "",
		},
		{
			host:     "example.com",
			expected: "",
		},
	}

	for _, test := range tests {
		got := getRoutingIDFromCockroachCloudURL(test.host)
		require.Equal(t, test.expected, got, "host: %s", test.host)
	}
}

func TestGetCockroachConnectionConfigAddsClientCertificateForAllHosts(t *testing.T) {
	certPEM, keyPEM := generateClientCertificatePEM(t)
	connConfig, err := getCockroachConnectionConfig(db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Username:             "dba",
			Host:                 "172.18.22.61,172.18.22.62,172.18.22.63",
			Port:                 "26257",
			UseSsl:               true,
			VerifyTlsCertificate: false,
			SslCert:              certPEM,
			SslKey:               keyPEM,
		},
		ConnectionContext: db.ConnectionContext{
			DatabaseName: "defaultdb",
		},
	})
	require.NoError(t, err)
	require.NotNil(t, connConfig.TLSConfig)
	require.Len(t, connConfig.TLSConfig.Certificates, 1)
	require.Len(t, connConfig.Fallbacks, 2)
	for _, fallback := range connConfig.Fallbacks {
		require.NotNil(t, fallback.TLSConfig)
		require.True(t, fallback.TLSConfig.InsecureSkipVerify)
		require.Len(t, fallback.TLSConfig.Certificates, 1)
	}
}

func TestGetCockroachConnectionConfigKeepsEachValueWhole(t *testing.T) {
	_, err := getCockroachConnectionConfig(db.ConnectionConfig{
		DataSource: &storepb.DataSource{Username: "dba", Host: "crdb.example.com", Port: "26257 host=other.example.com"},
	})
	require.ErrorContains(t, err, "invalid port")

	connConfig, err := getCockroachConnectionConfig(db.ConnectionConfig{
		DataSource: &storepb.DataSource{Username: "dba", Host: "routing-id.aws-us-east-1.cockroachlabs.cloud", Port: "26257"},
	})
	require.NoError(t, err)
	require.Equal(t, "--cluster=routing-id", connConfig.RuntimeParams["options"])
}

func TestGetCockroachConnectionConfigVerifiesCustomCAForAllHosts(t *testing.T) {
	hosts := []string{"crdb-1.example.com", "crdb-2.example.com", "crdb-3.example.com"}
	caPEM, serverCertDERByHost := generateCAAndServerCertificates(t, hosts)
	connConfig, err := getCockroachConnectionConfig(db.ConnectionConfig{
		DataSource: &storepb.DataSource{
			Username:             "dba",
			Host:                 "crdb-1.example.com,crdb-2.example.com,crdb-3.example.com",
			Port:                 "26257",
			UseSsl:               true,
			VerifyTlsCertificate: true,
			SslCa:                caPEM,
		},
		ConnectionContext: db.ConnectionContext{
			DatabaseName: "defaultdb",
		},
	})
	require.NoError(t, err)
	require.NotNil(t, connConfig.TLSConfig)
	require.NotNil(t, connConfig.TLSConfig.RootCAs)
	require.NotNil(t, connConfig.TLSConfig.VerifyPeerCertificate)
	require.NoError(t, connConfig.TLSConfig.VerifyPeerCertificate([][]byte{serverCertDERByHost[hosts[0]]}, nil))
	require.Len(t, connConfig.Fallbacks, 2)
	for i, fallback := range connConfig.Fallbacks {
		require.NotNil(t, fallback.TLSConfig)
		require.NotNil(t, fallback.TLSConfig.RootCAs)
		require.NotNil(t, fallback.TLSConfig.VerifyPeerCertificate)
		require.NoError(t, fallback.TLSConfig.VerifyPeerCertificate([][]byte{serverCertDERByHost[hosts[i+1]]}, nil))
	}
}

func generateClientCertificatePEM(t *testing.T) (string, string) {
	t.Helper()

	key, err := rsa.GenerateKey(rand.Reader, 2048)
	require.NoError(t, err)

	template := &x509.Certificate{
		SerialNumber: big.NewInt(1),
		Subject: pkix.Name{
			CommonName: "bytebase-test-client",
		},
		NotBefore: time.Now().Add(-time.Hour),
		NotAfter:  time.Now().Add(time.Hour),
		KeyUsage:  x509.KeyUsageDigitalSignature,
		ExtKeyUsage: []x509.ExtKeyUsage{
			x509.ExtKeyUsageClientAuth,
		},
	}
	certDER, err := x509.CreateCertificate(rand.Reader, template, template, &key.PublicKey, key)
	require.NoError(t, err)

	certPEM := pem.EncodeToMemory(&pem.Block{Type: "CERTIFICATE", Bytes: certDER})
	keyPEM := pem.EncodeToMemory(&pem.Block{Type: "RSA PRIVATE KEY", Bytes: x509.MarshalPKCS1PrivateKey(key)})
	return string(certPEM), string(keyPEM)
}

func generateCAAndServerCertificates(t *testing.T, hosts []string) (string, map[string][]byte) {
	t.Helper()

	caKey, err := rsa.GenerateKey(rand.Reader, 2048)
	require.NoError(t, err)
	caTemplate := &x509.Certificate{
		SerialNumber:          big.NewInt(1),
		Subject:               pkix.Name{CommonName: "bytebase-test-ca"},
		NotBefore:             time.Now().Add(-time.Hour),
		NotAfter:              time.Now().Add(time.Hour),
		KeyUsage:              x509.KeyUsageCertSign,
		BasicConstraintsValid: true,
		IsCA:                  true,
	}
	caDER, err := x509.CreateCertificate(rand.Reader, caTemplate, caTemplate, &caKey.PublicKey, caKey)
	require.NoError(t, err)

	serverCertDERByHost := make(map[string][]byte, len(hosts))
	for i, host := range hosts {
		serverKey, err := rsa.GenerateKey(rand.Reader, 2048)
		require.NoError(t, err)
		serverTemplate := &x509.Certificate{
			SerialNumber: big.NewInt(int64(i + 2)),
			Subject:      pkix.Name{CommonName: host},
			DNSNames:     []string{host},
			NotBefore:    time.Now().Add(-time.Hour),
			NotAfter:     time.Now().Add(time.Hour),
			KeyUsage:     x509.KeyUsageDigitalSignature,
			ExtKeyUsage:  []x509.ExtKeyUsage{x509.ExtKeyUsageServerAuth},
		}
		serverDER, err := x509.CreateCertificate(rand.Reader, serverTemplate, caTemplate, &serverKey.PublicKey, caKey)
		require.NoError(t, err)
		serverCertDERByHost[host] = serverDER
	}

	caPEM := pem.EncodeToMemory(&pem.Block{Type: "CERTIFICATE", Bytes: caDER})
	return string(caPEM), serverCertDERByHost
}

// encodeDiagram is how CockroachDB encodes the plan behind the link it prints:
// deflated, then base64 in the standard alphabet.
func encodeDiagram(t *testing.T, plan string) string {
	t.Helper()
	var deflated bytes.Buffer
	writer := zlib.NewWriter(&deflated)
	_, err := writer.Write([]byte(plan))
	require.NoError(t, err)
	require.NoError(t, writer.Close())
	return base64.RawStdEncoding.EncodeToString(deflated.Bytes())
}

func diagramResult(lines ...string) *v1pb.QueryResult {
	result := &v1pb.QueryResult{ColumnNames: []string{"info"}}
	for _, line := range lines {
		result.Rows = append(result.Rows, &v1pb.QueryRow{Values: []*v1pb.RowValue{
			{Kind: &v1pb.RowValue_StringValue{StringValue: line}},
		}})
	}
	return result
}

// TestPlanDiagram covers the one machine-readable plan CockroachDB publishes.
// Every EXPLAIN option of its own returns text; this is the payload behind the
// link EXPLAIN (DISTSQL) prints beside that text.
func TestPlanDiagram(t *testing.T) {
	t.Parallel()
	const plan = `{"processors":[{"core":{"title":"TableReader/0"}}],"edges":[]}`

	t.Run("reads the plan out of the link", func(t *testing.T) {
		t.Parallel()
		result, err := planDiagram(diagramResult(
			"distribution: local",
			"• scan",
			"Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#"+encodeDiagram(t, plan),
		))
		require.NoError(t, err)
		require.Equal(t, []string{"plan"}, result.GetColumnNames())
		require.Len(t, result.GetRows(), 1)
		require.JSONEq(t, plan, result.GetRows()[0].GetValues()[0].GetStringValue())
	})

	t.Run("reads a link written in the URL alphabet", func(t *testing.T) {
		t.Parallel()
		// The payload rides in a URL fragment, so either spelling of the two
		// bytes that differ between the alphabets has to decode.
		encoded := strings.NewReplacer("+", "-", "/", "_").Replace(encodeDiagram(t, plan))
		result, err := planDiagram(diagramResult("Diagram: https://example.test/#" + encoded))
		require.NoError(t, err)
		require.JSONEq(t, plan, result.GetRows()[0].GetValues()[0].GetStringValue())
	})

	t.Run("refuses a plan with no link in it", func(t *testing.T) {
		t.Parallel()
		_, err := planDiagram(diagramResult("distribution: local", "• scan"))
		require.ErrorContains(t, err, "no diagram")
	})

	t.Run("refuses a link it cannot read", func(t *testing.T) {
		t.Parallel()
		_, err := planDiagram(diagramResult("Diagram: https://example.test/#bm90emxpYg"))
		require.Error(t, err)
	})
}