Remote PostgreSQL DB on Ubuntu (EC2) not connecting

You've just installed PostgreSQL on an EC2 instance. You open the port in the security group. You connect from your local machine and get... nothing. A timeout. Or "connection refused." Or your app on the server connects fine but everything remote refuses. It probably isn't a password problem; you're smarter than that. Not a port problem. Definitely not a Postgres bug.

It's a layering issue. Five independent systems all need to say yes before a remote connection reaches your database. Miss any one of them like you've done and you'll be stuck guessing, convinced the gods are against you when the server was never listening where you thought it was.

A few years ago I had this issue and I literally couldn't crack this for quite sometime until some godsend StackOverflow answer. The last time someone asked me why their Postgres wasn't reachable, I had an answer. This is that answer.

These are the five layers, in the order you'll hit them when debugging:

1. AWS Security Group: cloud firewall, inbound rules

2. Ubuntu UFW: OS firewall on the box itself

3. postgresql.conf: where Postgres listens

4. pg_hba.conf: who's allowed to connect

5. App connection string: what your code actually readsz

The Security Group step is AWS-specific. Everything else (UFW, the Postgres config files, the SQL grants) applies the same way on DigitalOcean, Hetzner, a homelab, or any VPS.

Ofc I know database services like RDS exist. Managed backups, automatic patching, Multi-AZ, the whole thing. For real production work I'd weigh that seriously. But sometimes you just like pain and stress or your use case requires a box you can SSH into. Or you just want to understand what's happening underneath. This is for those times.

1. Network layer (AWS Security Group)

Postgres runs on TCP port 5432 by default. Your laptop is not talking to Postgres yet. It's talking to the Security Group attached to the EC2 instance, which decides whether the packet even reaches the box.

In the AWS console, find the Security Group for your instance and add an inbound rule:

On other clouds this is "firewall rules" or "network ACLs."

2. OS layer (Ubuntu UFW)

Ubuntu ships with UFW enabled. A wide-open Security Group plus a closed UFW still means the packet dies on arrival. Two firewalls running in sequence is normal, and both of them need to agree.

sudo ufw allow 5432/tcp
sudo ufw status

If your instance uses iptables or firewalld instead, open 5432/tcp there. The tool doesn't matter; the port does.

3. Database config layer

Check your Postgres version first — the version number maps directly to the config directory:

pg_lscluster

The output looks like 16 main 5432 online. That first number is your version. Config lives at /etc/postgresql/<version>/main/. Two files are responsible for "why won't anything outside localhost connect?"

postgresql.conf

Find listen_addresses and set it to:

listen_addresses = '*'

By default this is localhost. That means Postgres is only listening on the loopback interface. psql from inside the server works fine, but nothing from outside ever reaches it. The * tells it to listen on every interface the OS exposes.

pg_hba.conf

This file controls client authentication: who is allowed to connect, from where, and how. Add a line at the bottom (rules are evaluated in order, top to bottom):

host    all    all    0.0.0.0/0    md5

Depending on your Postgres version and the password_encryption setting, you may need scram-sha-256 instead of md5. Use whatever your server expects. Once you're done experimenting, tighten 0.0.0.0/0 down to your actual IP or office CIDR. Wide-open host-based auth is fine for learning; it's not fine past that.

One-liner to update both files and restart

If you want to skip the manual edits and do everything at once:

PG_VER=$(pg_lscluster | awk '{print $1}') && \
  sudo sed -i "s/^#*listen_addresses = .*/listen_addresses = '*'/" /etc/postgresql/$PG_VER/main/postgresql.conf && \
  echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/$PG_VER/main/pg_hba.conf && \
  sudo systemctl restart postgresql

This sets listen_addresses, appends the pg_hba.conf rule, and restarts Postgres in one shot. If you need scram-sha-256 instead of md5, swap it before running.

Or restart manually after editing both files yourself:

sudo systemctl restart postgresql

These two files are not AWS-specific at all. Every manual Postgres install on Linux hits this wall. Every time.

4. Permissions layer (SQL grants)

Network works. Auth works. You're still seeing "permission denied for schema public."

This one is the most annoying to debug because the connection actually succeeds, the auth succeeds, and then Postgres denies you anyway. It started happening more with Postgres 15, which tightened the default permissions on the public schema. Networking and authentication are completely separate from what a role is allowed to do once it gets in.

Connect as the superuser:

sudo -u postgres psql -d postgres

Then run your grants, replacing your_user and your_db with what your app actually uses:

GRANT ALL ON SCHEMA public TO your_user;
GRANT ALL PRIVILEGES ON DATABASE your_db TO your_user;
ALTER SCHEMA public OWNER TO your_user;

One trap I hit personally: running these grants on the postgres default database while my app was connecting to portfolio_db. Guess what? The grants don't carry over. Run them on the database your app actually uses.

5. Application layer (.env)

Point your client at the public IP the Security Group opened, with the user and database you configured:

DATABASE_URL="postgresql://your_user:PASSWORD@EC2_PUBLIC_IP:5432/your_db?schema=public"

If your app connects to a different database than the one you granted on, swap it here.

Verify the network pipe before anything else

Before you touch a single Postgres config file, run this from your local machine:

nc -zv EC2_PUBLIC_IP 5432

If that succeeds, the network path is open and the problem is in layers 3 through 5. If it times out or refuses, the problem is in layers 1 or 2. Fix Security Group and UFW first. Do not touch SQL until nc passes.

I wasted 30 minutes once going deep into pg_hba.conf only to find the Security Group rule had the wrong IP. nc would have told me in 5 seconds.

Debugging order when something's broken: Security Group, UFW, listen_addresses, pg_hba.conf, user grants, connection string. Work down the list in order.