Any advice on connecting to supabase when SSL is enabled?

[Johnjackbogart]

I am using drizzle and drizzle-kit to run migrations against my supabase DB.

I have SSL enforced, and can connect via psql (both with and without the SSL CLI flags, this doesn't make sense to me), but when I try to run run migrations with drizzle-kit db:push i get:

error: no pg_hba.conf entry for host "***.***.***.**", user "postgres", database "postgres", no encryption at Parser.parseErrorMessage (/Users/johnbogart/RCOOP/com/node_modules/drizzle-kit/index.cjs:40722:98) at Parser.handlePacket (/Users/johnbogart/RCOOP/com/node_modules/drizzle-kit/index.cjs:40563:25) at Parser.parse (/Users/johnbogart/RCOOP/com/node_modules/drizzle-kit/index.cjs:40487:34) at Socket.<anonymous> (/Users/johnbogart/RCOOP/com/node_modules/drizzle-kit/index.cjs:40763:44) at Socket.emit (node:events:527:28) at addChunk (node:internal/streams/readable:315:12) at readableAddChunk (node:internal/streams/readable:289:9) at Socket.Readable.push (node:internal/streams/readable:228:10) at TCP.onStreamRead (node:internal/stream_base_commons:190:23) { length: 161, severity: 'FATAL', code: '28000', detail: undefined, hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'auth.c', line: '536', routine: 'ClientAuthentication' } error: script "db:push" exited with code 1 (SIGHUP)

I've tried with different config options, different postgres clients, no success. has anyone gotten this to work?

[r-southworth]

No advice - there are several discussions on the Discord about the same thing. I am not sure where to raise the issue to get it some attention, but if you found a solution please let me know.

[jordanparker6]

Wild that there isn't a better solution for this. Prisma seem to have no issue with the certs.

I have solved this by setting the env variable NODE_EXTRA_CA_CERTS=./certs/mycert.crt

You can download the cert from the supabase settings page.

It doesn't look super straight forward to get this working with vercel though.

[budivoogt]

I got this working with Drizzle as follows:

drizzle.config.ts:

import { defineConfig } from 'drizzle-kit'
import * as fs from 'fs'

const caString = fs.readFileSync('./.supabase/certificates/prod-ca-2021.crt').toString()
// URL encode the certificate
const caStringEncoded = encodeURIComponent(caString)

// Construct the database URL with SSL parameters
const dbUrl = new URL(process.env.DEV_DATABASE_URL as string)
dbUrl.searchParams.append('sslmode', 'require')
dbUrl.searchParams.append('sslrootcert', caStringEncoded)

export default defineConfig({
	schema: './src/lib/database/schema.ts',
	out: './.drizzle/migrations',
	dialect: 'postgresql',
	dbCredentials: {
		url: dbUrl.toString()
	}
})

src/lib/database/db.ts:

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as fs from 'fs'

const client = postgres(process.env.DEV_DATABASE_URL as string, {
	ssl: {
		rejectUnauthorized: true,
        // Apply SSL certificate
		ca: fs.readFileSync('./.supabase/certificates/prod-ca-2021.crt').toString()
	}
})
export const db = drizzle(client)