Skip to content

An open-source, self-hostable PaaS alternative to Vercel, Heroku & Netlify that lets you easily deploy static sites, databases, full-stack applications and 360+ one-click services on your own servers.

17.1 KB
Raw Blame
<?php

namespace App\Actions\Database;

use App\Helpers\SshMultiplexingHelper;
use App\Helpers\SshRetryHandler;
use App\Models\S3Storage;
use App\Models\Server;
use App\Models\ServiceDatabase;
use App\Models\StandaloneSqlite;
use App\Models\SwarmDocker;
use App\Support\DatabaseBackupFileValidator;
use App\Support\DatabaseImport\DatabaseImportCleanup;
use App\Support\DatabaseImport\DatabaseImportCommandBuilder;
use App\Support\DatabaseImport\DatabaseImportException;
use App\Support\DatabaseImport\DatabaseImportSource;
use App\Support\DatabaseOperationReservation;
use App\Support\ResourceStartActivity;
use App\Support\ValidationPatterns;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use Lorisleiva\Actions\Concerns\AsAction;
use Spatie\Activitylog\Models\Activity;
use Throwable;

class StartDatabaseImport
{
    use AsAction;

    public const MAX_BYTES = 10 * 1024 * 1024 * 1024;

    public const LOCK_SECONDS = 1800;

    /**
     * Added to the SSH command timeout, so the remote restore ends (or times out) before the job.
     * The job stays this far below the Horizon worker timeout too.
     */
    public const JOB_TIMEOUT_MARGIN_SECONDS = 300;

    public function __construct(private readonly DatabaseImportCommandBuilder $commands) {}

    /**
     * The CoolifyTask timeout of an import. A restore can run until the SSH command timeout, so the
     * job must not time out before it. It stays below the Horizon worker timeout, which is below the
     * queue retry_after, so the worker does not kill or run the job again while it runs.
     */
    public static function jobTimeoutSeconds(): int
    {
        $timeout = (int) config('constants.ssh.command_timeout') + self::JOB_TIMEOUT_MARGIN_SECONDS;
        $workerTimeout = (int) config('horizon.worker_timeout');

        return $workerTimeout > 0 ? min($timeout, $workerTimeout - self::JOB_TIMEOUT_MARGIN_SECONDS) : $timeout;
    }

    public static function lockKey(string $resourceUuid): string
    {
        return "database-import:{$resourceUuid}";
    }

    public function handle(Model $resource, DatabaseImportSource $source, int $teamId): Activity
    {
        if (! $this->commands->supports($resource)) {
            throw new DatabaseImportException('Database imports are not supported for this database type.');
        }
        if (! str($resource->status)->startsWith('running')) {
            throw new DatabaseImportException('The database must be running before an import can start.');
        }
        $sqliteDatabase = $this->sqliteRestoreTarget($resource, $source);

        [$server, $container, $network] = $this->target($resource);
        $destination = $resource instanceof ServiceDatabase ? $resource->service?->destination : $resource->destination;
        if ($destination instanceof SwarmDocker) {
            throw new DatabaseImportException('Database imports are not supported for Swarm servers yet.', 501);
        }
        if (! $server || ! ValidationPatterns::isValidContainerName($container)) {
            throw new DatabaseImportException('The database server or container is invalid.', 400);
        }

        $lock = Cache::lock(self::lockKey($resource->uuid), self::LOCK_SECONDS);

        if (! $lock->get()) {
            throw new DatabaseImportException('A database import is already running.', 409);
        }

        // A start or restart can be queued without an activity yet: its reservation blocks the import.
        // The import holds the reservation until its own activity exists.
        $reservation = null;

        try {
            $reservation = DatabaseOperationReservation::acquire($resource->uuid);
            if ($reservation === null) {
                throw new DatabaseImportException(ResourceStartActivity::DATABASE_OPERATION_IN_PROGRESS_MESSAGE, 409);
            }

            return $this->startImport($resource, $source, $teamId, $server, $container, $network, $sqliteDatabase);
        } finally {
            DatabaseOperationReservation::release($resource->uuid, $reservation);
            $lock->release();
        }
    }

