Skip to content

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

29.1 KB
Raw Blame
// Package pg is the plugin for PostgreSQL driver.
package pg

import (
	"context"
	"database/sql"
	"fmt"
	"log/slog"
	"net"
	"regexp"
	"strconv"
	"strings"
	"time"
	"unicode"

	"cloud.google.com/go/cloudsqlconn"
	"github.com/aws/aws-sdk-go-v2/feature/rds/auth"
	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/stdlib"
	"github.com/pkg/errors"
	"go.uber.org/multierr"
	"golang.org/x/crypto/ssh"
	"google.golang.org/protobuf/types/known/durationpb"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/common/log"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/plugin/db"
	"github.com/bytebase/bytebase/backend/plugin/db/transaction"
	"github.com/bytebase/bytebase/backend/plugin/db/util"
	"github.com/bytebase/bytebase/backend/plugin/parser/base"
	pgparser "github.com/bytebase/bytebase/backend/plugin/parser/pg"
)

var (
	// driverName is the driver name that our driver dependence register, now is "pgx".
	driverName = "pgx"

	_ db.Driver = (*Driver)(nil)
)

func init() {
	db.Register(storepb.Engine_POSTGRES, newDriver)
}

// Driver is the Postgres driver.
type Driver struct {
	config db.ConnectionConfig

	db        *sql.DB
	sshClient *ssh.Client
	// cloudSQLDialer refreshes its ephemeral certificate in the background until
	// closed, so it lives and dies with the driver.
	cloudSQLDialer *cloudsqlconn.Dialer
	// connectionString is the connection string registered by pgx.
	// Unregister connectionString if we don't need it.
	connectionString string
	databaseName     string
	connectionCtx    db.ConnectionContext
}

func newDriver() db.Driver {
	return &Driver{}
}

// Open opens a Postgres driver.
func (d *Driver) Open(ctx context.Context, _ storepb.Engine, config db.ConnectionConfig) (db.Driver, error) {
	// A driver whose Open fails is discarded without a Close, so Open releases
	// what it acquired itself.
	opened := false
	defer func() {
		if !opened {
			_ = d.Close(ctx)
		}
	}()

	var pgxConnConfig *pgx.ConnConfig
	var err error

	switch config.DataSource.GetAuthenticationType() {
	case storepb.DataSource_GOOGLE_CLOUD_SQL_IAM:
		pgxConnConfig, err = d.getCloudSQLConnectionConfig(ctx, config)
	case storepb.DataSource_AWS_RDS_IAM:
		pgxConnConfig, err = getRDSConnectionConfig(ctx, config)
	default:
		pgxConnConfig, err = getPGConnectionConfig(config)
	}
	if err != nil {
		return nil, err
	}
	appName := "bytebase"
	if config.ConnectionContext.TaskRunUID != nil {
		appName = fmt.Sprintf("bytebase-taskrun-%d", *config.ConnectionContext.TaskRunUID)
	}
	pgxConnConfig.RuntimeParams["application_name"] = appName
	if config.ConnectionContext.ReadOnly {
		pgxConnConfig.RuntimeParams["default_transaction_read_only"] = "true"
	}

	if config.DataSource.GetSshHost() != "" {
		sshClient, err := util.GetSSHClient(config.DataSource)
		if err != nil {
			return nil, err
		}
		d.sshClient = sshClient

		pgxConnConfig.DialFunc = func(ctx context.Context, network, addr string) (net.Conn, error) {
			return util.DialSSHTunnel(ctx, sshClient, network, addr)
		}
	}

	d.databaseName = config.ConnectionContext.DatabaseName
	if config.ConnectionContext.DatabaseName != "" {
		pgxConnConfig.Database = config.ConnectionContext.DatabaseName
	} else if config.DataSource.GetDatabase() != "" {
		pgxConnConfig.Database = config.DataSource.GetDatabase()
	} else {
		pgxConnConfig.Database = "postgres"
	}
	d.config = config

	pgxConnConfig.OnNotice = d.onNotice

	d.connectionString = stdlib.RegisterConnConfig(pgxConnConfig)
	db, err := sql.Open(driverName, d.connectionString)
	if err != nil {
		return nil, err
	}
	d.db = db
	if config.ConnectionContext.TenantMode {
		owner, err := d.GetCurrentDatabaseOwner(ctx)
		if err != nil {
			return nil, errors.Wrapf(err, "failed to get database owner")
		}
		if _, err := d.db.ExecContext(ctx, fmt.Sprintf("SET ROLE \"%s\";", owner)); err != nil { // NOSONAR(go:S2077) owner is from pg_roles system catalog, not user input
			return nil, errors.Wrapf(err, "failed to set role to database owner %q", owner)
		}
	}
	d.connectionCtx = config.ConnectionContext
	opened = true
	return d, nil
}

