Back to tutorials
Tutorial

Install PostgreSQL on Ubuntu and Secure Remote Access

Install PostgreSQL on Ubuntu, lock down remote access, harden pg_hba.conf, and verify connections with firewall rules.

By Anurag Singh
Updated on Nov 08, 2026
Category: Tutorial
Share article
Install PostgreSQL on Ubuntu and Secure Remote Access

Running your database on the same VPS as your app is normal. Exposing it to the internet with no guardrails is how you end up rotating credentials at 3 a.m. This tutorial shows you how to install PostgreSQL on Ubuntu, then lock down remote access with sane authentication, firewall rules, a hardened pg_hba.conf, and a connection test you can trust.

The steps below target Ubuntu Server 24.04 LTS and PostgreSQL 16. That’s the stable branch most teams run in 2026.

On Ubuntu 22.04, the commands and paths are the same. However, the default repo may install an older Postgres release.

What you’ll build (and what you’ll avoid)

  • A production-ready PostgreSQL service running under systemd
  • Remote access enabled only for specific IPs, with password auth (SCRAM)
  • Firewall rules that allow TCP/5432 only from your trusted host(s)
  • A clean, verifiable remote connection test (no guessing)

You will not open PostgreSQL to the whole internet.

If you need broad access, put it behind a VPN or tunnel. Another option is routing traffic through an allowlisted bastion host.

Prerequisites

  • An Ubuntu VPS or dedicated server with root or sudo access
  • A domain is optional; PostgreSQL remote access is IP-based in this guide
  • A remote client machine (your laptop or an app server) with psql installed

If you need a server for this, a HostMyCode VPS gives you full root access, predictable performance, and a network perimeter you control.

Step 1: Update Ubuntu and install PostgreSQL

Update packages first. On a fresh VPS, this usually pulls in a batch of security fixes.

sudo apt update
sudo apt -y upgrade

Install PostgreSQL and the contrib package. It includes useful extensions and admin tooling.

sudo apt -y install postgresql postgresql-contrib

Confirm the service is up:

systemctl status postgresql --no-pager

You’ll often see active (exited) for the meta-service. The real cluster runs behind it.

Check the cluster status directly:

pg_lsclusters

Expected output includes a version (for example 16), a cluster name (usually main), and the port (5432).

Step 2: Set a secure password and create an application role

On Ubuntu, the default postgres database user uses local “peer” auth.

For remote access (and most apps), use a dedicated role with a strong password.

Switch to the postgres OS user and open psql:

sudo -iu postgres
psql

Create a database and role. Replace the placeholders with your own names and a long random password.

-- inside psql
CREATE DATABASE appdb;

CREATE ROLE appuser WITH LOGIN PASSWORD 'REPLACE_WITH_A_LONG_RANDOM_PASSWORD';

GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

Exit psql and return to your shell:

\q
exit

Common mistake: pointing your app at the postgres role. Don’t do it.

Keep superuser access separate. That way, an app compromise can’t become a full takeover.

Step 3: Enforce SCRAM password hashing (recommended for 2026)

SCRAM-SHA-256 is the baseline for password auth in 2026.

This step makes sure new passwords are stored using SCRAM.

Edit the main server config. First, confirm which config file your cluster is using:

sudo -iu postgres psql -tAc "SHOW config_file;"

On Ubuntu, it’s usually:

  • /etc/postgresql/16/main/postgresql.conf

Edit it:

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

Set (or confirm) this line:

password_encryption = scram-sha-256

Restart PostgreSQL after changing server config:

sudo systemctl restart postgresql

Note: this does not re-hash existing passwords.

If you created appuser earlier, reset its password after enabling SCRAM:

sudo -iu postgres psql -d postgres -c "ALTER ROLE appuser WITH PASSWORD 'REPLACE_WITH_A_LONG_RANDOM_PASSWORD';"

Step 4: Bind PostgreSQL to the right network interface

By default, PostgreSQL listens on localhost only. Remote access requires changing listen_addresses.

