How to Secure PostgreSQL on a Hostperl VPS

Secure PostgreSQL on a fresh Hostperl VPS

Use this guide if you want to secure PostgreSQL on a new Hostperl VPS before you connect an application or import live data. You’ll install PostgreSQL, create a non-root admin, limit network access, harden the database account, and verify that backups and log checks work. If you’re still choosing storage and instance size for a database-heavy workload, Hostperl’s VPS hosting is a practical place to start. It gives you room for database growth and predictable server control.

ssh root@203.0.113.10

That IP is a reserved documentation example. Replace 203.0.113.10 with the real public IP assigned to your server. If your Hostperl setup uses a different default login, use that account first and keep the root session open until the new sudo user is verified.

On your local computer, open a second terminal later so you can test the new user without risking lockout.

Confirm the operating system before you install anything

Run this on the VPS as root. It tells you whether you should follow the Ubuntu/Debian path or the AlmaLinux/Rocky Linux path.

cat /etc/os-release

You should see ID=ubuntu, ID=debian, ID=almalinux, or ID=rocky. The commands below are split by family because package names, firewall tools, and service behavior differ.

Create a non-root sudo user and keep root available for testing

Do this on the VPS as root. The goal is to move daily work away from root while keeping the original session open until the new login works.

Ubuntu and Debian

adduser deploy

This creates the deploy account and prompts for a password. Then grant sudo access:

usermod -aG sudo deploy

Create SSH access safely. Run the following as root, then paste your public key into the file.

install -d -m 700 -o deploy -g deploy /home/deploy/.ssh
nano /home/deploy/.ssh/authorized_keys
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys

Save and exit the editor after adding the key. The directory must be 700, and the key file must be 600.

AlmaLinux and Rocky Linux

useradd -m -s /bin/bash deploy
passwd deploy
usermod -aG wheel deploy

Create the SSH directory and key file with strict permissions:

install -d -m 700 -o deploy -g deploy /home/deploy/.ssh
nano /home/deploy/.ssh/authorized_keys
chown deploy:deploy /home/deploy/.ssh/authorized_keys
chmod 600 /home/deploy/.ssh/authorized_keys

Open a second terminal on your local computer and test the new login before you change any SSH settings.

ssh deploy@203.0.113.10

After you log in, verify sudo works.

sudo -v
whoami
pwd

You should see deploy from whoami and no sudo errors.

Update the server and install PostgreSQL

Use the package manager for your distribution. This keeps the database current enough for supported 2026 hosting setups without guessing service names.

Ubuntu and Debian

sudo apt update
sudo apt -y upgrade
sudo apt -y install postgresql postgresql-contrib

Confirm the version and that the service is running.

psql --version
systemctl status postgresql --no-pager

AlmaLinux and Rocky Linux

sudo dnf -y update
sudo dnf -y install postgresql-server postgresql-contrib

Initialize the database cluster, then start and enable the service.

sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql
psql --version
systemctl status postgresql --no-pager

If you’re setting up a new application stack alongside PostgreSQL, Hostperl’s managed VPS hosting gives you room for both the database and the app server without overcommitting shared resources.

Restrict PostgreSQL to the connections you actually need

By default, PostgreSQL often listens only locally. That works well for a single-server app. If your web app runs on the same VPS, keep it local and avoid exposing port 5432 to the internet. If another trusted host needs access, allow only that address.

Check the listening address

sudo ss -ltnp | grep 5432

If you see 127.0.0.1:5432 or localhost:5432, PostgreSQL is not exposed externally. That is the safest default.

Ubuntu and Debian firewall with UFW

sudo apt -y install ufw
sudo ufw allow OpenSSH
sudo ufw enable
sudo ufw status verbose

Do not open PostgreSQL yet unless you need remote access from a known IP. If you do, allow only that source:

sudo ufw allow from 203.0.113.20 to any port 5432 proto tcp

Replace 203.0.113.20 with your application server or office IP, not a public range.

AlmaLinux and Rocky Linux firewall with firewalld

sudo systemctl enable --now firewalld
sudo firewall-cmd --permanent --add-service=ssh
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

Allow PostgreSQL only from a trusted source if needed. Firewalld uses rich rules for source restrictions:

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

Secure the PostgreSQL account and default database access

Next, reduce the chance that a default local account becomes a weak point. PostgreSQL uses the postgres system account for administration on most Linux installs.

sudo -iu postgres psql

At the psql prompt, set a strong password for the PostgreSQL superuser and inspect current roles.

\password postgres
\du
\q

Back on the shell, verify the database is reachable only where you expect:

sudo -iu postgres psql -c "SELECT current_user, inet_server_addr(), inet_server_port();"

If your application needs its own account, create it with a login role and a dedicated database.

sudo -iu postgres createuser --pwprompt appuser
sudo -iu postgres createdb -O appuser appdb

That keeps the app away from the superuser account. For most hosted applications, this is the right model.

Harden pg_hba.conf and postgresql.conf

These files control authentication and network behavior. Edit them carefully, then test syntax by restarting the service after a backup.

Find the active configuration files

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

You will usually see paths under /etc/postgresql/ on Debian and Ubuntu, and under /var/lib/pgsql/data/ or similar on AlmaLinux and Rocky Linux.

