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

Question

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.

Rahul Sharma · Accepted Answer · 2022-09-14 14:30:20Z

3

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

answered Sep 14, 2022 at 14:30

Rahul Sharma

6,0555 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mitya Over a year ago

Interesting, thank you. I'll try that. Why would this not be a factor when connecting to the same remote DB via other means, e.g. via my Rails backend (connects to it fine) or a client like Heidi or DBeaver (ditto)? Why does SSL need this config when accessed over Node but not other means?

Rahul Sharma Over a year ago

There's a flag in node-pg rejectUnauthorized. If you set this to false, the connection will be established even if there is some problem with ssl. I just checked the connection using DBeaver. In the SSL tab, under Advanced section, there are various SSL modes. For modes verify-ca, verify-full and blank, it's not able to connect to the DB. Connection is getting established otherwise. I'm assuming some of the above mentioned flags are set by default in rails which is avoiding ssl connection failure.

Mitya Over a year ago

Interesting! Super helpful - thank you.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related