func (d *Driver) onNotice(_ *pgconn.PgConn, n *pgconn.Notice) {
	if n == nil {
		return
	}

	d.connectionCtx.AppendMessage(&v1pb.QueryResult_Message{
		Level:   convertLevel(n.Severity),
		Content: n.Message,
	})
}

func convertLevel(level string) v1pb.QueryResult_Message_Level {
	switch level {
	case "DEBUG":
		return v1pb.QueryResult_Message_DEBUG
	case "INFO":
		return v1pb.QueryResult_Message_INFO
	case "NOTICE":
		return v1pb.QueryResult_Message_NOTICE
	case "WARNING":
		return v1pb.QueryResult_Message_WARNING
	case "EXCEPTION":
		return v1pb.QueryResult_Message_EXCEPTION
	case "LOG":
		return v1pb.QueryResult_Message_LOG
	default:
		return v1pb.QueryResult_Message_LEVEL_UNSPECIFIED
	}
}

// PushAndClearMessages pushes and clears the messages.
func (d *Driver) PushAndClearMessages() []*v1pb.QueryResult_Message {
	messages := d.connectionCtx.MessageBuffer
	d.connectionCtx.MessageBuffer = nil
	return messages
}

func getPGConnectionConfig(config db.ConnectionConfig) (*pgx.ConnConfig, error) {
	if config.DataSource.Username == "" {
		return nil, errors.Errorf("user must be set")
	}

	if (config.DataSource.GetSslCert() == "" && config.DataSource.GetSslKey() != "") ||
		(config.DataSource.GetSslCert() != "" && config.DataSource.GetSslKey() == "") {
		return nil, errors.Errorf("ssl-cert and ssl-key must be both set or unset")
	}

	connStr, err := util.PGHostPort(config.DataSource.Host, config.DataSource.Port)
	if err != nil {
		return nil, err
	}
	if config.DataSource.GetUseSsl() {
		connStr += fmt.Sprintf(" sslmode=%s", util.GetPGSSLMode(config.DataSource))
	}

	// Add target_session_attrs=read-write if specified in ExtraConnectionParameters
	for key, value := range config.DataSource.GetExtraConnectionParameters() {
		connStr += fmt.Sprintf(" %s=%s", key, value)
	}

	connConfig, err := pgx.ParseConfig(connStr)
	if err != nil {
		return nil, err
	}
	connConfig.User = config.DataSource.Username
	connConfig.Password = config.Password
	connConfig.Database = config.ConnectionContext.DatabaseName

	tlscfg, err := util.GetTLSConfig(config.DataSource)
	if err != nil {
		return nil, err
	}
	if tlscfg != nil {
		connConfig.TLSConfig = tlscfg
		util.ApplyPGTLSConfig(tlscfg, connConfig.Host, connConfig.Fallbacks)
	}

	// Apply pgbouncer-safe defaults unless the user has explicitly configured
	// them via ExtraConnectionParameters (preserves escape hatches such as
	// default_query_exec_mode=simple_protocol for non-PostgreSQL-compatible proxies).
	extraParams := config.DataSource.GetExtraConnectionParameters()
	if _, ok := extraParams["default_query_exec_mode"]; !ok {
		connConfig.DefaultQueryExecMode = pgx.QueryExecModeExec
	}
	if _, ok := extraParams["statement_cache_capacity"]; !ok {
		connConfig.StatementCacheCapacity = 0
	}
	return connConfig, nil
}

