Add SSL to PGUI RDS Connection

In the following NextJS code:

// src/lib/db.js

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

// Function to create a pool based on the provided config identifier function createPool(configIdentifier) { // Base configuration options let connectionOptions = { database: process.env[PGDATABASE_${configIdentifier}], user: process.env[PGUSER_${configIdentifier}], password: process.env[PGPASSWORD_${configIdentifier}], // Add if necessary host: process.env[PGHOST_${configIdentifier}], port: process.env[PGPORT_${configIdentifier}], };

// Conditionally add SSL configuration const { baseUrl } = require('../../config'); if (baseUrl && baseUrl.startsWith('https')) { connectionOptions.ssl = { rejectUnauthorized: false }; }

// Create and return the pool const pool = new Pool(connectionOptions); return { query: (text, params) => pool.query(text, params), }; }

module.exports = createPool;

When I run it without the logic that is here:

if (baseUrl && baseUrl.startsWith('https')) { connectionOptions.ssl = { rejectUnauthorized: false }; }

we get a failure connecting to RDS Query execution error: self-signed certificate in certificate chain

How do we fix the certificate issue here? ChatGPT

The error you're encountering, "self-signed certificate in certificate chain," indicates that Node.js does not trust the certificate presented by your PostgreSQL server hosted on AWS RDS. This can occur when connecting to RDS instances that use SSL for secure connections. The SSL configuration you commented out was likely bypassing this certificate verification:

javascript

if (baseUrl && baseUrl.startsWith('https')) { connectionOptions.ssl = { rejectUnauthorized: false }; }

While setting rejectUnauthorized to false circumvents the issue by ignoring the certificate validation step, it's not recommended for production environments due to security concerns. A more secure approach involves explicitly trusting the certificate chain used by RDS. Steps to Securely Fix the Certificate Issue:

Download the RDS Root Certificate: AWS provides a root certificate that you can download and use to trust the SSL connection. The current certificate bundle (as of my last update) can be found at Amazon RDS SSL Support. Look for the section that refers to downloading the root certificate.

Modify Your createPool Function to Use the Certificate:

javascript

const fs = require('fs'); const path = require('path');

function createPool(configIdentifier) { // Base configuration options let connectionOptions = { database: process.env[PGDATABASE_${configIdentifier}], user: process.env[PGUSER_${configIdentifier}], password: process.env[PGPASSWORD_${configIdentifier}], // Add if necessary host: process.env[PGHOST_${configIdentifier}], port: process.env[PGPORT_${configIdentifier}], ssl: { rejectUnauthorized: true, // Now we want to enforce certificate validation // Specify the path to the downloaded RDS root certificate ca: fs.readFileSync(path.join(__dirname, 'rds-combined-ca-bundle.pem')).toString(), }, };

// The rest of your function remains the same... }

Ensure that you adjust path.join(__dirname, 'rds-combined-ca-bundle.pem') to point to the correct location of the downloaded RDS root certificate file within your project structure. Advantages of This Approach:

Security: By using the RDS root certificate, you maintain a secure SSL connection that validates the server's identity, protecting against Man-in-the-Middle (MITM) attacks.
Compliance: This method complies with best practices for secure database connections, as you're not disabling SSL certificate verification.

Make sure to test this configuration in your development environment before deploying to production to ensure everything works as expected with your AWS RDS instance.

JCKalt / General-Work

Add SSL to PGUI RDS Connection #171