Skip to content

PostgreSQL backup tool with Point-In-Time-Recovery and restore verification

28.5 KB
Raw Blame
package databases

import (
	"context"
	"errors"
	"fmt"
	"log/slog"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgconn"
	"gorm.io/gorm"

	audit_logs "databasus-backend/internal/features/audit_logs"
	audit_logs_models "databasus-backend/internal/features/audit_logs/models"
	physical_core_service "databasus-backend/internal/features/backups/backups/core/physical/service"
	postgresql_physical "databasus-backend/internal/features/databases/databases/postgresql/physical"
	"databasus-backend/internal/features/notifiers"
	users_models "databasus-backend/internal/features/users/models"
	"databasus-backend/internal/util/encryption"
)

type DatabaseService struct {
	dbRepository    databaseStore
	notifierService *notifiers.NotifierService
	logger          *slog.Logger

	dbCreationListener         []DatabaseCreationListener
	dbRemoveListener           []DatabaseRemoveListener
	dbCopyListener             []DatabaseCopyListener
	dbBackupTypeChangeListener []DatabaseBackupTypeChangeListener

	workspaceService      workspaceService
	auditLogService       *audit_logs.AuditLogService
	fieldEncryptor        encryption.FieldEncryptor
	physicalBackupService *physical_core_service.PhysicalBackupService
}

func (s *DatabaseService) AddDbCreationListener(
	dbCreationListener DatabaseCreationListener,
) {
	s.dbCreationListener = append(s.dbCreationListener, dbCreationListener)
}

func (s *DatabaseService) AddDbRemoveListener(
	dbRemoveListener DatabaseRemoveListener,
) {
	s.dbRemoveListener = append(s.dbRemoveListener, dbRemoveListener)
}

func (s *DatabaseService) AddDbCopyListener(
	dbCopyListener DatabaseCopyListener,
) {
	s.dbCopyListener = append(s.dbCopyListener, dbCopyListener)
}

func (s *DatabaseService) AddDbBackupTypeChangeListener(
	dbBackupTypeChangeListener DatabaseBackupTypeChangeListener,
) {
	s.dbBackupTypeChangeListener = append(s.dbBackupTypeChangeListener, dbBackupTypeChangeListener)
}

func (s *DatabaseService) GetNotifierAttachedDatabasesIDs(
	notifierID uuid.UUID,
) ([]uuid.UUID, error) {
	databasesIDs, err := s.dbRepository.GetDatabasesIDsByNotifierID(notifierID)
	if err != nil {
		return nil, err
	}

	return databasesIDs, nil
}

func (s *DatabaseService) CreateDatabase(
	ctx context.Context,
	user *users_models.User,
	workspaceID uuid.UUID,
	database *Database,
) (*Database, error) {
	canManage, err := s.workspaceService.CanUserManageDBs(ctx, workspaceID, user)
	if err != nil {
		return nil, err
	}
	if !canManage {
		return nil, errors.New("insufficient permissions to create database in this workspace")
	}

	database.WorkspaceID = &workspaceID

	if err := database.Validate(); err != nil {
		return nil, err
	}

	// The database has no ID until it is saved, so the workspace is all there is to correlate on.
	logger := s.logger.With("workspace_id", workspaceID)

	tunneledDatabase, err := OpenTunnel(ctx, OpenTunnelSpec{
		Database:  database,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return nil, err
	}

	defer tunneledDatabase.Close()

	// Never reassign database here: the original is what gets encrypted and saved below, and the
	// copy carries a loopback address, an ephemeral port and a disabled tunnel.
	databaseThroughTunnel := tunneledDatabase.GetDatabaseThroughTunnel()

	if err := databaseThroughTunnel.PopulateDbData(logger, s.fieldEncryptor); err != nil {
		return nil, fmt.Errorf("failed to auto-detect database data: %w", err)
	}

	if err := databaseThroughTunnel.TestConnection(logger, s.fieldEncryptor); err != nil {
		return nil, err
	}

	tunneledDatabase.CopyDiscoveredMetadataToOriginal()

	if err := database.EncryptSensitiveFields(s.fieldEncryptor); err != nil {
		return nil, fmt.Errorf("failed to encrypt sensitive fields: %w", err)
	}

	database, err = s.dbRepository.Save(database)
	if err != nil {
		return nil, err
	}

	for _, listener := range s.dbCreationListener {
		listener.OnDatabaseCreated(ctx, database.ID)
	}

	s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
		Message:     fmt.Sprintf("Database created: %s", database.Name),
		UserID:      &user.ID,
		WorkspaceID: &workspaceID,
	})

	return database, nil
}

