Skip to content

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

62.3 KB
Raw Blame
package v1

import (
	"context"
	"crypto/ecdsa"
	"crypto/ed25519"
	"crypto/rsa"
	"crypto/tls"
	"crypto/x509"
	"encoding/pem"
	"log/slog"
	"path/filepath"
	"slices"
	"strings"
	"time"

	"connectrpc.com/connect"
	celast "github.com/google/cel-go/common/ast"
	celoperators "github.com/google/cel-go/common/operators"
	"github.com/pkg/errors"
	"google.golang.org/protobuf/proto"
	"google.golang.org/protobuf/types/known/emptypb"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/common/log"
	"github.com/bytebase/bytebase/backend/component/config"
	"github.com/bytebase/bytebase/backend/component/dbfactory"
	"github.com/bytebase/bytebase/backend/component/sample"
	"github.com/bytebase/bytebase/backend/enterprise"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/generated-go/v1/v1connect"
	"github.com/bytebase/bytebase/backend/plugin/db"
	"github.com/bytebase/bytebase/backend/runner/schemasync"
	"github.com/bytebase/bytebase/backend/store"
)

// InstanceService implements the instance service.
type InstanceService struct {
	v1connect.UnimplementedInstanceServiceHandler
	store          *store.Store
	profile        *config.Profile
	licenseService *enterprise.LicenseService
	dbFactory      *dbfactory.DBFactory
	schemaSyncer   *schemasync.Syncer
	sampleManager  sample.Manager
}

const (
	connectionCategoryHeader             = "bytebase-connection-failure-category"
	connectionCategoryNetworkUnreachable = "network_unreachable"
	connectionCategoryAuthFailed         = "auth_failed"
	connectionCategoryPermissionDenied   = "permission_denied"
	connectionCategorySSLTLSFailed       = "ssl_tls_failed"
	connectionCategoryTimeout            = "timeout"
	connectionCategoryUnsupportedEngine  = "unsupported_engine"
	connectionCategoryUnknown            = "unknown"
	connectionCategorySuccess            = "success"
)

func classifyConnectionFailure(err error) string {
	if err == nil {
		return connectionCategorySuccess
	}
	var connectErr *connect.Error
	if errors.As(err, &connectErr) {
		if connectErr == nil {
			return connectionCategorySuccess
		}
		if category := connectErr.Meta().Get(connectionCategoryHeader); category != "" {
			return category
		}
	}
	text := strings.ToLower(err.Error())
	switch {
	case strings.Contains(text, "timeout"),
		strings.Contains(text, "timed out"),
		strings.Contains(text, "deadline exceeded"):
		return connectionCategoryTimeout
	case strings.Contains(text, "ssl"),
		strings.Contains(text, "tls"),
		strings.Contains(text, "certificate"),
		strings.Contains(text, "x509"):
		return connectionCategorySSLTLSFailed
	case strings.Contains(text, "permission denied"),
		strings.Contains(text, "insufficient permission"),
		strings.Contains(text, "insufficient privilege"),
		strings.Contains(text, "not authorized"):
		return connectionCategoryPermissionDenied
	case strings.Contains(text, "password"),
		strings.Contains(text, "authentication"),
		strings.Contains(text, "auth failed"),
		strings.Contains(text, "login failed"),
		strings.Contains(text, "invalid credential"),
		strings.Contains(text, "invalid password"),
		strings.Contains(text, "access denied"):
		return connectionCategoryAuthFailed
	case strings.Contains(text, "unsupported"):
		return connectionCategoryUnsupportedEngine
	case strings.Contains(text, "network"),
		strings.Contains(text, "unreachable"),
		strings.Contains(text, "no route"),
		strings.Contains(text, "no such host"),
		strings.Contains(text, "unknown host"),
		strings.Contains(text, "enotfound"),
		strings.Contains(text, "econnrefused"),
		strings.Contains(text, "connection refused"),
		strings.Contains(text, "connection reset"),
		strings.Contains(text, "could not translate host"):
		return connectionCategoryNetworkUnreachable
	default:
		return connectionCategoryUnknown
	}
}

func newConnectionTestErrorWithCategory(code connect.Code, err error, format string, args ...any) *connect.Error {
	category := classifyConnectionFailure(err)
	connectErr := connect.NewError(code, errors.Wrapf(err, format, args...))
	connectErr.Meta().Set(connectionCategoryHeader, category)
	return connectErr
}

func buildInstanceConnectionLogAttrs(method string, category string, instance *store.InstanceMessage, dataSource *storepb.DataSource, elapsed time.Duration) []any {
	attrs := []any{
		slog.String("method", method),
		slog.String("category", category),
		slog.Int64("elapsed_ms", elapsed.Milliseconds()),
		slog.Bool("has_ssl", hasDataSourceSSL(dataSource)),
		slog.Bool("has_ssh", hasDataSourceSSH(dataSource)),
		slog.Bool("has_external_secret", dataSource.GetExternalSecret() != nil),
	}
	if instance != nil {
		attrs = append(attrs, slog.String("engine", instance.Metadata.GetEngine().String()))
	}
	if dataSource != nil {
		attrs = append(attrs, slog.String("data_source_type", dataSource.GetType().String()))
	}
	return attrs
}

func hasDataSourceSSL(dataSource *storepb.DataSource) bool {
	return dataSource.GetUseSsl() ||
		dataSource.GetSslCa() != "" ||
		dataSource.GetObfuscatedSslCa() != "" ||
		dataSource.GetSslCert() != "" ||
		dataSource.GetObfuscatedSslCert() != "" ||
		dataSource.GetSslKey() != "" ||
		dataSource.GetObfuscatedSslKey() != "" ||
		dataSource.GetSslCaPath() != "" ||
		dataSource.GetObfuscatedSslCaPath() != "" ||
		dataSource.GetSslCertPath() != "" ||
		dataSource.GetObfuscatedSslCertPath() != "" ||
		dataSource.GetSslKeyPath() != "" ||
		dataSource.GetObfuscatedSslKeyPath() != ""
}

func hasDataSourceSSH(dataSource *storepb.DataSource) bool {
	return dataSource.GetSshHost() != "" ||
		dataSource.GetSshPort() != "" ||
		dataSource.GetSshUser() != "" ||
		dataSource.GetSshPassword() != "" ||
		dataSource.GetObfuscatedSshPassword() != "" ||
		dataSource.GetSshPrivateKey() != "" ||
		dataSource.GetObfuscatedSshPrivateKey() != ""
}

func (s *InstanceService) checkAndLogInstanceConnection(ctx context.Context, method string, instance *store.InstanceMessage, dataSource *storepb.DataSource) *connect.Error {
	start := time.Now()

	err := func() *connect.Error {
		driver, err := s.dbFactory.GetDataSourceDriver(
			ctx, instance, dataSource,
			db.ConnectionContext{
				ReadOnly: dataSource.GetType() == storepb.DataSourceType_READ_ONLY,
			},
		)
		if err != nil {
			return newConnectionTestErrorWithCategory(connect.CodeInternal, err, "failed to get database driver")
		}
		defer driver.Close(ctx)
		if err := driver.Ping(ctx); err != nil {
			return newConnectionTestErrorWithCategory(connect.CodeInvalidArgument, err, "failed to connect to database")
		}
		return nil
	}()
	category := classifyConnectionFailure(err)
	slog.Info("instance connection check completed", buildInstanceConnectionLogAttrs(method, category, instance, dataSource, time.Since(start))...)
	return err
}

// NewInstanceService creates a new InstanceService.
func NewInstanceService(store *store.Store, profile *config.Profile, licenseService *enterprise.LicenseService, dbFactory *dbfactory.DBFactory, schemaSyncer *schemasync.Syncer, sampleManager sample.Manager) *InstanceService {
	return &InstanceService{
		store:          store,
		profile:        profile,
		licenseService: licenseService,
		dbFactory:      dbFactory,
		schemaSyncer:   schemaSyncer,
		sampleManager:  sampleManager,
	}
}

// GetInstance gets an instance.
func (s *InstanceService) GetInstance(ctx context.Context, req *connect.Request[v1pb.GetInstanceRequest]) (*connect.Response[v1pb.Instance], error) {
	instance, err := getInstanceMessage(ctx, s.store, req.Msg.Name)
	if err != nil {
		return nil, err
	}
	result := s.convertToV1Instance(ctx, instance)
	return connect.NewResponse(result), nil
}

