gpt4 book ai didi

postgresql - 如何强制客户端对 postgresql 使用 SSL?

转载 作者:行者123 更新时间:2023-12-04 22:36:06 31 4
gpt4 key购买 nike

环境:

Windows 10, localhost, same machine
pg 12
node 14
openssl 1.1.1k
我已经阅读并完成了从 this 开始的 pg 文档.
postgresql.conf (在 C:\Program Files\PostgreSQL\12\data 中,我的理解是它控制 pg DB 服务器)
ssl = on # per pg doc: server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on
ssl_ca_file = 'root.crt' # per pg doc, 18.9.3: To require the client to supply a trusted certificate
ssl_crl_file = ''
pg_hba.conf (在 C:\Program Files\PostgreSQL\12\data 中,我的理解是它对客户端(如 Web API 或任何 DB 消费者)的影响,而不是 DB 服务器)
...
hostssl all all 127.0.0.1/32 cert clientcert=1
...
pSQL 显示它正在通过 SSL 进行通信:
enter image description here
但是一个简单的node项目可以连接 无 SSL :
require('dotenv').config({ path: './environment/PostgreSql.env'});

const pgp = require('pg-promise')();

const db = pgp(
{
user: process.env.PGuser,
host: process.env.PGhost,
database: process.env.PGdatabase,
password: process.env.PGpassword,
port: process.env.PGport,

ssl: false // optional, but true gets code: 'UNABLE_TO_VERIFY_LEAF_SIGNATURE'
}
);

var sql = 'select * from current.testssl()';
db.any(sql)
.then
(
good =>
{
console.log(good); // ssl false gets data
},
bad =>
{
console.log(bad);
/* ssl true gets
at TLSWrap.callbackTrampoline (internal/async_hooks.js:130:17)
{
code: 'UNABLE_TO_VERIFY_LEAF_SIGNATURE',
stack: 'Error: unable to verify the first certificate…ckTrampoline (internal/async_hooks.js:130:17)',
message: 'unable to verify the first certificate'
}
*/

}
);
最终解决方案基于@Lauranz Albe 和@jjanes,pg_hba.conf:
# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostnossl all all 0.0.0.0/0 reject # must be the 1st line!
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
hostssl all all 0.0.0.0/0 cert clientcert=verify-full

最佳答案

pg_hba.conf 的开头添加以下行:

hostnossl  all  all  0.0.0.0/0  reject
然后你必须重新加载 PostgreSQL(如果重新加载导致任何错误,请检查日志文件)。
这将拒绝所有使用未加密 TCP 连接的连接尝试。
the documentation详情。

关于postgresql - 如何强制客户端对 postgresql 使用 SSL?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/70190434/

31 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com