Make the change intentional. Don’t expose more interfaces than you need.

Pick the right target:

  • Same VPS only: leave it as localhost
  • Remote access from specific hosts: listen on the server’s private IP (best) or public IP (acceptable with a strict firewall)

List your server IPs:

ip -br a

Edit postgresql.conf again:

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

Use one of these patterns:

# Option A: listen on all interfaces (only safe with strict firewall rules)
listen_addresses = '*'

# Option B: listen only on a specific interface/IP (recommended when possible)
# listen_addresses = '10.0.0.10'

Restart and confirm PostgreSQL is listening where you expect:

sudo systemctl restart postgresql
sudo ss -lntp | grep 5432

If you still only see 127.0.0.1:5432, you likely edited the wrong file.

You may also have left the line commented, or forgotten to restart.

Step 5: Harden pg_hba.conf for remote password auth

pg_hba.conf controls who can connect, from where, and which auth method they must use.

Most “port is open but I still can’t connect” problems start here.

Find the active file:

sudo -iu postgres psql -tAc "SHOW hba_file;"

Typically:

  • /etc/postgresql/16/main/pg_hba.conf

Edit it:

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

Add a narrow allow rule near the top. PostgreSQL evaluates rules top-to-bottom.

Replace 203.0.113.10/32 with your client IP (single host) or a small CIDR block you control.

# Allow appuser to reach appdb from one trusted IP using SCRAM
host    appdb     appuser     203.0.113.10/32     scram-sha-256

Keep local access intact. You’ll usually already have entries like these:

# "local" is for Unix domain socket connections only
local   all       postgres                          peer
local   all       all                               peer

Recommended adjustment: if you don’t want OS users to implicitly access matching DB roles, switch local auth for non-postgres users to password-based.

Whether you do this depends on how you administer the box.

# Optional: require scram even locally for non-postgres roles
# local   all       all                               scram-sha-256

Reload PostgreSQL to apply pg_hba.conf changes. A reload is enough:

sudo systemctl reload postgresql

Quick sanity check that PostgreSQL accepted the config. This is useful for catching syntax errors:

sudo -iu postgres psql -c "SELECT 1;"

Step 6: Open the firewall only to the IPs you trust

Think of the firewall as your second lock.

Even with a strict pg_hba.conf, dropping unsolicited traffic at the edge cuts down noise and brute-force attempts.

If you use UFW, enable it carefully. Allow SSH first so you don’t lock yourself out:

sudo ufw allow OpenSSH

Then allow PostgreSQL only from your trusted client IP:

sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp

Enable UFW and verify what’s actually allowed:

sudo ufw enable
sudo ufw status verbose

Common mistake: running ufw allow 5432/tcp with no source restriction. That publishes Postgres to the entire internet.

Step 7: Require TLS for remote connections (practical baseline)

Password auth without TLS is a bad bet.

If the client is misconfigured, or the network path isn’t trustworthy, you can leak credentials and query data.

PostgreSQL supports TLS directly. Ubuntu packages make it straightforward once your certificates are in order.

Many teams reuse a Let’s Encrypt cert from a reverse proxy. PostgreSQL still needs files readable by the postgres user.

To keep permissions simple, the steps below use a small internal CA created with OpenSSL.

It’s self-contained and works well for app-to-db traffic.

Create a directory for PostgreSQL TLS materials:

sudo install -d -m 700 -o postgres -g postgres /etc/postgresql/16/main/tls

Create a small internal CA (run as root; keep the CA key safe):

sudo openssl genrsa -out /etc/postgresql/16/main/tls/ca.key 4096
sudo openssl req -x509 -new -nodes -key /etc/postgresql/16/main/tls/ca.key \
  -sha256 -days 3650 -out /etc/postgresql/16/main/tls/ca.crt \
  -subj "/CN=pg-internal-ca"

Create a server key and certificate signing request:

sudo openssl genrsa -out /etc/postgresql/16/main/tls/server.key 4096
sudo openssl req -new -key /etc/postgresql/16/main/tls/server.key \
  -out /etc/postgresql/16/main/tls/server.csr \
  -subj "/CN=postgresql-server"

Sign the server cert with your CA:

sudo openssl x509 -req -in /etc/postgresql/16/main/tls/server.csr \
  -CA /etc/postgresql/16/main/tls/ca.crt -CAkey /etc/postgresql/16/main/tls/ca.key \
  -CAcreateserial -out /etc/postgresql/16/main/tls/server.crt -days 825 -sha256

Fix ownership and permissions. PostgreSQL refuses overly-permissive key files:

sudo chown -R postgres:postgres /etc/postgresql/16/main/tls
sudo chmod 600 /etc/postgresql/16/main/tls/server.key
sudo chmod 644 /etc/postgresql/16/main/tls/server.crt /etc/postgresql/16/main/tls/ca.crt

Enable TLS in postgresql.conf:

sudo nano /etc/postgresql/16/main/postgresql.conf
ssl = on
ssl_cert_file = '/etc/postgresql/16/main/tls/server.crt'
ssl_key_file = '/etc/postgresql/16/main/tls/server.key'
ssl_ca_file = '/etc/postgresql/16/main/tls/ca.crt'

Restart PostgreSQL:

sudo systemctl restart postgresql

Confirm SSL is active:

sudo -iu postgres psql -tAc "SHOW ssl;"

Expected output: on

Client requirement: copy ca.crt to the client. Then connect with sslmode=verify-full or verify-ca.

If you don’t want to deal with hostname validation yet, start with verify-ca. Move to verify-full once your DNS naming is consistent.

Step 8: Verify remote connectivity from a client host

Run this test from the machine that should connect (your laptop or an app server).

If psql isn’t installed:

# On Ubuntu/Debian client
sudo apt update
sudo apt -y install postgresql-client

Copy the CA certificate to the client. For a quick test, use scp (replace server IP):

scp root@YOUR_SERVER_IP:/etc/postgresql/16/main/tls/ca.crt ./pg-ca.crt

Test the connection (replace server IP/DNS and credentials):

PGPASSWORD='REPLACE_WITH_A_LONG_RANDOM_PASSWORD' \
psql "host=YOUR_SERVER_IP port=5432 dbname=appdb user=appuser sslmode=verify-ca sslrootcert=$(pwd)/pg-ca.crt"

Once you’re in, confirm what you connected to:

SELECT version();
SELECT inet_client_addr(), inet_client_port();
\conninfo

Quit:

\q

Step 9: Quick diagnostics for the three usual failures

Most broken remote setups come down to one of these.

Check them in this order to save time.

1) Port not reachable (firewall or bind issue)

From the client:

nc -vz YOUR_SERVER_IP 5432

If this times out, verify on the server:

sudo ss -lntp | grep 5432
sudo ufw status verbose

Also confirm your VPS provider firewall/security group isn’t blocking 5432.

Some platforms default-deny anything outside common web ports.

2) “no pg_hba.conf entry”

This error means the connection reached PostgreSQL, but pg_hba.conf didn’t match any allow rule.

Usual causes:

  • Your client IP changed (common on home/office connections)
  • You edited the wrong cluster’s pg_hba.conf
  • The rule is below a broader “reject” rule or doesn’t match the database/user

On the server, tail logs while attempting a connection:

sudo journalctl -u postgresql -n 200 --no-pager

Then confirm you’re editing the active config files:

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

3) Password failures

Reset the app user password and retry:

sudo -iu postgres psql -c "ALTER ROLE appuser WITH PASSWORD 'REPLACE_WITH_A_LONG_RANDOM_PASSWORD';"

If you’re enforcing SCRAM, confirm the pg_hba.conf method is scram-sha-256, not md5.

Step 10: Production tightening (small changes that pay off)

You’ve got working remote access.

Before you point real traffic at it, tighten the basics.