// ListInstances lists all instances.
func (s *InstanceService) ListInstances(ctx context.Context, req *connect.Request[v1pb.ListInstancesRequest]) (*connect.Response[v1pb.ListInstancesResponse], error) {
	offset, err := parseLimitAndOffset(&pageSize{
		token:   req.Msg.PageToken,
		limit:   int(req.Msg.PageSize),
		maximum: 1000,
	})
	if err != nil {
		return nil, err
	}
	limitPlusOne := offset.limit + 1

	parentProjectID, err := s.getProjectInstanceParent(ctx, req.Msg.Parent)
	if err != nil {
		return nil, err
	}

	find := &store.FindInstanceMessage{
		Workspace:     common.GetWorkspaceIDFromContext(ctx),
		ProjectID:     parentProjectID,
		WorkspaceOnly: parentProjectID == nil,
		ShowDeleted:   req.Msg.ShowDeleted,
		Limit:         &limitPlusOne,
		Offset:        &offset.offset,
	}
	filterQ, err := store.GetListInstanceFilter(req.Msg.Filter)
	if err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}
	if err := validateProjectInstanceListFilter(parentProjectID, req.Msg.Filter); err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}
	find.FilterQ = filterQ

	orderByKeys, err := store.GetInstanceOrders(req.Msg.OrderBy)
	if err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}
	find.OrderByKeys = orderByKeys

	instances, err := s.store.ListInstances(ctx, find)
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}

	nextPageToken := ""
	if len(instances) == limitPlusOne {
		instances = instances[:offset.limit]
		if nextPageToken, err = offset.getNextPageToken(); err != nil {
			return nil, connect.NewError(connect.CodeInternal, errors.Wrapf(err, "failed to marshal next page token"))
		}
	}

	response := &v1pb.ListInstancesResponse{
		NextPageToken: nextPageToken,
	}
	workspaceID := common.GetWorkspaceIDFromContext(ctx)
	for _, instance := range instances {
		ins := convertToV1Instance(instance, s.licenseService.IsInstanceEffectivelyActivated(ctx, workspaceID, instance))
		response.Instances = append(response.Instances, ins)
	}
	return connect.NewResponse(response), nil
}

func validateProjectInstanceListFilter(parentProjectID *string, filter string) error {
	if parentProjectID == nil || filter == "" {
		return nil
	}
	ast, err := common.ParseCELFilter(filter)
	if err != nil {
		return err
	}

	var validate func(celast.Expr) error
	validate = func(expr celast.Expr) error {
		if expr.Kind() != celast.CallKind {
			return nil
		}
		call := expr.AsCall()
		switch call.FunctionName() {
		case celoperators.LogicalAnd, celoperators.LogicalOr:
			for _, arg := range call.Args() {
				if err := validate(arg); err != nil {
					return err
				}
			}
		case celoperators.Equals:
			variable, value := getVariableAndValueFromExpr(expr)
			if variable != "project" {
				break
			}
			projectName, ok := value.(string)
			if !ok {
				return nil
			}
			projectID, err := common.GetProjectID(projectName)
			if err != nil {
				return errors.Errorf("invalid project filter %q", projectName)
			}
			if projectID != *parentProjectID {
				return errors.Errorf("project filter %q does not match parent %q", projectName, common.FormatProject(*parentProjectID))
			}
		default:
			for _, arg := range call.Args() {
				if err := validate(arg); err != nil {
					return err
				}
			}
		}
		return nil
	}
	return validate(ast.NativeRep().Expr())
}

// ListInstanceDatabase list all databases in the instance.
func (s *InstanceService) ListInstanceDatabase(ctx context.Context, req *connect.Request[v1pb.ListInstanceDatabaseRequest]) (*connect.Response[v1pb.ListInstanceDatabaseResponse], error) {
	var instanceMessage *store.InstanceMessage

	if req.Msg.Instance != nil {
		projectID, instanceID, err := common.GetInstanceResourceName(req.Msg.Name)
		if err != nil {
			return nil, connect.NewError(connect.CodeInvalidArgument, err)
		}
		if projectID != nil {
			parent := common.FormatProject(*projectID)
			projectID, err = s.getProjectInstanceParent(ctx, &parent)
			if err != nil {
				return nil, err
			}
		}

		if instanceMessage, err = convertToStoreInstance(instanceID, req.Msg.Instance); err != nil {
			return nil, connect.NewError(connect.CodeInvalidArgument, err)
		}
		instanceMessage.Workspace = common.GetWorkspaceIDFromContext(ctx)
		instanceMessage.ProjectID = projectID
	} else {
		instance, err := getInstanceMessage(ctx, s.store, req.Msg.Name)
		if err != nil {
			return nil, connect.NewError(connect.CodeInvalidArgument, err)
		}
		instanceMessage = instance
	}

	instanceMeta, err := s.schemaSyncer.GetInstanceMeta(ctx, instanceMessage)
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}

	response := &v1pb.ListInstanceDatabaseResponse{}
	for _, database := range instanceMeta.Databases {
		response.Databases = append(response.Databases, database.Name)
	}
	return connect.NewResponse(response), nil
}

// CreateInstance creates an instance.
func (s *InstanceService) CreateInstance(ctx context.Context, req *connect.Request[v1pb.CreateInstanceRequest]) (*connect.Response[v1pb.Instance], error) {
	if req.Msg.Instance == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("instance must be set"))
	}
	if !isValidResourceID(req.Msg.InstanceId) {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf("invalid instance ID %v", req.Msg.InstanceId))
	}

	projectID, err := s.getProjectInstanceParent(ctx, req.Msg.Parent)
	if err != nil {
		return nil, err
	}

	if err := validateLabels(req.Msg.Instance.Labels); err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}

	instanceMessage, err := convertToStoreInstance(req.Msg.InstanceId, req.Msg.Instance)
	if err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}
	// Set the workspace ID early so that workspace-scoped license checks
	// (e.g. EXTERNAL_SECRET_MANAGER) work during the ValidateOnly test
	// connection below, before the instance is persisted.
	workspaceID := common.GetWorkspaceIDFromContext(ctx)
	instanceMessage.Workspace = workspaceID
	instanceMessage.ProjectID = projectID
	for _, ds := range instanceMessage.Metadata.GetDataSources() {
		if err := validateAndSanitizeDataSourceTLS(ds); err != nil {
			return nil, connect.NewError(connect.CodeInvalidArgument, err)
		}
	}
	if err := s.checkInstanceDataSources(ctx, instanceMessage, instanceMessage.Metadata.GetDataSources()); err != nil {
		return nil, err
	}

	// Test connection.
	if req.Msg.ValidateOnly {
		for _, ds := range instanceMessage.Metadata.GetDataSources() {
			if err := s.checkAndLogInstanceConnection(ctx, v1connect.InstanceServiceCreateInstanceProcedure, instanceMessage, ds); err != nil {
				return nil, err
			}
		}

		result := s.convertToV1Instance(ctx, instanceMessage)
		return connect.NewResponse(result), nil
	}
	if err := s.instanceCountGuard(ctx); err != nil {
		return nil, err
	}

	if err := s.checkActivationLimit(ctx, workspaceID, instanceMessage.Metadata.GetActivation()); err != nil {
		return nil, err
	}

	instance, err := s.store.CreateInstance(ctx, instanceMessage)
	if err != nil {
		if strings.Contains(err.Error(), "duplicate key") {
			return nil, connect.NewError(connect.CodeAlreadyExists, errors.Errorf("instance ID %q already exists", req.Msg.InstanceId))
		}
		return nil, connect.NewError(connect.CodeInternal, err)
	}

	driver, err := s.dbFactory.GetAdminDatabaseDriver(ctx, instance, nil /* database */, db.ConnectionContext{})
	if err == nil {
		defer driver.Close(ctx)
		updatedInstance, _, newDatabases, err := s.schemaSyncer.SyncInstance(ctx, instance)
		if err != nil {
			slog.Warn("Failed to sync instance",
				slog.String("instance", instance.ResourceID),
				log.BBError(err))
		} else {
			instance = updatedInstance
		}
		if instance.Metadata.SyncDatabases == nil {
			// Sync all databases in the instance asynchronously.
			s.schemaSyncer.SyncAllDatabases(ctx, instance)
		} else {
			s.schemaSyncer.SyncDatabasesAsync(newDatabases)
		}
	}

	result := s.convertToV1Instance(ctx, instance)
	return connect.NewResponse(result), nil
}