func (s *DatabaseService) UpdateDatabase(
	ctx context.Context,
	user *users_models.User,
	database *Database,
) error {
	if database.ID == uuid.Nil {
		return errors.New("database ID is required for update")
	}

	existingDatabase, err := s.dbRepository.FindByID(database.ID)
	if err != nil {
		return err
	}

	if existingDatabase.WorkspaceID == nil {
		return errors.New("cannot update database without workspace")
	}

	canManage, err := s.workspaceService.CanUserManageDBs(ctx, *existingDatabase.WorkspaceID, user)
	if err != nil {
		return err
	}
	if !canManage {
		return errors.New("insufficient permissions to update this database")
	}

	if err := database.ValidateUpdate(*existingDatabase, *database); err != nil {
		return err
	}

	for _, notifier := range database.Notifiers {
		if notifier.WorkspaceID != *existingDatabase.WorkspaceID {
			return errors.New("notifier does not belong to this workspace")
		}
	}

	oldBackupType := getPhysicalBackupType(existingDatabase)

	existingDatabase.Update(database)

	if err := existingDatabase.Validate(); err != nil {
		return err
	}

	logger := s.logger.With("database_id", existingDatabase.ID)

	tunneledDatabase, err := OpenTunnel(ctx, OpenTunnelSpec{
		Database:  existingDatabase,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return err
	}

	defer tunneledDatabase.Close()

	// Never reassign existingDatabase here: it is what gets encrypted and saved below.
	databaseThroughTunnel := tunneledDatabase.GetDatabaseThroughTunnel()

	if err := databaseThroughTunnel.PopulateDbData(logger, s.fieldEncryptor); err != nil {
		return fmt.Errorf("failed to auto-detect database data: %w", err)
	}

	if err := databaseThroughTunnel.TestConnection(logger, s.fieldEncryptor); err != nil {
		return err
	}

	tunneledDatabase.CopyDiscoveredMetadataToOriginal()

	oldName := existingDatabase.Name

	if err := existingDatabase.EncryptSensitiveFields(s.fieldEncryptor); err != nil {
		return fmt.Errorf("failed to encrypt sensitive fields: %w", err)
	}

	_, err = s.dbRepository.Save(existingDatabase)
	if err != nil {
		return err
	}

	if newBackupType := getPhysicalBackupType(existingDatabase); newBackupType != oldBackupType {
		for _, listener := range s.dbBackupTypeChangeListener {
			listener.OnBackupTypeChanged(ctx, BackupTypeChange{
				DatabaseID:    existingDatabase.ID,
				OldBackupType: oldBackupType,
				NewBackupType: newBackupType,
			})
		}
	}

	if oldName != existingDatabase.Name {
		s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
			Message:     fmt.Sprintf("Database updated and renamed from '%s' to '%s'", oldName, existingDatabase.Name),
			UserID:      &user.ID,
			WorkspaceID: existingDatabase.WorkspaceID,
		})
	} else {
		s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
			Message:     fmt.Sprintf("Database updated: %s", existingDatabase.Name),
			UserID:      &user.ID,
			WorkspaceID: existingDatabase.WorkspaceID,
		})
	}

	return nil
}

