Can't connect to Postgres with ssl #2128
Description
package-lock.json: "pg": "^7.18.0",
I have nodejs project and handling migrations using node-pg-migrate package which is using pg-client, works fine on my local until I need to connect to external database which requires ssl
import migrationRunner from 'node-pg-migrate';
import {Client} from 'pg';
import path from 'path';
import 'dotenv/config';
import {$LOCAL, $TESTING} from '../config';
let config = {
host: 'localhost',
user: process.env.DB_USERNAME,
password: process.env.DB_PASSWORD,
database: process.env.DB_DATABASE,
port: process.env.PG_PORT
};
if (!$LOCAL) {
config.host = process.env.PG_HOST;
if (!$TESTING) {
config.ssl = {
rejectUnauthorized: true
};
}
}
const client = new Client(config);
const runMigrations = async () => {
const options = {
direction: 'up',
dbClient: client,
dir: path.resolve(__dirname, "../database/migrations"),
migrationsTable: 'migrations'
};
try {
await client.connect();
await migrationRunner(options);
process.exit(0);
} catch(e) {
console.log(e);
process.exit(1);
}
};
runMigrations();
The error I'm getting:
error: unsupported frontend protocol 1234.5679: server supports 1.0 to 3.0
I tried to change PGGSSENCMODE=disable environment variable but nothing helped. I also tried to add sslmode: 'require' (and all other available modes) to config.ssl but it didn't help either.
My database repositories are using pg-promise npm package for Postgres connection and if I create database migrations manually and skip this step via deployment I can connect just fine using similar configuration of pg-promise
Which is defined like this:
import promise from 'bluebird';
import PgPromise, {QueryFile} from 'pg-promise';
import {$LOCAL, $TESTING} from '../config';
const config = {
host: process.env.PG_HOST,
port: process.env.PG_PORT,
database: process.env.DB_DATABASE,
user: process.env.DB_USERNAME,
password: process.env.DB_PASSWORD
};
if (!$LOCAL && !$TESTING) {
config.ssl = {
rejectUnauthorized: false
};
}
// Load and initialize pg-promise:
const pgp = new PgPromise(initOptions);
// Create the database instance:
const database = pgp(config);
export default database;
This script above makes me successfully connect to Postgres that requires ssl..