Skip to content

Infisical is the open-source platform for securing secrets, certificates, access, and AI Agents.

10.7 KB
Raw Blame
import fs from "fs";
import knex, { Knex } from "knex";
import oracledb from "oracledb";

import { verifyHostInputValidity } from "@app/ee/services/dynamic-secret/dynamic-secret-fns";
import { TGatewayPoolServiceFactory } from "@app/ee/services/gateway-pool/gateway-pool-service";
import { TGatewayV2ServiceFactory } from "@app/ee/services/gateway-v2/gateway-v2-service";
import {
  TSqlCredentialsRotationGeneratedCredentials,
  TSqlCredentialsRotationWithConnection
} from "@app/ee/services/secret-rotation-v2/shared/sql-credentials/sql-credentials-rotation-types";
import { getConfig } from "@app/lib/config/env";
import { BadRequestError, DatabaseError, NotFoundError } from "@app/lib/errors";
import { getMissingGatewayMessage } from "@app/lib/gateway-v2/gateway-errors";
import { withGatewayV2Proxy } from "@app/lib/gateway-v2/gateway-v2";
import { GatewayProxyProtocol } from "@app/lib/gateway-v2/types";
import { alphaNumericNanoId } from "@app/lib/nanoid";
import { getTlsServerNameOptions } from "@app/lib/tls";
import { AppConnection } from "@app/services/app-connection/app-connection-enums";
import { TAppConnectionRaw, TSqlConnection } from "@app/services/app-connection/app-connection-types";
import { TSqlConnectionConfig } from "@app/services/app-connection/shared/sql/sql-connection-types";

const EXTERNAL_REQUEST_TIMEOUT = 10 * 1000;

export const SQL_CONNECTION_CLIENT_MAP = {
  [AppConnection.Postgres]: "pg",
  [AppConnection.MsSql]: "mssql",
  [AppConnection.MySql]: "mysql2",
  [AppConnection.OracleDB]: "oracledb"
};

// Trivial "is this credential valid" query, per dialect (Oracle's SELECT needs a FROM clause).
export const getSqlConnectionVerifyQuery = (app: keyof typeof SQL_CONNECTION_CLIENT_MAP) =>
  app === AppConnection.OracleDB ? "SELECT 1 FROM DUAL" : "Select 1";

export const getConnectionConfig = ({
  app,
  credentials: { host, sslCertificate, sslEnabled, sslRejectUnauthorized }
}: {
  app: TSqlConnection["app"];
  credentials: Pick<TSqlConnection["credentials"], "host" | "sslCertificate" | "sslEnabled" | "sslRejectUnauthorized">;
}) => {
  switch (app) {
    case AppConnection.Postgres: {
      return {
        ssl: sslEnabled
          ? {
              rejectUnauthorized: sslRejectUnauthorized,
              ca: sslCertificate,
              ...getTlsServerNameOptions(host)
            }
          : false
      };
    }
    case AppConnection.MsSql: {
      return {
        options: sslEnabled
          ? {
              trustServerCertificate: !sslRejectUnauthorized,
              encrypt: true,
              cryptoCredentialsDetails: sslCertificate ? { ca: sslCertificate } : {}
            }
          : { encrypt: false }
      };
    }
    case AppConnection.MySql: {
      return {
        ssl: sslEnabled
          ? {
              rejectUnauthorized: sslRejectUnauthorized,
              ca: sslCertificate
            }
          : false
      };
    }

    case AppConnection.OracleDB: {
      return {
        ssl: sslEnabled
          ? {
              sslCA: sslCertificate,
              sslServerDNMatch: sslRejectUnauthorized
            }
          : false
      };
    }
    default:
      throw new Error(`Unhandled SQL Connection Config: ${app as AppConnection}`);
  }
};

