CODEWITHSUNDEEP
postgresqlsslnestjstypeorm
bbousq
bbousq

Reputation: 743

cannot connect an SSL secured database to typeorm

This is my first time using NestJS and I am having trouble connecting my Postgres database which is hosted on Digitalocean to NestJS.

I searched online for solutions and tried adding "ssl": "true" or "extra": { "ssl": "true" }

Heres my ormconfig.json

{
  "type": "postgres",
  "host": "host",
  "port": "port",
  "username": "username",
  "password": "password",
  "database": "database",
  "extra": {
    "ssl": "true"
  },
  "synchronize": "true",
  "logging": "true",
  "entities": ["src/**/*.entity.ts", "dist/**/*.entity.js"]
}

I expect it to connect to the server. The error I'm getting is [TypeOrmModule] Unable to connect to the database. error: no pg_hba.conf entry for host "", user "", database "", SSL off

Upvotes: 31

Views: 50893

Answers (10)

Fauricio Valencia
Fauricio Valencia

Reputation: 386

just in case anyone is facing a issue with sslmode=require and you are using:

  • neon as db

you will need to add an extra configuration in DataSource

    new DataSource({
    ... current configuration,
    ssl: true,
    extra: {
      ssl: {
        rejectUnauthorized: false,
      },
    },
   })

reference: https://community.neon.tech/t/cannot-connect-to-neon-database/570/7

Upvotes: 0

Mnai
Mnai

Reputation: 507

For SQL Server and typeorm of type: "mssql" I had to add the extra field with trustServerCertificate as true

import { DataSourceOptions } from 'typeorm';

const config: DataSourceOptions = {
    type: "mssql",
    host: "dev",
    database: "dev",
    username: "",
    password: "",
    synchronize: false,
    logging: false,
    entities: [
        "src/entity/**/*.ts"
    ],
    migrations: [
        "src/migration/**/*.ts"
    ],
    subscribers: [
        "src/subscriber/**/*.ts"
    ],
    extra: {
        trustServerCertificate: true,
    }
};

export default config;

Upvotes: 0

ulidtko
ulidtko

Reputation: 15633

With AWS RDS, it sufficed to point TypeORM to AWS's CA bundle:

      ssl: {
        ca: readFileSync(join(__dirname, 'assets', 'RDS.us-east-1.ca-bundle.pem')).toString()
      },

on the same level where url, username, password, logging... DataSourceOptions occur.

The CA-bundle PEM file itself was downloaded from https://truststore.pki.rds.amazonaws.com/us-east-1/us-east-1-bundle.pem — and plumbed to distribute with the app's assets.

The CA-certs there are valid until something like 2061, so should be fine to commit into git.

Dig into the AWS docs for further detail.

Upvotes: 3

Rambatino
Rambatino

Reputation: 4914

Similar to the above, I spent ages fiddling around with ssl and no-authorize and ca certs, but simply passing env var: PGSSLMODE=no-verify fixed all the issues for me.

Upvotes: 3

Kazi
Kazi

Reputation: 391

Add this line to your config settings:

options: { encrypt: false }

Your configuration should look something like this:

TypeOrmModule.forRoot({
  type: 'mssql',
  host: 'your_db_server_address',
  port: 1433,
  username: 'user',
  password: 'pwd',
  database: 'your_db_name_here',
  entities: [Subscription],
  options: { encrypt: false }
  
})

Upvotes: 0

Laura
Laura

Reputation: 3383

You can set the PQSSLMODE environment variable to require - libpq will read those automatically, if not set otherwise, and establish a secure connection.

See also: https://www.postgresql.org/docs/current/libpq-envars.html

Upvotes: 0

thisismydesign
thisismydesign

Reputation: 25142

This is my NestJS TypeORM config on Heroku:

TypeOrmModule.forRoot({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  autoLoadEntities: true,
  ssl:
    process.env.NODE_ENV === 'production'
      ? { rejectUnauthorized: false }
      : false,
}),

The SSL option is mandatory as described here: https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js

Upvotes: 7

noor
noor

Reputation: 1691

This works if you are connecting to postgres database on heroku from localhost using typeorm.

ormconfig.json

{
  "name": "default",
  "type": "postgres",
  "url": "postgres://username:password@host:port/database",
  "synchronize": true,
  "logging": true,
  "entities": ["src/entity/*.*"],
  "ssl": true,
  "extra": {
    "ssl": {
      "rejectUnauthorized": false
    }
  }
}

Upvotes: 39

BKH
BKH

Reputation: 706

  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
    key: fs.readFileSync('/path/to/client-key/postgresql.key').toString(),
    cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt').toString(),
  },

via https://node-postgres.com/features/ssl

Upvotes: 2

bbousq
bbousq

Reputation: 743

If anyone has the same issue, I fixed it by adding a field for ssl and setting my ca certificate that I got from Digital Ocean. Heres what my ormconfig looks like:

module.exports = {
  name: 'default',
  type: 'postgres',
  host: 'host',
  port: port,
  username: 'username',
  password: 'password',
  database: 'database',
  synchronize: true,
  dropSchema: false,
  logging: true,
  ssl: {
    ca: process.env.SSL_CERT,
  },
  entities: ['src/**/*.entity.ts', 'dist/**/*.entity.js'],
};

Upvotes: 35

Related Questions