You're stuck, I get it. Let's fix it.
You set up PostgreSQL on your server, but when you try to connect from another computer, you get something like "could not connect to server: Connection refused" or "no pg_hba.conf entry for host." It's frustrating because local connections work fine. Here's the real fix.
The Fix: Edit pg_hba.conf
PostgreSQL uses a file called pg_hba.conf to decide who can connect and how. By default, it only allows local connections. You need to add a line for remote ones.
Step 1: Find pg_hba.conf
On Linux (Ubuntu, Debian, CentOS), it's usually in /etc/postgresql/[version]/main/pg_hba.conf. On Windows, it's in C:\Program Files\PostgreSQL\[version]\data\pg_hba.conf. If you can't find it, run psql -U postgres -c "SHOW hba_file" — that tells you the exact path.
Step 2: Back up the file (don't skip)
Copy it first. On Linux: sudo cp /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.backup. On Windows, just copy-paste it to a safe folder. If you mess up, you can restore it.
Step 3: Add a remote access line
Open the file with a text editor (on Linux: sudo nano /etc/postgresql/16/main/pg_hba.conf, on Windows: Notepad as Administrator). Scroll to the bottom. Add this line:
host all all 0.0.0.0/0 md5That allows all remote connections to all databases using password authentication. If you want only a specific IP (like 192.168.1.100), replace 0.0.0.0/0 with 192.168.1.100/32. For a whole subnet (like 192.168.1.0/24), use 192.168.1.0/24. I always use md5 for authentication — it's secure and works everywhere. Don't use trust unless you're in a lab.
Step 4: Edit postgresql.conf to listen on all addresses
PostgreSQL also needs to listen on the network. Open postgresql.conf (same folder as pg_hba.conf). Find the line that says #listen_addresses = 'localhost'. Change it to:
listen_addresses = '*'If you want only a specific IP (like the server's LAN IP), use listen_addresses = '192.168.1.50' instead of '*'. After changing it, save the file.
Step 5: Restart PostgreSQL
On Linux (systemd): sudo systemctl restart postgresql. On older systems: sudo service postgresql restart. On Windows: open Services (services.msc), find PostgreSQL, right-click and restart. After restart, you should see the service start without errors. If it fails, check the logs — usually in /var/log/postgresql/postgresql-[version]-main.log on Linux or C:\Program Files\PostgreSQL\[version]\data\pg_log on Windows.
Step 6: Test the connection
From another machine, run: psql -h [server IP] -U postgres -d postgres. Replace [server IP] with the actual IP of your PostgreSQL server. If it asks for a password and you connect, you're done. If it still says "Connection refused," check the firewall — that's the next section.
Why This Worked
PostgreSQL by default only listens on localhost (127.0.0.1) and only allows local connections via Unix socket or loopback. The pg_hba.conf file controls authentication rules — without a line for remote hosts, PostgreSQL rejects them. By adding the host line, you tell PostgreSQL "let anyone from any IP connect if they give a valid password." Changing listen_addresses tells the server to actually open the network port. Restarting applies both changes.
Less Common Variations
Firewall is blocking port 5432
Even after fixing pg_hba.conf, if the server's firewall blocks port 5432, you can't connect. On Linux with UFW: sudo ufw allow 5432/tcp. With firewalld: sudo firewall-cmd --add-port=5432/tcp --permanent && sudo firewall-cmd --reload. On Windows: open Windows Defender Firewall, add an inbound rule for TCP port 5432. Test with telnet [server IP] 5432 — if it connects, the firewall is open. If it hangs, firewall is the problem.
PostgreSQL is not running
Check the service status: sudo systemctl status postgresql. If it's inactive or failed, start it: sudo systemctl start postgresql. Look at the logs for why it failed — often it's a typo in pg_hba.conf or postgresql.conf. The error message usually tells you the line number.
Using Docker or containerized PostgreSQL
If you're running PostgreSQL in Docker, you need to map port 5432 and edit pg_hba.conf inside the container. A common command: docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword postgres. Then exec into the container: docker exec -it [container_id] bash, edit /var/lib/postgresql/data/pg_hba.conf inside the container, and restart the container. Don't forget to set listen_addresses too.
SSL/TLS requirements
Some PostgreSQL configurations require SSL for remote connections. If you see pg_hba.conf rejects connection for host SSL off, add hostssl instead of host and set up SSL certificates. For most setups, host with md5 is fine.
How to Prevent This
Before setting up a new PostgreSQL server, edit pg_hba.conf and postgresql.conf right after installation. Don't wait until you need remote access. Also, write down the exact IP ranges you'll use — it saves time later. Keep a backup of the default config files so you can compare if something breaks. And always restart the service after any config change — not reload, a full restart. I've seen reload not pick up listen_addresses changes.