pangolin/server/setup/scriptsSqlite/1.26.0.ts

import { APP_PATH } from "@server/lib/consts";
import Database from "better-sqlite3";
import path from "path";
import fs from "fs";
import * as yaml from "js-yaml";
import z from "zod";
import { fromZodError } from "zod-validation-error";

const version = "1.26.0";

export default async function migration() {
    console.log(`Running setup script ${version}...`);

    const location = path.join(APP_PATH, "db", "db.sqlite");
    const db = new Database(location);

    try {
        db.transaction(() => {
            db.prepare(
                `
                ALTER TABLE 'resources' ADD 'matchBaseDomain' integer DEFAULT false NOT NULL;
                `
            ).run();
            db.prepare(
                `ALTER TABLE 'orgs' ADD 'settingsLogRetentionDaysSsh' integer DEFAULT 0 NOT NULL;`
            ).run();
            db.prepare(
                `
                CREATE TABLE 'sshSessionLog' (
                    'id' integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                    'sessionId' text NOT NULL,
                    'orgId' text NOT NULL,
                    'siteId' integer NOT NULL,
                    'siteResourceId' integer,
                    'resourceId' integer,
                    'clientId' integer,
                    'userId' text,
                    'transport' text DEFAULT 'ssh' NOT NULL,
                    'sourceAddr' text NOT NULL,
                    'destAddr' text NOT NULL,
                    'username' text NOT NULL,
                    'authMethod' text,
                    'keyFingerprint' text,
                    'certKeyId' text,
                    'commandCount' integer DEFAULT 0 NOT NULL,
                    'startedAt' integer NOT NULL,
                    'endedAt' integer,
                    FOREIGN KEY ('orgId') REFERENCES 'orgs'('orgId') ON UPDATE no action ON DELETE cascade,
                    FOREIGN KEY ('siteId') REFERENCES 'sites'('siteId') ON UPDATE no action ON DELETE cascade,
                    FOREIGN KEY ('siteResourceId') REFERENCES 'siteResources'('siteResourceId') ON UPDATE no action ON DELETE set null,
                    FOREIGN KEY ('resourceId') REFERENCES 'resources'('resourceId') ON UPDATE no action ON DELETE set null,
                    FOREIGN KEY ('clientId') REFERENCES 'clients'('clientId') ON UPDATE no action ON DELETE set null,
                    FOREIGN KEY ('userId') REFERENCES 'user'('id') ON UPDATE no action ON DELETE set null
                );
                `
            ).run();
            db.prepare(
                `
                CREATE TABLE 'sshCommandLog' (
                    'id' integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                    'sshSessionLogId' integer NOT NULL,
                    'orgId' text NOT NULL,
                    'command' text NOT NULL,
                    'channel' text NOT NULL,
                    'inexact' integer DEFAULT false NOT NULL,
                    'timestamp' integer NOT NULL,
                    FOREIGN KEY ('sshSessionLogId') REFERENCES 'sshSessionLog'('id') ON UPDATE no action ON DELETE cascade,
                    FOREIGN KEY ('orgId') REFERENCES 'orgs'('orgId') ON UPDATE no action ON DELETE cascade
                );
                `
            ).run();
            db.prepare(
                `CREATE UNIQUE INDEX 'idx_ssh_session_log_site_session' ON 'sshSessionLog' ('siteId','sessionId');`
            ).run();
            db.prepare(
                `CREATE INDEX 'idx_ssh_session_log_org_started' ON 'sshSessionLog' ('orgId','startedAt');`
            ).run();
            db.prepare(
                `CREATE INDEX 'idx_ssh_session_log_org_site_resource_started' ON 'sshSessionLog' ('orgId','siteResourceId','startedAt');`
            ).run();
            db.prepare(
                `CREATE INDEX 'idx_ssh_session_log_org_resource_started' ON 'sshSessionLog' ('orgId','resourceId','startedAt');`
            ).run();
            db.prepare(
                `CREATE INDEX 'idx_ssh_session_log_org_user_started' ON 'sshSessionLog' ('orgId','userId','startedAt');`
            ).run();
            db.prepare(
                `CREATE INDEX 'idx_ssh_command_log_session_timestamp' ON 'sshCommandLog' ('sshSessionLogId','timestamp');`
            ).run();
            db.prepare(
                `CREATE INDEX 'idx_ssh_command_log_org_timestamp' ON 'sshCommandLog' ('orgId','timestamp');`
            ).run();

            db.prepare(
                `CREATE TABLE 'customDnsRecords' (
                    'customDnsRecordId' integer PRIMARY KEY AUTOINCREMENT NOT NULL,
                    'domainId' text NOT NULL,
                    'name' text NOT NULL,
                    'fullDomain' text NOT NULL,
                    'type' text NOT NULL,
                    'values' text NOT NULL,
                    'ttl' integer DEFAULT 300 NOT NULL,
                    'priority' integer,
                    'weight' integer,
                    'port' integer,
                    'enabled' integer DEFAULT true NOT NULL,
                    'createdAt' integer NOT NULL,
                    'updatedAt' integer NOT NULL,
                    FOREIGN KEY ('domainId') REFERENCES 'domains'('domainId') ON UPDATE no action ON DELETE cascade
                );`
            ).run();

            db.prepare(
                `CREATE INDEX 'idx_customDnsRecords_fullDomain' ON 'customDnsRecords' ('fullDomain');`
            ).run();

            db.prepare(
                `CREATE INDEX 'idx_customDnsRecords_domainId' ON 'customDnsRecords' ('domainId');`
            ).run();

            db.prepare(
                `CREATE TABLE 'resourceSessionReauth' (
                    'userSessionId' text NOT NULL,
                    'resourceId' integer NOT NULL,
                    'revokedAt' integer NOT NULL,
                    PRIMARY KEY('userSessionId', 'resourceId'),
                    FOREIGN KEY ('userSessionId') REFERENCES 'session'('id') ON UPDATE no action ON DELETE cascade,
                    FOREIGN KEY ('resourceId') REFERENCES 'resources'('resourceId') ON UPDATE no action ON DELETE cascade
                );`
            ).run();

            db.prepare(
                `CREATE TABLE 'idpGrants' (
                    'grantId' text PRIMARY KEY NOT NULL,
                    'idpId' integer NOT NULL,
                    'refreshToken' text NOT NULL,
                    'validatedAt' integer NOT NULL,
                    'attemptedAt' integer,
                    'leaseId' text,
                    'leaseExpiresAt' integer,
                    'revokedAt' integer,
                    FOREIGN KEY ('idpId') REFERENCES 'idp'('idpId') ON UPDATE no action ON DELETE cascade
                );`
            ).run();

            db.prepare(
                `CREATE TABLE 'sessionIdpGrants' (
                    'sessionId' text PRIMARY KEY NOT NULL,
                    'grantId' text NOT NULL,
                    FOREIGN KEY ('sessionId') REFERENCES 'session'('id') ON UPDATE no action ON DELETE cascade,
                    FOREIGN KEY ('grantId') REFERENCES 'idpGrants'('grantId') ON UPDATE no action ON DELETE cascade
                );`
            ).run();

            db.prepare(
                `ALTER TABLE 'deviceWebAuthCodes' ADD 'approvingSessionId' text;`
            ).run();
            db.prepare(
                `ALTER TABLE 'idp' ADD 'sessionRevalidation' integer DEFAULT false NOT NULL;`
            ).run();

            db.prepare(
                `ALTER TABLE 'resourceSessions' ADD 'requestSessionId' text;`
            ).run();
            db.prepare(`ALTER TABLE 'resourceSessions' ADD 'ip' text;`).run();
            db.prepare(
                `ALTER TABLE 'resourceSessions' ADD 'userAgent' text;`
            ).run();
            db.prepare(
                `ALTER TABLE 'resourceSessions' ADD 'countryCode' text;`
            ).run();
            db.prepare(
                `ALTER TABLE 'resourceSessions' ADD 'asn' integer;`
            ).run();
            db.prepare(`ALTER TABLE 'session' ADD 'ip' text;`).run();
            db.prepare(`ALTER TABLE 'session' ADD 'userAgent' text;`).run();
            db.prepare(`ALTER TABLE 'session' ADD 'countryCode' text;`).run();
            db.prepare(`ALTER TABLE 'session' ADD 'asn' integer;`).run();

            db.prepare(
                `ALTER TABLE 'idpOrg' ADD 'autoProvision' integer DEFAULT false NOT NULL;`
            ).run();
            db.prepare(
                `
            UPDATE 'idpOrg'
            SET "autoProvision" = COALESCE((
                SELECT "autoProvision" FROM 'idp'
                WHERE "idp"."idpId" = "idpOrg"."idpId"
            ), false);
                `
            ).run();
        })();

        console.log("Migrated database");
    } catch (e) {
        console.log("Failed to migrate db:", e);
        throw e;
    }

    try {
        const traefikPath = path.join(
            APP_PATH,
            "traefik",
            "traefik_config.yml"
        );

        const schema = z.object({
            experimental: z.object({
                plugins: z.object({
                    badger: z.object({
                        moduleName: z.string(),
                        version: z.string()
                    })
                })
            })
        });

        const traefikFileContents = fs.readFileSync(traefikPath, "utf8");
        const traefikConfig = yaml.load(traefikFileContents) as any;

        const parsedConfig = schema.safeParse(traefikConfig);

        if (!parsedConfig.success) {
            throw new Error(fromZodError(parsedConfig.error).toString());
        }

        traefikConfig.experimental.plugins.badger.version = "v1.8.0";

        const updatedTraefikYaml = yaml.dump(traefikConfig);

        fs.writeFileSync(traefikPath, updatedTraefikYaml, "utf8");

        console.log(
            "Updated the version of Badger in your Traefik configuration to v1.8.0"
        );
    } catch (e) {
        console.log(
            "We were unable to update the version of Badger in your Traefik configuration. Please update it manually. Check the release notes for this version for more information."
        );
        console.error(e);
    }

    console.log(`${version} migration complete`);
}