The Problem
I recently ran into a brick wall while connecting a Node.js backend to a fresh Postgres 15 instance. Local connections worked perfectly. However, the second I pointed my app server (IP 1.2.3.4) at the database, it slammed the door shut.
PostgreSQL is locked down by design. It refuses any connection from the outside network unless you explicitly whitelist the IP address, the user, and the target database. If these rules aren't set, the server rejects the handshake and throws a FATAL error.
The Error Message
FATAL: no pg_hba.conf entry for host "1.2.3.4", user "admin", database "prod_db", SSL off
Why This Happens
This error usually boils down to two missing configuration steps:
- The Door is Locked: By default, Postgres only listens on
localhost(127.0.0.1). It won't even notice traffic hitting the server's public IP or private network interface. - The Security Guard is Strict: The
pg_hba.conffile acts as a database-level firewall. If your client's IP doesn't match an entry in this file, Postgres drops the connection immediately.
Step 1: Open the Listener
First, ensure the database is actually looking for network traffic. You need to tweak the main configuration file.
Locate your postgresql.conf file. On Ubuntu, check /etc/postgresql/15/main/postgresql.conf. If you are using Docker, this file usually lives inside your mapped data volume.
sudo nano /etc/postgresql/15/main/postgresql.conf
Look for the listen_addresses setting. It is likely commented out or set to 'localhost'. Change it to listen on all interfaces:
# Allow the server to listen on all network interfaces
listen_addresses = '*'
Note: If you want to be extra secure, you can replace '' with your server's specific private IP address.*
Step 2: Authorize the Client IP
Next, you must tell Postgres that the IP 1.2.3.4 has permission to log in as user admin to the prod_db database.
Open the HBA (Host-Based Authentication) file in the same directory:
sudo nano /etc/postgresql/15/main/pg_hba.conf
Add a new line at the very bottom. The syntax follows this pattern: TYPE DATABASE USER ADDRESS METHOD.
# Allow a specific application server IP
host prod_db admin 1.2.3.4/32 scram-sha-256
# OR allow an entire private subnet (e.g., 10.0.0.0 to 10.0.0.255)
host prod_db admin 10.0.0.0/24 scram-sha-256
Which authentication method should you use?
scram-sha-256: The modern standard for Postgres 13 and newer. Use this.md5: Common in older setups (Postgres 12 and below).trust: Avoid this in production. It lets anyone in without a password.
Step 3: Reload the Configuration
Configuration changes don't take effect until you notify the service. You don't always need a full restart, which is great for avoiding downtime.
Run this from your terminal to restart the service:
sudo systemctl restart postgresql
Alternatively, if you are already inside a psql session, you can reload the config without kicking off current users:
SELECT pg_reload_conf();
Testing the Connection
Switch back to your remote machine (1.2.3.4) and try to connect. Use the -h flag to specify the database server's IP:
psql -h 10.0.0.50 -U admin -d prod_db
If you see a password prompt, you've succeeded. If the connection hangs, double-check your cloud security groups (like AWS SG) or local firewall (UFW). Port 5432 must be open for inbound traffic.
Pro-Tips for Production
Avoid the temptation to use 0.0.0.0/0 in your pg_hba.conf. While it makes things work instantly, it exposes your database to the entire internet. Always narrow access down to specific CIDR blocks.
When managing complex VPCs with multiple subnets, I use the Subnet Calculator on ToolCraft. It’s a quick way to get the correct CIDR notation for a range of IPs so I don't accidentally whitelist more than I need to. It's browser-based and keeps the data local, which is perfect for quick networking fixes.
Finally, if your logs still mention SSL off but your security policy requires encryption, change the TYPE from host to hostssl. This forces the client to negotiate an encrypted tunnel before passing credentials.