func getRDSConnectionPassword(ctx context.Context, conf db.ConnectionConfig) (string, error) {
	cfg, err := util.GetAWSConnectionConfig(ctx, conf)
	if err != nil {
		return "", errors.Wrap(err, "load aws config failed")
	}

	// Handle cross-account role assumption if configured
	if err := util.AssumeRoleIfNeeded(ctx, &cfg, conf.ConnectionContext, conf.DataSource.GetAwsCredential()); err != nil {
		return "", err
	}

	dbEndpoint := fmt.Sprintf("%s:%s", conf.DataSource.Host, conf.DataSource.Port)
	authenticationToken, err := auth.BuildAuthToken(
		ctx, dbEndpoint, conf.DataSource.GetRegion(), conf.DataSource.Username, cfg.Credentials)
	if err != nil {
		return "", errors.Wrap(err, "failed to create authentication token")
	}

	return authenticationToken, nil
}

// getRDSConnectionConfig returns connection config for AWS RDS.
//
// https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.Go.html
func getRDSConnectionConfig(ctx context.Context, conf db.ConnectionConfig) (*pgx.ConnConfig, error) {
	password, err := getRDSConnectionPassword(ctx, conf)
	if err != nil {
		return nil, err
	}

	hostPort, err := util.PGHostPort(conf.DataSource.Host, conf.DataSource.Port)
	if err != nil {
		return nil, err
	}
	dsn := fmt.Sprintf("%s user=%s password=%s", hostPort, util.QuotePGValue(conf.DataSource.Username), util.QuotePGValue(password))
	config, err := pgx.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}
	config.DefaultQueryExecMode = pgx.QueryExecModeExec
	config.StatementCacheCapacity = 0
	return config, nil
}

// getCloudSQLConnectionConfig returns config for Cloud SQL connector.
// refs:
// https://cloud.google.com/sql/docs/postgres/connect-connectors
// https://github.com/GoogleCloudPlatform/golang-samples/blob/main/cloudsql/postgres/database-sql/cloudsql.go
func (d *Driver) getCloudSQLConnectionConfig(ctx context.Context, conf db.ConnectionConfig) (*pgx.ConnConfig, error) {
	dialer, err := util.GetGCPConnectionConfig(ctx, conf)
	if err != nil {
		return nil, errors.Wrap(err, "load gcp config failed")
	}
	d.cloudSQLDialer = dialer

	dsn := fmt.Sprintf("user=%s", util.QuotePGValue(conf.DataSource.Username))
	config, err := pgx.ParseConfig(dsn)
	if err != nil {
		return nil, err
	}
	config.DialFunc = func(ctx context.Context, _, _ string) (net.Conn, error) {
		return dialer.Dial(ctx, conf.DataSource.Host)
	}

	config.DefaultQueryExecMode = pgx.QueryExecModeExec
	config.StatementCacheCapacity = 0
	return config, nil
}

// Close closes the driver.
func (d *Driver) Close(context.Context) error {
	stdlib.UnregisterConnConfig(d.connectionString)
	var err error
	if d.db != nil {
		err = multierr.Append(err, d.db.Close())
	}
	if d.sshClient != nil {
		err = multierr.Append(err, d.sshClient.Close())
	}
	if d.cloudSQLDialer != nil {
		err = multierr.Append(err, d.cloudSQLDialer.Close())
	}
	return err
}

// Ping pings the database.
func (d *Driver) Ping(ctx context.Context) error {
	return d.db.PingContext(ctx)
}

// GetDB gets the database.
func (d *Driver) GetDB() *sql.DB {
	return d.db
}

// getDatabases gets all databases of an instance.
func (d *Driver) getDatabases(ctx context.Context) ([]*metadatapb.DatabaseSchemaMetadata, error) {
	var databases []*metadatapb.DatabaseSchemaMetadata
	rows, err := d.db.QueryContext(ctx, "SELECT datname, pg_encoding_to_char(encoding), datcollate, pg_catalog.pg_get_userbyid(datdba) as db_owner FROM pg_database;")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		database := &metadatapb.DatabaseSchemaMetadata{}
		if err := rows.Scan(&database.Name, &database.CharacterSet, &database.Collation, &database.Owner); err != nil {
			return nil, err
		}
		databases = append(databases, database)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return databases, nil
}

