Support for AWS RDS Postgres SSL Connection with PEM Key

gregorymey-dev

Hi JSReport Community,

I am currently deploying a JSReport instance that needs to connect securely to an AWS RDS Postgres database using SSL encryption. I must provide the AWS RDS root certificate (PEM key) to establish a trusted connection. However, I am facing difficulties configuring the SSL connection using the PEM key in the jsreport.config.json file.
Issue Description

When configuring the Postgres-store extension for SSL connections, the SSL property only allows a boolean value. However, to connect to AWS RDS with SSL, I need to specify the path to the RDS root certificate or embed the certificate details directly.

Below is a typical connection string in the jsreport.config.json

"postgres-store": {
"host": "<RDS_ENDPOINT>",
"port": 5432,
"database": "<DB_NAME>",
"user": "<DB_USER>",
"password": "<DB_PASSWORD>",
"ssl": true
}

I'm trying to achieve the following:

"postgres-store": {
"host": "rds.amazonaws.com",
"port": 5432,
"database": "xxxxxx",
"user": "xxxxxxxxx",
"password": "xxxxxx",
"ssl": {
"require": true,
"rejectUnauthorized": true,
"ca": "-----BEGIN CERTIFICATE-----\n...\n-----END CERTIFICATE-----\n"
}

For the ca property, I need to insert the contents of your rds-combined-ca-bundle.pem file as a string, with the actual certificate contents in place of the ellipsis (...). I would include the -----BEGIN CERTIFICATE----- and -----END CERTIFICATE----- lines.

Since JSON doesn't support multiline strings, I must replace line breaks with \n in my certificate string. And then ensure the entire certificate string is on one line in the JSON file.

Connection String Requirements

For AWS RDS SSL connections, the typical requirements are:

SSL: The connection should be encrypted using SSL.
SSL Mode: The mode should be set to verify-full to verify the server's identity.
SSL Root Certificate: The path to the AWS RDS root certificate file (PEM file) must be provided, or the certificate content must be embedded in the connection string.

The connection string or configuration should look something like this in a Node.js environment:

const { Pool } = require('pg');
const fs = require('fs');

const sslConfig = {
rejectUnauthorized: true,
ca: fs.readFileSync('/path/to/rds-combined-ca-bundle.pem').toString()
};

const pool = new Pool({
user: '<DB_USER>',
host: '<RDS_ENDPOINT>',
database: '<DB_NAME>',
password: '<DB_PASSWORD>',
port: 5432,
ssl: sslConfig
});

Request for Guidance or Feature Addition

I would like guidance on incorporating the AWS RDS root certificate into my JSReport configuration. Suppose direct support for this feature is not currently available. In that case, I request a feature specifying the SSL configuration in more detail, particularly for cases like AWS RDS, where a root certificate is required for a secure connection.

I would appreciate any guidance.

admin

Hmm, I don't see a reason why your solution with \n shouldn't work actually...

Is it an option for you to wire this directly to the jsreport startup file server.js? At least to test things.

const fs = require('fs')

const jsreport = require('jsreport')({
   extensions: {
     "postgres-store": {
       "host": "<RDS_ENDPOINT>",
       "port": 5432,
       "database": "<DB_NAME>",
       "user": "<DB_USER>",
       "password": "<DB_PASSWORD>",
       "ssl": {
           rejectUnauthorized: true,
           ca: fs.readFileSync('/path/to/rds-combined-ca-bundle.pem').toString()
       }
     }
  }
})

if (process.env.JSREPORT_CLI) {
  // export jsreport instance to make it possible to use jsreport-cli
  module.exports = jsreport
} else {
  jsreport.init().then(() => {
    // running
  }).catch((e) => {
    // error during startup
    console.error(e.stack)
    process.exit(1)
  })
}

gregorymey-dev

I'm sorry for not getting back to you sooner. With a massive implementation here in South Africa, The "\n" works; however, it is one endless line and kind of not pretty. :)

Let me give this a go today: Adding it to the server.js
"ssl": {
rejectUnauthorized: true,
ca: fs.readFileSync('/path/to/rds-combined-ca-bundle.pem').toString()

I will post the outcome.

Many Thanks