    private function startImport(Model $resource, DatabaseImportSource $source, int $teamId, Server $server, string $container, string $network, ?string $sqliteDatabase): Activity
    {
        $active = ResourceStartActivity::active($resource->uuid, ResourceStartActivity::DATABASE_IMPORT_OPERATION, $teamId);
        if (ResourceStartActivity::failStale($active)->isNotEmpty()) {
            throw new DatabaseImportException('A database import is already running.', 409);
        }
        $activeStarts = ResourceStartActivity::active($resource->uuid, ResourceStartActivity::DATABASE_START_OPERATION, $teamId);
        if (ResourceStartActivity::failStale($activeStarts)->isNotEmpty()) {
            throw new DatabaseImportException(ResourceStartActivity::DATABASE_OPERATION_IN_PROGRESS_MESSAGE, 409);
        }

        $operation = (string) Str::uuid();
        $containerPath = "/tmp/restore_{$operation}";
        $scriptPath = "/tmp/restore_{$operation}.sh";
        $commandList = [];
        $cleanup = ['container' => $container, 'containerTmpPath' => $containerPath, 'scriptPath' => $scriptPath, 'serverId' => $server->id, 'operationUuid' => $operation];

        if ($source->type === 'upload') {
            $staged = $source->uploadId
                ? "upload/imports/{$teamId}/{$resource->uuid}/{$source->uploadId}/restore"
                : "upload/{$resource->uuid}/restore";
            if (! Storage::exists($staged)) {
                throw new DatabaseImportException('The completed upload was not found.');
            }
            $local = Storage::path($staged);
            if ($this->commands->databaseType($resource) === 'postgresql' && DatabaseBackupFileValidator::fileContainsPostgresqlProgramExecution($local)) {
                Storage::delete($staged);
                throw new DatabaseImportException('The uploaded backup contains disallowed PostgreSQL restore directives.');
            }
            $serverPath = "/tmp/database-import-{$operation}";
            $magic = bin2hex((string) file_get_contents($local, length: 6));
            instant_scp($local, $serverPath, $server);
            $source->uploadId ? Storage::deleteDirectory(dirname($staged)) : Storage::delete($staged);
            $commandList = [...$commandList, ...$this->copyIntoContainer($serverPath, $magic, $server, $operation, $container, $containerPath, $cleanup)];
            $commandList[] = 'rm -f '.escapeshellarg($serverPath);
            $cleanup['serverTmpPath'] = $serverPath;
        } elseif ($source->type === 'server') {
            $this->assertServerPath($source->path);
            $size = (int) trim((string) instant_remote_process(['stat -c %s -- '.escapeshellarg($source->path)], $server));
            if ($size < 1 || $size > self::MAX_BYTES) {
                throw new DatabaseImportException('The backup file is empty or exceeds the 10 GiB limit.');
            }
            $magic = (string) instant_remote_process(['head -c 6 -- '.escapeshellarg($source->path).' | od -An -tx1'], $server);
            $commandList = [...$commandList, ...$this->copyIntoContainer($source->path, $magic, $server, $operation, $container, $containerPath, $cleanup)];
        } else {
            $storage = S3Storage::ownedByCurrentTeamAPI($teamId)
                ->where(fn ($query) => $query->whereUuid($source->s3StorageUuid)->orWhere('id', ctype_digit((string) $source->s3StorageUuid) ? (int) $source->s3StorageUuid : -1))
                ->where('is_usable', true)->first();
            if (! $storage || ! ValidationPatterns::isValidS3BucketName($storage->bucket)) {
                throw new DatabaseImportException('S3 storage was not found or has an invalid bucket.');
            }
            $key = ltrim((string) $source->path, '/');
            $this->assertS3Path($key);
            $disk = $storage->filesystem();
            if (! $disk->exists($key) || $disk->size($key) > self::MAX_BYTES) {
                throw new DatabaseImportException('The S3 backup was not found or exceeds the 10 GiB limit.');
            }
            $helper = "s3-restore-{$operation}";
            $this->startS3Helper($storage, $server, $helper, $network);
            $sourceArg = escapeshellarg("s3temp/{$storage->bucket}/{$key}");
            $commandList = [
                'docker exec '.escapeshellarg($helper).' mc cp '.$sourceArg.' /tmp/restore',
                ...$this->streamFromHelper($helper, $container, $containerPath),
                'docker rm -f '.escapeshellarg($helper).' 2>/dev/null || true',
            ];
            $cleanup['containerName'] = $helper;
        }

        if ($safety = $this->commands->buildPostgresSafetyCommand($resource, $container, $containerPath)) {
            $commandList[] = $safety;
        }
        $restore = base64_encode($this->commands->buildRestoreCommand($resource, $containerPath, $source->dumpAll, $source->replaceExisting, $source->keepOwners, $sqliteDatabase, $source->restoreMysqlUsers));
        $commandList[] = 'echo '.escapeshellarg($restore).' | base64 -d > '.escapeshellarg($scriptPath);
        $commandList[] = 'chmod +x '.escapeshellarg($scriptPath);
        $commandList[] = 'docker cp '.escapeshellarg($scriptPath).' '.escapeshellarg("{$container}:{$scriptPath}");
        $commandList[] = 'rm -f '.escapeshellarg($scriptPath);
        // The restore reports its own 124 and 255 as 1: Coolify reads those exit codes of the SSH
        // command as a local timeout or a lost connection and then stops the restore.
        $commandList[] = 'docker exec '.escapeshellarg($container).' sh -c '.escapeshellarg('"$0"; status=$?; [ "$status" -ne 124 ] && [ "$status" -ne 255 ] || status=1; exit "$status"').' '.escapeshellarg($scriptPath);

        // The operation properties are set when the activity is created: the CoolifyTask job can
        // load and save the activity before a later update, which would drop them again.
        // The cleanup data (names and paths only, no credentials) lets Coolify stop and clean up
        // an import that it fails after a restart or because it is stale.
        return remote_process($commandList, $server, type_uuid: $resource->uuid, model: $resource, callEventOnFinish: 'DatabaseImportFinished', callEventData: $cleanup, timeout: self::jobTimeoutSeconds(), properties: [
            'operation' => ResourceStartActivity::DATABASE_IMPORT_OPERATION,
            'resource_kind' => $resource instanceof ServiceDatabase ? 'service_database' : 'standalone_database',
            'operation_uuid' => $operation,
            DatabaseImportCleanup::PROPERTY => DatabaseImportCleanup::storable($cleanup),
        ]);
    }