// PrepareSampleProjectInstance provisions a Sample Project Instance for the
// requested project. The lifetime entitlement itself is enforced by the
// lifecycle manager under a workspace row lock.
func (s *InstanceService) PrepareSampleProjectInstance(ctx context.Context, req *connect.Request[v1pb.PrepareSampleProjectInstanceRequest]) (*connect.Response[v1pb.Instance], error) {
	projectID, err := s.getSampleProjectInstanceParent(ctx, &req.Msg.Parent)
	if err != nil {
		return nil, err
	}
	if projectID == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("Sample Project Instance parent is required"))
	}
	if s.sampleManager == nil {
		return nil, connect.NewError(connect.CodeFailedPrecondition, errors.New("Sample Project Instance is not configured"))
	}
	if err := s.sampleManager.CheckAvailable(ctx); err != nil {
		return nil, sampleProjectInstanceConnectError(err)
	}
	workspaceID := common.GetWorkspaceIDFromContext(ctx)
	if err := s.instanceCountGuard(ctx); err != nil {
		return nil, err
	}
	instance, err := s.sampleManager.PrepareSampleProjectInstance(ctx, sample.PrepareRequest{
		WorkspaceID: workspaceID,
		ProjectID:   *projectID,
	})
	if err != nil {
		return nil, sampleProjectInstanceConnectError(err)
	}
	return connect.NewResponse(s.convertToV1Instance(ctx, instance)), nil
}

func sampleProjectInstanceConnectError(err error) error {
	switch sample.FailureKindOf(err) {
	case sample.FailureFailedPrecondition:
		return connect.NewError(connect.CodeFailedPrecondition, err)
	case sample.FailureUnavailable:
		return connect.NewError(connect.CodeUnavailable, err)
	case sample.FailureDeadlineExceeded:
		return connect.NewError(connect.CodeDeadlineExceeded, err)
	default:
		return connect.NewError(connect.CodeInternal, err)
	}
}

func (s *InstanceService) getSampleProjectInstanceParent(ctx context.Context, parent *string) (*string, error) {
	projectID, err := s.getProjectInstanceParent(ctx, parent)
	if err == nil || connect.CodeOf(err) != connect.CodeNotFound || parent == nil {
		return projectID, err
	}
	_, parseErr := common.GetProjectID(*parent)
	if parseErr != nil {
		return nil, err
	}
	reservation, storeErr := s.store.GetSampleInstanceSetup(ctx, common.GetWorkspaceIDFromContext(ctx))
	if storeErr != nil {
		return nil, connect.NewError(connect.CodeInternal, storeErr)
	}
	if reservation != nil {
		return nil, connect.NewError(connect.CodeFailedPrecondition, errors.New("Sample Project Instance entitlement is already consumed"))
	}
	return nil, err
}

func (s *InstanceService) getProjectInstanceParent(ctx context.Context, parent *string) (*string, error) {
	if parent == nil {
		return nil, nil
	}
	projectID, err := common.GetProjectID(*parent)
	if err != nil || projectID == "-" {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf("invalid instance parent %q", *parent))
	}
	workspaceID := common.GetWorkspaceIDFromContext(ctx)
	if common.IsDefaultProject(workspaceID, projectID) {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf("default project %q cannot own instances", *parent))
	}
	project, err := s.store.GetProject(ctx, &store.FindProjectMessage{
		ResourceID: &projectID,
		Workspace:  workspaceID,
	})
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}
	if project == nil || project.Deleted {
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("project %q not found", *parent))
	}
	return &projectID, nil
}

func instanceWithMetadata(instance *store.InstanceMessage, metadata *storepb.Instance) *store.InstanceMessage {
	candidate := *instance
	candidate.Metadata = metadata
	return &candidate
}

func (s *InstanceService) convertToV1Instance(ctx context.Context, instance *store.InstanceMessage) *v1pb.Instance {
	return convertToV1Instance(instance, s.licenseService.IsInstanceEffectivelyActivated(ctx, common.GetWorkspaceIDFromContext(ctx), instance))
}

func (s *InstanceService) checkInstanceDataSources(ctx context.Context, instance *store.InstanceMessage, dataSources []*storepb.DataSource) error {
	for _, ds := range dataSources {
		if err := s.checkDataSource(ctx, instance, ds); err != nil {
			return err
		}
	}
	if err := store.ValidateDataSources(dataSources); err != nil {
		return connect.NewError(connect.CodeInvalidArgument, err)
	}
	return nil
}

func validateAndSanitizeDataSourceTLS(ds *storepb.DataSource) error {
	if !ds.GetUseSsl() {
		ds.SslCa = ""
		ds.SslCert = ""
		ds.SslKey = ""
		ds.SslCaPath = ""
		ds.SslCertPath = ""
		ds.SslKeyPath = ""
		return nil
	}
	for _, conflict := range []struct {
		inlineField string
		pathField   string
		inlineValue string
		pathValue   string
	}{
		{"ssl_ca", "ssl_ca_path", ds.GetSslCa(), ds.GetSslCaPath()},
		{"ssl_cert", "ssl_cert_path", ds.GetSslCert(), ds.GetSslCertPath()},
		{"ssl_key", "ssl_key_path", ds.GetSslKey(), ds.GetSslKeyPath()},
	} {
		if conflict.inlineValue != "" && conflict.pathValue != "" {
			return errors.Errorf("cannot set both %s and %s; clear one of them by including the field in the update_mask with an empty value when switching TLS material source", conflict.inlineField, conflict.pathField)
		}
	}
	for _, pathField := range []struct {
		field string
		path  string
	}{
		{"ssl_ca_path", ds.GetSslCaPath()},
		{"ssl_cert_path", ds.GetSslCertPath()},
		{"ssl_key_path", ds.GetSslKeyPath()},
	} {
		if pathField.path != "" && !filepath.IsAbs(pathField.path) {
			return errors.Errorf("%s must be an absolute path", pathField.field)
		}
	}
	if ds.GetSslCa() != "" {
		if err := validateInlineCAPEM([]byte(ds.GetSslCa())); err != nil {
			return errors.Wrap(err, "invalid ssl_ca PEM")
		}
	}
	certSet := ds.GetSslCert() != "" || ds.GetSslCertPath() != ""
	keySet := ds.GetSslKey() != "" || ds.GetSslKeyPath() != ""
	if certSet != keySet {
		return errors.New("ssl_cert and ssl_key must be both set or unset")
	}
	if ds.GetSslCert() != "" && ds.GetSslKey() != "" {
		if _, err := tls.X509KeyPair([]byte(ds.GetSslCert()), []byte(ds.GetSslKey())); err != nil {
			return errors.Wrap(err, "invalid ssl_cert or ssl_key PEM")
		}
	}
	if ds.GetSslCert() != "" && ds.GetSslKey() == "" {
		if err := validateInlineCertPEM([]byte(ds.GetSslCert())); err != nil {
			return errors.Wrap(err, "invalid ssl_cert PEM")
		}
	}
	if ds.GetSslKey() != "" && ds.GetSslCert() == "" {
		if err := validateInlineKeyPEM([]byte(ds.GetSslKey())); err != nil {
			return errors.Wrap(err, "invalid ssl_key PEM")
		}
	}
	return nil
}

func validateInlineCAPEM(data []byte) error {
	pool := x509.NewCertPool()
	if ok := pool.AppendCertsFromPEM(data); !ok {
		return errors.New("no valid CERTIFICATE PEM block found")
	}
	return nil
}

func validateInlineCertPEM(data []byte) error {
	var certs [][]byte
	for {
		var block *pem.Block
		block, data = pem.Decode(data)
		if block == nil {
			break
		}
		if block.Type == "CERTIFICATE" {
			certs = append(certs, block.Bytes)
		}
	}
	if len(certs) == 0 {
		return errors.New("no CERTIFICATE PEM block found")
	}
	if _, err := x509.ParseCertificate(certs[0]); err != nil {
		return err
	}
	return nil
}

func validateInlineKeyPEM(data []byte) error {
	var block *pem.Block
	for {
		block, data = pem.Decode(data)
		if block == nil {
			return errors.New("no PRIVATE KEY PEM block found")
		}
		if block.Type == "PRIVATE KEY" || strings.HasSuffix(block.Type, " PRIVATE KEY") {
			break
		}
	}
	_, err := parseInlinePrivateKey(block.Bytes)
	return err
}