func (s *DatabaseService) DeleteDatabase(
	ctx context.Context,
	user *users_models.User,
	id uuid.UUID,
) error {
	existingDatabase, err := s.dbRepository.FindByID(id)
	if err != nil {
		return err
	}

	if existingDatabase.WorkspaceID == nil {
		return errors.New("cannot delete database without workspace")
	}

	canManage, err := s.workspaceService.CanUserManageDBs(ctx, *existingDatabase.WorkspaceID, user)
	if err != nil {
		return err
	}
	if !canManage {
		return errors.New("insufficient permissions to delete this database")
	}

	for _, listener := range s.dbRemoveListener {
		if err := listener.OnBeforeDatabaseRemove(ctx, id); err != nil {
			return err
		}
	}

	s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
		Message:     fmt.Sprintf("Database deleted: %s", existingDatabase.Name),
		UserID:      &user.ID,
		WorkspaceID: existingDatabase.WorkspaceID,
	})

	return s.dbRepository.Delete(id)
}

// DeleteForTest removes a database row through the listener chain without
// permission checks or audit logging. The path matters because listeners
// own external cleanup (backup rows, replication slots on source PG); tests
// that DELETE'd through raw SQL used to leak those resources.
//
// Idempotent: a missing row is treated as success so a test that already
// deleted the database through the public API does not crash on the fixture's
// teardown re-delete.
// DeleteForTest removes a database and its dependent rows during test teardown. Under the
// parallel test suite (go test -p=N) the cascade can deadlock against a concurrent writer in
// the same process (a backup scheduler still settling), surfacing as SQLSTATE 40P01. The
// aborted transaction is transient, so the whole delete — idempotent on a re-run — is retried.
func (s *DatabaseService) DeleteForTest(ctx context.Context, id uuid.UUID) error {
	const maxAttempts = 5

	deleteOnce := func() error {
		for _, listener := range s.dbRemoveListener {
			if err := listener.OnBeforeDatabaseRemove(ctx, id); err != nil {
				return err
			}
		}

		if err := s.dbRepository.Delete(id); err != nil && !errors.Is(err, gorm.ErrRecordNotFound) {
			return err
		}

		return nil
	}

	var err error
	for attempt := range maxAttempts {
		err = deleteOnce()
		if err == nil || !isTransientSerializationError(err) {
			return err
		}

		time.Sleep(time.Duration(attempt+1) * 20 * time.Millisecond)
	}

	return err
}

// isTransientSerializationError reports whether err is a PostgreSQL deadlock (40P01) or
// serialization failure (40001) — both transient, where retrying the aborted transaction is
// the correct response rather than failing.
func isTransientSerializationError(err error) bool {
	var pgErr *pgconn.PgError

	return errors.As(err, &pgErr) && (pgErr.Code == "40P01" || pgErr.Code == "40001")
}

func (s *DatabaseService) GetDatabase(
	ctx context.Context,
	user *users_models.User,
	id uuid.UUID,
) (*Database, error) {
	database, err := s.dbRepository.FindByID(id)
	if err != nil {
		return nil, err
	}

	if database.WorkspaceID == nil {
		return nil, errors.New("cannot access database without workspace")
	}

	canAccess, _, err := s.workspaceService.CanUserAccessWorkspace(ctx, *database.WorkspaceID, user)
	if err != nil {
		return nil, err
	}
	if !canAccess {
		return nil, errors.New("insufficient permissions to access this database")
	}

	database.HideSensitiveData()
	return database, nil
}

func (s *DatabaseService) GetDatabasesByWorkspace(
	ctx context.Context,
	user *users_models.User,
	workspaceID uuid.UUID,
) ([]*Database, error) {
	canAccess, _, err := s.workspaceService.CanUserAccessWorkspace(ctx, workspaceID, user)
	if err != nil {
		return nil, err
	}
	if !canAccess {
		return nil, errors.New("insufficient permissions to access this workspace")
	}

	databases, err := s.dbRepository.FindByWorkspaceID(workspaceID)
	if err != nil {
		return nil, err
	}

	for _, database := range databases {
		database.HideSensitiveData()
	}

	s.fillPhysicalLastBackupTimes(ctx, databases)

	return databases, nil
}

