Configure PostgreSQL Remote Access with pg_hba.conf

PostgreSQL remote access means letting a client on another machine connect to your database over TCP. By default, most installs only listen on localhost and only accept local connections, so you have to change two things: listen_addresses in postgresql.conf and the rules in pg_hba.conf. This guide walks through both, plus the firewall step, and shows how to test the result from a remote host.

The examples use Ubuntu 22.04/24.04 and Debian 12 paths, but the same files exist on AlmaLinux and Rocky 8–9 under /var/lib/pgsql/data. Adjust the paths if your distribution differs.

Before you start

You need:

• A Linux VPS with PostgreSQL installed and running. If you have not installed it yet, see Install PostgreSQL and create your first database or Install the latest PostgreSQL from the official PGDG repository.
• Root or sudo access on the server. You will edit files as the postgres user or with sudo.
• A database user and database that the remote client will use. If you do not have one, follow Create PostgreSQL users, roles and databases.
• The public IP of the client that will connect, or a CIDR range. Use 203.0.113.10 in the examples below.
• A firewall you control. On most VPSs that is ufw (Ubuntu/Debian) or firewalld (AlmaLinux/Rocky).

Find the config files

PostgreSQL keeps its configuration in a data directory. On Debian and Ubuntu that is /etc/postgresql/<version>/main/. On RHEL-family systems it is /var/lib/pgsql/data/.

Ask the server where the files are:

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

You will get two paths back. Use those exact paths in the next steps.

Set listen_addresses

Open postgresql.conf and find the listen_addresses line. It is often commented out.

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

Edit the file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Change the line to bind to all interfaces, or to a specific private IP if your VPS has one:

listen_addresses = '*'

Save the file. Do not restart yet — you still need to edit pg_hba.conf.

Add a pg_hba.conf rule

pg_hba.conf is read top to bottom, and the first matching line wins. Open it:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add a line for your remote client above any broad host all all rules. For a single IP using password authentication:

# TYPE  DATABASE  USER      ADDRESS          METHOD
host    mydb      deploy    203.0.113.10/32  scram-sha-256

For a subnet, widen the mask:

host    mydb      deploy    203.0.113.0/24   scram-sha-256

Notes on the fields:

• host means TCP/IP with or without SSL. Use hostssl if you want to force TLS.
• The database and user can be all, but naming them is safer.
• scram-sha-256 is the modern password method. md5 still works but is weaker. trust accepts anyone — never use it for remote access.
• The address column is a CIDR block. /32 is a single IPv4 host, /128 a single IPv6 host.

Reload the configuration so PostgreSQL picks up the changes without dropping existing sessions:

sudo systemctl reload postgresql

On RHEL-family systems:

sudo systemctl reload postgresql-16

If the reload fails, check the syntax with:

sudo -u postgres psql -c "SELECT pg_reload_conf();"

Open the firewall

The default PostgreSQL port is 5432. Open it only to the client IP.

With ufw:

sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp
sudo ufw status

With firewalld:

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

For more on rich rules, see Write firewalld rich rules for IP-based access.

Confirm the port is listening on the public interface:

sudo ss -tlnp | grep 5432

You should see 0.0.0.0:5432 or the specific IP you set in listen_addresses.

Test from the remote client

From the client machine, use psql:

psql "host=203.0.113.10 port=5432 dbname=mydb user=deploy"

You will be prompted for the password. If it connects, run a quick query:

SELECT current_user, inet_server_addr(), version();

inet_server_addr shows the server IP the client reached, which confirms you are talking to the remote instance and not a local one.

If you do not have psql on the client, install the client package (postgresql-client on Debian/Ubuntu, postgresql on RHEL-family) or use a GUI tool such as pgAdmin or DBeaver with the same host, port, database and user.

Check it works

Three checks confirm the setup end to end:

1. The server is listening: sudo ss -tlnp | grep 5432 shows the expected address.
2. The rule matches: sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL;" returns no rows, meaning every line parsed cleanly.
3. The client connects and SELECT inet_server_addr; returns the server's address, not 127.0.0.1.

If all three pass, remote access is working. Now tighten anything you left loose — for example, remove temporary all rules and confirm the firewall only allows your client IP.

Troubleshooting

• could not connect to server: Connection refused — PostgreSQL is not listening on that address. Check listen_addresses in postgresql.conf and confirm the service reloaded. Run sudo ss -tlnp | grep 5432 to see the actual bind address.
• no pg_hba.conf entry for host "203.0.113.10" — the client IP does not match any rule. Add a host line for that exact address or subnet, then reload. Remember rules are matched top-down, so an earlier reject line can shadow yours.
• password authentication failed for user "deploy" — the password is wrong, or the role has no password set. Reset it with ALTER ROLE deploy WITH PASSWORD '...';. If you recently changed the method from md5 to scram-sha-256, the stored hash may need to be reset too. See Reset the PostgreSQL postgres user password for the same procedure on any role.
• Connection times out with no error — a firewall is dropping packets. Check ufw status or firewall-cmd --list-all, and confirm the rule allows the client IP on port 5432.
• FATAL: no pg_hba.conf entry appears only for some clients — you scoped the rule to a single /32 but the client's outbound IP changes (common on mobile or dynamic connections). Widen to the correct CIDR, or use a VPN with a fixed address.

Next steps

• Back up the database before you change anything else: Back up and restore PostgreSQL with pg_dump and pg_restore.
• Learn the everyday commands: psql cheat sheet: essential PostgreSQL commands.
• Create dedicated roles for each remote client: Create PostgreSQL users, roles and databases.
• If you also run MySQL or MariaDB, apply the same care: Install and secure MariaDB or MySQL, and allow remote access safely.