Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
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)
})
}