[DISCORD:1416346091108175873] Issue with AWS Lamba + Supabase Postgres

[wardpeet]

This issue was created from Discord post: https://discord.com/channels/1309558646228779139/1416346091108175873

Hello,

I am encountering an issue with mastra deployed to AWS Lambda and using Supabase Postrges.

Getting the following in the logs:

id: 'MASTRA_STORAGE_PG_STORE_CREATE_TABLE_FAILED',
domain: 'STORAGE',
category: 'THIRD_PARTY',
details: { tableName: 'mastra_workflow_snapshot' },
[cause]: error: connection failure during authentication

The connection string is correct. This setup works on my local (connecting to Supabase Cloud server).

[gugu91]

Done some digging as I encountered this issue in Vercel. The problem is the following:

• Supabase seem to use their own CA to sign the certificate
• This would mean that if you are in verify-full mode, the connection will fail because you have a self signed cert in the chain (at least in my case it was)

The way I worked around this:

• download the cert from the link above
• store it somewhere accessible from where you run the agent (in my case Vercel)
• not rely on pure connection string for the connection, but specify a custom CA

Here's the code below:

import { readFileSync } from "fs";
import { join } from "path";
import postgres from "postgres";

interface SSLConfig {
  require: boolean;
  rejectUnauthorized: boolean;
  ca?: string;
}

interface DatabaseConnectionParams {
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
  ssl: SSLConfig;
}

interface DatabaseConnectionString {
  connectionString: string;
}

type DatabaseConfig = DatabaseConnectionParams | DatabaseConnectionString;

export function isDatabaseConnectionParams(
  config: DatabaseConfig
): config is DatabaseConnectionParams {
  return "host" in config;
}

export function isDatabaseConnectionString(
  config: DatabaseConfig
): config is DatabaseConnectionString {
  return "connectionString" in config;
}

function parseConnectionString(connectionString: string) {
  if (!connectionString) {
    throw new Error(
      "Database connection string is required and cannot be empty"
    );
  }

  if (typeof connectionString !== "string") {
    throw new Error(
      `Database connection string must be a string, received: ${typeof connectionString}`
    );
  }

  let url: URL;
  try {
    url = new URL(connectionString);
  } catch (error) {
    throw new Error(
      `Invalid database connection string format: ${error instanceof Error ? error.message : String(error)}`
    );
  }

  // Validate required components
  if (!url.hostname) {
    throw new Error("Database connection string missing hostname");
  }

  if (!url.username) {
    throw new Error("Database connection string missing username");
  }

  if (!url.password) {
    throw new Error("Database connection string missing password");
  }

  const database = url.pathname.slice(1); // Remove leading slash
  if (!database) {
    throw new Error("Database connection string missing database name");
  }

  const port = parseInt(url.port) || 5432;
  if (isNaN(port) || port < 1 || port > 65535) {
    throw new Error(
      `Invalid database port: ${url.port || "default"}. Port must be between 1 and 65535`
    );
  }

  if (!["postgresql:", "postgres:"].includes(url.protocol)) {
    throw new Error(
      `Unsupported database protocol: ${url.protocol}. Only 'postgresql:' and 'postgres:' are supported`
    );
  }

  const requireSSL =
    url.hostname.includes("supabase.co") ||
    url.searchParams.get("sslmode") === "require" ||
    url.searchParams.get("sslmode") === "verify-full";

  return {
    hostname: url.hostname,
    host: url.hostname,
    port,
    database,
    user: url.username,
    password: url.password,
    requireSSL,
  };
}

function getSSLConfig(
  hostname: string,
  requireSSL: boolean
): SSLConfig | undefined {
  if (!requireSSL) {
    return undefined;
  }

  if (hostname.includes("supabase.co")) {
    try {
      const publicCertPath = join(process.cwd(), "public", "supabase-ca.crt");

      const caCert = readFileSync(publicCertPath, "utf8");

      return {
        require: true,
        rejectUnauthorized: true,
        ca: caCert,
      };
    } catch (error) {
      console.warn(
        "Could not load Supabase CA certificate, falling back to system CA:",
        error
      );
      return {
        require: true,
        rejectUnauthorized: true,
      };
    }
  }

  return {
    require: true,
    rejectUnauthorized: true,
  };
}

export function createPostgresConnection(
  connectionString: string,
  options: postgres.Options<{}> = {}
) {
  const parsed = parseConnectionString(connectionString);
  const sslConfig = getSSLConfig(parsed.hostname, parsed.requireSSL);

  if (sslConfig) {
    const connectionOptions: postgres.Options<{}> = {
      ...options,
      host: parsed.host,
      port: parsed.port,
      database: parsed.database,
      user: parsed.user,
      password: parsed.password,
      ssl: sslConfig,
    };

    return postgres(connectionOptions);
  }

  // Use simple connection on local where no ssl is required
  return postgres(connectionString, options);
}

export function createDatabaseConfig(connectionString: string): DatabaseConfig {
  const parsed = parseConnectionString(connectionString);
  const sslConfig = getSSLConfig(parsed.hostname, parsed.requireSSL);

  if (sslConfig) {
    // Return separate connection parameters when SSL config is needed
    return {
      host: parsed.host,
      port: parsed.port,
      database: parsed.database,
      user: parsed.user,
      password: parsed.password,
      ssl: sslConfig,
    } satisfies DatabaseConnectionParams;
  }

  // Return simple connection string when no SSL config needed (local)
  return {
    connectionString,
  } satisfies DatabaseConnectionString;
}

export type {
  DatabaseConfig,
  DatabaseConnectionParams,
  DatabaseConnectionString,
  SSLConfig,
};

import { PostgresStore } from "@mastra/pg";
import { createDatabaseConfig, isDatabaseConnectionString } from "./connection";

export function createMastraPostgresStore(connectionString: string) {
  const dbConfig = createDatabaseConfig(connectionString);

  return new PostgresStore({
    ...(isDatabaseConnectionString(dbConfig)
      ? { connectionString: dbConfig.connectionString }
      : dbConfig),
    schemaName: "mastra",
  });
}