Can't connect to AWS DB from locally-hosted Node.js app-CodePudding

I'm running a locally-hosted (i.e. on my machine) Node.js app and am trying to connect it to a remote AWS-hosted Postgres DB.

My app is an Azure Function and looks like this:

const { Client } = require('pg');
const client = new Client({
    host: '*******.eu-west-1.compute.amazonaws.com',
    port: 5432,
    user: '********',
    password: '************',
    database: '************',
    ssl: true
})
client.connect();
module.exports = async (ctx, req) => {
    //...todo
}

When I run it, via func start, I get this:

If I take Azure out of the equation, and just run the script manually, via

node path/to/script/js

...I instead get:

If I set ssl to false and run as above, I get:

The credentials are definitely correct. If I use the same credentials to connect to the DB via a DB client such as Heidi SQL or DBeaver, it connects fine. It even connects from my locally-running Rails app. So something is preventing Node from connecting to it.

Possibly related: I have tried to set up an API Connection within the Azure Portal to my DB and that fails to connect also.

There is nothing I can see on the AWS DB itself to suggest why this should happen, but I'm no server admin so perhaps there's some security setting I need to change.

Thanks very much in advance.

CodePudding user response：

You've ssl: true in your client connection config. The pg client tries to establish a secure SSL/TLS connection with the remote database. But, for AWS RDS instances you need to supply additional configurations for SSL to work. That's why the error. You can go through the guide here to know more about how SSL works with RDS instances.

To summarize, you need to pass ssl field as an object instead of just true providing the pem/cer file in the ca field. Depending upon the region where your AWS RDS instance is hosted, you can download the pem/cer file from here.

Then you can pass the file as the parameter using fs. See example below:

const { Client } = require('pg');
const fs = require('fs');
const client = new Client({
    host: 'aws_rds_host',
    port: 5432,
    user: 'my_username',
    password: 'my_pass',
    database: 'my_db',
    ssl: {        
        ca: fs.readFileSync('/path/to/pem/file').toString()
    }
})
client.connect();