SSL

node-postgres supports TLS/SSL connections to your PostgreSQL server as long as the server is configured to support it. When instantiating a pool or a client you can provide an ssl property on the config object and it will be passed to the constructor for the node TLSSocket.

Self-signed cert

Here's an example of a configuration you can use to connect a client or a pool to a PostgreSQL server.

const config = {
  database: 'database-name',
  host: 'host-or-ip',
  // this object will be passed to the TLSSocket constructor
  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(),
  },
}
import { Client, Pool } from 'pg'
const client = new Client(config)
client.connect(err => {
  if (err) {
    console.error('error connecting', err.stack)
  } else {
    console.log('connected')
    client.end()
  }
})
const pool = new Pool(config)
pool
  .connect()
  .then(client => {
    console.log('connected')
    client.release()
  })
  .catch(err => console.error('error connecting', err.stack))
  .then(() => pool.end())

Usage with connectionString

If you plan to use a combination of a database connection string from the environment and SSL settings in the config object directly, then you must avoid including any of sslcert, sslkey, sslrootcert, or sslmode in the connection string. If any of these options are used then the ssl object is replaced and any additional options provided there will be lost.

Here's an example in that the CA file passed as parameter won't work due to the sslmode=require in connectionString:

const config = {
  connectionString: 'postgres://user:password@host:port/db?sslmode=require',
  // Beware! The ssl object is overwritten when parsing the connectionString
  // If it is a self signed certificate, probably will yield to `self signed certificate in certificate chain` error
  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
  },
}

But this will work:

const config = {
  connectionString: 'postgres://user:password@host:port/db',
  // The ssl object won't be overwritten because there are no config ssl on connectionString
  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
  },
}