Recommended edits for a single-server app

Open the files with your editor and make sure local authentication uses modern password handling. Keep loopback access enabled, and only add remote entries if you truly need them.

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

On Debian and Ubuntu, set or confirm:

listen_addresses = 'localhost'
password_encryption = scram-sha-256

For the matching pg_hba.conf, keep local rules like this:

local   all             postgres                                peer
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

On AlmaLinux and Rocky Linux, the paths are commonly under /var/lib/pgsql/data/. Use the same rule set there.

Save and exit the editor, then back up the old files before reloading the service.

sudo cp /etc/postgresql/*/main/pg_hba.conf /etc/postgresql/*/main/pg_hba.conf.bak 2>/dev/null || true
sudo cp /etc/postgresql/*/main/postgresql.conf /etc/postgresql/*/main/postgresql.conf.bak 2>/dev/null || true
sudo systemctl restart postgresql

After restart, confirm the service came back cleanly.

systemctl status postgresql --no-pager
sudo journalctl -u postgresql -n 50 --no-pager

Test local and remote access the right way

From the VPS, connect with the app user and make a simple query. This proves password auth, database creation, and service health in one step.

psql -U appuser -d appdb -h 127.0.0.1 -c "SELECT version();"

Expected result: PostgreSQL returns its version and exits without an authentication error.

If you have a separate application server, test remote access from that host only after you have added a firewall rule and a matching pg_hba.conf entry for its IP. A refusal usually means one of those two controls is still blocking traffic.

Set up a simple backup and restore check

A database is not secure if you cannot restore it. A backup test matters more than a backup file sitting on disk.

Run this on the VPS as the non-root sudo user or as postgres. It creates a plain SQL dump in a protected directory.

sudo install -d -m 700 /var/backups/postgresql
sudo -iu postgres pg_dump appdb | sudo tee /var/backups/postgresql/appdb.sql >/dev/null
sudo chmod 600 /var/backups/postgresql/appdb.sql
ls -l /var/backups/postgresql/appdb.sql

Now create a restore test database and load the dump into it.

sudo -iu postgres createdb appdb_restore_test
sudo -iu postgres psql -d appdb_restore_test < /var/backups/postgresql/appdb.sql
sudo -iu postgres psql -d appdb_restore_test -c "\dt"

If the table list appears, the restore path works. Remove the test database after verification.

sudo -iu postgres dropdb appdb_restore_test

For teams comparing database hosting options, Hostperl’s VPS hosting for databases is a better fit than oversize shared plans when you need predictable I/O and regular restore testing.

Keep PostgreSQL maintainable after launch

Post-launch problems are often simple: package drift, neglected logs, or an accidental firewall change. Check these items after every maintenance window.

• Service status: systemctl status postgresql --no-pager
• Listening port: sudo ss -ltnp | grep 5432
• Logs: sudo journalctl -u postgresql -n 100 --no-pager
• Firewall rules: sudo ufw status verbose or sudo firewall-cmd --list-all
• Authentication: psql -U appuser -d appdb -h 127.0.0.1 -c "SELECT 1;"

For Ubuntu and Debian, keep up with apt update and apt upgrade. For AlmaLinux and Rocky Linux, use dnf update. If you run SELinux on the RHEL-compatible path, keep the default PostgreSQL labels intact and avoid moving the data directory unless you know how to relabel it correctly.

Troubleshooting the most common failures

1. PostgreSQL will not start after a config edit. Run sudo journalctl -u postgresql -n 50 --no-pager. The usual clue is a syntax error or a bad file path. Reopen the edited file, fix the line, and restart PostgreSQL.

2. Remote connections fail even after opening the firewall. Check sudo ss -ltnp | grep 5432 and sudo grep -n "listen_addresses" /etc/postgresql/*/main/postgresql.conf or the equivalent AlmaLinux path. If PostgreSQL listens only on localhost, remote access will fail by design.

3. The app user gets a password authentication error. Run sudo -iu postgres psql -c "\du" and inspect pg_hba.conf. The fix is usually changing md5 to scram-sha-256 or correcting the source address entry.

4. The restore test fails. Check the dump file size with ls -lh /var/backups/postgresql/appdb.sql. If it is zero bytes, your dump command failed or lacked permissions. Regenerate the backup and repeat the restore test before relying on it.

Frequently asked questions

Should PostgreSQL listen on a public IP?

Usually no. Keep it on localhost unless a trusted remote application needs direct access, and then restrict it by source IP.

Which authentication method should I use?

Use scram-sha-256 for password logins. It is the better default for current Linux hosting setups.

How often should I test restores?

At least after each major application change and on a regular schedule, such as monthly. A backup you have not restored is only a file.

Can I manage PostgreSQL on the same VPS as my website?

Yes. That is common for small-business sites and early-stage applications. Watch memory, disk space, and backup timing as traffic grows.

What should I verify after a reboot?

Check systemctl status postgresql --no-pager, confirm port 5432 is listening, and run a simple query with the app user.

Once your database is stable, keep an eye on disk growth and backup logs. A well-sized Hostperl VPS paired with disciplined PostgreSQL maintenance is usually enough for a clean, predictable launch.