node.js - 与指定的 sslrootcert 连接的 Pg-Promise 问题

Question

我在尝试连接到具有 sslmode=require 的 Digital Ocean 上的托管数据库时遇到问题。
我使用的连接字符串如下:

postgresql://username:password@server:port/mydb?sslmode=require

我的 db.js 文件如下所示:

"use strict";
const fs = require('fs')
const pgPromise = require("pg-promise");
const {ConnectionString} = require('connection-string');
const path = require('path');

var options = {
// Initialization Options
};
let pgp = pgPromise(options);

const dotenv = require('dotenv');
dotenv.config();

const a = new ConnectionString('postgresql://username:password@server:port/mydb?sslmode=require')

var cert= fs.readFileSync(__dirname + '/certs/ca-certificate.crt', 'utf8')
a.setDefaults({   
    params: {
        sslrootcert : cert
    }
});

var connstring = a.toString();
let dbpool = pgp(connstring);
module.exports = { dbpool };

当我最初启动该过程时，一切似乎都很好，但是当我尝试访问数据库时，我得到:

Error: ENOENT: no such file or directory, open 'C:\Users\Me\Documents\GitHub\tester\-----BEGIN CERTIFICATE----- certificate info -----END CERTIFICATE-----

如果我将 pgp 连接更改为接受 ConnectionString 对象
例如。 let dbpool = pgp(a);然后我似乎与服务器连接，但出现身份验证错误。使用 let dbpool = pgp(a) 更改我的连接字符串以指向我的本地数据库导致我收到奇怪的错误，例如列不存在。但是用 let dbpool = pgp(connstring); 指向本地似乎工作正常。因此，我假设我需要使用 let dbpool = pgp(connstring); .
我的其余相关代码(这只是一个用于连接托管数据库的简单测试项目)如下:
路线/index.js

"use strict";
var __awaiter = (this && this.__awaiter) || function (thisArg, _arguments, P, generator) {
  function adopt(value) { return value instanceof P ? value : new P(function (resolve) { resolve(value); }); }
  return new (P || (P = Promise))(function (resolve, reject) {
      function fulfilled(value) { try { step(generator.next(value)); } catch (e) { reject(e); } }
      function rejected(value) { try { step(generator["throw"](value)); } catch (e) { reject(e); } }
      function step(result) { result.done ? resolve(result.value) : adopt(result.value).then(fulfilled, rejected); }
      step((generator = generator.apply(thisArg, _arguments || [])).next());
  });
};
const userrepository_1 = require("../repositories/userrepository");
var express = require('express');
var router = express.Router();

/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
   });

/* HIT DATABASE. */
router.get('/testdb',  (req, res) => __awaiter(void 0, void 0, void 0, function* () {

  let userRepos = new userrepository_1.UserRepository();
    let userid = yield userRepos.getuserbyusername("myusername");
    
    if (userid == null) {
        return res.status(404).send({ auth: false, message: 'No user found' });
    }
  res.render('dbtest', { userid: userid });
}))

module.exports = router;

存储库/userrepository.js

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
const dbProvider = require("../db");

class UserRepository {
    constructor() {
        this.db = dbProvider.dbpool;
    }

    getuserbyusername(username) {
        return new Promise((resolve, reject) => { resolve(this.db.oneOrNone('SELECT * FROM users 
where isdeleted=false AND username=$1', [username])); })
        .then((user) => {
            if (user != null) {
            
               let uid = user.userid;
            
                return uid;
            }
            else {
                return null;
            }
        });
    }
}
exports.UserRepository = UserRepository;

我的目录结构是:

/bin
    www
/certs
    ca-certificate.crt
/node_modules
/public
/repositories
    userrepository.js
/routes
    index.js
/views
app.js
db.js

就像我说的，我认为问题在于 let dbpool = pgp(connstring);

Answer 1

好的，这是一个简单的解决方法。而不是用 const cert = fs.readFileSync(__dirname + '/certs/ca-certificate.crt', 'utf8') 读取文件，我只需要指定位置。因此:

const path = require('path');

const cs = new ConnectionString('postgresql://username:password@server:port/mydb?sslmode=require');

const sslrootcert = path.join(__dirname, 'ca-certificate.crt');

cs.setDefaults({   
    params: { sslrootcert }
});

const db = pgp(cs.toString());

(我还将证书移到了我的主目录)