Install and Secure PostgreSQL on Ubuntu VPS

PostgreSQL is the most reliable open-source database you can run on a VPS — and one of the most commonly misconfigured. The default Ubuntu install opens the door, but it doesn't lock it: out of the box, the postgres superuser has trust authentication on local sockets and the database is wide open to anyone who reaches the port. This guide installs Postgres 16, sets up authentication properly, configures TLS, hardens pg_hba.conf, and gets you to a state where the database is genuinely production-ready. Allow about twenty minutes.

Step 1: Add the PGDG repository

Ubuntu's default repos ship a stable but sometimes lagging Postgres version. The official PostgreSQL Global Development Group repo (PGDG) gives you the latest minor versions and lets you pin to any major release. On Ubuntu 24.04 you can skip this if you're happy with the bundled 16.x, but adding PGDG is harmless and gives you faster security patches.

sudo apt update
sudo apt install -y curl ca-certificates gnupg
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | \
  sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update

Step 2: Install PostgreSQL 16

Install the server, the client tools, and the contrib package — contrib has extensions you'll likely want later (pg_stat_statements, pgcrypto, hstore, etc).

sudo apt install -y postgresql-16 postgresql-contrib-16
sudo systemctl status postgresql

You should see active (exited) for the postgresql service and active (running) for postgresql@16-main. Postgres on Ubuntu uses a meta-service that wraps the actual cluster; this is normal.

Step 3: Set the postgres user password

Out of the box, the postgres Linux user can connect to the database via peer authentication (matching Linux user to database role) without a password. That's fine for local admin work, but you still want a real password set on the database role itself — both for safety and because most tools assume password auth exists.

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'use-a-long-random-string-here';"

Generate a real password with openssl rand -base64 32 and store it in a password manager. Don't reuse it anywhere. The postgres role is the database superuser — losing this password means losing the cluster.

Step 4: Create a database and an application user

You almost never want your application connecting as postgres. Create a dedicated role with only the permissions it needs.

sudo -u postgres psql <<'SQL'
CREATE USER appuser WITH PASSWORD 'another-strong-random-password';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\c appdb
GRANT ALL ON SCHEMA public TO appuser;
SQL

That last GRANT ALL ON SCHEMA public matters in Postgres 15+: as of 15, the public schema is no longer writable by everyone by default. New users get connect rights on the database but can't create tables in public until granted explicitly. Skip that line and your migrations will fail with permission denied errors.

Step 5: Configure pg_hba.conf properly

This is the file that nearly every "I got hacked" Postgres post-mortem points to. pg_hba.conf is the host-based authentication config — it decides who can connect, from where, as which user, using which method.

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

The default file has reasonable local rules. The lines that matter — and that you'll need to change for any non-trivial setup — look like this:

# TYPE  DATABASE  USER      ADDRESS         METHOD
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
# Add a line for your application server only — DO NOT use 0.0.0.0/0
host    appdb     appuser   10.0.0.5/32     scram-sha-256

Reload Postgres to apply the changes (no restart needed):

sudo systemctl reload postgresql

Step 6: Enable TLS for connections

Postgres on Ubuntu generates a self-signed certificate by default and TLS is enabled. You can verify with:

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

Self-signed is fine for internal traffic between your application and database on the same VPS, but if you're connecting from outside the host you want a real certificate. The simplest path: get a Let's Encrypt cert via certbot for a subdomain pointed at the VPS, then point Postgres at the cert files.

# After certbot has issued certs at /etc/letsencrypt/live/db.example.com/
sudo cp /etc/letsencrypt/live/db.example.com/fullchain.pem /etc/postgresql/16/main/server.crt
sudo cp /etc/letsencrypt/live/db.example.com/privkey.pem /etc/postgresql/16/main/server.key
sudo chown postgres:postgres /etc/postgresql/16/main/server.{crt,key}
sudo chmod 600 /etc/postgresql/16/main/server.key
sudo systemctl reload postgresql

Set up a deploy hook in /etc/letsencrypt/renewal-hooks/deploy/ to copy the renewed cert and reload Postgres automatically — otherwise your TLS cert will silently expire in 90 days.