    private function target(Model $resource): array
    {
        if ($resource instanceof ServiceDatabase) {
            return [$resource->service?->server, $resource->name.'-'.$resource->service?->uuid, $resource->service?->destination?->network ?? 'coolify'];
        }

        return [$resource->destination?->server, $resource->uuid, $resource->destination?->network ?? 'coolify'];
    }

    /**
     * The SQLite file a backup is restored into. It must be one of the database's own files; without
     * a choice it is the file named in the backup path, else the first file.
     */
    private function sqliteRestoreTarget(Model $resource, DatabaseImportSource $source): ?string
    {
        if (! $resource instanceof StandaloneSqlite) {
            return null;
        }

        $file = $source->sqliteDatabase ?? $resource->defaultRestoreFile($source->type === 'upload' ? null : $source->path);
        if ($file === null || ! in_array($file, $resource->databaseFiles(), true)) {
            throw new DatabaseImportException('The SQLite database file is not one of the files of this database.');
        }

        return $file;
    }

    /**
     * Server backups can have any file name; the restore script checks the format.
     */
    private function assertServerPath(?string $path): void
    {
        if (! $path || ! str_starts_with($path, '/') || preg_match('/\.\.|[$()`|;&><\r\n\0\'"\\\\]/', $path)) {
            throw new DatabaseImportException('The server path is invalid.');
        }
    }

    private function assertS3Path(string $path): void
    {
        if ($path === '' || preg_match('/\.\.|[$()`|;&><\r\n\0\'"\\\\]/', $path) || ! DatabaseBackupFileValidator::hasAllowedExtension(basename($path))) {
            throw new DatabaseImportException('The S3 path is invalid.');
        }
    }

