How to connect to postgresql database inside AWS EC2 instance using Node.js and Knex

Question

I rented an EC2 instance of Ubuntu 16.xx on AWS and installed PostgreSQL on it. I created a database and table inside the PostgreSQL on EC2. Right now I am trying to connect to and get data from the database via a local Node.js project using knex.

I already enabled the inbound rule for port 5432 to IP from anywhere.

However, it returns error message as below:

Error: connect ECONNREFUSED 13.229.xxx.xxx:5432
    at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1142:16) {
  errno: -111,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '13.229.xxx.xxx',
  port: 5432
}

How am I gonna fix it? Do I need to install and implement a reversed proxy? If so, how do I set it up? I know there is RDS on AWS, but I need to use EC2 to implement it.

Here are some of my codes:

This is the knex setting, I have pg installed. The connection to a local database is successful. But when I switch the host to whether a public IP/ private IP/ ec2-13-229-xxx-xxx.ap-southeast-1.compute.amazonaws.com. They all return the above error message.

development: {
        client: 'postgresql',
        connection: {
            host: '13.229.xxx.xxx',
            database: 'project2',
            user: 'postgres',
            password: 'postgres',
            port: 5432,
        },
        pool: {
            min: 2,
            max: 10,
        },
        migrations: {
            tableName: 'knex_migrations',
        },
    },

This is the Node.js code that I used to connect to the server. A very simple one, just to test the connection.

const express = require('express');
const hbs = require('hbs');
const app = express();
const knexConfig = require('./knexfile')['development'];
const knex = require('knex')(knexConfig);

let query = knex.select('*').from('users');

query
    .then((data) => {
        console.log(data);
    })
    .catch((err) => console.log(err));

This is my firewall setting which is turned off

Also, I paused my Kaspersky.

This is my pg_hba.conf file

And I am not sure where to add the permission of my personal IP.

Can you confirm you have enabled inbound access via the Postgres DBs security group? — Chris Williams
– Chris Williams, Commented Jun 9, 2020 at 9:52
Yes, I did. There are two 5432 ports are open to anywhere, one is ::/0, another is 0.0.0.0/0 — JetCat
– JetCat, Commented Jun 9, 2020 at 9:56
Any way you can include any more of your code? Can we rule out NACLs, and your internal office firewall. Finally can you confirm the contents of the pg_hba.conf file? — Chris Williams
– Chris Williams, Commented Jun 9, 2020 at 10:02
@mokugo-devops sure! but how do I cinfirm the contents of pg_hba.conf, I command "cd", and nothing is shown — JetCat
– JetCat, Commented Jun 9, 2020 at 11:05

Chris Williams · Accepted Answer · 2020-06-10 14:18:20Z

2

This issue was related to the pg_hba.conf being restricted to localhost only.

Additionally the postgres.conf needed to have listen_addresses = '*'.

By whitelisting outside access, it was possible to access the database.

Additional support from this article.

edited Jun 10, 2020 at 14:18

answered Jun 9, 2020 at 14:03

Chris Williams

35.7k4 gold badges46 silver badges79 bronze badges

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

1 Comment

Abel Callejo Over a year ago

Yes, especially the listen_addresses = '*' part where you can whitelist certain IP addresses/hostnames

Collectives™ on Stack Overflow

How to connect to postgresql database inside AWS EC2 instance using Node.js and Knex

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related