// GetSearchPath gets the current search path of the database.
// It returns the search path as a raw comma-separated string of schema names.
func (d *Driver) GetSearchPath(ctx context.Context) (string, error) {
	// SHOW search_path returns the current search path.
	// PostgreSQL supports it since 8.2.
	// https://www.postgresql.org/docs/current/sql-show.html
	query := "SHOW search_path"
	var searchPath string
	if err := d.db.QueryRowContext(ctx, query).Scan(&searchPath); err != nil {
		if err == sql.ErrNoRows {
			return "", common.FormatDBErrorEmptyRowWithQuery(query)
		}
		return "", util.FormatErrorWithQuery(err, query)
	}
	return strings.TrimSpace(searchPath), nil
}

// getVersion gets the version of Postgres server.
func (d *Driver) getVersion(ctx context.Context) (string, error) {
	// SHOW server_version_num returns an integer such as 100005, which means 10.0.5.
	// It is more convenient to use SHOW server_version to get the version string.
	// PostgreSQL supports it since 8.2.
	// https://www.postgresql.org/docs/current/functions-info.html
	query := "SHOW server_version_num"
	var version string
	if err := d.db.QueryRowContext(ctx, query).Scan(&version); err != nil {
		if err == sql.ErrNoRows {
			return "", common.FormatDBErrorEmptyRowWithQuery(query)
		}
		return "", util.FormatErrorWithQuery(err, query)
	}
	versionNum, err := strconv.Atoi(version)
	if err != nil {
		return "", err
	}
	// https://www.postgresql.org/docs/current/libpq-status.html#LIBPQ-PQSERVERVERSION
	// Convert to semantic version.
	major, minor, patch := versionNum/1_00_00, (versionNum/100)%100, versionNum%100
	return fmt.Sprintf("%d.%d.%d", major, minor, patch), nil
}

// Execute will execute the statement. For CREATE DATABASE statement, some types of databases such as Postgres
// will not use transactions to execute the statement but will still use transactions to execute the rest of statements.
func (d *Driver) Execute(ctx context.Context, statement string, opts db.ExecuteOptions) (int64, error) {
	if opts.CreateDatabase {
		if err := d.createDatabaseExecute(ctx, statement); err != nil {
			return 0, err
		}
		return 0, nil
	}

	// Parse transaction mode from the script
	config, cleanedStatement := base.ParseTransactionConfig(statement)
	statement = cleanedStatement
	transactionMode := config.Mode

	// Apply default when transaction mode is not specified
	if transactionMode == transaction.ModeUnspecified {
		transactionMode = transaction.DefaultMode()
	}

	owner, err := d.GetCurrentDatabaseOwner(ctx)
	if err != nil {
		return 0, err
	}

	var commands []base.Statement
	var nonTransactionAndSetRoleStmts []base.Statement
	var isPlsql bool
	// HACK(p0ny): always split for pg
	//nolint
	if true || len(statement) <= common.MaxSheetCheckSize {
		singleSQLs, err := pgparser.SplitSQL(statement)
		if err != nil {
			return 0, err
		}
		commands = base.FilterEmptyStatements(singleSQLs)

		// If the statement is a single statement and is a PL/pgSQL block,
		// we should execute it as a single statement without transaction.
		// If the statement is a PL/pgSQL block, we should execute it as a single statement.
		// https://www.postgresql.org/docs/current/plpgsql-control-structures.html
		if len(singleSQLs) == 1 && pgparser.IsPlSQLBlock(singleSQLs[0].Text) {
			isPlsql = true
		}

		var tmpCommands []base.Statement
		for _, command := range commands {
			switch {
			case isSetRoleStatement(command.Text):
				nonTransactionAndSetRoleStmts = append(nonTransactionAndSetRoleStmts, command)
			case IsNonTransactionStatement(command.Text):
				nonTransactionAndSetRoleStmts = append(nonTransactionAndSetRoleStmts, command)
				continue
			case isSuperuserStatement(command.Text) && d.connectionCtx.TenantMode:
				// Use superuser privilege to run privileged statements.
				slog.Info("Use superuser privilege to run privileged statements", slog.String("statement", command.Text))
				ct := command.Text
				if !strings.HasSuffix(strings.TrimRightFunc(ct, unicode.IsSpace), ";") {
					ct += ";"
				}
				command.Text = fmt.Sprintf("SET LOCAL ROLE NONE;%sSET LOCAL ROLE '%s';", ct, owner)
			}
			tmpCommands = append(tmpCommands, command)
		}
		commands = tmpCommands
	}

	// Execute based on transaction mode
	var affectedRows int64
	if transactionMode == transaction.ModeOff {
		affectedRows, err = d.executeInAutoCommitMode(ctx, owner, statement, commands, nonTransactionAndSetRoleStmts, opts, isPlsql)
	} else {
		affectedRows, err = d.executeInTransactionMode(ctx, owner, statement, commands, nonTransactionAndSetRoleStmts, opts, isPlsql)
	}
	if err == nil {
		return affectedRows, nil
	}
	var lockErr *LockTimeoutError
	if !errors.As(err, &lockErr) {
		return affectedRows, err
	}

	// Lock timeout retries.
	for i := range opts.MaximumRetries {
		// Random retry interval.
		interval := (150 + (i+1)*100/opts.MaximumRetries) * int(time.Millisecond)
		time.Sleep(time.Duration(interval))

		// Log retry info.
		opts.LogRetryInfo(err, i+1)

		// Do retry.
		if transactionMode == transaction.ModeOff {
			affectedRows, err = d.executeInAutoCommitMode(ctx, owner, statement, commands, nonTransactionAndSetRoleStmts, opts, isPlsql)
		} else {
			affectedRows, err = d.executeInTransactionMode(ctx, owner, statement, commands, nonTransactionAndSetRoleStmts, opts, isPlsql)
		}
		if err == nil {
			break
		}
		if !errors.As(err, &lockErr) {
			break
		}
	}

	return affectedRows, err
}