func parseInlinePrivateKey(der []byte) (any, error) {
	if key, err := x509.ParsePKCS1PrivateKey(der); err == nil {
		return key, nil
	}
	if key, err := x509.ParsePKCS8PrivateKey(der); err == nil {
		switch key := key.(type) {
		case *rsa.PrivateKey, *ecdsa.PrivateKey, ed25519.PrivateKey:
			return key, nil
		default:
			return nil, errors.Errorf("unsupported private key type %T", key)
		}
	}
	if key, err := x509.ParseECPrivateKey(der); err == nil {
		return key, nil
	}
	return nil, errors.New("failed to parse private key")
}

const instanceExceededError = "activation instance count has reached the limit (%v)"

func (s *InstanceService) checkActivationLimit(ctx context.Context, workspaceID string, activating bool) error {
	if !activating || s.licenseService.IsUnifiedInstanceLicense(ctx, workspaceID) {
		return nil
	}
	activatedInstanceLimit := s.licenseService.GetActivatedInstanceLimit(ctx, workspaceID)
	count, err := s.store.GetActivatedInstanceCount(ctx, workspaceID)
	if err != nil {
		return connect.NewError(connect.CodeInternal, err)
	}
	if count >= activatedInstanceLimit {
		return connect.NewError(connect.CodeResourceExhausted, errors.Errorf(instanceExceededError, activatedInstanceLimit))
	}
	return nil
}

func (s *InstanceService) checkDataSource(ctx context.Context, instance *store.InstanceMessage, dataSource *storepb.DataSource) error {
	if dataSource.GetId() == "" {
		return connect.NewError(connect.CodeInvalidArgument, errors.New("data source id is required"))
	}

	// Validate IAM credential restrictions in SaaS mode
	if err := s.validateIAMCredentialForSaaS(dataSource); err != nil {
		return err
	}

	// Validate external secret restrictions in SaaS mode
	if err := s.validateExternalSecretForSaaS(dataSource); err != nil {
		return err
	}

	if err := s.licenseService.IsFeatureEnabledForInstance(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_EXTERNAL_SECRET_MANAGER, instance); err != nil {
		missingFeatureError := connect.NewError(connect.CodePermissionDenied, err)
		if dataSource.GetExternalSecret() != nil {
			return missingFeatureError
		}
		return nil
	}

	// Validate extra connection parameters for MySQL-compatible engines
	if err := validateExtraConnectionParameters(instance.Metadata.GetEngine(), dataSource.GetExtraConnectionParameters()); err != nil {
		return connect.NewError(connect.CodeInvalidArgument, err)
	}

	return nil
}

func (s *InstanceService) validateIAMCredentialForSaaS(dataSource *storepb.DataSource) error {
	if !s.profile.SaaS {
		return nil
	}

	// Check if using IAM authentication
	iamAuthTypes := map[storepb.DataSource_AuthenticationType]bool{
		storepb.DataSource_GOOGLE_CLOUD_SQL_IAM: true,
		storepb.DataSource_AWS_RDS_IAM:          true,
		storepb.DataSource_AZURE_IAM:            true,
	}

	if !iamAuthTypes[dataSource.GetAuthenticationType()] {
		return nil
	}

	// Check if using default credentials (iam_extension is not set)
	if dataSource.GetIamExtension() == nil {
		return connect.NewError(
			connect.CodeInvalidArgument,
			errors.New("default credentials are not allowed in SaaS mode for security. Please provide specific credentials"),
		)
	}

	// An IAM extension whose fields are all empty behaves exactly like the
	// default credential chain at connect time (the host's own cloud
	// identity), so reject it for the same reason.
	return validateIAMCredentialNotEmpty(dataSource)
}

func validateIAMCredentialNotEmpty(dataSource *storepb.DataSource) error {
	switch {
	case dataSource.GetAwsCredential() != nil:
		awsCredential := dataSource.GetAwsCredential()
		if awsCredential.GetAccessKeyId() == "" && awsCredential.GetRoleArn() == "" {
			return connect.NewError(
				connect.CodeInvalidArgument,
				errors.New("AWS credential requires an access key ID or a role ARN in SaaS mode"),
			)
		}
	case dataSource.GetGcpCredential() != nil:
		if dataSource.GetGcpCredential().GetContent() == "" {
			return connect.NewError(
				connect.CodeInvalidArgument,
				errors.New("GCP credential content is required in SaaS mode"),
			)
		}
	case dataSource.GetAzureCredential() != nil:
		azureCredential := dataSource.GetAzureCredential()
		if azureCredential.GetTenantId() == "" || azureCredential.GetClientId() == "" || azureCredential.GetClientSecret() == "" {
			return connect.NewError(
				connect.CodeInvalidArgument,
				errors.New("Azure credential requires tenant ID, client ID, and client secret in SaaS mode"),
			)
		}
	default:
	}
	return nil
}

// validateExternalSecretForSaaS blocks external secret credential sources that
// read from the Bytebase host in SaaS mode. The configured secret store URL is
// user-controlled, so resolving a credential from the host environment or
// filesystem would let a tenant exfiltrate host env vars or files to an endpoint
// they own (the resolved value is sent to that URL as the Vault token / AppRole
// secret id).
func (s *InstanceService) validateExternalSecretForSaaS(dataSource *storepb.DataSource) error {
	if !s.profile.SaaS {
		return nil
	}

	externalSecret := dataSource.GetExternalSecret()
	switch externalSecret.GetAuthType() {
	case storepb.DataSourceExternalSecret_TOKEN:
		switch externalSecret.GetTokenType() {
		case storepb.DataSourceExternalSecret_ENVIRONMENT, storepb.DataSourceExternalSecret_FILE:
			return connect.NewError(
				connect.CodeInvalidArgument,
				errors.New("environment variable and file token sources are not allowed in SaaS mode for security. Please provide the token directly"),
			)
		default:
			return nil
		}
	case storepb.DataSourceExternalSecret_VAULT_APP_ROLE:
		if externalSecret.GetAppRole().GetType() == storepb.DataSourceExternalSecret_AppRoleAuthOption_ENVIRONMENT {
			return connect.NewError(
				connect.CodeInvalidArgument,
				errors.New("environment variable secret id is not allowed in SaaS mode for security. Please provide the secret id directly"),
			)
		}
		return nil
	default:
		return nil
	}
}

