PostgreSQL migration issue in AWS RDS with Drizzle ORM: 'no pg_hba.conf entry for host' error

Question

I've been using drizzle for a new project over the past few weeks, and it has provided a great developer experience. Now, it's time to bring the app to production. However, I'm currently facing an issue with the migrations.

The app is deployed in AWS ECS and connects to a PostgreSQL database in AWS RDS. I have provided the necessary IAM policies in the ECS task role to access the RDS instance. The connection is established but after that when I execute the migrate function, I'm encountering an error that I didn't experience while running locally.

Error Reference:

PostgresError: no pg_hba.conf entry for host <MY_EC2_IP>, user <MY_USER>, database <MY_DATABASE>, no encryption
    at ErrorResponse (/app/node_modules/postgres/cjs/src/connection.js:771:26)
    at handle (/app/node_modules/postgres/cjs/src/connection.js:473:6)
    at Socket.data (/app/node_modules/postgres/cjs/src/connection.js:314:9)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    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)

I tried changing postgres client.

I had never experienced such issue when using other ORMs like Prisma and TypeORM. Is this something specific to how Drizzle executes SQL in Postgres?

Answer 1

I was struggling with this as well I got the same error no pg_hba.conf entry for host <MY_EC2_IP>, user <MY_USER>, database <MY_DATABASE>, no encryption.

My RDS instance is not publicly accessible the following set up helped me successfully migrate using drizzle migrate.

Instructions on how to setup ssh tunnel if needed https://www.youtube.com/watch?v=buqBSiEEdQc&ab_channel=BeABetterDev

Without checkServerIdentity() => undefined I would get the error no pg_hba.conf entry for host.

index.ts

import { drizzle } from "drizzle-orm/node-postgres";
import { env } from "./env";
import * as fs from "fs";
try {
  const filePath = "../secrets/aws-certificate-rds.pem";
  console.log("File path:", filePath); // Check the resolved path
  const db = drizzle({
    connection: {
      // connectionString: `postgresql://${env.dbUsername}:${env.dbPassword}@localhost:${env.dbPort}/${env.dbName}`,
      host: "localhost",
      port: env.dbPort,
      user: env.dbUsername,
      password: env.dbPassword,
      database: env.dbName,
      ssl: {
        // rejectUnauthorized: true,
        checkServerIdentity: () => undefined,
        ca: fs.readFileSync("aws-certificate-rds.pem").toString(),
      },
    },
  });
} catch (error) {
  console.log("error", error);
}

Download the aws pem file specific to your region: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html

ap-southeast-2-bundle.pem

Drizzle config.ts

import { env } from "./env";
import * as fs from "fs";
import { defineConfig } from "drizzle-kit";
export default defineConfig({
  out: "./drizzle",
  schema: "./src/db/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    // url: `postgresql://${env.dbUsername}:${env.dbPassword}@localhost:${env.dbPort}/${env.dbName}`,
    ssl: {
      //   rejectUnauthorized: true,
      checkServerIdentity: () => undefined,
      ca: fs.readFileSync("aws-certificate-rds.pem").toString(),
    },
    host: "localhost",
    port: env.dbPort,
    user: env.dbUsername,
    password: env.dbPassword,
    database: env.dbName,
    // ssl: false,
  },
});