func (s *DatabaseService) IsNotifierUsing(
	ctx context.Context,
	user *users_models.User,
	notifierID uuid.UUID,
) (bool, error) {
	_, err := s.notifierService.GetNotifier(ctx, user, notifierID)
	if err != nil {
		return false, err
	}

	return s.dbRepository.IsNotifierUsing(notifierID)
}

func (s *DatabaseService) CountDatabasesByNotifier(
	ctx context.Context,
	user *users_models.User,
	notifierID uuid.UUID,
) (int, error) {
	_, err := s.notifierService.GetNotifier(ctx, user, notifierID)
	if err != nil {
		return 0, err
	}

	databaseIDs, err := s.dbRepository.GetDatabasesIDsByNotifierID(notifierID)
	if err != nil {
		return 0, err
	}

	return len(databaseIDs), nil
}

func (s *DatabaseService) TestDatabaseConnection(
	ctx context.Context,
	user *users_models.User,
	databaseID uuid.UUID,
) error {
	database, err := s.dbRepository.FindByID(databaseID)
	if err != nil {
		return err
	}

	if database.WorkspaceID == nil {
		return errors.New("cannot test connection for database without workspace")
	}

	canAccess, _, err := s.workspaceService.CanUserAccessWorkspace(ctx, *database.WorkspaceID, user)
	if err != nil {
		return err
	}
	if !canAccess {
		return errors.New("insufficient permissions to test connection for this database")
	}

	logger := s.logger.With("database_id", database.ID)

	tunneledDatabase, err := OpenTunnel(ctx, OpenTunnelSpec{
		Database:  database,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return err
	}

	defer tunneledDatabase.Close()

	if err := tunneledDatabase.GetDatabaseThroughTunnel().TestConnection(logger, s.fieldEncryptor); err != nil {
		return err
	}

	tunneledDatabase.CopyDiscoveredMetadataToOriginal()

	database.LastBackupErrorMessage = nil

	_, err = s.dbRepository.Save(database)
	if err != nil {
		return err
	}

	return nil
}

func (s *DatabaseService) TestDatabaseConnectionDirect(
	ctx context.Context,
	user *users_models.User,
	database *Database,
) error {
	usingDatabase, err := s.resolveAuthorizedConnectionTarget(ctx, user, database)
	if err != nil {
		return err
	}

	return s.testDatabaseConnection(ctx, usingDatabase)
}

func (s *DatabaseService) ProbeDatabaseLiveness(
	ctx context.Context,
	database *Database,
) error {
	if database == nil {
		return errors.New("database is required")
	}

	logger := s.logger.With("database_id", database.ID)

	tunneledDatabase, err := OpenTunnel(ctx, OpenTunnelSpec{
		Database:  database,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return err
	}

	defer tunneledDatabase.Close()

	return tunneledDatabase.GetDatabaseThroughTunnel().ProbeLiveness(ctx, logger, s.fieldEncryptor)
}

func (s *DatabaseService) GetDatabaseByID(
	id uuid.UUID,
) (*Database, error) {
	return s.dbRepository.FindByID(id)
}

func (s *DatabaseService) GetAllDatabases() ([]*Database, error) {
	return s.dbRepository.GetAllDatabases()
}

func (s *DatabaseService) SetBackupError(databaseID uuid.UUID, errorMessage string) error {
	database, err := s.dbRepository.FindByID(databaseID)
	if err != nil {
		return err
	}

	database.LastBackupErrorMessage = &errorMessage
	_, err = s.dbRepository.Save(database)
	if err != nil {
		return err
	}

	return nil
}

func (s *DatabaseService) SetLastBackupTime(databaseID uuid.UUID, backupTime time.Time) error {
	database, err := s.dbRepository.FindByID(databaseID)
	if err != nil {
		return err
	}

	database.LastBackupTime = &backupTime
	database.LastBackupErrorMessage = nil // Clear any previous error
	_, err = s.dbRepository.Save(database)
	if err != nil {
		return err
	}

	return nil
}

func (s *DatabaseService) CopyDatabase(
	ctx context.Context,
	user *users_models.User,
	databaseID uuid.UUID,
) (*Database, error) {
	existingDatabase, err := s.dbRepository.FindByID(databaseID)
	if err != nil {
		return nil, err
	}

	if existingDatabase.WorkspaceID == nil {
		return nil, errors.New("cannot copy database without workspace")
	}

	canManage, err := s.workspaceService.CanUserManageDBs(ctx, *existingDatabase.WorkspaceID, user)
	if err != nil {
		return nil, err
	}
	if !canManage {
		return nil, errors.New("insufficient permissions to copy this database")
	}

	newDatabase := existingDatabase.CopyForNewDatabase()
	newDatabase.Name = existingDatabase.Name + " (Copy)"

	if err := newDatabase.Validate(); err != nil {
		return nil, err
	}

	copiedDatabase, err := s.dbRepository.Save(newDatabase)
	if err != nil {
		return nil, err
	}

	for _, listener := range s.dbCreationListener {
		listener.OnDatabaseCreated(ctx, copiedDatabase.ID)
	}

	for _, listener := range s.dbCopyListener {
		listener.OnDatabaseCopied(ctx, databaseID, copiedDatabase.ID)
	}

	s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
		Message:     fmt.Sprintf("Database copied: %s to %s", existingDatabase.Name, copiedDatabase.Name),
		UserID:      &user.ID,
		WorkspaceID: existingDatabase.WorkspaceID,
	})

	return copiedDatabase, nil
}