// UpdateInstance updates an instance.
func (s *InstanceService) UpdateInstance(ctx context.Context, req *connect.Request[v1pb.UpdateInstanceRequest]) (*connect.Response[v1pb.Instance], error) {
	if req.Msg.Instance == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("instance must be set"))
	}
	if req.Msg.UpdateMask == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("update_mask must be set"))
	}

	instance, err := getInstanceMessage(ctx, s.store, req.Msg.Instance.Name)
	if err != nil {
		if strings.Contains(err.Error(), "not found") && req.Msg.AllowMissing {
			// When allow_missing is true and instance doesn't exist, create a new one
			projectID, instanceID, ierr := common.GetInstanceResourceName(req.Msg.Instance.Name)
			if ierr != nil {
				return nil, connect.NewError(connect.CodeInvalidArgument, ierr)
			}

			return s.CreateInstance(ctx, connect.NewRequest(&v1pb.CreateInstanceRequest{
				InstanceId: instanceID,
				Instance:   req.Msg.Instance,
				Parent:     instanceCollectionParent(projectID),
			}))
		}
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}
	if instance.Deleted {
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q has been deleted", req.Msg.Instance.Name))
	}

	metadata := proto.CloneOf(instance.Metadata)
	patch := &store.UpdateInstanceMessage{
		ResourceID: &instance.ResourceID,
		Workspace:  instance.Workspace,
		Metadata:   metadata,
	}
	updateActivation := false
	updateSyncInterval := false
	for _, path := range req.Msg.UpdateMask.Paths {
		switch path {
		case "title":
			patch.Metadata.Title = req.Msg.Instance.Title
		case "environment":
			if req.Msg.Instance.Environment == nil || *req.Msg.Instance.Environment == "" {
				// Clear the environment if null or empty string is provided
				patch.EnvironmentID = new("")
			} else {
				envID, err := common.GetEnvironmentID(*req.Msg.Instance.Environment)
				if err != nil {
					return nil, connect.NewError(connect.CodeInvalidArgument, err)
				}
				environment, err := s.store.GetEnvironmentByID(ctx, common.GetWorkspaceIDFromContext(ctx), envID)
				if err != nil {
					return nil, connect.NewError(connect.CodeInternal, err)
				}
				if environment == nil {
					return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("environment %q not found", envID))
				}
				patch.EnvironmentID = &envID
			}
		case "external_link":
			patch.Metadata.ExternalLink = req.Msg.Instance.ExternalLink
		case "data_sources":
			dataSources, err := convertV1DataSources(req.Msg.Instance.DataSources)
			if err != nil {
				return nil, connect.NewError(connect.CodeInvalidArgument, err)
			}
			if err := retainStoredKeytabs(dataSources, instance.Metadata.GetDataSources()); err != nil {
				return nil, connect.NewError(connect.CodeInvalidArgument, err)
			}
			normalizeGCPDataSources(instance.Metadata.GetEngine(), dataSources)
			for _, ds := range dataSources {
				if err := validateAndSanitizeDataSourceTLS(ds); err != nil {
					return nil, connect.NewError(connect.CodeInvalidArgument, err)
				}
			}
			if err := s.checkInstanceDataSources(ctx, instance, dataSources); err != nil {
				return nil, err
			}
			patch.Metadata.DataSources = dataSources
		case "activation":
			if !instance.Metadata.GetActivation() && req.Msg.Instance.Activation {
				updateActivation = true
			}
			patch.Metadata.Activation = req.Msg.Instance.Activation
		case "sync_interval":
			updateSyncInterval = true
			patch.Metadata.SyncInterval = req.Msg.Instance.SyncInterval
		case "sync_databases":
			patch.Metadata.SyncDatabases = convertToStoreSyncDatabases(req.Msg.Instance.SyncDatabases)
		case "labels":
			if err := validateLabels(req.Msg.Instance.Labels); err != nil {
				return nil, connect.NewError(connect.CodeInvalidArgument, err)
			}
			patch.Metadata.Labels = req.Msg.Instance.Labels
		default:
			return nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf(`unsupported update_mask "%s"`, path))
		}
	}

	// Check feature gates after all fields are processed so that activation
	// changes in the same request are visible to the license check.
	if updateSyncInterval {
		patchedInstance := *instance
		patchedInstance.Metadata = patch.Metadata
		if err := s.licenseService.IsFeatureEnabledForInstance(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_CUSTOM_INSTANCE_SYNC_TIME, &patchedInstance); err != nil {
			return nil, connect.NewError(connect.CodePermissionDenied, err)
		}
	}

	workspaceID := common.GetWorkspaceIDFromContext(ctx)
	if err := s.checkActivationLimit(ctx, workspaceID, updateActivation); err != nil {
		return nil, err
	}

	ins, err := s.store.UpdateInstance(ctx, patch)
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}
	result := s.convertToV1Instance(ctx, ins)
	return connect.NewResponse(result), nil
}

// DeleteInstance deletes an instance.
func (s *InstanceService) DeleteInstance(ctx context.Context, req *connect.Request[v1pb.DeleteInstanceRequest]) (*connect.Response[emptypb.Empty], error) {
	instance, err := getInstanceMessageForLifecycle(ctx, s.store, req.Msg.Name)
	if err != nil {
		return nil, err
	}
	if req.Msg.Force && instance.ProjectID != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("force deletion is only supported for workspace instances"))
	}

	// Handle purge (hard delete) of soft-deleted instance
	if req.Msg.Purge {
		// Following AIP-165, purge only works on already soft-deleted instances
		if !instance.Deleted {
			return nil, connect.NewError(connect.CodeFailedPrecondition, errors.Errorf("instance %q must be archived before it can be deleted", req.Msg.Name))
		}

		// Permanently delete the instance and all related resources
		if err := s.store.DeleteInstance(ctx, instance.Workspace, instance.ResourceID); err != nil {
			return nil, connect.NewError(connect.CodeInternal, errors.Wrap(err, "failed to purge instance"))
		}

		return connect.NewResponse(&emptypb.Empty{}), nil
	}

	// Regular soft delete flow. Already archived instances still pass through
	// the lifecycle guard so legacy active task runs are reported consistently.
	alreadyDeleted := instance.Deleted
	var moveDatabasesToProjectID *string
	if !alreadyDeleted && instance.ProjectID == nil {
		if req.Msg.Force {
			defaultProjectID, err := s.store.GetDefaultProjectID(ctx, common.GetWorkspaceIDFromContext(ctx))
			if err != nil {
				return nil, connect.NewError(connect.CodeInternal, errors.Wrap(err, "failed to get default project ID"))
			}
			moveDatabasesToProjectID = &defaultProjectID
		} else {
			databases, err := s.store.ListDatabases(ctx, &store.FindDatabaseMessage{Workspace: common.GetWorkspaceIDFromContext(ctx), InstanceID: &instance.ResourceID})
			if err != nil {
				return nil, err
			}
			var databaseNames []string
			for _, database := range databases {
				if !common.IsDefaultProject(common.GetWorkspaceIDFromContext(ctx), database.ProjectID) {
					databaseNames = append(databaseNames, database.DatabaseName)
				}
			}
			if len(databaseNames) > 0 {
				return nil, connect.NewError(connect.CodeFailedPrecondition, errors.New("all databases should be transferred to the unassigned project before deleting the instance"))
			}
		}
	}

	patch := &store.UpdateInstanceMessage{
		ResourceID:               &instance.ResourceID,
		Workspace:                instance.Workspace,
		Deleted:                  &deletePatch,
		MoveDatabasesToProjectID: moveDatabasesToProjectID,
	}
	if instance.ProjectID == nil {
		metadata := proto.CloneOf(instance.Metadata)
		metadata.Activation = false
		patch.Metadata = metadata
	}
	if _, err := s.store.UpdateInstance(ctx, patch); err != nil {
		if common.ErrorCode(err) == common.Conflict {
			return nil, connect.NewError(connect.CodeFailedPrecondition, err)
		}
		return nil, connect.NewError(connect.CodeInternal, err)
	}

	// Handle sample instance deletion if applicable
	if !alreadyDeleted && s.sampleManager != nil {
		if err := s.sampleManager.HandleInstanceLifecycle(ctx, common.GetWorkspaceIDFromContext(ctx), instance.ResourceID, true); err != nil {
			slog.Warn("failed to handle sample instance deletion", log.BBError(err), slog.String("instance", instance.ResourceID))
		}
	}

	return connect.NewResponse(&emptypb.Empty{}), nil
}

// UndeleteInstance undeletes an instance.
func (s *InstanceService) UndeleteInstance(ctx context.Context, req *connect.Request[v1pb.UndeleteInstanceRequest]) (*connect.Response[v1pb.Instance], error) {
	instance, err := getInstanceMessageForLifecycle(ctx, s.store, req.Msg.Name)
	if err != nil {
		return nil, err
	}
	// Idempotent: if already active, return the instance
	if !instance.Deleted {
		result := s.convertToV1Instance(ctx, instance)
		return connect.NewResponse(result), nil
	}
	if s.sampleManager != nil {
		if err := s.sampleManager.ValidateInstanceRestore(ctx, instance.Workspace, instance.ResourceID); err != nil {
			return nil, sampleProjectInstanceConnectError(err)
		}
	}
	activeTaskRunCount, err := s.store.GetActiveTaskRunCountForInstance(ctx, instance.ResourceID)
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}
	if activeTaskRunCount > 0 {
		return nil, connect.NewError(connect.CodeFailedPrecondition, errors.Errorf("instance %s has %d active task run(s); cancel them or wait for them to finish before restoring", instance.ResourceID, activeTaskRunCount))
	}
	if err := s.instanceCountGuard(ctx); err != nil {
		return nil, err
	}
	if err := s.checkActivationLimit(ctx, instance.Workspace, instance.Metadata.GetActivation()); err != nil {
		return nil, err
	}

	ins, err := s.store.UpdateInstance(ctx, &store.UpdateInstanceMessage{
		ResourceID: &instance.ResourceID,
		Workspace:  instance.Workspace,
		Deleted:    &undeletePatch,
	})
	if err != nil {
		if common.ErrorCode(err) == common.Conflict {
			return nil, connect.NewError(connect.CodeFailedPrecondition, err)
		}
		return nil, connect.NewError(connect.CodeInternal, err)
	}

	// Handle sample instance undelete (restart) if applicable
	if s.sampleManager != nil {
		if err := s.sampleManager.HandleInstanceLifecycle(ctx, ins.Workspace, ins.ResourceID, false); err != nil {
			slog.Warn("failed to handle sample instance undelete", log.BBError(err), slog.String("instance", ins.ResourceID))
		}
	}

	result := s.convertToV1Instance(ctx, ins)
	return connect.NewResponse(result), nil
}

