Allow Remote Connections to PostgreSQL Safely

PostgreSQL listens only on localhost after installation. Allowing another server or your laptop to connect takes two configuration changes — where Postgres listens (postgresql.conf) and who may authenticate from where (pg_hba.conf) — plus a firewall rule. Let’s do all three with security in mind.

Paths below are for Debian/Ubuntu (/etc/postgresql/<version>/main/). On RHEL-family systems they’re in the data directory, e.g. /var/lib/pgsql/data/. Find them with:

sudo -u postgres psql -c "SHOW config_file;" -c "SHOW hba_file;"

Option 0: SSH tunnel (no changes needed)

For admin access from your own machine, tunnel through SSH instead of opening the port:

ssh -N -L 5433:127.0.0.1:5432 [email protected]
psql -h 127.0.0.1 -p 5433 -U myapp myapp

pgAdmin, DBeaver and DataGrip all support SSH tunnels in their connection settings. See SSH port forwarding.

Step 1: listen on the right address

In postgresql.conf:

listen_addresses = 'localhost,10.0.0.5'   # private/VPN IP — preferred
# listen_addresses = '*'                  # all interfaces — only with a strict firewall

This setting requires a restart, not just a reload:

sudo systemctl restart postgresql
sudo ss -tlnp | grep 5432

Step 2: allow the client in pg_hba.conf

pg_hba.conf is read top to bottom; the first matching line decides. Add a line for your client:

# TYPE   DATABASE  USER    ADDRESS          METHOD
host     myapp     myapp   10.0.0.6/32      scram-sha-256

host — TCP/IP connections (SSL or not). Use hostssl to require SSL.
myapp myapp — only this database and this role.
10.0.0.6/32 — exactly one IP. A subnet like 10.0.0.0/24 is also possible.
scram-sha-256 — modern password authentication (default since PostgreSQL 14).

Never use trust for remote connections, and avoid all all 0.0.0.0/0.

Apply it — pg_hba.conf only needs a reload:

sudo systemctl reload postgresql

Step 3: make sure the password is SCRAM-hashed

If the role’s password was set before switching to SCRAM, it may be stored as MD5, and SCRAM authentication fails. Reset it:

sudo -u postgres psql -c "SHOW password_encryption;"    # should be scram-sha-256
sudo -u postgres psql -c "\password myapp"

Step 4: firewall

sudo ufw allow from 10.0.0.6 to any port 5432 proto tcp

firewalld:

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.6" port port="5432" protocol="tcp" accept'
sudo firewall-cmd --reload

Step 5: test

From the client:

psql "host=10.0.0.5 port=5432 dbname=myapp user=myapp"

Step 6: SSL for internet-facing connections

Debian/Ubuntu packages enable SSL with a self-signed “snakeoil” certificate (ssl = on). For real protection against impersonation, install a certificate from a CA you trust (e.g. Let’s Encrypt for the server’s hostname, or your own private CA) and set:

ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file  = '/etc/postgresql/ssl/server.key'

Require it in pg_hba.conf with hostssl, and on the client use sslmode=verify-full with the CA certificate. sslmode=require encrypts but doesn’t verify the server’s identity.

Troubleshooting

could not connect to server: Connection refused — Postgres isn’t listening on that interface. Check listen_addresses and that you restarted.

Connection timed out — a firewall drops the traffic: UFW/firewalld on the server, or a provider-level firewall.

no pg_hba.conf entry for host "10.0.0.6", user "myapp", database "myapp", SSL off — no matching line. Check the IP (behind NAT it’s the public NAT address), database and user names, and whether the line requires SSL (hostssl).

password authentication failed for user "myapp" — wrong password, or MD5-stored password with SCRAM required. Reset with \password.

FATAL: Peer authentication failed — that’s a local socket connection using the peer method; add -h 127.0.0.1 locally. See peer authentication failed.

Check the server log for the exact reason:

sudo tail -n 50 /var/log/postgresql/postgresql-*-main.log

Connection pooling

Many remote clients opening many connections can exhaust PostgreSQL’s process-per-connection model. Put PgBouncer in front — see PgBouncer connection pooling.

Checklist

SSH tunnel or VPN considered first
listen_addresses limited to needed interfaces
pg_hba.conf line: specific database, role and /32 address, scram-sha-256
Firewall allows only that client
hostssl + verified certificates over the internet
No trust entries for network connections

PostgreSQL
Security
Databases