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 v1
import (
"context"
"strings"
"testing"
"time"
"connectrpc.com/connect"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/pkg/errors"
"github.com/stretchr/testify/require"
"github.com/bytebase/bytebase/backend/common"
"github.com/bytebase/bytebase/backend/common/permission"
"github.com/bytebase/bytebase/backend/common/testcontainer"
"github.com/bytebase/bytebase/backend/component/iam"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
parserbase "github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/store"
"github.com/bytebase/bytebase/backend/store/model"
)
func TestSQLIAMDatabaseResourceUsesCanonicalInstanceScope(t *testing.T) {
t.Parallel()
projectID := "project-a"
projectInstance := &store.InstanceMessage{ResourceID: "instance-a", ProjectID: &projectID}
workspaceInstance := &store.InstanceMessage{ResourceID: "instance-b"}
database := &store.DatabaseMessage{DatabaseName: "app"}
projectDatabaseName := formatDatabaseResourceName(projectInstance, &store.DatabaseMessage{
InstanceID: projectInstance.ResourceID,
DatabaseName: database.DatabaseName,
})
require.Equal(t,
"projects/project-a/instances/instance-a/databases/app",
projectDatabaseName,
)
require.Equal(t,
"instances/instance-b/databases/app",
formatDatabaseResourceName(workspaceInstance, &store.DatabaseMessage{
InstanceID: workspaceInstance.ResourceID,
DatabaseName: database.DatabaseName,
}),
)
attributes := map[string]any{common.CELAttributeResourceDatabase: projectDatabaseName}
allowed, err := evaluateQueryExportPolicyCondition(
`resource.database == "projects/project-a/instances/instance-a/databases/app"`, attributes)
require.NoError(t, err)
require.True(t, allowed)
allowed, err = evaluateQueryExportPolicyCondition(
`resource.database == "instances/instance-a/databases/app"`, attributes)
require.NoError(t, err)
require.False(t, allowed)
require.Equal(t,
"projects/project-a/instances/instance-a/databases/app/schemas/public/tables/users",
formatWriteTargetResource(projectDatabaseName, "public", "users"),
)
}
// TestExportRejectsRetiredRolloutNames pins the retired export-data routes:
// rollout/stage names must fail name parsing with InvalidArgument, not fall
// through as 500s.
func TestExportRejectsRetiredRolloutNames(t *testing.T) {
t.Parallel()
ctx := context.WithValue(context.Background(), common.UserContextKey, &store.UserMessage{Email: "[email protected]"})
s := &SQLService{}
for _, name := range []string{
"projects/p/plans/1/rollout",
"projects/p/plans/1/rollout/stages/dev",
} {
_, err := s.Export(ctx, connect.NewRequest(&v1pb.ExportRequest{Name: name, Statement: "SELECT 1"}))
require.Equal(t, connect.CodeInvalidArgument, connect.CodeOf(err), "name %q", name)
}
}
func TestRequiresAdminDataSourceForSQLServer(t *testing.T) {
t.Parallel()
ctx := context.Background()
require.False(t, requiresAdminDataSource(ctx, storepb.Engine_MSSQL, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\nSELECT id FROM t"))
require.True(t, requiresAdminDataSource(ctx, storepb.Engine_MSSQL, "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\nDELETE FROM t"))
require.True(t, requiresAdminDataSource(ctx, storepb.Engine_MSSQL, "DECLARE @n int = 1; SELECT @n"))
}
// TestSelectBestAccessGrantPrefersUnmask covers the Unmask-only ranking:
// Export plays no role (Export callers already filtered the pool via the
// `requireExport` CEL filter, and Query never reads `Payload.Export`).
// Preferring Unmask=true is what addresses PR #20491 bot review
// (#3349086819) — a user with an active unmask grant should never be
// masked by Query when an export-only grant shares the slice.
func TestSelectBestAccessGrantPrefersUnmask(t *testing.T) {
grantOf := func(unmask, export bool) *store.AccessGrantMessage {
return &store.AccessGrantMessage{
Payload: &storepb.AccessGrantPayload{Unmask: unmask, Export: export},
}
}
t.Run("returns nil for empty input", func(t *testing.T) {
require.Nil(t, selectBestAccessGrant(nil))
require.Nil(t, selectBestAccessGrant([]*store.AccessGrantMessage{}))
})
t.Run("skips nil-payload grants and returns nil when none remain", func(t *testing.T) {
got := selectBestAccessGrant([]*store.AccessGrantMessage{
{Payload: nil},
{Payload: nil},
})
require.Nil(t, got)
})
t.Run("returns the only valid grant when others have nil payloads", func(t *testing.T) {
valid := grantOf(true, false)
got := selectBestAccessGrant([]*store.AccessGrantMessage{
{Payload: nil},
valid,
{Payload: nil},
})
require.Same(t, valid, got)
})
t.Run("unmask grant wins over export-only grant regardless of slice order", func(t *testing.T) {
unmaskGrant := grantOf(true, false)
exportOnly := grantOf(false, true)
// Put exportOnly first to prove the win isn't due to position —
// Unmask scoring is what selects unmaskGrant.
got := selectBestAccessGrant([]*store.AccessGrantMessage{exportOnly, unmaskGrant})
require.Same(t, unmaskGrant, got)
})
t.Run("among multiple unmask grants the first in slice order wins", func(t *testing.T) {
first := grantOf(true, false)
second := grantOf(true, true)
// Both have Unmask=true → tied score (Export plays no role). First
// in slice wins via strict ">". Equivalent for Query callers
// since both grants yield the same SkipMasking=true.
got := selectBestAccessGrant([]*store.AccessGrantMessage{first, second})
require.Same(t, first, got)
})
t.Run("returns a no-unmask grant when none have unmask", func(t *testing.T) {
// All-zero-score case: a no-unmask grant is still returned (it
// confers ACL bypass for Query, even if it can't unmask).
exportOnly := grantOf(false, true)
got := selectBestAccessGrant([]*store.AccessGrantMessage{exportOnly})
require.Same(t, exportOnly, got)
})
}
// TestBuildExportQueryContextPropagatesSkipMasking pins the bug fix from PR
// #20487: when an export is authorized by a JIT grant with unmask=true,
// SkipMasking must reach db.QueryContext so the driver doesn't mask rows at
// query time. The earlier code only consulted skipMasking around the
// post-execution MaskResults pass — by then drivers like postgres with
// query-time masking rewrites had already returned masked rows.
func TestBuildExportQueryContextPropagatesSkipMasking(t *testing.T) {
restriction := &store.EffectiveQueryDataPolicy{
MaximumResultRows: 1000,
MaximumResultSize: 1 << 20,
}
t.Run("grant with unmask=true sets SkipMasking", func(t *testing.T) {
qc := buildExportQueryContext(restriction, "[email protected]", nil, "", true)
require.True(t, qc.SkipMasking, "SkipMasking must propagate so driver-level masking is bypassed")
})
t.Run("grant with unmask=false keeps SkipMasking false", func(t *testing.T) {
qc := buildExportQueryContext(restriction, "[email protected]", nil, "", false)
require.False(t, qc.SkipMasking, "SkipMasking must stay false so masking still applies")
})
}
// TestBuildExportQueryContextPropagatesOtherFields guards against accidental
// drops of the surrounding fields if buildExportQueryContext is edited.
func TestBuildExportQueryContextPropagatesOtherFields(t *testing.T) {
t.Parallel()
schema := "public"
restriction := &store.EffectiveQueryDataPolicy{
MaximumResultRows: 500,
MaximumResultSize: 2 << 20,
MaxQueryTimeoutInSeconds: 30,
}
qc := buildExportQueryContext(restriction, "[email protected]", &schema, "customers", false)
require.Equal(t, 500, qc.Limit)
require.Equal(t, "[email protected]", qc.OperatorEmail)
require.Equal(t, int64(2<<20), qc.MaximumSQLResultSize)
require.Equal(t, "public", qc.Schema)
require.Equal(t, "customers", qc.Container)
require.NotNil(t, qc.Timeout)
require.Equal(t, int64(30), qc.Timeout.Seconds)
}
// TestBuildExportQueryContextOmitsTimeoutWhenZero verifies that an unset
// MaxQueryTimeoutInSeconds doesn't leak a zero-Duration timeout into the
// query context (which the driver layer treats differently from "no timeout").
func TestBuildExportQueryContextOmitsTimeoutWhenZero(t *testing.T) {
t.Parallel()
restriction := &store.EffectiveQueryDataPolicy{
MaximumResultRows: 100,
MaximumResultSize: 1 << 20,
}
qc := buildExportQueryContext(restriction, "[email protected]", nil, "", false)
require.Nil(t, qc.Timeout)
require.Equal(t, "", qc.Schema)
}
func TestResolveDataSourceIDUsesAdminForNonReadOnlyAutomaticQueryWhenAllowed(t *testing.T) {
t.Parallel()
instance := &store.InstanceMessage{
Metadata: &storepb.Instance{
Engine: storepb.Engine_MYSQL,
DataSources: []*storepb.DataSource{
{Id: "admin", Type: storepb.DataSourceType_ADMIN},
{Id: "readonly", Type: storepb.DataSourceType_READ_ONLY},
},
},
}
got, err := resolveDataSourceID(context.Background(), instance, "", "INSERT INTO books VALUES (1, 'Bytebase');", true)
require.NoError(t, err)
require.Equal(t, "admin", got)
}
func TestResolveDataSourceIDKeepsReadOnlyForReadOnlyAutomaticQueryWhenAllowed(t *testing.T) {
t.Parallel()
instance := &store.InstanceMessage{
Metadata: &storepb.Instance{
Engine: storepb.Engine_MYSQL,
DataSources: []*storepb.DataSource{
{Id: "admin", Type: storepb.DataSourceType_ADMIN},
{Id: "readonly", Type: storepb.DataSourceType_READ_ONLY},
},
},
}
got, err := resolveDataSourceID(context.Background(), instance, "", "SELECT * FROM books;", true)
require.NoError(t, err)
require.Equal(t, "readonly", got)
}
func TestResolveDataSourceIDKeepsReadOnlyForNonReadOnlyAutomaticQueryWhenAdminDisallowed(t *testing.T) {
t.Parallel()
instance := &store.InstanceMessage{
Metadata: &storepb.Instance{
Engine: storepb.Engine_MYSQL,
DataSources: []*storepb.DataSource{
{Id: "admin", Type: storepb.DataSourceType_ADMIN},
{Id: "readonly", Type: storepb.DataSourceType_READ_ONLY},
},
},
}
got, err := resolveDataSourceID(context.Background(), instance, "", "INSERT INTO books VALUES (1, 'Bytebase');", false)
require.NoError(t, err)
require.Equal(t, "readonly", got)
}
func TestResolveDataSourceIDUsesAdminForDocumentEngineAutomaticWriteQueryWhenAllowed(t *testing.T) {
t.Parallel()
tests := []struct {
name string
engine storepb.Engine
statement string
}{
{
name: "MongoDB DML",
engine: storepb.Engine_MONGODB,
statement: `db.users.insertOne({name: "Bytebase"})`,
},
{
name: "MongoDB DDL",
engine: storepb.Engine_MONGODB,
statement: `db.createCollection("users")`,
},
{
name: "Elasticsearch DML",
engine: storepb.Engine_ELASTICSEARCH,
statement: "POST /users/_doc\n{\"name\":\"Bytebase\"}",
},
{
name: "Elasticsearch DDL",
engine: storepb.Engine_ELASTICSEARCH,
statement: "PUT /users",
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
t.Parallel()
instance := &store.InstanceMessage{
Metadata: &storepb.Instance{
Engine: tc.engine,
DataSources: []*storepb.DataSource{
{Id: "admin", Type: storepb.DataSourceType_ADMIN},
{Id: "readonly", Type: storepb.DataSourceType_READ_ONLY},
},
},
}
got, err := resolveDataSourceID(context.Background(), instance, "", tc.statement, true)
require.NoError(t, err)
require.Equal(t, "admin", got)
})
}
}
func TestResolveDataSourceIDKeepsReadOnlyForDocumentEngineAutomaticReadQueryWhenAllowed(t *testing.T) {
t.Parallel()
tests := []struct {
name string
engine storepb.Engine
statement string
}{
{
name: "MongoDB read",
engine: storepb.Engine_MONGODB,
statement: `db.users.find({})`,
},
{
name: "Elasticsearch read",
engine: storepb.Engine_ELASTICSEARCH,
statement: "GET /users/_search",
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
t.Parallel()
instance := &store.InstanceMessage{
Metadata: &storepb.Instance{
Engine: tc.engine,
DataSources: []*storepb.DataSource{
{Id: "admin", Type: storepb.DataSourceType_ADMIN},
{Id: "readonly", Type: storepb.DataSourceType_READ_ONLY},
},
},
}
got, err := resolveDataSourceID(context.Background(), instance, "", tc.statement, true)
require.NoError(t, err)
require.Equal(t, "readonly", got)
})
}
}
// TestValidateExplainFormat pins the engine-to-format support table. A format an
// engine cannot produce has to be refused here, because the drivers below map
// anything that reaches them onto their own default rather than failing.
func TestValidateExplainFormat(t *testing.T) {
t.Parallel()
for _, tc := range []struct {
name string
engine storepb.Engine
format v1pb.QueryOption_ExplainFormat
wantErr bool
}{
{name: "unspecified is always the engine default", engine: storepb.Engine_MYSQL, format: v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED},
{name: "postgres json", engine: storepb.Engine_POSTGRES, format: v1pb.QueryOption_JSON},
{name: "postgres xml", engine: storepb.Engine_POSTGRES, format: v1pb.QueryOption_XML},
{name: "postgres text", engine: storepb.Engine_POSTGRES, format: v1pb.QueryOption_TEXT},
{name: "postgres yaml", engine: storepb.Engine_POSTGRES, format: v1pb.QueryOption_YAML},
{name: "mssql yaml", engine: storepb.Engine_MSSQL, format: v1pb.QueryOption_YAML, wantErr: true},
{name: "mssql xml", engine: storepb.Engine_MSSQL, format: v1pb.QueryOption_XML},
{name: "mssql json", engine: storepb.Engine_MSSQL, format: v1pb.QueryOption_JSON, wantErr: true},
{name: "spanner json", engine: storepb.Engine_SPANNER, format: v1pb.QueryOption_JSON},
{name: "spanner has no text plan", engine: storepb.Engine_SPANNER, format: v1pb.QueryOption_TEXT, wantErr: true},
{name: "mysql text", engine: storepb.Engine_MYSQL, format: v1pb.QueryOption_TEXT},
{name: "mysql json", engine: storepb.Engine_MYSQL, format: v1pb.QueryOption_JSON},
{name: "mysql has no xml plan", engine: storepb.Engine_MYSQL, format: v1pb.QueryOption_XML, wantErr: true},
{name: "mariadb json", engine: storepb.Engine_MARIADB, format: v1pb.QueryOption_JSON},
{name: "tidb json", engine: storepb.Engine_TIDB, format: v1pb.QueryOption_JSON},
{name: "oceanbase json", engine: storepb.Engine_OCEANBASE, format: v1pb.QueryOption_JSON},
{name: "oracle json", engine: storepb.Engine_ORACLE, format: v1pb.QueryOption_JSON},
{name: "oracle xml is not implemented yet", engine: storepb.Engine_ORACLE, format: v1pb.QueryOption_XML, wantErr: true},
{name: "clickhouse json", engine: storepb.Engine_CLICKHOUSE, format: v1pb.QueryOption_JSON},
{name: "trino json", engine: storepb.Engine_TRINO, format: v1pb.QueryOption_JSON},
// CockroachDB's own EXPLAIN takes no JSON — the plan the driver reads is
// the one encoded in the diagram EXPLAIN (DISTSQL) links to.
{name: "cockroachdb json", engine: storepb.Engine_COCKROACHDB, format: v1pb.QueryOption_JSON},
{name: "starrocks json is a syntax error on the engine", engine: storepb.Engine_STARROCKS, format: v1pb.QueryOption_JSON, wantErr: true},
{name: "doris text", engine: storepb.Engine_DORIS, format: v1pb.QueryOption_TEXT},
{name: "doris json is a syntax error on the engine too", engine: storepb.Engine_DORIS, format: v1pb.QueryOption_JSON, wantErr: true},
{name: "hive json", engine: storepb.Engine_HIVE, format: v1pb.QueryOption_JSON},
{name: "redis explains nothing at all", engine: storepb.Engine_REDIS, format: v1pb.QueryOption_TEXT, wantErr: true},
{name: "mongodb json", engine: storepb.Engine_MONGODB, format: v1pb.QueryOption_JSON},
{name: "mongodb has no text plan", engine: storepb.Engine_MONGODB, format: v1pb.QueryOption_TEXT, wantErr: true},
// A driver that ignores the explain flag runs the statement instead, and
// the explain path skips the read-only validation, so an unspecified
// format must be refused here too rather than reaching the driver.
{name: "cassandra refuses even an unspecified format", engine: storepb.Engine_CASSANDRA, format: v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED, wantErr: true},
{name: "cosmosdb refuses even an unspecified format", engine: storepb.Engine_COSMOSDB, format: v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED, wantErr: true},
// Databricks plans a statement through Spark's own EXPLAIN, whose only
// plan is text.
{name: "databricks text", engine: storepb.Engine_DATABRICKS, format: v1pb.QueryOption_TEXT},
{name: "databricks json has no reader", engine: storepb.Engine_DATABRICKS, format: v1pb.QueryOption_JSON, wantErr: true},
// Elasticsearch states one plan, its profile, which it answers with only
// for a search it ran; the driver refuses to profile anything else.
{name: "elasticsearch default", engine: storepb.Engine_ELASTICSEARCH, format: v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED},
{name: "elasticsearch json", engine: storepb.Engine_ELASTICSEARCH, format: v1pb.QueryOption_JSON},
{name: "elasticsearch has no text plan", engine: storepb.Engine_ELASTICSEARCH, format: v1pb.QueryOption_TEXT, wantErr: true},
} {
t.Run(tc.name, func(t *testing.T) {
t.Parallel()
err := validateExplainFormat(tc.engine, tc.format)
if !tc.wantErr {
require.NoError(t, err)
return
}
require.Error(t, err)
require.Equal(t, connect.CodeInvalidArgument, connect.CodeOf(err))
})
}
}
// explainPrefixEngines are the engines that plan a statement by prefixing EXPLAIN
// to it, and so run a different statement than the one the caller sent.
//
// The engine parsers are registered for the whole v1 test binary by blank imports
// elsewhere in the package, so each call below returns that engine's real verdict
// rather than a no-parser default.
//
// Databricks prefixes EXPLAIN too and is left out: it is the one SQL engine with
// no read-only validator registered, so the gate has nothing to classify the
// planned form with — for an ordinary query as much as for an explain. What
// keeps a write from running there is Spark's EXPLAIN, which compiles a
// statement without executing it, and the planner reading the caller's comments
// as Databricks does, so a write behind one is planned rather than preserved.
// See TestExplainGateOnlyPlansOnDatabricks.
var explainPrefixEngines = []storepb.Engine{
storepb.Engine_POSTGRES, storepb.Engine_MYSQL, storepb.Engine_MARIADB,
storepb.Engine_OCEANBASE, storepb.Engine_TIDB, storepb.Engine_REDSHIFT,
storepb.Engine_COCKROACHDB, storepb.Engine_SNOWFLAKE, storepb.Engine_CLICKHOUSE,
storepb.Engine_STARROCKS, storepb.Engine_DORIS, storepb.Engine_HIVE,
storepb.Engine_TRINO,
}
// TestExplainGateOnlyPlansOnDatabricks locks what stands in for the smuggle
// defense on the one engine whose planned form the gate cannot classify: every
// statement an explain request reaches the driver with begins with EXPLAIN, so
// Spark compiles it rather than running it. A write the caller sends is planned,
// not executed.
func TestExplainGateOnlyPlansOnDatabricks(t *testing.T) {
t.Parallel()
for _, tc := range []struct {
name string
statement string
want string
}{
{name: "a write is planned", statement: "DELETE FROM t WHERE id = 1", want: "EXPLAIN FORMATTED DELETE FROM t WHERE id = 1"},
{name: "ANALYZE DELETE is planned", statement: "ANALYZE DELETE FROM t", want: "EXPLAIN FORMATTED ANALYZE DELETE FROM t"},
// Databricks block comments nest, so the EXPLAIN here is inside one and
// the statement is the delete.
{name: "a write behind a nested comment", statement: "/* outer /* inner */\nEXPLAIN\n*/\nDELETE FROM t", want: "EXPLAIN FORMATTED /* outer /* inner */\nEXPLAIN\n*/\nDELETE FROM t"},
{name: "a plan the caller asked for", statement: "EXPLAIN SELECT 1", want: "EXPLAIN SELECT 1"},
} {
t.Run(tc.name, func(t *testing.T) {
t.Parallel()
planned, err := parserbase.ExplainStatement(storepb.Engine_DATABRICKS, tc.statement, parserbase.ExplainFormatDefault)
require.NoError(t, err)
require.Equal(t, tc.want, planned)
})
}
}
// TestExplainGateRejectsSmuggledWrite locks the smuggle defense. An explain
// request carries the bare statement, so "ANALYZE DELETE FROM t" — not valid SQL
// on its own — would become EXPLAIN ANALYZE DELETE and execute the DELETE if
// anything simply prefixed it. Every prefix engine must refuse it, by parsing the
// statement it was handed or by classifying the planned form as non-read-only, so
// it never reaches the driver.
func TestExplainGateRejectsSmuggledWrite(t *testing.T) {
t.Parallel()
for _, engine := range explainPrefixEngines {
t.Run(engine.String(), func(t *testing.T) {
t.Parallel()
instance := &store.InstanceMessage{Metadata: &storepb.Instance{Engine: engine}}
require.Error(t, validateExplainStatements(instance, "ANALYZE DELETE FROM t", v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED),
"EXPLAIN ANALYZE DELETE must not pass the read-only gate")
})
}
}
// TestExplainGatePlansAPlanOnce locks the rebuild. A statement that already asks
// for a plan is planned once: the request's EXPLAIN replaces the caller's rather
// than wrapping it, which a second EXPLAIN would turn into a syntax error.
func TestExplainGatePlansAPlanOnce(t *testing.T) {
t.Parallel()
for _, engine := range explainPrefixEngines {
t.Run(engine.String(), func(t *testing.T) {
t.Parallel()
instance := &store.InstanceMessage{Metadata: &storepb.Instance{Engine: engine}}
require.NoError(t, validateExplainStatements(instance, "EXPLAIN SELECT 1", v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED))
})
}
}
// TestExplainGateNeverAnalyzes locks what the rebuild is for: an EXPLAIN ANALYZE
// of a write executes the write, so no explain request may end up running one,
// whatever the caller wrote. Each engine either plans the write without the
// caller's ANALYZE or refuses the statement — a dialect with no EXPLAIN ANALYZE
// cannot parse it, and ClickHouse and Hive have no AST to rebuild it with, so
// theirs runs as written and their read-only gate refuses it.
func TestExplainGateNeverAnalyzes(t *testing.T) {
t.Parallel()
for _, engine := range explainPrefixEngines {
t.Run(engine.String(), func(t *testing.T) {
t.Parallel()
planned, err := parserbase.ExplainStatement(engine, "EXPLAIN ANALYZE DELETE FROM t", parserbase.ExplainFormatDefault)
if err != nil {
instance := &store.InstanceMessage{Metadata: &storepb.Instance{Engine: engine}}
require.Error(t, validateExplainStatements(instance, "EXPLAIN ANALYZE DELETE FROM t", v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED),
"a statement the parser will not plan must not reach the driver")
return
}
if strings.Contains(strings.ToUpper(planned), "ANALYZE") {
// The statement runs as the caller wrote it, so the read-only gate
// is what stands between it and the DELETE.
require.Equal(t, "EXPLAIN ANALYZE DELETE FROM t", planned)
instance := &store.InstanceMessage{Metadata: &storepb.Instance{Engine: engine}}
require.Error(t, validateExplainStatements(instance, "EXPLAIN ANALYZE DELETE FROM t", v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED))
return
}
require.Equal(t, "EXPLAIN DELETE FROM t", planned)
})
}
}
// TestExplainGateAllowsPlans confirms the gate does not over-reject legitimate
// plans. A plain EXPLAIN plans without executing, so a read or a write is allowed;
// and because the driver splits a multi-statement request and prefixes EXPLAIN to
// each statement (pg.go and siblings), the gate does the same with the same
// per-engine splitter — a batch of plain writes is planned one at a time. A write
// smuggled into a batch as EXPLAIN ANALYZE is still refused wherever it sits;
// TestExplainGateRejectsSmuggledWrite is the single-statement sweep over every
// engine.
func TestExplainGateAllowsPlans(t *testing.T) {
t.Parallel()
pg := &store.InstanceMessage{Metadata: &storepb.Instance{Engine: storepb.Engine_POSTGRES}}
// Plain single-statement plans, read or write, are allowed.
for _, stmt := range []string{"SELECT 1", "DELETE FROM t"} {
require.NoError(t, validateExplainStatements(pg, stmt, v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED), stmt)
}
// Engines that split on ';' plan each statement in its own non-executing EXPLAIN,
// so a batch of plain writes is allowed rather than over-rejected.
for _, engine := range []storepb.Engine{storepb.Engine_POSTGRES, storepb.Engine_MYSQL} {
instance := &store.InstanceMessage{Metadata: &storepb.Instance{Engine: engine}}
require.NoErrorf(t, validateExplainStatements(instance, "DELETE FROM hello; DELETE FROM hello", v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED), "%s: batch of plain writes", engine)
}
// A write smuggled into a batch is refused wherever it sits — on engines that
// split it (pg, mysql) and on one whose splitter keeps it whole and validates
// the unwrapped tail (clickhouse).
for _, engine := range []storepb.Engine{storepb.Engine_POSTGRES, storepb.Engine_MYSQL, storepb.Engine_CLICKHOUSE} {
instance := &store.InstanceMessage{Metadata: &storepb.Instance{Engine: engine}}
require.Errorf(t, validateExplainStatements(instance, "SELECT 1; ANALYZE DELETE FROM t", v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED), "%s: smuggle after a read", engine)
require.Errorf(t, validateExplainStatements(instance, "ANALYZE DELETE FROM t; SELECT 1", v1pb.QueryOption_EXPLAIN_FORMAT_UNSPECIFIED), "%s: smuggle before a read", engine)
}
}
// explainAccessFixture is one instance holding a database of project A and a
// database of project B, with users whose explain and select rights differ
// between the two projects.
type explainAccessFixture struct {
service *SQLService
instance *store.InstanceMessage
database *store.DatabaseMessage
}
func newExplainAccessFixture(ctx context.Context, t *testing.T, engine storepb.Engine) *explainAccessFixture {
t.Helper()
db, stores, _ := testcontainer.NewMetadataDB(t)
_, err := db.ExecContext(ctx, `
INSERT INTO workspace (resource_id) VALUES ('default');
INSERT INTO principal (name, email, password_hash) VALUES
('explain-a', '[email protected]', 'unused'),
('explain-ab', '[email protected]', 'unused'),
('select-a', '[email protected]', 'unused'),
('both-a', '[email protected]', 'unused');
INSERT INTO project (resource_id, workspace, name) VALUES ('project-a', 'default', 'A'), ('project-b', 'default', 'B');`)
require.NoError(t, err)
for _, role := range []struct {
id string
perm permission.Permission
}{
{id: "explainer", perm: permission.SQLExplain},
{id: "selector", perm: permission.SQLSelect},
} {
_, err = stores.CreateRole(ctx, &store.RoleMessage{
ResourceID: role.id, Workspace: "default", Name: role.id,
Permissions: map[permission.Permission]bool{role.perm: true},
})
require.NoError(t, err)
}
for _, binding := range []struct {
project string
role string
members []string
}{
{project: "project-a", role: "explainer", members: []string{"[email protected]", "[email protected]", "[email protected]"}},
{project: "project-a", role: "selector", members: []string{"[email protected]", "[email protected]"}},
{project: "project-b", role: "explainer", members: []string{"[email protected]"}},
} {
members := make([]string, 0, len(binding.members))
for _, member := range binding.members {
members = append(members, common.FormatUserEmail(member))
}
_, err = stores.PatchIamPolicy(ctx, "default", storepb.Policy_PROJECT, common.FormatProject(binding.project), func(policy *storepb.IamPolicy) error {
policy.Bindings = append(policy.Bindings, &storepb.Binding{Role: "roles/" + binding.role, Members: members})
return nil
})
require.NoError(t, err)
}
instance, err := stores.CreateInstance(ctx, &store.InstanceMessage{
ResourceID: "test", Workspace: "default",
// lower_case_table_names=1 makes the instance fold case, so DB_B names db_b.
Metadata: &storepb.Instance{Engine: engine, Activation: true, MysqlLowerCaseTableNames: 1, DataSources: []*storepb.DataSource{{Id: "admin", Type: storepb.DataSourceType_ADMIN}}},
})
require.NoError(t, err)
var connected *store.DatabaseMessage
for name, project := range map[string]string{"db_a": "project-a", "db_b": "project-b"} {
database, err := stores.UpsertDatabase(ctx, &store.DatabaseMessage{
InstanceID: "test", DatabaseName: name, ProjectID: project, Metadata: &storepb.DatabaseMetadata{},
})
require.NoError(t, err)
if name == "db_a" {
connected = database
}
}
iamManager, err := iam.NewManager(stores, nil, false)
require.NoError(t, err)
return &explainAccessFixture{
service: &SQLService{store: stores, iamManager: iamManager},
instance: instance,
database: connected,
}
}
func (f *explainAccessFixture) check(ctx context.Context, email string, isExplain bool, statement string, span *parserbase.QuerySpan) error {
user := &store.UserMessage{Email: email}
return f.service.accessCheckWithGrantedTargets(ctx, f.instance, f.database, user, []*parserbase.QuerySpan{span}, isExplain, []parserbase.Statement{{Text: statement}}, nil, false, "", false)
}
func reads(databases ...string) parserbase.SourceColumnSet {
columns := parserbase.SourceColumnSet{}
for _, database := range databases {
columns[parserbase.ColumnResource{Database: database, Table: "t"}] = true
}
return columns
}
func requireExplainDenied(t *testing.T, err error, perm permission.Permission, resources ...string) {
t.Helper()
var denied *queryError
require.ErrorAs(t, err, &denied)
require.Equal(t, connect.CodePermissionDenied, connect.CodeOf(denied.err))
require.Equal(t, perm, denied.permission)
require.Equal(t, resources, denied.resources)
}
// TestExplainRequiresEachReferencedDatabase pins the plan boundary: a plan
// describes every database its statement references, a database in another
// project fails closed as it does for a write, and a typed EXPLAIN ANALYZE
// needs the statement's permission as well as explain.
func TestExplainRequiresEachReferencedDatabase(t *testing.T) {
t.Parallel()
ctx := context.WithValue(context.Background(), common.WorkspaceIDContextKey, "default")
f := newExplainAccessFixture(ctx, t, storepb.Engine_MYSQL)
const dbB = "instances/test/databases/db_b"
t.Run("explain request", func(t *testing.T) {
t.Parallel()
crossRead := &parserbase.QuerySpan{Type: parserbase.Select, SourceColumns: reads("db_a", "db_b")}
require.NoError(t, f.check(ctx, "[email protected]", true, "SELECT 1", &parserbase.QuerySpan{Type: parserbase.Select, SourceColumns: reads("db_a")}))
requireExplainDenied(t, f.check(ctx, "[email protected]", true, "SELECT 1", crossRead), permission.SQLExplain, dbB)
requireExplainDenied(t, f.check(ctx, "[email protected]", true, "SELECT 1", crossRead), permission.SQLExplain, dbB)
requireExplainDenied(t, f.check(ctx, "[email protected]", true, "SELECT 1", crossRead), permission.SQLExplain, "instances/test/databases/db_a", dbB)
require.NoError(t, f.check(ctx, "[email protected]", true, "SELECT 1", &parserbase.QuerySpan{Type: parserbase.Select, SourceColumns: reads("db_a", "SYS")}), "an untracked database is left to the connected database's check")
})
t.Run("typed EXPLAIN", func(t *testing.T) {
t.Parallel()
crossRead := &parserbase.QuerySpan{Type: parserbase.Explain, SourceColumns: reads("db_a", "db_b")}
requireExplainDenied(t, f.check(ctx, "[email protected]", false, "EXPLAIN SELECT 1", crossRead), permission.SQLExplain, dbB)
requireExplainDenied(t, f.check(ctx, "[email protected]", false, "EXPLAIN SELECT 1", &parserbase.QuerySpan{Type: parserbase.Explain, SourceColumns: reads("DB_B")}), permission.SQLExplain, dbB)
require.NoError(t, f.check(ctx, "[email protected]", false, "EXPLAIN SELECT 1", &parserbase.QuerySpan{Type: parserbase.Explain, SourceColumns: reads("db_a")}))
})
t.Run("typed EXPLAIN ANALYZE", func(t *testing.T) {
t.Parallel()
analyze := &parserbase.QuerySpan{Type: parserbase.Select, ExplainAnalyze: true, SourceColumns: reads("db_a")}
requireExplainDenied(t, f.check(ctx, "[email protected]", false, "EXPLAIN ANALYZE SELECT 1", analyze), permission.SQLSelect, "instances/test/databases/db_a/tables/t")
requireExplainDenied(t, f.check(ctx, "[email protected]", false, "EXPLAIN ANALYZE SELECT 1", analyze), permission.SQLExplain, "instances/test/databases/db_a")
require.NoError(t, f.check(ctx, "[email protected]", false, "EXPLAIN ANALYZE SELECT 1", analyze))
})
}
// TestExplainOfAWriteNamesItsTables pins explain over DML: the databases its
// tables live in are checked like a read's, and a write that names no table,
// such as a procedure call, is checked on the connected database alone.
func TestExplainOfAWriteNamesItsTables(t *testing.T) {
t.Parallel()
ctx := context.WithValue(context.Background(), common.WorkspaceIDContextKey, "default")
f := newExplainAccessFixture(ctx, t, storepb.Engine_MYSQL)
require.NoError(t, f.check(ctx, "[email protected]", true, "DELETE FROM t", &parserbase.QuerySpan{Type: parserbase.DML, SourceColumns: reads("db_a")}))
crossRead := &parserbase.QuerySpan{Type: parserbase.DML, SourceColumns: reads("db_a", "db_b")}
requireExplainDenied(t, f.check(ctx, "[email protected]", true, "INSERT INTO t SELECT * FROM db_b.t", crossRead), permission.SQLExplain, "instances/test/databases/db_b")
call := &parserbase.QuerySpan{Type: parserbase.DML, SourceColumns: parserbase.SourceColumnSet{}}
require.NoError(t, f.check(ctx, "[email protected]", true, "CALL p()", call))
requireExplainDenied(t, f.check(ctx, "[email protected]", true, "CALL p()", call), permission.SQLExplain, "instances/test/databases/db_a")
}
func TestRemapBackupTableColumns(t *testing.T) {
dbMeta := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
Name: "db",
Schemas: []*metadatapb.SchemaMetadata{{
Name: common.BackupDatabaseNameOfEngine(storepb.Engine_POSTGRES),
Tables: []*metadatapb.TableMetadata{{Name: "bak_t", Comment: "bytebase, source table (public, t)"}},
}},
}, []byte{}, &storepb.DatabaseConfig{}, storepb.Engine_POSTGRES, true /* caseSensitive */)
backup := parserbase.ColumnResource{Database: "db", Schema: common.BackupDatabaseNameOfEngine(storepb.Engine_POSTGRES), Table: "bak_t", Column: "secret"}
source := parserbase.ColumnResource{Database: "db", Schema: "public", Table: "t", Column: "secret"}
span := &parserbase.QuerySpan{
SourceColumns: parserbase.SourceColumnSet{backup: true},
PredicateColumns: parserbase.SourceColumnSet{backup: true},
Results: []parserbase.QuerySpanResult{{Name: "secret", SourceColumns: parserbase.SourceColumnSet{backup: true}}},
}
remapBackupTableColumns(storepb.Engine_POSTGRES, span, getSchemaMetadata(storepb.Engine_POSTGRES, dbMeta))
want := parserbase.SourceColumnSet{source: true}
require.Equal(t, want, span.SourceColumns)
require.Equal(t, want, span.PredicateColumns)
require.Equal(t, want, span.Results[0].SourceColumns)
}
func TestResultForTimeoutNamesTheStatementItCutOff(t *testing.T) {
t.Parallel()
results := resultForTimeout("SELECT * FROM t", &queryTimeoutError{timeout: 30 * time.Second})
require.Len(t, results, 1)
require.Equal(t, "SELECT * FROM t", results[0].Statement)
require.Equal(t, "timeout reached: 30s", results[0].Error)
}
func TestResultForTimeoutLeavesAnEngineErrorAlone(t *testing.T) {
t.Parallel()
// Only a run the server cut off has no result of its own; an engine error
// already reaches the editor as the driver reported it.
require.Nil(t, resultForTimeout("SELECT * FROM t", errors.New("syntax error")))
require.Nil(t, resultForTimeout("SELECT * FROM t", nil))
}
func TestResultForTimeoutUnwrapsAWrappedTimeout(t *testing.T) {
t.Parallel()
err := errors.Wrap(&queryTimeoutError{timeout: time.Second}, "failed to query")
results := resultForTimeout("SELECT 1", err)
require.Len(t, results, 1)
require.Equal(t, "SELECT 1", results[0].Statement)
}