PostgreSQL 连接 nodejs 的 SSL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22301722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSL for PostgreSQL connection nodejs
提问by Stefan
I am trying to connect to my Heroku PostgreSQL DB and I keep getting an SSL error. Does anyone have an idea on how to enable SSL in the connection string?
我正在尝试连接到我的 Heroku PostgreSQL 数据库，但我不断收到 SSL 错误。有没有人知道如何在连接字符串中启用 SSL？
postgres://user:pass@host:port/database;
Been looking for it everywhere but it does not seem to be a very popular topic. By the way, I am running Nodejs and the node-pg module with its connection-pooled method:
到处寻找它，但它似乎不是一个非常受欢迎的话题。顺便说一下，我正在运行 Nodejs 和带有连接池方法的 node-pg 模块：
pg.connect(connString, function(err, client, done) {
// Should work.
});
Comments are much appreciated.
非常感谢评论。
回答by Jér?me Verstrynge
You can achieve this like this:
你可以这样实现：
postgres://user:pass@host:port/database?ssl=true
回答by felipekm
You also can use this code below when create a new Client from node-postgres:
从node-postgres创建新客户端时，您也可以使用以下代码：
var pg = require("pg");
var client = new pg.Client({
user: "yourUser",
password: "yourPass",
database: "yourDatabase",
port: 5432,
host: "host.com",
ssl: true
});
client.connect();
var query = client.query('CREATE TABLE people(id SERIAL PRIMARY KEY, name VARCHAR(100) not null)');
query.on('row', function(row) {
console.log(row.name);
});
query.on('end', client.end.bind(client));
Hope this helps!
希望这可以帮助！
回答by amotzg
With Google Cloud PG and pg-promiseI had a similar need.
The error I got (using ?ssl=true) was connection requires a valid client certificate.
对于 Google Cloud PG 和pg-promise，我也有类似的需求。我得到（使用?ssl=true）的错误是connection requires a valid client certificate.
SSL connection is not documented for pg-promisebut it is built on node-postgres. As explained in the link, the sslconfig parameter can be more than just true:
SSL 连接没有记录在案，pg-promise但它建立在node-postgres 上。如链接中所述，ssl配置参数可以不仅仅是true：
const pgp = require('pg-promise')();
const fs = require('fs');
const connectionConf = {
host: 'myhost.com',
port: 5432,
database: 'specific_db_name',
user: 'my_App_user',
password: 'aSecretePass',
ssl: {
rejectUnauthorized : false,
ca : fs.readFileSync("server-ca.pem").toString(),
key : fs.readFileSync("client-key.pem").toString(),
cert : fs.readFileSync("client-cert.pem").toString(),
}
};
const new_db = pgp(connectionConf);
new_db.any('SELECT * FROM interesting_table_a LIMIT 10')
.then(res => {console.log(res);})
.catch(err => {console.error(err);})
.then(() => {new_db.$pool.end()});
回答by Karim Varela
For anybody looking for a TypeORM solution, it's also {ssl: true}.
对于任何寻找 TypeORM 解决方案的人来说，它也是{ssl: true}.
Full example:
完整示例：
const connectionOptions: PostgresConnectionOptions = {
name: `default`,
type: `postgres`,
url: process.env.DATABASE_URL,
ssl: process.env.DATABASE_SSL === `true`
}回答by Abraham Anak Agung
I have the same problem.
As for today, there is a problem with pg >= 8.0.0.
So if you have this problem use pg version 7 and below.
我也有同样的问题。至于今天，有一个问题pg >= 8.0.0。因此，如果您遇到此问题，请使用 pg 版本 7 及以下版本。
yarn add pg@7
回答by Yuci
You can also use environment variables to set up the connection. Here is an example.
您还可以使用环境变量来设置连接。这是一个例子。
(Assuming you have a Postgres DB running on port 5432@localhost and the DB supports SSL connection)
（假设你有一个 Postgres DB 在端口 5432@localhost 上运行并且 DB 支持 SSL 连接）
.env
.env
PGHOST=localhost
PGPORT=5432
PGDATABASE=mydb
PGUSER=pguser1
PGPASSWORD=mypassword
PGSSLMODE=require
(Ensure you set PGSSLMODEto requireas shown above.)
（确保您设置PGSSLMODE为require如上所示。）
db.js
数据库.js
require('dotenv').config()
const { Pool } = require('pg')
// pools will use environment variables for connection information
const pool = new Pool()
// const pool = new Pool({ ssl: true }); This works too in the absence of PGSSLMODE
pool.on('error', function (err) {
console.log('idle client error', err.message, err.stack)
})
module.exports = {
pool,
query: (text, params, callback) => {
return pool.query(text, params, callback)
}
}
server.js
服务器.js
const express = require('express')
const { pool } = require('./db')
const app = express()
const port = 3000
app.get('/', async (req, res) => {
console.log('Request received...')
const result = await pool.query(`SELECT * FROM organization`);
res.send(result)
})
app.listen(port, () => console.log(`Example app listening on port ${port}!`))
Note: in case your Postgres database does not support SSL connections, you will have the following error when your application tries to make a query:
注意：如果您的 Postgres 数据库不支持 SSL 连接，当您的应用程序尝试进行查询时，您将遇到以下错误：
Error: The server does not support SSL connections
at Socket.<anonymous> (node_modules/pg/lib/connection.js:87:35)
References:
参考：