// if TNS_ADMIN is set and the directory exists, we assume it's a wallet connection for OracleDB
const isOracleWalletConnection = (app: AppConnection): boolean => {
  const { TNS_ADMIN } = getConfig();

  return app === AppConnection.OracleDB && !!TNS_ADMIN && fs.existsSync(TNS_ADMIN);
};

const getOracleWalletKnexClient = (
  credentials: Pick<TSqlConnection["credentials"], "username" | "password" | "database">
): Knex => {
  if (oracledb.thin) {
    try {
      oracledb.initOracleClient();
    } catch (err) {
      const errorMessage = err instanceof Error ? err.message : String(err);
      throw new BadRequestError({
        message: `Failed to initialize Oracle client: ${errorMessage}. See documentation for instructions: https://infisical.com/docs/integrations/app-connections/oracledb#mutual-tls-wallet`
      });
    }
  }
  return knex({
    client: SQL_CONNECTION_CLIENT_MAP[AppConnection.OracleDB],
    connection: {
      user: credentials.username,
      password: credentials.password,
      connectString: credentials.database
    }
  });
};

export const getSqlConnectionClient = async (appConnection: Pick<TSqlConnection, "credentials" | "app">) => {
  const {
    app,
    credentials: { host: baseHost, database, port, password, username }
  } = appConnection;

  if (isOracleWalletConnection(app)) {
    return getOracleWalletKnexClient({ username, password, database });
  }

  const [host] = await verifyHostInputValidity({ host: baseHost, isDynamicSecret: false });

  const client = knex({
    client: SQL_CONNECTION_CLIENT_MAP[app],
    connection: {
      database,
      port,
      host: app === AppConnection.Postgres ? host : baseHost,
      user: username,
      password,
      connectionTimeoutMillis: EXTERNAL_REQUEST_TIMEOUT,
      ...getConnectionConfig(appConnection)
    }
  });

  return client;
};

export const executeWithPotentialGateway = async <T>(
  config: TSqlConnectionConfig,
  gatewayV2Service: Pick<TGatewayV2ServiceFactory, "getPlatformConnectionDetailsByGatewayId">,
  operation: (client: Knex) => Promise<T>,
  gatewayPoolService?: Pick<TGatewayPoolServiceFactory, "resolveEffectiveGatewayId">
): Promise<T> => {
  const { credentials, app, gatewayId: directGatewayId, gatewayPoolId } = config;

  if (gatewayPoolId && !gatewayPoolService) {
    throw new BadRequestError({
      message: "Pool-backed connections require gatewayPoolService at the call site"
    });
  }
  const gatewayId =
    gatewayPoolId && gatewayPoolService
      ? await gatewayPoolService.resolveEffectiveGatewayId({ gatewayId: directGatewayId, gatewayPoolId })
      : directGatewayId;

  if (gatewayId) {
    const [targetHost] = await verifyHostInputValidity({
      host: credentials.host,
      isGateway: true,
      isDynamicSecret: false
    });
    const platformConnectionDetails = await gatewayV2Service.getPlatformConnectionDetailsByGatewayId({
      gatewayId,
      targetHost,
      targetPort: credentials.port
    });

    // Falling through to the direct path here would silently bypass the gateway the connection is
    // pinned to and dial the target host from the platform instead.
    if (!platformConnectionDetails) {
      throw new NotFoundError({ message: getMissingGatewayMessage(gatewayId) });
    }

    const createClient = (proxyPort: number): Knex => {
      const { database, username, password } = credentials;
      if (isOracleWalletConnection(app)) {
        return getOracleWalletKnexClient({ username, password, database });
      }

      return knex({
        client: SQL_CONNECTION_CLIENT_MAP[app],
        connection: {
          database: credentials.database,
          port: proxyPort,
          host: "localhost",
          user: credentials.username,
          password: credentials.password,
          connectionTimeoutMillis: EXTERNAL_REQUEST_TIMEOUT,
          ...getConnectionConfig({ app, credentials })
        }
      });
    };

    return withGatewayV2Proxy(
      async (proxyPort) => {
        const client = createClient(proxyPort);
        try {
          return await operation(client);
        } finally {
          await client.destroy();
        }
      },
      {
        protocol: GatewayProxyProtocol.Tcp,
        ...platformConnectionDetails
      }
    );
  }

  // Non-gateway path
  const client = await getSqlConnectionClient({ app, credentials });
  try {
    return await operation(client);
  } finally {
    await client.destroy();
  }
};

