Pg-Promise problem connecting with sslrootcert specified

Question

I am having issues trying to connect to a managed database on Digital Ocean which has sslmode=require.

The connection string I have been given to use is as follows:

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

my db.js file looks like this:

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

When I initially start the process, everything seems to go fine, but when I attempt to hit the database I get:

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

if I change the pgp connection to accept the ConnectionString object instead eg. let dbpool = pgp(a); then I seem to connect with the server, but get authentication errors. Changing my connection string to point at my local db with let dbpool = pgp(a)results in me getting strange errors such as column does not exist. But pointing at local with let dbpool = pgp(connstring); seems to work fine. Due to this, I am presuming that I need to be using let dbpool = pgp(connstring);.

The rest of my relevant code (this is just a simple test project for connecting to the managed db) is as follows:

routes/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;

repositories/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;

My directory structure is:

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

Like I say I think the issue is with let dbpool = pgp(connstring);

Answer 1

Okay, was a simple fix for this. Rather than reading the file with const cert = fs.readFileSync(__dirname + '/certs/ca-certificate.crt', 'utf8'), I just needed to specify the location. Hence:

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());

(I also moved the certificate to my home directory)