Skip to content

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

25.1 KB
Raw Blame
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")
}