Drizzle Team•5mo ago

Error: self-signed certificate in certificate chain

Hello I'm trying to drizzle-kit push into Amazon RDS and I'm facing error: "self-signed certificate in certificate chain". Here is my client setup:

Import { drizzle } from "drizzle-orm/node-postgres";
import { Client } from "pg";
import * as schema from "./schema";
import fs from "fs";
export const client = new Client({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: parseInt(process.env.DB_PORT || "5432"),
  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync("certificates/global-bundle.pem").toString(),
  },
});
client.connect();

export const db = drizzle(client, { schema });

Here drizzle.config.ts

import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env" });

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./migrations",
  dialect: "postgresql",
  dbCredentials: {
    database: process.env.DATABASE!,
    host: process.env.DB_HOST!,
    port: parseInt(process.env.DB_PORT!, 10),
    user: process.env.DB_USER!,
    password: process.env.DB_PASSWORD!,
    ssl: true,
  },
});

7 Replies

FilipOP•5mo ago

hello? can someone help? Is this posible to pass ca certyficate to config?

sbbu•4mo ago

i'm wondering the same thing

PabloHDev 🧩•2mo ago

I have a same issue

johnhenry_6889•2mo ago

Hi ran into this issue as well a few times. Mostly worked though the issue would happen on new environment setups and when I had not run drizzle-kit generate & drizzle-kit migrate to set everthing up and it seem to work fine. Ran into the issue again when I updated my schema and had to do the migration again. Issue is related to the ssl connection and the DB refusing it. Not sure why it worked at the beginning. To resolve the issue permanently. In drizzle.config I added :

ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync("path.to.pem.file").toString(),
  },

ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync("path.to.pem.file").toString(),
  },

You can download the certificates from AWS (use the region you have the DB hosted). https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html#UsingWithRDS.SSL.CertificatesAllRegions Download and save the pem file for you region . fs.readFileSync point to location of file.

playsonmac•2mo ago

I have the same issue with Supabase. There was a post saying that this works:

const caString = fs.readFileSync("xxx.crt").toString();
// URL encode the certificate
const caStringEncoded = encodeURIComponent(caString);

// Construct the database URL with SSL parameters
const dbUrl = new URL(env.DB_URL!);
dbUrl.searchParams.append("sslmode", "require");
dbUrl.searchParams.append("sslrootcert", caStringEncoded);

export default defineConfig({
  dialect: "postgresql",
  out: "./drizzle",
  schema: "./db/schema/*",
  dbCredentials: {
    url: dbUrl.toString(),
  },
  schemaFilter: ["public"],
});

const caString = fs.readFileSync("xxx.crt").toString();
// URL encode the certificate
const caStringEncoded = encodeURIComponent(caString);

// Construct the database URL with SSL parameters
const dbUrl = new URL(env.DB_URL!);
dbUrl.searchParams.append("sslmode", "require");
dbUrl.searchParams.append("sslrootcert", caStringEncoded);

export default defineConfig({
  dialect: "postgresql",
  out: "./drizzle",
  schema: "./db/schema/*",
  dbCredentials: {
    url: dbUrl.toString(),
  },
  schemaFilter: ["public"],
});

Unfortunately, I get ENAMETOOLONG when I encode the supabase certificate into the dbUrl :/

johnhenry_6889•2mo ago

@playsonmac sorry not tried to integrate with supabase but should be similar, pretty sure they are aws rds under the hood. I would suggest just checking that you have the correct certificate for the correct region where the supabase instance is hosted. Also have you tried using non-url connection? so using host/passwoord etc.

export default defineConfig({
  dialect: "postgresql",
  // Pick up all our schema files
  schema: ["./packages/src/**/*.sql.ts"],
  out: "./packages/core/migrations/",
  dbCredentials: {
    host: Resource.host,
    port: Resource.port,
    user: Resource.username,
    password: Resource.password,
    database: Resource.database,
    ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync("path.to.pem.file").toString(),
    }
  }
});

export default defineConfig({
  dialect: "postgresql",
  // Pick up all our schema files
  schema: ["./packages/src/**/*.sql.ts"],
  out: "./packages/core/migrations/",
  dbCredentials: {
    host: Resource.host,
    port: Resource.port,
    user: Resource.username,
    password: Resource.password,
    database: Resource.database,
    ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync("path.to.pem.file").toString(),
    }
  }
});

playsonmac•2mo ago

I couldn't get it to work locally, but it turns out that it actually works in the github action so that's good

Gaming

Programming

Error: self-signed certificate in certificate chain