can connect to remote database using psql but not with node-postgres

Question

I can connect to my database with psql

 ❮❮❮ psql postgres://postgres:<password>@<host>:5432/postgres
psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1), server 13.10)
WARNING: psql major version 12, server major version 13.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# create database test;
CREATE DATABASE
postgres=# drop database test;
DROP DATABASE

However, when I try to connect using node-postgres like so:

const subscriberURI = process.env.DB_MIGRATION_SUBSCRIBER_URI;

async function dbExistsInSubscriber(dbName: string): Promise<boolean> {
  console.log(subscriberURI)
  const client = new Client({ connectionString: subscriberURI });
  await client.connect();
  const res = await client.query("SELECT * FROM pg_database WHERE datname = $1", [dbName]);
  await client.end();
  return res.rows.length > 0;
}

I get the following output

postgres://postgres:<password>@<host>:5432/postgres
error: no pg_hba.conf entry for host "52.34.107.193", user "postgres", database "postgres", SSL off
    at Parser.parseErrorMessage (/home/ubuntu/code/goldsky-infra/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/home/ubuntu/code/goldsky-infra/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/home/ubuntu/code/goldsky-infra/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/home/ubuntu/code/goldsky-infra/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 155,
  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: '503',
  routine: 'ClientAuthentication'
}

I've tried changing the connection options in node-postgres to explicitly enable ssl and explicitly disable ssl but neither helped.

Why is it that psql connects without issues while node-postgres fails? How do I fix this such that both work without issue?

I'm having exactly the same problem right now! Hope someone knows how to fix 😓 — Tim Leach
– Tim Leach, Commented Apr 28, 2023 at 10:46
Hah, I added a comment below where I indicated how chatgpt helped solve the problem. Then I got a temporary suspension even though the solution was correct. Let me post it again but in my own words. — Paymahn Moghadasian
– Paymahn Moghadasian, Commented Apr 28, 2023 at 11:26

Paymahn Moghadasian · Accepted Answer · 2023-04-28 11:27:38Z

0

This was happening because of TLS. I had to add the following to my nodejs connection options:

  ssl: {
    rejectUnauthorized: false
  }

answered Apr 28, 2023 at 11:27

Paymahn Moghadasian

10.5k20 gold badges76 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bergi Over a year ago

Rather than ignoring those, better make node.js use the correct certificate chain

Paymahn Moghadasian Over a year ago

would love if you could post an answer showing how to do that!

Collectives™ on Stack Overflow

can connect to remote database using psql but not with node-postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related