export const validateSqlConnectionCredentials = async (
  config: TSqlConnectionConfig,
  gatewayV2Service: Pick<TGatewayV2ServiceFactory, "getPlatformConnectionDetailsByGatewayId">
) => {
  try {
    await executeWithPotentialGateway(config, gatewayV2Service, async (client) => {
      await client.raw(getSqlConnectionVerifyQuery(config.app));
    });
    return config.credentials;
  } catch (error) {
    throw new BadRequestError({
      message: `Unable to validate connection: ${
        (error as Error)?.message?.replaceAll(config.credentials.password, "********************") ??
        "verify credentials"
      }`
    });
  }
};

// PlanetScale requires the connection username in `<user>.<branch>` form so its proxy can route to
// the correct branch, but the underlying DB role is just `<user>`. Strip the branch suffix for
// role-level statements (ALTER USER / ALTER LOGIN). Only applies to PlanetScale hosts so that
// legitimate dotted usernames on other providers are left untouched.
const PLANETSCALE_HOST_SUFFIX = ".psdb.cloud";

export const getRoleUsernameForHost = (username: string, host: string) => {
  const isPlanetScaleHost = host.toLowerCase().endsWith(PLANETSCALE_HOST_SUFFIX);
  if (isPlanetScaleHost && username.includes(".")) {
    return username.slice(0, username.indexOf("."));
  }
  return username;
};

export const SQL_CONNECTION_ALTER_LOGIN_STATEMENT: Record<
  TSqlCredentialsRotationWithConnection["connection"]["app"],
  (credentials: TSqlCredentialsRotationGeneratedCredentials[number]) => [string, Knex.RawBinding]
> = {
  [AppConnection.Postgres]: ({ username, password }) => [`ALTER USER ?? WITH PASSWORD '${password}';`, [username]],
  [AppConnection.MsSql]: ({ username, password }) => [`ALTER LOGIN ?? WITH PASSWORD = '${password}';`, [username]],
  [AppConnection.MySql]: ({ username, password }) => [`ALTER USER ??@'%' IDENTIFIED BY '${password}';`, [username]],
  [AppConnection.OracleDB]: ({ username, password }) => [`ALTER USER ?? IDENTIFIED BY "${password}"`, [username]]
};

export const transferSqlConnectionCredentialsToPlatform = async (
  config: TSqlConnectionConfig,
  callback: (credentials: TSqlConnectionConfig["credentials"]) => Promise<TAppConnectionRaw>,
  gatewayV2Service: Pick<TGatewayV2ServiceFactory, "getPlatformConnectionDetailsByGatewayId">
) => {
  const { credentials, app } = config;

  const newPassword = alphaNumericNanoId(32);

  try {
    return await executeWithPotentialGateway(config, gatewayV2Service, (client) => {
      return client.transaction(async (tx) => {
        const filteredUsername = getRoleUsernameForHost(credentials.username, credentials.host);

        await tx.raw(
          ...SQL_CONNECTION_ALTER_LOGIN_STATEMENT[app]({ username: filteredUsername, password: newPassword })
        );
        return callback({
          ...credentials,
          password: newPassword
        });
      });
    });
  } catch (error) {
    // update/create service function will handle
    if (error instanceof DatabaseError) {
      throw error;
    }

    throw new BadRequestError({
      message:
        (error as Error)?.message?.replaceAll(newPassword, "********************") ??
        "Encountered an error transferring credentials to platform"
    });
  }
};