Allowing remote PostgreSQL connections on a VPS, safely

By default PostgreSQL listens only on 127.0.0.1. Accepting connections from outside takes three adjustments: tell it which address to listen on (listen_addresses), tell it who may come in (pg_hba.conf), and open port 5432 for that address only in the firewall. This assumes a VPS with root, preferably running Ubuntu 22.04 LTS, and the configuration and security are yours.

Before you open anything, ask yourself whether an SSH tunnel would not be enough. With a tunnel no port is opened and the database stays invisible to the internet. See the SSH tunnel. Follow the steps below only if an application on another server truly has to talk to the database directly.

Step by step

1 Listen on another address. Edit /etc/postgresql/<version>/main/postgresql.conf and change listen_addresses = 'localhost' to listen_addresses = 'localhost,<VPS-IP>'. Avoid '*' if you can.
2 Say who may enter. In pg_hba.conf add a line for the application’s exact address only, with an encrypted password:host database_name user 203.0.113.10/32 scram-sha-256The /32 means “this address only”. 203.0.113.10 is just an example.
3 Open the port for it alone. With ufw: ufw allow from 203.0.113.10 to any port 5432 proto tcp.
4 Restart. systemctl restart postgresql.
5 Check. On the VPS, ss -ltn | grep 5432 shows the addresses it listens on. From the other server, psql -h <VPS-IP> -U user -d database_name.
pg_hba.conf line Meaning
host database user IP/32 scram-sha-256 Good. One database, one user, one address, encrypted password.
host all all 0.0.0.0/0 scram-sha-256 Bad. Anyone in the world can try to get into any database.
host all all 0.0.0.0/0 trust A disaster. Anyone gets in without a password.
What usually goes wrong: changing postgresql.conf and forgetting pg_hba.conf (the error is no pg_hba.conf entry); opening the port in the server’s firewall but not in another firewall in front of it; and leaving a weak password. Bots sweep port 5432 all day. See ports and firewall on a VPS.

How to make changes without locking yourself out

Before editing, copy both files (for example, cp pg_hba.conf pg_hba.conf.before). Change one thing at a time and test. A useful difference: changes to pg_hba.conf apply with systemctl reload postgresql, but listen_addresses only changes with systemctl restart postgresql. In the firewall, make sure the rule for your SSH port exists first before you switch it on: switch it on without that and you lose access to the server. If you still lock yourself out, see the four usual causes.

Encrypt the connection. A database reachable over the internet without TLS sends the password and the data in clear. If the application supports it, use SSL on the connection. Alternatively an SSH tunnel gives you encryption without configuring anything in PostgreSQL. See keeping your VPS secure.

Lost access to the VPS after changing the firewall? Write to us with the server name and the time of the change.

Open a support ticket

SEE ALSO

Reaching MySQL or PostgreSQL through an SSH tunnel, step by step

Ports and firewall on a VPS: why your app is not reachable

Keeping your VPS or dedicated server secure: the six that matter

RECOMMENDED PRODUCT

VPS server with root access

Resources of your own, the OS you choose, reinstall whenever you like. from ₦12.600,00/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
PostgreSQL in cPanel: creating a database and connecting from PHP On shared hosting PostgreSQL 13.23 is active and has tools in cPanel. Create the database and user under PostgreSQL Data...