如何解决本地Node.js+Knex连接AWS EC2上PostgreSQL的连接拒绝问题

Let's start with the good news—you don't need a reverse proxy for this issue. The problem almost certainly lies in how PostgreSQL is configured to accept external connections, not in needing extra proxy layers. Let's walk through the fixes step by step:

Step 1: Update PostgreSQL's Listen Address (postgresql.conf) #

By default, PostgreSQL only listens for connections from localhost (the EC2 instance itself), which is why your local Node.js app can't reach it. Here's how to change that:

• SSH into your EC2 Ubuntu instance.
• Locate the postgresql.conf file (for Ubuntu 16.xx, it's usually at /etc/postgresql/<your-postgres-version>/main/postgresql.conf—replace <your-postgres-version> with the actual version like 9.5 or 9.6).
• Open the file with a text editor (e.g., sudo nano /etc/postgresql/9.5/main/postgresql.conf).
• Find the line starting with listen_addresses and change its value from localhost to '*':

  listen_addresses = '*'
  

• Save the file and restart PostgreSQL to apply changes:

  sudo systemctl restart postgresql
  

Step 2: Allow Your IP in pg_hba.conf #

PostgreSQL uses the pg_hba.conf file to control which IPs/users can connect to which databases. Here's how to add your local IP:

• Stay in the same directory as postgresql.conf and open pg_hba.conf (e.g., sudo nano /etc/postgresql/9.5/main/pg_hba.conf).
• Add one of the following lines at the end of the file (pick the safer option for your use case):
  • Recommended (secure): Allow only your public IP to connect to your project2 database as the postgres user:

    host    project2    postgres    <your-public-ip>/32    scram-sha-256
    

    (If your PostgreSQL version is older than 10, replace scram-sha-256 with md5.)
  • Temporary test (insecure for production): Allow any IP to connect (use this only to rule out IP-specific issues):

    host    project2    postgres    0.0.0.0/0    scram-sha-256
    

• Save the file and restart PostgreSQL again to apply the new access rules:

  sudo systemctl restart postgresql
  

Step 3: Verify PostgreSQL is Listening Correctly #

On your EC2 instance, run this command to confirm PostgreSQL is listening on all network interfaces (not just localhost):

netstat -tulpn | grep 5432

You should see output like 0.0.0.0:5432 or :::5432—this means it's accepting external connections.

Also, test a local connection on EC2 to ensure the database itself is working:

psql -U postgres -d project2

If you can log into the database successfully, the core service is healthy.

Step 4: Double-Check AWS Security Group #

Even though you mentioned adding a rule, it's worth verifying again:

• Go to the EC2 Console, select your instance, and navigate to its attached security group.
• Confirm there's an inbound rule for PostgreSQL (TCP port 5432) with a source of either 0.0.0.0/0 (all IPs) or your specific public IP.
• Ensure there are no outbound rules blocking traffic (the default security group allows all outbound traffic, so this is rarely an issue).

Step 5: Validate Your Knex Configuration #

Quickly double-check your knexfile.js settings:

• Make sure host is set to your EC2 instance's public IP (not the private IP, unless you're connecting from within the same AWS VPC).
• Confirm user, password, and database match exactly what you set up on EC2's PostgreSQL instance.
• You already confirmed pg is installed, but you can double-check with npm list pg to be sure.

Troubleshooting If It Still Fails #

• On your local machine, test if the port is reachable with:

  nc -zv 13.229.xxx.xxx 5432
  

  If this fails, go back and recheck your security group and PostgreSQL listen settings.
• Confirm your local firewall/antivirus is truly disabled (you mentioned pausing Kaspersky, but it's worth double-checking).

内容的提问来源于stack exchange，提问作者JetCat