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.

Answer 1

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.