    /**
     * Copies a backup into the database container. bz2, xz and zip backups go through
     * the Coolify helper image, because database images do not ship those tools.
     *
     * @param  array<string, mixed>  $cleanup
     * @return list<string>
     */
    private function copyIntoContainer(string $path, string $magic, Server $server, string $operation, string $container, string $containerPath, array &$cleanup): array
    {
        $magic = strtolower((string) preg_replace('/[^0-9a-f]/i', '', $magic));
        if (! preg_match('/^(425a68|fd377a585a00|504b0304)/', $magic)) {
            return ['docker cp '.escapeshellarg($path).' '.escapeshellarg("{$container}:{$containerPath}")];
        }

        $helper = "backup-decompress-{$operation}";
        $image = escapeshellarg(coolifyHelperImage().':'.getHelperVersion());

        try {
            instant_remote_process([
                'docker rm -f '.escapeshellarg($helper).' 2>/dev/null || true',
                'docker run -d --network none --name '.escapeshellarg($helper).' '.$image.' sleep 86400',
            ], $server);
        } catch (Throwable) {
            instant_remote_process(['docker rm -f '.escapeshellarg($helper).' 2>/dev/null || true'], $server, throwError: false);

            throw new DatabaseImportException('Unable to start the backup decompression helper.');
        }
        $cleanup['containerName'] = $helper;

        return [
            'docker cp '.escapeshellarg($path).' '.escapeshellarg("{$helper}:/tmp/restore"),
            ...$this->streamFromHelper($helper, $container, $containerPath),
            'docker rm -f '.escapeshellarg($helper).' 2>/dev/null || true',
        ];
    }

    /**
     * Prepares /tmp/restore in a helper container, then streams it into the database
     * container. Preparing first keeps decompression failures from being lost in a pipe.
     *
     * @return list<string>
     */
    private function streamFromHelper(string $helper, string $container, string $containerPath): array
    {
        $target = escapeshellarg($containerPath);
        $writer = "cat > {$target} && [ -s {$target} ] || { echo 'The backup is empty or could not be read.' >&2; exit 1; }";

        return [
            'docker exec '.escapeshellarg($helper).' sh -c '.escapeshellarg($this->commands->buildNormalizeScript('/tmp/restore', '/tmp/restore.prepared')),
            'docker exec '.escapeshellarg($helper).' cat /tmp/restore.prepared | docker exec -i '.escapeshellarg($container).' sh -c '.escapeshellarg($writer),
        ];
    }

    /**
     * Starts the S3 restore helper and sets its `s3temp` mc alias. The S3 keys go to mc through the
     * stdin of a separate SSH process, so they are not in the arguments of any process on Coolify or
     * on the server. The helper is removed when either step fails.
     */
    private function startS3Helper(S3Storage $storage, Server $server, string $helper, string $network): void
    {
        $image = escapeshellarg(coolifyHelperImage().':'.getHelperVersion());

        try {
            instant_remote_process([
                'docker rm -f '.escapeshellarg($helper).' 2>/dev/null || true',
                'docker run -d --network '.escapeshellarg($network)
                    .' --name '.escapeshellarg($helper)
                    .' '.$storage->minioClientRegionOption()
                    .' '.$image.' sleep 86400',
            ], $server);
            $this->setMinioClientAlias($storage, $server, $helper);
        } catch (Throwable) {
            instant_remote_process(['docker rm -f '.escapeshellarg($helper).' 2>/dev/null || true'], $server, throwError: false);

            throw new DatabaseImportException('Unable to start the S3 restore helper.');
        }
    }

    private function setMinioClientAlias(S3Storage $storage, Server $server, string $helper): void
    {
        $remoteCommand = ($server->isNonRoot() ? 'sudo ' : '').'docker exec -i '.escapeshellarg($helper)
            .' mc alias set'.$storage->minioClientAliasOptions().' s3temp '.escapeshellarg((string) $storage->endpoint).' >/dev/null';
        $timeout = (int) config('constants.ssh.command_timeout');

        SshRetryHandler::retry(
            function () use ($server, $remoteCommand, $storage, $timeout) {
                $sshCommand = SshMultiplexingHelper::generateSshStdinCommand($server, $remoteCommand, disableMultiplexing: true, commandTimeout: $timeout);
                $process = runSshProcess($sshCommand, $timeout, $storage->key."\n".$storage->secret."\n");

                if ($process->exitCode() !== 0) {
                    excludeCertainErrors($process->errorOutput(), $process->exitCode());
                }
            },
            ['server' => $server->ip, 'function' => 'setMinioClientAlias'],
        );
    }
}