type LockTimeoutError struct {
	Message string
}

func (e *LockTimeoutError) Error() string {
	return e.Message
}

func isLockTimeoutError(message string) bool {
	return strings.Contains(message, "canceling statement due to lock timeout")
}

func (d *Driver) executeInTransactionMode(
	ctx context.Context,
	owner string,
	statement string,
	commands []base.Statement,
	nonTransactionAndSetRoleStmts []base.Statement,
	opts db.ExecuteOptions,
	isPlsql bool,
) (int64, error) {
	conn, err := d.db.Conn(ctx)
	if err != nil {
		return 0, errors.Wrapf(err, "failed to get connection")
	}
	defer conn.Close()

	if isPlsql {
		if d.connectionCtx.TenantMode {
			// USE SET SESSION ROLE to set the role for the current session.
			if _, err := conn.ExecContext(ctx, fmt.Sprintf("SET SESSION ROLE '%s'", owner)); err != nil { // NOSONAR(go:S2077) owner is from pg_roles system catalog, not user input
				return 0, errors.Wrapf(err, "failed to set role to database owner %q", owner)
			}
		}
		opts.LogCommandExecute(&storepb.Range{Start: 0, End: int32(len(statement))}, statement)
		if _, err := conn.ExecContext(ctx, statement); err != nil {
			opts.LogCommandResponse(0, []int64{0}, err.Error())
			return 0, err
		}
		opts.LogCommandResponse(0, []int64{0}, "")

		return 0, nil
	}

	totalRowsAffected := int64(0)

	totalCommands := len(commands)
	if totalCommands > 0 {
		err = conn.Raw(func(driverConn any) error {
			conn := driverConn.(*stdlib.Conn).Conn()

			tx, err := conn.Begin(ctx)
			if err != nil {
				opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_BEGIN, err.Error())
				return errors.Wrapf(err, "failed to begin transaction")
			}
			opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_BEGIN, "")

			committed := false
			defer func() {
				rollbackErr := tx.Rollback(ctx)
				if committed {
					return
				}
				var rerr string
				if rollbackErr != nil {
					rerr = rollbackErr.Error()
				}
				opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_ROLLBACK, rerr)
			}()

			if d.connectionCtx.TenantMode {
				// Set the current transaction role to the database owner so that the owner of created objects will be the same as the database owner.
				if _, err := tx.Exec(ctx, fmt.Sprintf("SET LOCAL ROLE '%s'", owner)); err != nil {
					return err
				}
			}

			for _, command := range commands {
				opts.LogCommandExecute(command.Range, command.Text)

				rr := tx.Conn().PgConn().Exec(ctx, command.Text)
				results, err := rr.ReadAll()
				if err != nil {
					opts.LogCommandResponse(0, nil, err.Error())

					return err
				}

				var rowsAffected int64
				var allRowsAffected []int64
				for _, result := range results {
					ra := result.CommandTag.RowsAffected()
					allRowsAffected = append(allRowsAffected, ra)
					rowsAffected += ra
				}
				opts.LogCommandResponse(rowsAffected, allRowsAffected, "")

				totalRowsAffected += rowsAffected
			}

			if err := tx.Commit(ctx); err != nil {
				opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_COMMIT, err.Error())
				return errors.Wrapf(err, "failed to commit transaction")
			}
			opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_COMMIT, "")
			committed = true

			return nil
		})
		if err != nil {
			if isLockTimeoutError(err.Error()) {
				return 0, &LockTimeoutError{
					Message: err.Error(),
				}
			}
			return 0, err
		}
	}

	if d.connectionCtx.TenantMode {
		// USE SET SESSION ROLE to set the role for the current session.
		if _, err := conn.ExecContext(ctx, fmt.Sprintf("SET SESSION ROLE '%s'", owner)); err != nil { // NOSONAR(go:S2077) owner is from pg_roles system catalog, not user input
			return 0, errors.Wrapf(err, "failed to set role to database owner %q", owner)
		}
	}
	// Run non-transaction statements at the end.
	for _, stmt := range nonTransactionAndSetRoleStmts {
		opts.LogCommandExecute(stmt.Range, stmt.Text)
		if _, err := conn.ExecContext(ctx, stmt.Text); err != nil {
			opts.LogCommandResponse(0, []int64{0}, err.Error())
			return 0, err
		}
		opts.LogCommandResponse(0, []int64{0}, "")
	}
	return totalRowsAffected, nil
}