// SyncInstance syncs the instance.
func (s *InstanceService) SyncInstance(ctx context.Context, req *connect.Request[v1pb.SyncInstanceRequest]) (*connect.Response[v1pb.SyncInstanceResponse], error) {
	instance, err := getInstanceMessage(ctx, s.store, req.Msg.Name)
	if err != nil {
		return nil, err
	}
	if instance.Deleted {
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q has been deleted", req.Msg.Name))
	}

	updatedInstance, allDatabases, newDatabases, err := s.schemaSyncer.SyncInstance(ctx, instance)
	if err != nil {
		return nil, err
	}
	if req.Msg.EnableFullSync {
		// Sync all databases in the instance asynchronously.
		s.schemaSyncer.SyncAllDatabases(ctx, updatedInstance)
	} else {
		s.schemaSyncer.SyncDatabasesAsync(newDatabases)
	}

	response := &v1pb.SyncInstanceResponse{}
	for _, database := range allDatabases {
		response.Databases = append(response.Databases, database.Name)
	}
	return connect.NewResponse(response), nil
}

// BatchSyncInstances syncs multiple instances.
func (s *InstanceService) BatchSyncInstances(ctx context.Context, req *connect.Request[v1pb.BatchSyncInstancesRequest]) (*connect.Response[v1pb.BatchSyncInstancesResponse], error) {
	names := make([]string, 0, len(req.Msg.Requests))
	for _, request := range req.Msg.Requests {
		names = append(names, request.Name)
	}
	instances, err := s.getInstanceCollection(ctx, req.Msg.Parent, names)
	if err != nil {
		return nil, err
	}
	for i, instance := range instances {
		r := req.Msg.Requests[i]

		updatedInstance, _, newDatabases, err := s.schemaSyncer.SyncInstance(ctx, instance)
		if err != nil {
			return nil, connect.NewError(connect.CodeInternal, errors.Wrapf(err, "failed to sync instance"))
		}
		if r.EnableFullSync {
			// Sync all databases in the instance asynchronously.
			s.schemaSyncer.SyncAllDatabases(ctx, updatedInstance)
		} else {
			s.schemaSyncer.SyncDatabasesAsync(newDatabases)
		}
	}

	return connect.NewResponse(&v1pb.BatchSyncInstancesResponse{}), nil
}

// BatchUpdateInstances update multiple instances.
func (s *InstanceService) BatchUpdateInstances(ctx context.Context, req *connect.Request[v1pb.BatchUpdateInstancesRequest]) (*connect.Response[v1pb.BatchUpdateInstancesResponse], error) {
	projectID, err := s.getProjectInstanceParent(ctx, req.Msg.Parent)
	if err != nil {
		return nil, err
	}
	for _, updateReq := range req.Msg.GetRequests() {
		name := updateReq.GetInstance().GetName()
		instanceProjectID, _, err := common.GetInstanceResourceName(name)
		if err != nil {
			return nil, connect.NewError(connect.CodeInvalidArgument, err)
		}
		if (projectID == nil) != (instanceProjectID == nil) || projectID != nil && *projectID != *instanceProjectID {
			return nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf("instance %q is not in its requested collection", name))
		}
	}
	for _, updateReq := range req.Msg.GetRequests() {
		name := updateReq.GetInstance().GetName()
		instance, err := getInstanceMessage(ctx, s.store, name)
		if err != nil {
			if connect.CodeOf(err) == connect.CodeNotFound && updateReq.AllowMissing {
				continue
			}
			return nil, err
		}
		if instance.Deleted {
			return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q has been deleted", name))
		}
	}
	response := &v1pb.BatchUpdateInstancesResponse{}
	for _, updateReq := range req.Msg.GetRequests() {
		updated, err := s.UpdateInstance(ctx, connect.NewRequest(updateReq))
		if err != nil {
			return nil, err
		}
		response.Instances = append(response.Instances, updated.Msg)
	}
	return connect.NewResponse(response), nil
}

// AddDataSource adds a data source to an instance.
func (s *InstanceService) AddDataSource(ctx context.Context, req *connect.Request[v1pb.AddDataSourceRequest]) (*connect.Response[v1pb.Instance], error) {
	if req.Msg.DataSource == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("data sources is required"))
	}
	// We only support add RO type datasouce to instance now, see more details in instance_service.proto.
	if req.Msg.DataSource.Type != v1pb.DataSourceType_READ_ONLY {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("only support adding read-only data source"))
	}

	dataSource, err := convertV1DataSource(req.Msg.DataSource)
	if err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("failed to convert data source"))
	}

	instance, err := getInstanceMessage(ctx, s.store, req.Msg.Name)
	if err != nil {
		return nil, err
	}
	if instance.Deleted {
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q has been deleted", req.Msg.Name))
	}
	normalizeGCPDataSources(instance.Metadata.GetEngine(), []*storepb.DataSource{dataSource})
	for _, ds := range instance.Metadata.GetDataSources() {
		if ds.GetId() == req.Msg.DataSource.Id {
			return nil, connect.NewError(connect.CodeNotFound, errors.New("data source already exists with the same name"))
		}
	}
	if err := s.checkDataSource(ctx, instance, dataSource); err != nil {
		return nil, err
	}
	if err := validateAndSanitizeDataSourceTLS(dataSource); err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}
	metadata := proto.CloneOf(instance.Metadata)
	metadata.DataSources = append(metadata.DataSources, dataSource)
	if err := s.checkInstanceDataSources(ctx, instance, metadata.GetDataSources()); err != nil {
		return nil, err
	}

	if err := s.licenseService.IsFeatureEnabledForInstance(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_INSTANCE_READ_ONLY_CONNECTION, instance); err != nil {
		return nil, connect.NewError(connect.CodePermissionDenied, err)
	}

	// Test connection.
	if req.Msg.ValidateOnly {
		if err := s.checkAndLogInstanceConnection(ctx, v1connect.InstanceServiceAddDataSourceProcedure, instance, dataSource); err != nil {
			return nil, err
		}
		result := s.convertToV1Instance(ctx, instanceWithMetadata(instance, metadata))
		return connect.NewResponse(result), nil
	}

	if dataSource.GetType() != storepb.DataSourceType_READ_ONLY {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("only read-only data source can be added"))
	}

	instance, err = s.store.UpdateInstance(ctx, &store.UpdateInstanceMessage{
		ResourceID: &instance.ResourceID,
		Workspace:  instance.Workspace,
		Metadata:   metadata,
	})
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}

	result := s.convertToV1Instance(ctx, instance)
	return connect.NewResponse(result), nil
}