func (s *DatabaseService) TransferDatabaseToWorkspace(
	ctx context.Context,
	databaseID uuid.UUID,
	targetWorkspaceID uuid.UUID,
) error {
	database, err := s.dbRepository.FindByID(databaseID)
	if err != nil {
		return err
	}

	sourceWorkspaceID := database.WorkspaceID
	database.WorkspaceID = &targetWorkspaceID

	_, err = s.dbRepository.Save(database)
	if err != nil {
		return err
	}

	sourceWorkspace, err := s.workspaceService.GetWorkspaceByID(*sourceWorkspaceID)
	if err != nil {
		return fmt.Errorf("failed to get source workspace: %w", err)
	}

	targetWorkspace, err := s.workspaceService.GetWorkspaceByID(targetWorkspaceID)
	if err != nil {
		return fmt.Errorf("failed to get target workspace: %w", err)
	}

	s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
		Message: fmt.Sprintf(
			"Database transferred: %s from workspace '%s' to workspace '%s'",
			database.Name,
			sourceWorkspace.Name,
			targetWorkspace.Name,
		),
		UserID:      nil,
		WorkspaceID: &targetWorkspaceID,
	})

	return nil
}

func (s *DatabaseService) UpdateDatabaseNotifiers(
	databaseID uuid.UUID,
	newNotifiers []notifiers.Notifier,
) error {
	database, err := s.dbRepository.FindByID(databaseID)
	if err != nil {
		return err
	}

	database.Notifiers = newNotifiers

	_, err = s.dbRepository.Save(database)
	return err
}

func (s *DatabaseService) SetHealthStatus(
	databaseID uuid.UUID,
	healthStatus *HealthStatus,
) error {
	database, err := s.dbRepository.FindByID(databaseID)
	if err != nil {
		return err
	}

	database.HealthStatus = healthStatus
	_, err = s.dbRepository.Save(database)
	if err != nil {
		return err
	}

	return nil
}

func (s *DatabaseService) OnBeforeWorkspaceDeletion(workspaceID uuid.UUID) error {
	databases, err := s.dbRepository.FindByWorkspaceID(workspaceID)
	if err != nil {
		return err
	}

	if len(databases) > 0 {
		return fmt.Errorf(
			"workspace contains %d databases that must be deleted",
			len(databases),
		)
	}

	return nil
}

