psql可连接远程PostgreSQL，node-postgres连接失败如何解决？

我可以通过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

但使用node-postgres连接时却失败了，代码如下：

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;
}

得到的错误输出：

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'
}

我已经尝试修改node-postgres的连接选项，显式开启或关闭ssl，但都无效。请问为什么psql能正常连接，node-postgres却失败？该如何解决让两者都正常工作？

从psql的连接日志可以看到，它自动建立了SSL连接，而node-postgres默认未启用SSL，或者你的显式配置未正确生效，导致连接时以非SSL方式请求，触发了pg_hba.conf的规则限制——服务器仅允许该主机通过SSL方式连接postgres数据库。

方法一：在连接字符串中添加SSL参数 #

直接在subscriberURI末尾追加?sslmode=require，强制启用SSL：

postgres://postgres:<password>@<host>:5432/postgres?sslmode=require

方法二：在Client配置中显式设置SSL选项 #

若使用对象配置而非连接字符串，确保SSL配置正确：

const client = new Client({
  connectionString: subscriberURI,
  ssl: { rejectUnauthorized: false } // 服务器使用自签名证书时需添加该选项；正规CA签发证书可设为true，或直接写ssl: true
});

注意：如果之前直接设置ssl: true无效，大概率是客户端未信任服务器证书，此时添加rejectUnauthorized: false可绕过验证（仅建议测试环境使用，生产环境需配置信任的证书）。

方法三：修改pg_hba.conf规则（需服务器操作权限） #

若允许非SSL连接，可在pg_hba.conf中添加以下规则，允许你的主机以非SSL方式连接：

host    postgres     postgres     52.34.107.193/32     scram-sha-256

修改后需重启PostgreSQL服务，或执行SELECT pg_reload_conf();重载配置。但从安全性考虑，更推荐保持SSL连接。

内容的提问来源于Stack Exchange，提问作者Paymahn Moghadasian