// UpdateDataSource updates a data source of an instance.
func (s *InstanceService) UpdateDataSource(ctx context.Context, req *connect.Request[v1pb.UpdateDataSourceRequest]) (*connect.Response[v1pb.Instance], error) {
	if req.Msg.DataSource == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("datasource is required"))
	}
	if req.Msg.UpdateMask == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("update_mask must be set"))
	}

	instance, err := getInstanceMessage(ctx, s.store, req.Msg.Name)
	if err != nil {
		return nil, err
	}
	if instance.Deleted {
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q has been deleted", req.Msg.Name))
	}
	metadata := proto.CloneOf(instance.Metadata)
	var dataSource *storepb.DataSource
	for _, ds := range metadata.GetDataSources() {
		if ds.GetId() == req.Msg.DataSource.Id {
			dataSource = ds
			break
		}
	}
	if dataSource == nil {
		if req.Msg.AllowMissing {
			return s.AddDataSource(ctx, connect.NewRequest(&v1pb.AddDataSourceRequest{
				Name:         req.Msg.Name,
				DataSource:   req.Msg.DataSource,
				ValidateOnly: req.Msg.ValidateOnly,
			}))
		}
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf(`cannot found data source "%s"`, req.Msg.DataSource.Id))
	}

	if dataSource.GetType() == storepb.DataSourceType_READ_ONLY {
		if err := s.licenseService.IsFeatureEnabledForInstance(ctx, common.GetWorkspaceIDFromContext(ctx), v1pb.PlanFeature_FEATURE_INSTANCE_READ_ONLY_CONNECTION, instance); err != nil {
			return nil, connect.NewError(connect.CodePermissionDenied, err)
		}
	}

	// dataSource points into the cloned metadata and is patched in place below,
	// so keep the pre-image: keytab retention compares the whole merged result
	// against it, which the mask loop cannot do while it is still running.
	storedDataSource := proto.CloneOf(dataSource)

	// Then drop the stored keytab off the value being patched. Only the
	// sasl_config path writes one, so after the loop a keytab is present iff
	// THIS request supplied it — which is what retention has to decide on.
	// Left in place, a mask that never names sasl_config would carry the
	// stored keytab through untouched and retention would read it as supplied,
	// letting update_mask=["host"] alone move it to the caller's address.
	if krb := dataSource.GetSaslConfig().GetKrbConfig(); krb != nil {
		krb.Keytab = nil
	}

	if err := applyDataSourceUpdateMask(dataSource, req.Msg.DataSource, req.Msg.UpdateMask.Paths); err != nil {
		return nil, err
	}

	// Run on the merged result, not inside the mask loop: whether the keytab
	// may be inherited depends on the destination the request ends at, and the
	// paths that move it can be applied after sasl_config.
	if err := retainStoredKeytabOnEmptyUpdate(dataSource, storedDataSource); err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}

	clearDataSourceAuthentication(dataSource)
	normalizeGCPDataSources(instance.Metadata.GetEngine(), []*storepb.DataSource{dataSource})
	if err := validateAndSanitizeDataSourceTLS(dataSource); err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}
	// Validate the merged result so untouched stored credentials keep passing:
	// the create-path SaaS checks never run for updates, which would otherwise
	// let an update strip an IAM credential down to the default chain.
	if err := s.validateIAMCredentialForSaaS(dataSource); err != nil {
		return nil, err
	}

	if err := s.checkInstanceDataSources(ctx, instance, metadata.GetDataSources()); err != nil {
		return nil, err
	}

	// Test connection.
	if req.Msg.ValidateOnly {
		if err := s.checkAndLogInstanceConnection(ctx, v1connect.InstanceServiceUpdateDataSourceProcedure, instance, dataSource); err != nil {
			return nil, err
		}
		result := s.convertToV1Instance(ctx, instanceWithMetadata(instance, metadata))
		return connect.NewResponse(result), nil
	}

	instance, err = s.store.UpdateInstance(ctx, &store.UpdateInstanceMessage{
		ResourceID: &instance.ResourceID,
		Workspace:  instance.Workspace,
		Metadata:   metadata,
	})
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}
	result := s.convertToV1Instance(ctx, instance)
	return connect.NewResponse(result), nil
}

// applyDataSourceUpdateMask patches dataSource in place with the fields the
// mask names. It is the whole of what update_mask means for a data source, and
// it is pure so it can be exercised without a store.
func applyDataSourceUpdateMask(dataSource *storepb.DataSource, src *v1pb.DataSource, paths []string) error {
	// The authentication type the merged result will end at: the stored one
	// unless this request masks it. Resolved before the loop so a credential
	// path validates the same way whichever order the paths arrive in.
	effectiveAuthType := dataSource.GetAuthenticationType()
	if slices.Contains(paths, "authentication_type") {
		effectiveAuthType = convertV1AuthenticationType(src.AuthenticationType)
	}

	for _, path := range paths {
		switch path {
		case "username":
			dataSource.Username = src.Username
		case "password":
			dataSource.Password = src.Password
		case "ssl_ca":
			dataSource.SslCa = src.SslCa
		case "ssl_ca_path":
			dataSource.SslCaPath = src.SslCaPath
		case "ssl_cert":
			dataSource.SslCert = src.SslCert
		case "ssl_cert_path":
			dataSource.SslCertPath = src.SslCertPath
		case "ssl_key":
			dataSource.SslKey = src.SslKey
		case "ssl_key_path":
			dataSource.SslKeyPath = src.SslKeyPath
		case "host":
			dataSource.Host = src.Host
		case "port":
			dataSource.Port = src.Port
		case "database":
			dataSource.Database = src.Database
		case "srv":
			dataSource.Srv = src.Srv
		case "authentication_database":
			dataSource.AuthenticationDatabase = src.AuthenticationDatabase
		case "sid":
			dataSource.Sid = src.Sid
		case "service_name":
			dataSource.ServiceName = src.ServiceName
		case "ssh_host":
			dataSource.SshHost = src.SshHost
		case "ssh_port":
			dataSource.SshPort = src.SshPort
		case "ssh_user":
			dataSource.SshUser = src.SshUser
		case "ssh_password":
			dataSource.SshPassword = src.SshPassword
		case "ssh_private_key":
			dataSource.SshPrivateKey = src.SshPrivateKey
		case "authentication_private_key":
			dataSource.AuthenticationPrivateKey = src.AuthenticationPrivateKey
		case "authentication_private_key_passphrase":
			dataSource.AuthenticationPrivateKeyPassphrase = src.AuthenticationPrivateKeyPassphrase
		case "external_secret":
			externalSecret, err := convertV1DataSourceExternalSecret(src.ExternalSecret)
			if err != nil {
				return err
			}
			dataSource.ExternalSecret = externalSecret
		case "sasl_config":
			dataSource.SaslConfig = convertV1DataSourceSaslConfig(src.SaslConfig)
		case "authentication_type":
			dataSource.AuthenticationType = convertV1AuthenticationType(src.AuthenticationType)
		case "additional_addresses":
			dataSource.AdditionalAddresses = convertAdditionalAddresses(src.AdditionalAddresses)
		case "replica_set":
			dataSource.ReplicaSet = src.ReplicaSet
		case "direct_connection":
			dataSource.DirectConnection = src.DirectConnection
		case "region":
			dataSource.Region = src.Region
		case "warehouse_id":
			dataSource.WarehouseId = src.WarehouseId
		case "use_ssl":
			dataSource.UseSsl = src.UseSsl
		case "verify_tls_certificate":
			dataSource.VerifyTlsCertificate = src.VerifyTlsCertificate
		case "redis_type":
			dataSource.RedisType = convertV1RedisType(src.RedisType)
		case "cloud_sql_ip_type":
			dataSource.CloudSqlIpType = convertV1CloudSQLIPType(src.CloudSqlIpType)
		case "master_name":
			dataSource.MasterName = src.MasterName
		case "master_username":
			dataSource.MasterUsername = src.MasterUsername
		case "master_password":
			dataSource.MasterPassword = src.MasterPassword
		case "extra_connection_parameters":
			dataSource.ExtraConnectionParameters = src.ExtraConnectionParameters
		case "project_id":
			dataSource.ProjectId = src.ProjectId
		case "instance_id":
			dataSource.InstanceId = src.InstanceId
		case "azure_credential", "aws_credential", "gcp_credential":
			// Dispatch on the mask path, never on the request's
			// authentication_type: an unset one used to fall through an empty
			// branch, so rotating a leaked credential returned 200 and wrote
			// nothing, and a mismatched one wrote a different credential than
			// the mask named while moving authentication_type itself.
			if want := credentialPathAuthType[path]; want != effectiveAuthType {
				return connect.NewError(connect.CodeInvalidArgument, errors.Errorf(
					`update_mask %q requires authentication_type %q, but the data source resolves to %q`,
					path, want, effectiveAuthType))
			}
			applyIAMCredential(dataSource, path, src)
		default:
			return connect.NewError(connect.CodeInvalidArgument, errors.Errorf(`unsupported update_mask "%s"`, path))
		}
	}

	return nil
}

// credentialPathAuthType maps each IAM credential mask path to the single
// authentication type that owns it.
var credentialPathAuthType = map[string]storepb.DataSource_AuthenticationType{
	"azure_credential": storepb.DataSource_AZURE_IAM,
	"aws_credential":   storepb.DataSource_AWS_RDS_IAM,
	"gcp_credential":   storepb.DataSource_GOOGLE_CLOUD_SQL_IAM,
}

