Error: self signed certificate in certificate chain

[laubonghaudoi]

Hello, I am a new user of drizzle and trying to run the command for the first time to connect to my Supabase instance:

drizzle-kit introspect:pg

But I got this error

[⣟] 0 tables fetching
[⣟] 0 columns fetching
[⣟] 0 enums fetching
[⣟] 0 indexes fetching
[⣟] 0 foreign keys fetching
Error: self signed certificate in certificate chain
    at TLSSocket.onConnectSecure (node:_tls_wrap:1530:34)
    at TLSSocket.emit (node:events:390:28)
    at TLSSocket._finishInit (node:_tls_wrap:944:8)
    at TLSWrap.ssl.onhandshakedone (node:_tls_wrap:725:12) {
  code: 'SELF_SIGNED_CERT_IN_CHAIN'
}

I am connecting drizzle to my Supabase instance, and my config file is like this

import type { Config } from "drizzle-kit";

export default {
  schema: "./src/schema.ts",
  driver: "pg",
  out: "./drizzle",
  dbCredentials: {
    host: process.env.host,
    port: 5432,
    user: "postgres",
    password: process.env.password,
    database: "postgres",
    ssl: true,
  },
} satisfies Config;

I have already enabled ssl, why does this happen and how to resolve this?

[corbinday]

I had the same problem with Heroku, but I found this on the Drizzle Discord server and it worked:

?sslmode=no-verify

link

[gpsmatty]

this worked for me too on a digital ocean managed db

[reisblucas]

Here was almost different, not work this way at all, inserting it directly in the URL link doesn't work: false, no-verify, require, true...

Packages:
pg: ^8.13.1
drizzle: ^0.36.4
Database: PostgreSQL
cli: pnpm exec drizzle-kit introspect

The solution was set directly in drizzle.config.ts, where you should add most of credentials at dbCredentials to work as expected:

• url // my url doesn't bring some of the credentials below
• host
• user
• password
• database
• ssl: "require" // don't tried other ssl configs

[volkyeth]

Managed to get it working with Supabase postgres (based on brianc/node-postgres#2558 (comment))

1. Download CA from supabase
   Project Settings > [Configuration] Database > SSL Configuration > Download certificate
   

2. save CA content as an env var (or read file on drizzle config)

#.env
DATABASE_CA="-----BEGIN CERTIFICATE-----
MIIFfjCCA2agAwIBAgIUFzt/hG+Kx2L1z9uzChgojfS2lcQwDQYJKoZIhvcNAQEL
[...]
RF9269bOFWLNGVRYXAj9XdE7
-----END CERTIFICATE-----"

3. include CA in drizzle config

#drizzle.config.ts`
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    out: './drizzle',
    schema: './src/db/schema.ts',
    dialect: 'postgresql',
    dbCredentials: {
        // using url doesn't work because it seems to override the ssl config
        // url: process.env.DATABASE_URL!,
        host: process.env.DATABASE_HOST!,
        port: parseInt(process.env.DATABASE_PORT!),
        user: process.env.DATABASE_USER!,
        password: process.env.DATABASE_PASSWORD!,
        database: process.env.DATABASE_NAME!,
        ssl: { ca: process.env.DATABASE_CA! },
    },
});

should work now