Set sensible connection limits for the app role

One bad deploy shouldn’t open 1,000 connections and starve the box.

sudo -iu postgres psql -c "ALTER ROLE appuser CONNECTION LIMIT 50;"

Then set max_connections based on RAM and your pooling strategy.

If the site is busy, add PgBouncer.

Treat that as its own project. Don’t bolt it on mid-incident.

Turn on logging you’ll actually use

Edit postgresql.conf and set:

log_line_prefix = '%m [%p] %u@%d %r '
log_connections = on
log_disconnections = on
log_min_duration_statement = 1000

This logs queries slower than 1s.

You’ll catch obvious problems without turning your disk into a log sink.

Verify disk and memory basics (VPS reality check)

Databases feel storage latency immediately.

If your workload is growing, NVMe-backed plans matter.

lsblk
free -h
df -h

If your app and database are fighting over RAM on a small VPS, consider splitting roles.

Put the app on one node and PostgreSQL on another.

A larger HostMyCode dedicated server is often the simpler move once you’re I/O bound.

Backups: don’t stop at “we dump nightly”

A backup you haven’t restored is just a file you hope you can use.

If you want a complete workflow (including a restore test), follow: Set Up PostgreSQL Backups With pg_dump + Restore Test.

At minimum, schedule a nightly dump and keep it off the server. Example (server side):

sudo install -d -m 700 -o postgres -g postgres /var/backups/postgresql
sudo -iu postgres bash -c 'pg_dump -Fc appdb > /var/backups/postgresql/appdb-$(date +%F).dump'

Then copy backups to object storage or another VPS using rsync or rclone.

If you want a hands-off migration or a backup redesign, HostMyCode can help via managed migrations.

Security notes that matter on real servers

  • Never expose PostgreSQL to 0.0.0.0/0 unless you have a very specific reason and compensating controls.
  • Keep SSH locked down and patched. Automated updates help on smaller fleets. See: Set Up Automated Security Updates on Ubuntu VPS.
  • If PostgreSQL must be reachable from multiple changing IPs, stop playing whack-a-mole with firewall rules. Use a VPN.

Related tutorials you’ll likely need next

Summary checklist (printable)

  • PostgreSQL installed and cluster running: pg_lsclusters
  • Dedicated app role created (not postgres)
  • password_encryption = scram-sha-256 set
  • listen_addresses set intentionally (not accidentally)
  • pg_hba.conf allow rule limited to known IP/CIDR
  • UFW allows 5432 only from trusted IPs
  • TLS enabled and verified from a remote client
  • Backups scheduled and a restore test planned

If you’re running PostgreSQL for a production website or app, disk I/O and networking predictability matter as much as configuration. Start on a HostMyCode VPS, and move to managed VPS hosting if you want OS patching and core maintenance handled for you.

FAQ

Should I allow remote PostgreSQL access over the public internet?

Only if you restrict it to specific IPs, require strong password auth (SCRAM), and enforce TLS. For broader access, use a VPN or a private network between servers.

Why does PostgreSQL still refuse connections after I opened the port?

Usually because pg_hba.conf has no matching rule. Check the client IP, rule order, and that you edited the right cluster’s config.

Is listen_addresses = '*' unsafe?

It can be safe if your firewall drops everything except trusted IPs and your pg_hba.conf is strict. If you can bind to a private IP only, that’s cleaner.

Do I need TLS if I’m already using a strong password?

Yes. TLS protects credentials and data in transit. Without it, a network observer can capture auth attempts and query payloads.

What’s the fastest way to confirm I’m using SSL from the client?

After connecting in psql, run \conninfo. It reports whether SSL is in use and the cipher details.

If you want to expand this setup (connection pooling, replication, or separate DB nodes), do it on infrastructure with consistent CPU performance and NVMe storage.

If you’re starting from scratch and need a clean base to install PostgreSQL on Ubuntu, a HostMyCode VPS is a solid place to begin, with room to scale later.