// applyIAMCredential writes the one credential named by path. A path naming an
// absent credential clears the extension, which is what a mask that names the
// field with no value means.
func applyIAMCredential(dataSource *storepb.DataSource, path string, src *v1pb.DataSource) {
	switch path {
	case "azure_credential":
		azureCredential := src.GetAzureCredential()
		if azureCredential == nil {
			dataSource.IamExtension = nil
			return
		}
		dataSource.IamExtension = &storepb.DataSource_AzureCredential_{
			AzureCredential: &storepb.DataSource_AzureCredential{
				TenantId:     azureCredential.TenantId,
				ClientId:     azureCredential.ClientId,
				ClientSecret: azureCredential.ClientSecret,
			},
		}
	case "aws_credential":
		awsCredential := src.GetAwsCredential()
		if awsCredential == nil {
			dataSource.IamExtension = nil
			return
		}
		dataSource.IamExtension = &storepb.DataSource_AwsCredential{
			AwsCredential: &storepb.DataSource_AWSCredential{
				AccessKeyId:     awsCredential.AccessKeyId,
				SecretAccessKey: awsCredential.SecretAccessKey,
				SessionToken:    awsCredential.SessionToken,
				RoleArn:         awsCredential.RoleArn,
				ExternalId:      awsCredential.ExternalId,
			},
		}
	case "gcp_credential":
		gcpCredential := src.GetGcpCredential()
		if gcpCredential == nil {
			dataSource.IamExtension = nil
			return
		}
		dataSource.IamExtension = &storepb.DataSource_GcpCredential{
			GcpCredential: &storepb.DataSource_GCPCredential{
				Content: gcpCredential.Content,
			},
		}
	default:
	}
}

// RemoveDataSource removes a data source to an instance.
func (s *InstanceService) RemoveDataSource(ctx context.Context, req *connect.Request[v1pb.RemoveDataSourceRequest]) (*connect.Response[v1pb.Instance], error) {
	if req.Msg.DataSource == nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("data sources is required"))
	}

	instance, err := getInstanceMessage(ctx, s.store, req.Msg.Name)
	if err != nil {
		return nil, err
	}
	if instance.Deleted {
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q has been deleted", req.Msg.Name))
	}

	metadata := proto.CloneOf(instance.Metadata)
	var updatedDataSources []*storepb.DataSource
	var dataSource *storepb.DataSource
	for _, ds := range instance.Metadata.GetDataSources() {
		if ds.GetId() == req.Msg.DataSource.Id {
			dataSource = ds
		} else {
			updatedDataSources = append(updatedDataSources, ds)
		}
	}
	if dataSource == nil {
		return nil, connect.NewError(connect.CodeNotFound, errors.New("data source not found"))
	}

	// We only support remove RO type datasource to instance now, see more details in instance_service.proto.
	if dataSource.GetType() != storepb.DataSourceType_READ_ONLY {
		return nil, connect.NewError(connect.CodeInvalidArgument, errors.New("only support remove read-only data source"))
	}

	metadata.DataSources = updatedDataSources
	instance, err = s.store.UpdateInstance(ctx, &store.UpdateInstanceMessage{
		ResourceID: &instance.ResourceID,
		Workspace:  instance.Workspace,
		Metadata:   metadata,
	})
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}

	result := s.convertToV1Instance(ctx, instance)
	return connect.NewResponse(result), nil
}

func getInstanceMessage(ctx context.Context, stores *store.Store, name string) (*store.InstanceMessage, error) {
	return getInstanceMessageWithArchivedProject(ctx, stores, name, false)
}

func getInstanceMessageForLifecycle(ctx context.Context, stores *store.Store, name string) (*store.InstanceMessage, error) {
	return getInstanceMessageWithArchivedProject(ctx, stores, name, true)
}

func getInstanceMessageWithArchivedProject(ctx context.Context, stores *store.Store, name string, allowArchivedProject bool) (*store.InstanceMessage, error) {
	projectID, instanceID, err := common.GetInstanceResourceName(name)
	if err != nil {
		return nil, connect.NewError(connect.CodeInvalidArgument, err)
	}

	find := &store.FindInstanceMessage{
		Workspace:     common.GetWorkspaceIDFromContext(ctx),
		ResourceID:    &instanceID,
		ProjectID:     projectID,
		WorkspaceOnly: projectID == nil,
	}
	instance, err := stores.GetInstance(ctx, find)
	if err != nil {
		return nil, connect.NewError(connect.CodeInternal, err)
	}
	if instance == nil {
		return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q not found", name))
	}
	if !allowArchivedProject {
		if err := ensureProjectInstanceIsActive(ctx, stores, instance); err != nil {
			return nil, err
		}
	}

	return instance, nil
}

func ensureProjectInstanceIsActive(ctx context.Context, stores *store.Store, instance *store.InstanceMessage) error {
	if instance.ProjectID == nil {
		return nil
	}
	project, err := stores.GetProject(ctx, &store.FindProjectMessage{
		Workspace:  common.GetWorkspaceIDFromContext(ctx),
		ResourceID: instance.ProjectID,
	})
	if err != nil {
		return connect.NewError(connect.CodeInternal, errors.Wrap(err, "failed to get instance project"))
	}
	if project == nil || project.Deleted {
		return connect.NewError(connect.CodeNotFound, errors.Errorf("project %q not found", *instance.ProjectID))
	}
	return nil
}

func instanceCollectionParent(projectID *string) *string {
	if projectID == nil {
		return nil
	}
	return new(common.FormatProject(*projectID))
}

func (s *InstanceService) getInstanceCollection(ctx context.Context, parent *string, names []string) ([]*store.InstanceMessage, error) {
	projectID, err := s.getProjectInstanceParent(ctx, parent)
	if err != nil {
		return nil, err
	}
	instances := make([]*store.InstanceMessage, 0, len(names))
	for _, name := range names {
		instance, err := getInstanceMessage(ctx, s.store, name)
		if err != nil {
			return nil, err
		}
		if instance.Deleted {
			return nil, connect.NewError(connect.CodeNotFound, errors.Errorf("instance %q has been deleted", name))
		}
		if (projectID == nil) != (instance.ProjectID == nil) || projectID != nil && *projectID != *instance.ProjectID {
			return nil, connect.NewError(connect.CodeInvalidArgument, errors.Errorf("instance %q is not in its requested collection", name))
		}
		instances = append(instances, instance)
	}
	return instances, nil
}

// buildInstanceName builds an instance name in its owner collection.
func buildInstanceName(instanceID string, projectID *string) string {
	if projectID != nil {
		return common.FormatProjectInstance(*projectID, instanceID)
	}
	var b strings.Builder
	b.Grow(len(common.InstanceNamePrefix) + len(instanceID))
	_, _ = b.WriteString(common.InstanceNamePrefix)
	_, _ = b.WriteString(instanceID)
	return b.String()
}

// buildEnvironmentName builds the environment name with the given environment ID.
func buildEnvironmentName(environmentID *string) *string {
	if environmentID == nil || *environmentID == "" {
		return nil
	}
	var b strings.Builder
	b.Grow(len("environments/") + len(*environmentID))
	_, _ = b.WriteString("environments/")
	_, _ = b.WriteString(*environmentID)
	result := b.String()
	return &result
}

func (s *InstanceService) instanceCountGuard(ctx context.Context) error {
	workspaceID := common.GetWorkspaceIDFromContext(ctx)
	instanceLimit := s.licenseService.GetInstanceLimit(ctx, workspaceID)

	count, err := s.store.CountActiveInstances(ctx, workspaceID)
	if err != nil {
		return connect.NewError(connect.CodeInternal, err)
	}
	if count >= instanceLimit {
		return connect.NewError(connect.CodeResourceExhausted, errors.Errorf("reached the maximum instance count %d", instanceLimit))
	}

	return nil
}

// validateExtraConnectionParameters validates extra connection parameters for security risks.
func validateExtraConnectionParameters(engine storepb.Engine, params map[string]string) error {
	// Validate MySQL-compatible engines
	switch engine {
	case storepb.Engine_MYSQL, storepb.Engine_MARIADB, storepb.Engine_OCEANBASE, storepb.Engine_TIDB:
		for key := range params {
			normalizedKey := strings.ToLower(strings.TrimSpace(key))
			if normalizedKey == "allowallfiles" {
				// Disables file allowlist for LOAD DATA LOCAL INFILE and allows all files (might be insecure)
				return errors.Errorf("connection parameter %q is not allowed for security reasons. This parameter can allow a malicious database server to read arbitrary files from the client", key)
			}
		}
	default:
		// No validation needed for other engines
	}
	return nil
}