Step 7: Open the firewall (or don't)

If your application runs on the same VPS as Postgres, the database should never accept external connections. Keep listen_addresses at the default (localhost) and the firewall closed. Most "secure Postgres" tutorials skip this question entirely — and that's how databases end up exposed to the internet for no reason.

# Same-host: keep this in postgresql.conf
listen_addresses = 'localhost'

# Multi-host: only if needed, bind to private interface only
listen_addresses = '10.0.0.10'

If the database genuinely does need to be reached from another host, expose it on the private network interface (your provider's internal network) — never on the public IP. If your provider doesn't offer a private network and the database has to traverse the public internet, use WireGuard or Tailscale to tunnel the traffic instead of opening port 5432 to the world.

Step 8: Tune for your VPS size

Postgres ships with extremely conservative defaults — designed to start on a 256 MB Raspberry Pi without crashing, not to perform on a real server. The single most impactful change is shared_buffers, which controls how much RAM Postgres uses for caching disk pages.

# In /etc/postgresql/16/main/postgresql.conf
# Rough rule: shared_buffers = 25% of RAM
shared_buffers = 256MB        # for 1 GB VPS
# shared_buffers = 512MB      # for 2 GB
# shared_buffers = 1GB        # for 4 GB
# shared_buffers = 2GB        # for 8 GB

effective_cache_size = 768MB  # ~75% of RAM (estimate, not allocated)
work_mem = 16MB               # per-operation; small VPS keep low
maintenance_work_mem = 64MB
random_page_cost = 1.1        # NVMe storage; default 4.0 assumes spinning disks

max_connections = 50          # most apps don't need 100; use a pooler for more

That last setting matters more than people realize. Each Postgres connection is a full process with its own memory; 100 idle connections at 10 MB each is a gigabyte of RAM gone for nothing. If your application opens lots of connections, run pgBouncer in front of Postgres rather than raising max_connections.

Step 9: Set up automatic backups

The shortest possible production-grade backup script: pg_dump on cron, compress, push to off-VPS storage. Anything fancier than that you can add later; anything less is irresponsible.

sudo mkdir -p /var/backups/postgres
sudo chown postgres:postgres /var/backups/postgres

# /usr/local/bin/backup-postgres.sh
sudo tee /usr/local/bin/backup-postgres.sh > /dev/null <<'EOF'
#!/bin/bash
set -e
TS=$(date +%Y%m%d-%H%M%S)
DEST=/var/backups/postgres
sudo -u postgres pg_dumpall | gzip > "$DEST/pg-$TS.sql.gz"
# Keep last 14 days
find "$DEST" -name 'pg-*.sql.gz' -mtime +14 -delete
EOF
sudo chmod +x /usr/local/bin/backup-postgres.sh

# Add to root's crontab — runs at 03:15 every day
echo "15 3 * * * /usr/local/bin/backup-postgres.sh" | sudo crontab -

Then push /var/backups/postgres off the VPS — to S3, Backblaze B2, another OliveVPS instance in a different region, your home NAS, anywhere that isn't the same machine. A backup that lives only on the database server is not a backup.

Common issues and fixes

FATAL: password authentication failed for user "appuser" — almost always a pg_hba.conf issue, not a wrong password. Check the order of rules; the first matching line wins, and a wide local all all peer earlier in the file will override your specific user rule. Tail the Postgres log with sudo tail -f /var/log/postgresql/postgresql-16-main.log while you connect — it tells you exactly which line matched.

could not connect to server: Connection refused — Postgres isn't listening where you're connecting. Run sudo ss -tlnp | grep postgres. If it's only on 127.0.0.1 and you're connecting remotely, that's the issue. Also check listen_addresses in postgresql.conf matches what you expect.

SSL connection error from client — your client is trying to verify a self-signed cert. Either install a real Let's Encrypt cert (Step 6) or set sslmode=require instead of verify-full in your client connection string. Don't disable SSL just to make the error go away.

Performance feels bad on small VPS — check shared_buffers first. The default 128 MB is too low for any real workload. Then check random_page_cost — if you're on NVMe and it's still set to 4.0, you're telling the planner to avoid index scans for no reason.

FAQ