func (s *DatabaseService) ShouldSuggestReadOnlyUser(
	ctx context.Context,
	user *users_models.User,
	database *Database,
) (bool, []string, error) {
	var usingDatabase *Database

	if database.ID != uuid.Nil {
		existingDatabase, err := s.dbRepository.FindByID(database.ID)
		if err != nil {
			return false, nil, err
		}

		if existingDatabase.WorkspaceID == nil {
			return false, nil, errors.New("cannot check user for database without workspace")
		}

		canAccess, _, err := s.workspaceService.CanUserAccessWorkspace(
			ctx,
			*existingDatabase.WorkspaceID,
			user,
		)
		if err != nil {
			return false, nil, err
		}
		if !canAccess {
			return false, nil, errors.New("insufficient permissions to access this database")
		}

		if database.WorkspaceID != nil && *existingDatabase.WorkspaceID != *database.WorkspaceID {
			return false, nil, errors.New("database does not belong to this workspace")
		}

		existingDatabase.Update(database)

		if err := existingDatabase.Validate(); err != nil {
			return false, nil, err
		}

		usingDatabase = existingDatabase
	} else {
		if database.WorkspaceID != nil {
			canAccess, _, err := s.workspaceService.CanUserAccessWorkspace(
				ctx,
				*database.WorkspaceID,
				user,
			)
			if err != nil {
				return false, nil, err
			}
			if !canAccess {
				return false, nil, errors.New("insufficient permissions to access this workspace")
			}
		}

		usingDatabase = database
	}

	tunnelCtx, cancel := context.WithTimeout(ctx, 15*time.Second)
	defer cancel()

	logger := s.logger.With("database_id", usingDatabase.ID)

	tunneledDatabase, err := OpenTunnel(tunnelCtx, OpenTunnelSpec{
		Database:  usingDatabase,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return false, nil, err
	}

	defer tunneledDatabase.Close()

	return tunneledDatabase.GetDatabaseThroughTunnel().ShouldSuggestReadOnlyUser(tunnelCtx, logger, s.fieldEncryptor)
}

func (s *DatabaseService) CreateReadOnlyUser(
	ctx context.Context,
	user *users_models.User,
	database *Database,
) (string, string, error) {
	var usingDatabase *Database

	if database.ID != uuid.Nil {
		existingDatabase, err := s.dbRepository.FindByID(database.ID)
		if err != nil {
			return "", "", err
		}

		if existingDatabase.WorkspaceID == nil {
			return "", "", errors.New("cannot create user for database without workspace")
		}

		canManage, err := s.workspaceService.CanUserManageDBs(ctx, *existingDatabase.WorkspaceID, user)
		if err != nil {
			return "", "", err
		}
		if !canManage {
			return "", "", errors.New("insufficient permissions to manage this database")
		}

		if database.WorkspaceID != nil && *existingDatabase.WorkspaceID != *database.WorkspaceID {
			return "", "", errors.New("database does not belong to this workspace")
		}

		existingDatabase.Update(database)

		if err := existingDatabase.Validate(); err != nil {
			return "", "", err
		}

		usingDatabase = existingDatabase
	} else {
		if database.WorkspaceID != nil {
			canManage, err := s.workspaceService.CanUserManageDBs(ctx, *database.WorkspaceID, user)
			if err != nil {
				return "", "", err
			}
			if !canManage {
				return "", "", errors.New("insufficient permissions to manage this workspace")
			}
		}

		usingDatabase = database
	}

	// MySQL and MariaDB commit CREATE USER and each GRANT implicitly, so their transaction cannot
	// roll a half-granted user back. WithoutCancel keeps the request_id while stopping a client
	// disconnect from cutting the sequence in half.
	tunnelCtx, cancel := context.WithTimeout(context.WithoutCancel(ctx), 30*time.Second)
	defer cancel()

	logger := s.logger.With("database_id", usingDatabase.ID)

	tunneledDatabase, err := OpenTunnel(tunnelCtx, OpenTunnelSpec{
		Database:  usingDatabase,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return "", "", err
	}

	defer tunneledDatabase.Close()

	databaseThroughTunnel := tunneledDatabase.GetDatabaseThroughTunnel()

	var username, password string

	switch usingDatabase.Type {
	case DatabaseTypePostgresLogical:
		username, password, err = databaseThroughTunnel.PostgresqlLogical.CreateReadOnlyUser(
			tunnelCtx, logger, s.fieldEncryptor,
		)
	case DatabaseTypeMysql:
		username, password, err = databaseThroughTunnel.Mysql.CreateReadOnlyUser(
			tunnelCtx, logger, s.fieldEncryptor,
		)
	case DatabaseTypeMariadb:
		username, password, err = databaseThroughTunnel.Mariadb.CreateReadOnlyUser(
			tunnelCtx, logger, s.fieldEncryptor,
		)
	case DatabaseTypeMongodb:
		username, password, err = databaseThroughTunnel.Mongodb.CreateReadOnlyUser(
			tunnelCtx, logger, s.fieldEncryptor,
		)
	default:
		return "", "", errors.New("read-only user creation not supported for this database type")
	}

	if err != nil {
		return "", "", err
	}

	if usingDatabase.WorkspaceID != nil {
		s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
			Message: fmt.Sprintf(
				"Read-only user created for database: %s (username: %s)",
				usingDatabase.Name,
				username,
			),
			UserID:      &user.ID,
			WorkspaceID: usingDatabase.WorkspaceID,
		})
	}

	return username, password, nil
}