func (d *Driver) executeInAutoCommitMode(
	ctx context.Context,
	owner string,
	statement string,
	commands []base.Statement,
	nonTransactionAndSetRoleStmts []base.Statement,
	opts db.ExecuteOptions,
	isPlsql bool,
) (int64, error) {
	// For auto-commit mode, treat all statements as non-transactional
	nonTransactionAndSetRoleStmts = append(nonTransactionAndSetRoleStmts, commands...)

	conn, err := d.db.Conn(ctx)
	if err != nil {
		return 0, errors.Wrapf(err, "failed to get connection")
	}
	defer conn.Close()

	if d.connectionCtx.TenantMode {
		// USE SET SESSION ROLE to set the role for the current session.
		if _, err := conn.ExecContext(ctx, fmt.Sprintf("SET SESSION ROLE '%s'", owner)); err != nil { // NOSONAR(go:S2077) owner is from pg_roles system catalog, not user input
			return 0, errors.Wrapf(err, "failed to set role to database owner %q", owner)
		}
	}

	if isPlsql {
		opts.LogCommandExecute(&storepb.Range{Start: 0, End: int32(len(statement))}, statement)
		if _, err := conn.ExecContext(ctx, statement); err != nil {
			opts.LogCommandResponse(0, []int64{0}, err.Error())
			return 0, err
		}
		opts.LogCommandResponse(0, []int64{0}, "")
		return 0, nil
	}

	totalRowsAffected := int64(0)
	// Execute all statements individually in auto-commit mode
	for _, stmt := range nonTransactionAndSetRoleStmts {
		opts.LogCommandExecute(stmt.Range, stmt.Text)

		sqlResult, err := conn.ExecContext(ctx, stmt.Text)
		if err != nil {
			opts.LogCommandResponse(0, []int64{0}, err.Error())
			if isLockTimeoutError(err.Error()) {
				return totalRowsAffected, &LockTimeoutError{
					Message: err.Error(),
				}
			}
			return totalRowsAffected, err
		}

		rowsAffected, err := sqlResult.RowsAffected()
		if err != nil {
			// PostgreSQL returns error for statements that don't support RowsAffected
			rowsAffected = 0
		}

		opts.LogCommandResponse(rowsAffected, []int64{rowsAffected}, "")
		totalRowsAffected += rowsAffected
	}

	return totalRowsAffected, nil
}

