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 pg
import (
"context"
"crypto/rand"
"crypto/rsa"
"crypto/x509"
"crypto/x509/pkix"
"encoding/json"
"encoding/pem"
"math/big"
"strings"
"testing"
"time"
"cloud.google.com/go/cloudsqlconn"
"github.com/jackc/pgx/v5"
"github.com/stretchr/testify/require"
"github.com/bytebase/bytebase/backend/common/testcontainer"
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"
pgparser "github.com/bytebase/bytebase/backend/plugin/parser/pg"
)
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 TestGetPGConnectionConfigUsesPgBouncerCompatibleQueryMode(t *testing.T) {
connConfig, err := getPGConnectionConfig(db.ConnectionConfig{
DataSource: &storepb.DataSource{
Username: "dba",
Host: "pgbouncer.example.com",
Port: "6432",
},
ConnectionContext: db.ConnectionContext{
DatabaseName: "postgres",
},
})
require.NoError(t, err)
require.Equal(t, pgx.QueryExecModeExec, connConfig.DefaultQueryExecMode)
require.Zero(t, connConfig.StatementCacheCapacity)
}
func TestGetPGConnectionConfigPreservesExplicitQueryExecMode(t *testing.T) {
connConfig, err := getPGConnectionConfig(db.ConnectionConfig{
DataSource: &storepb.DataSource{
Username: "dba",
Host: "proxy.example.com",
Port: "6432",
ExtraConnectionParameters: map[string]string{
"default_query_exec_mode": "simple_protocol",
"statement_cache_capacity": "128",
},
},
ConnectionContext: db.ConnectionContext{
DatabaseName: "postgres",
},
})
require.NoError(t, err)
require.Equal(t, pgx.QueryExecModeSimpleProtocol, connConfig.DefaultQueryExecMode)
require.Equal(t, 128, connConfig.StatementCacheCapacity)
}
func TestGetPGConnectionConfigKeepsEachValueWhole(t *testing.T) {
open := func(edit func(*storepb.DataSource)) (*pgx.ConnConfig, error) {
dataSource := &storepb.DataSource{Username: "dba", Host: "db.example.com", Port: "5432"}
edit(dataSource)
return getPGConnectionConfig(db.ConnectionConfig{DataSource: dataSource})
}
t.Run("a port cannot name another host", func(t *testing.T) {
_, err := open(func(ds *storepb.DataSource) { ds.Port = "5432 host=other.example.com" })
require.ErrorContains(t, err, "invalid port")
})
t.Run("a port cannot name a CA file", func(t *testing.T) {
missing := t.TempDir() + "/missing-ca.pem"
_, err := open(func(ds *storepb.DataSource) { ds.Port = "5432 sslmode=verify-full sslrootcert=" + missing })
require.ErrorContains(t, err, "invalid port")
})
t.Run("a host carrying options stays one host", func(t *testing.T) {
connConfig, err := open(func(ds *storepb.DataSource) { ds.Host = "db.example.com port=6543" })
require.NoError(t, err)
require.Equal(t, "db.example.com port=6543", connConfig.Host)
require.Equal(t, uint16(5432), connConfig.Port)
})
t.Run("an extra parameter keeps libpq syntax", func(t *testing.T) {
connConfig, err := open(func(ds *storepb.DataSource) {
ds.ExtraConnectionParameters = map[string]string{"options": "'-c search_path=hr'"}
})
require.NoError(t, err)
require.Equal(t, "-c search_path=hr", connConfig.RuntimeParams["options"])
})
t.Run("surrounding whitespace is ignored", func(t *testing.T) {
connConfig, err := open(func(ds *storepb.DataSource) {
ds.Host = " db.example.com "
ds.Port = " 5432 "
})
require.NoError(t, err)
require.Equal(t, "db.example.com", connConfig.Host)
require.Equal(t, uint16(5432), connConfig.Port)
})
t.Run("a blank host is refused", func(t *testing.T) {
_, err := open(func(ds *storepb.DataSource) { ds.Host = " " })
require.ErrorContains(t, err, "host must be set")
})
t.Run("a blank port is refused", func(t *testing.T) {
_, err := open(func(ds *storepb.DataSource) { ds.Port = " " })
require.ErrorContains(t, err, "port must be set")
})
}
func TestGetRDSConnectionConfigKeepsTheUserWhole(t *testing.T) {
// Static credentials sign the token locally; no AWS file or endpoint is read.
t.Setenv("AWS_CONFIG_FILE", t.TempDir()+"/config")
t.Setenv("AWS_SHARED_CREDENTIALS_FILE", t.TempDir()+"/credentials")
t.Setenv("AWS_EC2_METADATA_DISABLED", "true")
// A set AWS_PROFILE makes the SDK load that profile strictly, and the files
// above have none.
t.Setenv("AWS_PROFILE", "")
connConfig, err := getRDSConnectionConfig(context.Background(), db.ConnectionConfig{
DataSource: &storepb.DataSource{
Host: "rds.example.com",
Port: "5432",
Username: "dba host=other.example.com",
Region: "us-east-1",
AuthenticationType: storepb.DataSource_AWS_RDS_IAM,
IamExtension: &storepb.DataSource_AwsCredential{
AwsCredential: &storepb.DataSource_AWSCredential{AccessKeyId: "AKIDEXAMPLE", SecretAccessKey: "not-a-real-secret"},
},
},
})
require.NoError(t, err)
require.Equal(t, "rds.example.com", connConfig.Host)
require.Equal(t, "dba host=other.example.com", connConfig.User)
}
func TestGetCloudSQLConnectionConfigKeepsTheUserWhole(t *testing.T) {
config := cloudSQLIAMConnectionConfig(cloudSQLTestInstance)
config.DataSource.Username = "bytebase service account"
driver := &Driver{}
connConfig, err := driver.getCloudSQLConnectionConfig(context.Background(), config)
require.NoError(t, err)
t.Cleanup(func() { _ = driver.cloudSQLDialer.Close() })
require.Equal(t, "bytebase service account", connConfig.User)
}
func TestGetPGConnectionConfigDisablesTLSVerificationForAllHosts(t *testing.T) {
connConfig, err := getPGConnectionConfig(db.ConnectionConfig{
DataSource: &storepb.DataSource{
Username: "dba",
Host: "172.18.22.61,172.18.22.62,172.18.22.63",
Port: "5432",
UseSsl: true,
VerifyTlsCertificate: false,
},
ConnectionContext: db.ConnectionContext{
DatabaseName: "lapidlive",
},
})
require.NoError(t, err)
require.NotNil(t, connConfig.TLSConfig)
require.True(t, connConfig.TLSConfig.InsecureSkipVerify)
require.Len(t, connConfig.Fallbacks, 2)
for _, fallback := range connConfig.Fallbacks {
require.NotNil(t, fallback.TLSConfig)
require.True(t, fallback.TLSConfig.InsecureSkipVerify)
}
}
func TestGetPGConnectionConfigAddsClientCertificateForAllHosts(t *testing.T) {
certPEM, keyPEM := generateClientCertificatePEM(t)
connConfig, err := getPGConnectionConfig(db.ConnectionConfig{
DataSource: &storepb.DataSource{
Username: "dba",
Host: "172.18.22.61,172.18.22.62,172.18.22.63",
Port: "5432",
UseSsl: true,
VerifyTlsCertificate: false,
SslCert: certPEM,
SslKey: keyPEM,
},
ConnectionContext: db.ConnectionContext{
DatabaseName: "lapidlive",
},
})
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.Len(t, fallback.TLSConfig.Certificates, 1)
}
}
func TestGetPGConnectionConfigVerifiesCustomCAForAllHosts(t *testing.T) {
hosts := []string{"pg-1.example.com", "pg-2.example.com", "pg-3.example.com"}
caPEM, serverCertDERByHost := generateCAAndServerCertificates(t, hosts)
connConfig, err := getPGConnectionConfig(db.ConnectionConfig{
DataSource: &storepb.DataSource{
Username: "dba",
Host: "pg-1.example.com,pg-2.example.com,pg-3.example.com",
Port: "5432",
UseSsl: true,
VerifyTlsCertificate: true,
SslCa: caPEM,
},
ConnectionContext: db.ConnectionContext{
DatabaseName: "lapidlive",
},
})
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
}
func TestQueryConnSearchPathIncludesPublicAfterSelectedSchema(t *testing.T) {
t.Parallel()
ctx := context.Background()
pgContainer := testcontainer.SharedPgContainer(t)
dbName, rawDB := testcontainer.NewPgDatabase(t)
_, err := rawDB.ExecContext(ctx, `
CREATE SCHEMA app;
CREATE TABLE public.lookup_precedence (marker text);
CREATE TABLE app.lookup_precedence (marker text);
INSERT INTO public.lookup_precedence VALUES ('public');
INSERT INTO app.lookup_precedence VALUES ('app');
CREATE FUNCTION public.polar_osfs_extract_table_name(rel text) RETURNS text
LANGUAGE sql
AS $$ SELECT rel $$;
CREATE FUNCTION public.polar_alter_relation_to_oss_with_indexes(rel text) RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
RETURN polar_osfs_extract_table_name(rel);
END;
$$;
`)
require.NoError(t, err)
driver, err := (&Driver{}).Open(ctx, storepb.Engine_POSTGRES, db.ConnectionConfig{
DataSource: &storepb.DataSource{
Host: pgContainer.GetHost(),
Port: pgContainer.GetPort(),
Username: "postgres",
},
Password: "root-password",
ConnectionContext: db.ConnectionContext{DatabaseName: dbName},
})
require.NoError(t, err)
defer driver.Close(ctx)
conn, err := driver.GetDB().Conn(ctx)
require.NoError(t, err)
defer conn.Close()
queryContext := db.QueryContext{
Schema: "app",
Limit: 5000,
MaximumSQLResultSize: 1 << 30,
}
results, err := driver.QueryConn(ctx, conn, `SELECT public.polar_alter_relation_to_oss_with_indexes('content_messages_2025q1'::text);`, queryContext)
require.NoError(t, err)
require.Equal(t, "content_messages_2025q1", firstStringValue(t, results))
results, err = driver.QueryConn(ctx, conn, `SELECT marker FROM lookup_precedence;`, queryContext)
require.NoError(t, err)
require.Equal(t, "app", firstStringValue(t, results))
}
func TestQueryConnSearchPathEscapesSelectedSchemaName(t *testing.T) {
t.Parallel()
ctx := context.Background()
pgContainer := testcontainer.SharedPgContainer(t)
dbName, rawDB := testcontainer.NewPgDatabase(t)
_, err := rawDB.ExecContext(ctx, `
CREATE SCHEMA "app""schema";
CREATE TABLE "app""schema".lookup_precedence (marker text);
INSERT INTO "app""schema".lookup_precedence VALUES ('quoted');
`)
require.NoError(t, err)
driver, err := (&Driver{}).Open(ctx, storepb.Engine_POSTGRES, db.ConnectionConfig{
DataSource: &storepb.DataSource{
Host: pgContainer.GetHost(),
Port: pgContainer.GetPort(),
Username: "postgres",
},
Password: "root-password",
ConnectionContext: db.ConnectionContext{DatabaseName: dbName},
})
require.NoError(t, err)
defer driver.Close(ctx)
conn, err := driver.GetDB().Conn(ctx)
require.NoError(t, err)
defer conn.Close()
results, err := driver.QueryConn(ctx, conn, `SELECT marker FROM lookup_precedence;`, db.QueryContext{
Schema: `app"schema`,
Limit: 5000,
MaximumSQLResultSize: 1 << 30,
})
require.NoError(t, err)
require.Equal(t, "quoted", firstStringValue(t, results))
}
// TestQueryConnExplainFormat pins both explain outputs at the driver boundary:
// the default stays the human-readable text plan, and JSON returns the plan tree
// as a single row. The JSON assertion is what a plan renderer depends on, and
// running it through QueryConn also proves the editor's read-only validator
// accepts the parenthesized EXPLAIN form.
func TestQueryConnExplainFormat(t *testing.T) {
t.Parallel()
ctx := context.Background()
pgContainer := testcontainer.SharedPgContainer(t)
dbName, rawDB := testcontainer.NewPgDatabase(t)
_, err := rawDB.ExecContext(ctx, `
CREATE TABLE plan_target (id int PRIMARY KEY, note text);
INSERT INTO plan_target SELECT g, 'n' || g FROM generate_series(1, 500) g;
ANALYZE plan_target;
`)
require.NoError(t, err)
driver, err := (&Driver{}).Open(ctx, storepb.Engine_POSTGRES, db.ConnectionConfig{
DataSource: &storepb.DataSource{
Host: pgContainer.GetHost(),
Port: pgContainer.GetPort(),
Username: "postgres",
},
Password: "root-password",
ConnectionContext: db.ConnectionContext{DatabaseName: dbName},
})
require.NoError(t, err)
defer driver.Close(ctx)
conn, err := driver.GetDB().Conn(ctx)
require.NoError(t, err)
defer conn.Close()
const statement = `SELECT note FROM plan_target WHERE id > 100;`
textResults, err := driver.QueryConn(ctx, conn, statement, db.QueryContext{
Explain: true,
Limit: 5000,
MaximumSQLResultSize: 1 << 30,
})
require.NoError(t, err)
require.NotEmpty(t, textResults)
require.Empty(t, textResults[0].GetError())
require.Contains(t, firstStringValue(t, textResults), "(cost=")
jsonResults, err := driver.QueryConn(ctx, conn, statement, db.QueryContext{
Explain: true,
Limit: 5000,
MaximumSQLResultSize: 1 << 30,
Option: &v1pb.QueryOption{ExplainFormat: v1pb.QueryOption_JSON},
})
require.NoError(t, err)
require.NotEmpty(t, jsonResults)
require.Empty(t, jsonResults[0].GetError())
require.Len(t, jsonResults[0].GetRows(), 1)
var plan []struct {
Plan struct {
NodeType string `json:"Node Type"`
RelationName string `json:"Relation Name"`
TotalCost float64 `json:"Total Cost"`
PlanRows float64 `json:"Plan Rows"`
} `json:"Plan"`
}
require.NoError(t, json.Unmarshal([]byte(firstStringValue(t, jsonResults)), &plan))
require.Len(t, plan, 1)
require.NotEmpty(t, plan[0].Plan.NodeType)
require.Equal(t, "plan_target", plan[0].Plan.RelationName)
require.Positive(t, plan[0].Plan.TotalCost)
require.Positive(t, plan[0].Plan.PlanRows)
// A statement that already asks for a plan is planned once: the request's
// format replaces the caller's EXPLAIN instead of wrapping it, which the
// server would reject as a syntax error.
planOfPlan, err := driver.QueryConn(ctx, conn, "EXPLAIN (FORMAT TEXT) "+statement, db.QueryContext{
Explain: true,
Limit: 5000,
MaximumSQLResultSize: 1 << 30,
Option: &v1pb.QueryOption{ExplainFormat: v1pb.QueryOption_JSON},
})
require.NoError(t, err)
require.NotEmpty(t, planOfPlan)
require.Empty(t, planOfPlan[0].GetError())
require.NoError(t, json.Unmarshal([]byte(firstStringValue(t, planOfPlan)), &plan))
require.Len(t, plan, 1)
require.Equal(t, "plan_target", plan[0].Plan.RelationName)
}
func TestTypedExplainPlan(t *testing.T) {
t.Parallel()
for _, tc := range []struct {
statement string
want *v1pb.QueryResult_QueryPlan
}{
{statement: "SELECT 1"},
{statement: "EXPLAIN SELECT 1", want: &v1pb.QueryResult_QueryPlan{Format: v1pb.QueryOption_TEXT}},
{statement: "EXPLAIN (FORMAT JSON) SELECT 1", want: &v1pb.QueryResult_QueryPlan{Format: v1pb.QueryOption_JSON}},
{statement: "EXPLAIN (ANALYZE, FORMAT XML) SELECT 1", want: &v1pb.QueryResult_QueryPlan{Format: v1pb.QueryOption_XML, Executed: true}},
{statement: "EXPLAIN (FORMAT YAML) SELECT 1", want: &v1pb.QueryResult_QueryPlan{Format: v1pb.QueryOption_YAML}},
} {
require.Equal(t, tc.want, db.TypedExplainPlan(pgparser.DescribeExplain(tc.statement)), tc.statement)
}
}
func firstStringValue(t *testing.T, results []*v1pb.QueryResult) string {
t.Helper()
require.NotEmpty(t, results)
require.Empty(t, results[0].GetError())
require.NotEmpty(t, results[0].GetRows())
require.NotEmpty(t, results[0].GetRows()[0].GetValues())
return results[0].GetRows()[0].GetValues()[0].GetStringValue()
}
// TestQueryConnMergeCTEDoesNotLeakRows is a security regression test for the
// PostgreSQL data-masking bypass where a MERGE smuggled into a data-modifying CTE
//
// WITH x AS (MERGE ... RETURNING masked_col) SELECT masked_col FROM x
//
// was misclassified as a read-only query (hasDMLInTree did not recognise MergeStmt
// as a write). That routed it through QueryConn's row-returning QueryContext path,
// so the RETURNING columns came back to the client and — because the query-span
// extractor cannot trace lineage through a MERGE — were returned UNMASKED.
//
// The fix classifies MERGE as a write, so the editor routes the statement to
// ExecContext, which discards the RETURNING rows. This test asserts the
// security-relevant outcome at the driver boundary: the MERGE-CTE must NOT return
// the secret value as data rows. MERGE ... RETURNING requires PostgreSQL 17.
func TestQueryConnMergeCTEDoesNotLeakRows(t *testing.T) {
t.Parallel()
ctx := context.Background()
pgContainer := testcontainer.SharedPg17Container(t)
dbName, rawDB := testcontainer.NewPg17Database(t)
const secret = "TOP-SECRET-SSN-123-45-6789"
require.NoError(t, rawDB.Ping())
_, err := rawDB.ExecContext(ctx, `CREATE TABLE secret_doc (id int PRIMARY KEY, secret text);`)
require.NoError(t, err)
_, err = rawDB.ExecContext(ctx, `INSERT INTO secret_doc VALUES (1, '`+secret+`');`)
require.NoError(t, err)
driver, err := (&Driver{}).Open(ctx, storepb.Engine_POSTGRES, db.ConnectionConfig{
DataSource: &storepb.DataSource{
Host: pgContainer.GetHost(),
Port: pgContainer.GetPort(),
Username: "postgres",
},
Password: "root-password",
ConnectionContext: db.ConnectionContext{DatabaseName: dbName},
})
require.NoError(t, err)
defer driver.Close(ctx)
conn, err := driver.GetDB().Conn(ctx)
require.NoError(t, err)
defer conn.Close()
// Baseline: a plain SELECT returns the secret as a data row. This proves the
// harness actually surfaces row data, so the negative assertion below is meaningful.
selectResults, err := driver.QueryConn(ctx, conn, `SELECT secret FROM secret_doc`, db.QueryContext{Limit: 5000, MaximumSQLResultSize: 1 << 30})
require.NoError(t, err)
require.True(t, resultsContainValue(selectResults, secret),
"baseline plain SELECT should return the secret as a data row")
// Exploit attempt: a MERGE smuggled into a data-modifying CTE. After the fix it
// is classified as a write and routed to Exec, so the RETURNING rows are dropped
// and the secret is never returned as data.
const mergeCTE = `WITH x AS (
MERGE INTO secret_doc t USING secret_doc s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET id = t.id
RETURNING t.secret AS secret
) SELECT secret FROM x`
mergeResults, err := driver.QueryConn(ctx, conn, mergeCTE, db.QueryContext{Limit: 5000, MaximumSQLResultSize: 1 << 30})
require.NoError(t, err)
require.False(t, resultsContainValue(mergeResults, secret),
"SECURITY: MERGE-in-CTE must not return the masked column as data rows; got %v", mergeResults)
}
// resultsContainValue reports whether any string cell in the results contains want.
func resultsContainValue(results []*v1pb.QueryResult, want string) bool {
for _, r := range results {
for _, row := range r.GetRows() {
for _, v := range row.GetValues() {
if sv, ok := v.GetKind().(*v1pb.RowValue_StringValue); ok && strings.Contains(sv.StringValue, want) {
return true
}
}
}
}
return false
}
// cloudSQLIAMConnectionConfig returns a Google Cloud SQL IAM data source that
// cloudsqlconn accepts offline. The credential is an authorized_user document so
// that building the connector needs neither a key nor a network round trip; it
// is never good enough to dial with.
const cloudSQLTestInstance = "example-project:us-central1:example-instance"
func cloudSQLIAMConnectionConfig(instance string) db.ConnectionConfig {
return db.ConnectionConfig{
DataSource: &storepb.DataSource{
Type: storepb.DataSourceType_ADMIN,
Host: instance,
Username: "[email protected]",
AuthenticationType: storepb.DataSource_GOOGLE_CLOUD_SQL_IAM,
IamExtension: &storepb.DataSource_GcpCredential{
GcpCredential: &storepb.DataSource_GCPCredential{
Content: `{"type":"authorized_user","client_id":"not-a-real-client","client_secret":"not-a-real-secret","refresh_token":"not-a-real-token"}`,
},
},
},
ConnectionContext: db.ConnectionContext{DatabaseName: "postgres"},
}
}
func TestCloudSQLIAMDialerClosesWithDriver(t *testing.T) {
ctx := context.Background()
driver := &Driver{}
opened, err := driver.Open(ctx, storepb.Engine_POSTGRES, cloudSQLIAMConnectionConfig(cloudSQLTestInstance))
require.NoError(t, err)
require.Same(t, driver, opened)
require.NotNil(t, driver.cloudSQLDialer)
dialer := driver.cloudSQLDialer
require.NoError(t, driver.Close(ctx))
// The connector keeps refreshing its ephemeral certificate until it is
// closed, so a driver that leaves it open leaks it for the life of the
// process, once per connection opened.
_, err = dialer.Dial(ctx, cloudSQLTestInstance)
require.ErrorIs(t, err, cloudsqlconn.ErrDialerClosed)
}
func TestCloudSQLIAMDialerClosesWhenOpenFails(t *testing.T) {
ctx := context.Background()
config := cloudSQLIAMConnectionConfig(cloudSQLTestInstance)
// pgx refuses a NUL byte anywhere in a connection string, so this username
// fails Open after the connector has been built.
config.DataSource.Username = "bytebase\x00"
driver := &Driver{}
_, err := driver.Open(ctx, storepb.Engine_POSTGRES, config)
require.Error(t, err)
// A driver whose Open fails is discarded without a Close, so Open has to have
// closed the connector itself.
require.NotNil(t, driver.cloudSQLDialer)
_, err = driver.cloudSQLDialer.Dial(ctx, cloudSQLTestInstance)
require.ErrorIs(t, err, cloudsqlconn.ErrDialerClosed)
}
func TestOpenFailureReleasesConnectionPool(t *testing.T) {
ctx := context.Background()
driver := &Driver{}
// Tenant mode looks up the database owner, so Open fails against a dead port
// only after it has built the connection pool.
_, err := driver.Open(ctx, storepb.Engine_POSTGRES, db.ConnectionConfig{
DataSource: &storepb.DataSource{
Type: storepb.DataSourceType_ADMIN,
Host: "127.0.0.1",
Port: "1",
Username: "bytebase",
},
ConnectionContext: db.ConnectionContext{DatabaseName: "postgres", TenantMode: true},
})
require.Error(t, err)
// A driver whose Open fails is discarded without a Close, so the pool has to
// be closed already. database/sql reports that only by message.
require.NotNil(t, driver.db)
require.ErrorContains(t, driver.db.PingContext(ctx), "database is closed")
}