func (s *DatabaseService) CreateReplicationOnlyUser(
	ctx context.Context,
	user *users_models.User,
	database *Database,
) (*postgresql_physical.ReplicationOnlyUser, error) {
	var usingDatabase *Database

	if database.ID != uuid.Nil {
		existingDatabase, err := s.dbRepository.FindByID(database.ID)
		if err != nil {
			return nil, err
		}

		if existingDatabase.WorkspaceID == nil {
			return nil, errors.New("cannot create user for database without workspace")
		}

		canManage, err := s.workspaceService.CanUserManageDBs(ctx, *existingDatabase.WorkspaceID, user)
		if err != nil {
			return nil, err
		}
		if !canManage {
			return nil, errors.New("insufficient permissions to manage this database")
		}

		if database.WorkspaceID != nil && *existingDatabase.WorkspaceID != *database.WorkspaceID {
			return nil, errors.New("database does not belong to this workspace")
		}

		existingDatabase.Update(database)

		if err := existingDatabase.Validate(); err != nil {
			return nil, err
		}

		usingDatabase = existingDatabase
	} else {
		if database.WorkspaceID != nil {
			canManage, err := s.workspaceService.CanUserManageDBs(ctx, *database.WorkspaceID, user)
			if err != nil {
				return nil, err
			}
			if !canManage {
				return nil, errors.New("insufficient permissions to manage this workspace")
			}
		}

		usingDatabase = database
	}

	if usingDatabase.Type != DatabaseTypePostgresPhysical {
		return nil, errors.New(
			"replication-only user creation is only supported for POSTGRES_PHYSICAL databases",
		)
	}

	if usingDatabase.PostgresqlPhysical == nil {
		return nil, errors.New("physical database details are missing")
	}

	tunnelCtx, cancel := context.WithTimeout(ctx, 30*time.Second)
	defer cancel()

	logger := s.logger.With("database_id", usingDatabase.ID)

	tunneledDatabase, err := OpenTunnel(tunnelCtx, OpenTunnelSpec{
		Database:  usingDatabase,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return nil, err
	}

	defer tunneledDatabase.Close()

	createdUser, err := tunneledDatabase.GetDatabaseThroughTunnel().
		PostgresqlPhysical.CreateReplicationOnlyUser(tunnelCtx, logger, s.fieldEncryptor)
	if err != nil {
		return nil, err
	}

	if usingDatabase.WorkspaceID != nil {
		s.auditLogService.WriteAuditLog(ctx, audit_logs_models.AuditEntry{
			Message: fmt.Sprintf(
				"Replication-only user created for database: %s (username: %s)",
				usingDatabase.Name,
				createdUser.Username,
			),
			UserID:      &user.ID,
			WorkspaceID: usingDatabase.WorkspaceID,
		})
	}

	return createdUser, nil
}

func (s *DatabaseService) testDatabaseConnection(ctx context.Context, database *Database) error {
	if database == nil {
		return errors.New("database is required")
	}

	logger := s.logger.With("database_id", database.ID)

	tunneledDatabase, err := OpenTunnel(ctx, OpenTunnelSpec{
		Database:  database,
		Logger:    logger,
		Encryptor: s.fieldEncryptor,
	})
	if err != nil {
		return err
	}

	defer tunneledDatabase.Close()

	return tunneledDatabase.GetDatabaseThroughTunnel().TestConnection(logger, s.fieldEncryptor)
}

// fillPhysicalLastBackupTimes populates LastBackupTime for physical databases,
// whose backups (FULL / INCREMENTAL / WAL) live outside the databases table and so
// are not denormalized onto the row the way logical backups are. A failure here is
// non-fatal: the list must still render, just without the last-backup decoration.
func (s *DatabaseService) fillPhysicalLastBackupTimes(ctx context.Context, databases []*Database) {
	var physicalDatabaseIDs []uuid.UUID

	for _, database := range databases {
		if database.Type == DatabaseTypePostgresPhysical {
			physicalDatabaseIDs = append(physicalDatabaseIDs, database.ID)
		}
	}

	if len(physicalDatabaseIDs) == 0 {
		return
	}

	lastBackupTimes, err := s.physicalBackupService.GetLastBackupTimesByDatabaseIDs(physicalDatabaseIDs)
	if err != nil {
		s.logger.ErrorContext(ctx, "failed to load physical last backup times", "error", err)

		return
	}

	for _, database := range databases {
		if lastBackupTime, hasBackup := lastBackupTimes[database.ID]; hasBackup {
			database.LastBackupTime = &lastBackupTime
		}
	}
}

func (s *DatabaseService) resolveAuthorizedConnectionTarget(
	ctx context.Context,
	user *users_models.User,
	database *Database,
) (*Database, error) {
	if database == nil {
		return nil, errors.New("database is required")
	}

	if database.ID == uuid.Nil {
		if database.WorkspaceID == nil {
			return nil, errors.New("workspaceId is required")
		}

		canManage, err := s.workspaceService.CanUserManageDBs(ctx, *database.WorkspaceID, user)
		if err != nil {
			return nil, errors.Join(ErrDatabaseConnectionAuthorizationLookup, err)
		}
		if !canManage {
			return nil, ErrInsufficientPermissionsToTestDatabaseConnection
		}

		return database, nil
	}

	existingDatabase, err := s.dbRepository.FindByID(database.ID)
	if err != nil {
		if errors.Is(err, gorm.ErrRecordNotFound) {
			return nil, ErrInsufficientPermissionsToTestDatabaseConnection
		}

		return nil, errors.Join(ErrDatabaseConnectionTargetLookup, err)
	}

	if existingDatabase.WorkspaceID == nil {
		return nil, ErrInsufficientPermissionsToTestDatabaseConnection
	}

	canManage, err := s.workspaceService.CanUserManageDBs(ctx, *existingDatabase.WorkspaceID, user)
	if err != nil {
		return nil, errors.Join(ErrDatabaseConnectionAuthorizationLookup, err)
	}
	if !canManage {
		return nil, ErrInsufficientPermissionsToTestDatabaseConnection
	}

	if database.WorkspaceID != nil && existingDatabase.WorkspaceID != nil &&
		*existingDatabase.WorkspaceID != *database.WorkspaceID {
		return nil, errors.New("database does not belong to this workspace")
	}

	existingDatabase.Update(database)

	if err := existingDatabase.Validate(); err != nil {
		return nil, err
	}

	return existingDatabase, nil
}

func getPhysicalBackupType(database *Database) postgresql_physical.BackupType {
	if database.PostgresqlPhysical == nil {
		return ""
	}

	return database.PostgresqlPhysical.BackupType
}