func (d *Driver) createDatabaseExecute(ctx context.Context, statement string) error {
	databaseName, err := getDatabaseInCreateDatabaseStatement(statement)
	if err != nil {
		return err
	}
	databases, err := d.getDatabases(ctx)
	if err != nil {
		return err
	}
	for _, database := range databases {
		if database.Name == databaseName {
			// Database already exists.
			return nil
		}
	}

	for _, s := range strings.Split(statement, "\n") {
		if _, err := d.db.ExecContext(ctx, s); err != nil {
			return err
		}
	}
	return nil
}

var (
	// DROP DATABASE cannot run inside a transaction block.
	// DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]。
	dropDatabaseReg = regexp.MustCompile(`(?i)DROP DATABASE`)
	// CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
	// CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ] ...
	createIndexReg = regexp.MustCompile(`(?i)CREATE(\s+(UNIQUE\s+)?)INDEX(\s+)CONCURRENTLY`)
	// DROP INDEX CONCURRENTLY cannot run inside a transaction block.
	// DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ].
	dropIndexReg = regexp.MustCompile(`(?i)DROP(\s+)INDEX(\s+)CONCURRENTLY`)
	// VACUUM cannot run inside a transaction block.
	// VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
	// VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ].
	vacuumReg = regexp.MustCompile(`(?i)^\s*VACUUM`)
	// SET ROLE is a special statement that should be run before any other statements containing inside a transaction block or not.
	setRoleReg = regexp.MustCompile(`(?i)SET\s+((SESSION|LOCAL)\s+)?ROLE`)
)

func isSetRoleStatement(stmt string) bool {
	return len(setRoleReg.FindString(stmt)) > 0
}

func IsNonTransactionStatement(stmt string) bool {
	if len(dropDatabaseReg.FindString(stmt)) > 0 {
		return true
	}
	if len(createIndexReg.FindString(stmt)) > 0 {
		return true
	}
	if len(dropIndexReg.FindString(stmt)) > 0 {
		return true
	}
	return len(vacuumReg.FindString(stmt)) > 0
}

func isSuperuserStatement(stmt string) bool {
	upperCaseStmt := strings.ToUpper(strings.TrimLeftFunc(stmt, unicode.IsSpace))
	if strings.HasPrefix(upperCaseStmt, "GRANT") || strings.HasPrefix(upperCaseStmt, "CREATE EXTENSION") || strings.HasPrefix(upperCaseStmt, "CREATE EVENT TRIGGER") || strings.HasPrefix(upperCaseStmt, "COMMENT ON EVENT TRIGGER") || strings.HasPrefix(upperCaseStmt, "COMMENT ON EXTENSION") {
		return true
	}
	return false
}

func getDatabaseInCreateDatabaseStatement(createDatabaseStatement string) (string, error) {
	raw := strings.TrimRight(createDatabaseStatement, ";")
	raw = strings.TrimPrefix(raw, "CREATE DATABASE")
	tokens := strings.Fields(raw)
	if len(tokens) == 0 {
		return "", errors.Errorf("database name not found")
	}
	databaseName := strings.TrimLeft(tokens[0], `"`)
	databaseName = strings.TrimRight(databaseName, `"`)
	return databaseName, nil
}

// GetCurrentDatabaseOwner gets the role of the current database.
func (d *Driver) GetCurrentDatabaseOwner(ctx context.Context) (string, error) {
	const query = `
		SELECT
			u.rolname
		FROM
			pg_roles AS u JOIN pg_database AS d ON (d.datdba = u.oid)
		WHERE
			d.datname = current_database();
		`
	var owner string
	if err := d.db.QueryRowContext(ctx, query).Scan(&owner); err != nil {
		return "", err
	}
	return owner, nil
}

// QueryConn queries a SQL statement in a given connection.
func (d *Driver) QueryConn(ctx context.Context, conn *sql.Conn, statement string, queryContext db.QueryContext) ([]*v1pb.QueryResult, error) {
	singleSQLs, err := pgparser.SplitSQL(statement)
	if err != nil {
		return nil, err
	}
	singleSQLs = base.FilterEmptyStatements(singleSQLs)
	if len(singleSQLs) == 0 {
		return nil, nil
	}

	var results []*v1pb.QueryResult
	for _, singleSQL := range singleSQLs {
		statement := singleSQL.Text
		if queryContext.Explain {
			explained, err := base.ExplainStatement(storepb.Engine_POSTGRES, statement, db.ExplainFormat(queryContext.Option.GetExplainFormat()))
			if err != nil {
				return nil, err
			}
			statement = explained
		} else if queryContext.Limit > 0 {
			statement = base.StatementWithResultLimit(storepb.Engine_POSTGRES, statement, queryContext.Limit, "")
		}

		_, allQuery, err := base.ValidateSQLForEditor(storepb.Engine_POSTGRES, statement)
		if err != nil {
			return nil, err
		}

		// Sanitize the schema name by escaping any quotes.
		safeSchemaName := strings.ReplaceAll(queryContext.Schema, "\"", "\"\"")

		// If the queryContext.Schema is not empty, set the search path for the database connection to the specified schema.
		// Keep public in the path because PostgreSQL extensions commonly install functions there, and their
		// function bodies may call helper functions without schema qualification.
		if queryContext.Schema != "" {
			if _, err := conn.ExecContext(ctx, fmt.Sprintf(`SET search_path TO "%s", public;`, safeSchemaName)); err != nil { // NOSONAR(go:S2077) safeSchemaName is sanitized via strings.ReplaceAll above
				return nil, err
			}
		}

		startTime := time.Now()
		queryResult, err := func() (*v1pb.QueryResult, error) {
			if allQuery {
				location := util.PgSessionLocation(conn)
				rows, err := conn.QueryContext(ctx, statement)
				if err != nil {
					return nil, err
				}
				defer rows.Close()
				r, err := util.RowsToQueryResult(rows, makeValueByTypeName, convertValueIn(location), queryContext.MaximumSQLResultSize)
				if err != nil {
					return nil, err
				}
				r.Messages = d.PushAndClearMessages()
				if err := rows.Err(); err != nil {
					return nil, err
				}
				if !queryContext.Explain {
					r.QueryPlan = db.TypedExplainPlan(pgparser.DescribeExplain(statement))
				}
				return r, nil
			}

			sqlResult, err := conn.ExecContext(ctx, statement)
			if err != nil {
				return nil, err
			}
			affectedRows, err := sqlResult.RowsAffected()
			if err != nil {
				slog.Info("rowsAffected returns error", log.BBError(err))
			}
			return util.BuildAffectedRowsResult(affectedRows, d.PushAndClearMessages()), nil
		}()
		stop := false
		if err != nil {
			queryResult = &v1pb.QueryResult{
				Error:         err.Error(),
				DetailedError: getPgError(err),
			}
			stop = true
		}
		queryResult.Statement = statement
		queryResult.Latency = durationpb.New(time.Since(startTime))
		queryResult.RowsCount = int64(len(queryResult.Rows))
		results = append(results, queryResult)
		if stop {
			break
		}
	}

	return results, nil
}

func getPgError(e error) *v1pb.QueryResult_PostgresError_ {
	if e == nil {
		return nil
	}
	var pge *pgconn.PgError
	if errors.As(e, &pge) {
		return &v1pb.QueryResult_PostgresError_{
			PostgresError: &v1pb.QueryResult_PostgresError{
				Severity:         pge.Severity,
				Code:             pge.Code,
				Message:          pge.Message,
				Detail:           pge.Detail,
				Hint:             pge.Hint,
				Position:         pge.Position,
				InternalPosition: pge.InternalPosition,
				InternalQuery:    pge.InternalQuery,
				Where:            pge.Where,
				SchemaName:       pge.SchemaName,
				TableName:        pge.TableName,
				ColumnName:       pge.ColumnName,
				DataTypeName:     pge.DataTypeName,
				ConstraintName:   pge.ConstraintName,
				File:             pge.File,
				Line:             pge.Line,
				Routine:          pge.Routine,
			},
		}
	}
	return nil
}