How to Set Up PostgreSQL 16 & pgAdmin 4 on Ubuntu VPS (Hardened Guide)

Why PostgreSQL 16 is the Preferred Relational Engine

PostgreSQL has earned its title as the world's most advanced open-source relational database. With version 16, developers enjoy massive enhancements to query parallelism, logical replication, and JSON data processing. In this tutorial, we will install PostgreSQL 16 on Ubuntu 24.04, harden its network configuration, and deploy pgAdmin 4 for visual management.

Step 1: Installing PostgreSQL 16 from Upstream APT Repository

Install the official PostgreSQL Global Development Group (PGDG) repository:

# Import the PostgreSQL signing key
sudo install -d /etc/apt/keyrings
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/keyrings/postgresql.gpg

# Add the repository definition
echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# Install PostgreSQL 16 server and client
sudo apt update
sudo apt install -y postgresql-16 postgresql-contrib-16

Step 2: Securing the Default postgres Superuser

By default, the postgres system user connects via Unix domain sockets without a password. Assign a strong cryptographic password for administrative operations:

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'YOUR_STRONG_POSTGRES_PASSWORD';
\q

Step 3: Creating a Production Database and Application Role

Never connect web applications using the master postgres superuser. Create an isolated database and an unprivileged application user:

sudo -u postgres psql

CREATE DATABASE production_db;
CREATE USER app_user WITH ENCRYPTED PASSWORD 'SECURE_APPLICATION_PASSWORD';
GRANT ALL PRIVILEGES ON DATABASE production_db TO app_user;
\c production_db
GRANT ALL ON SCHEMA public TO app_user;
\q

Step 4: Hardening pg_hba.conf and postgresql.conf

Open /etc/postgresql/16/main/postgresql.conf and ensure PostgreSQL listens only to localhost unless remote access is explicitly required:

listen_addresses = 'localhost'
max_connections = 100
shared_buffers = 2GB  # (Set to 25% of total system RAM)
work_mem = 32MB

Inspect authentication methods in /etc/postgresql/16/main/pg_hba.conf and verify scram-sha-256 is enforced for all password authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

Restart the service to apply changes:

sudo systemctl restart postgresql

Step 5: Automated Daily Backups via Cron

Implement an automated database dump script to safeguard your data:

sudo nano /usr/local/bin/backup-postgres.sh

#!/bin/bash
BACKUP_DIR="/var/backups/postgres"
DATE=$(date +"%Y%m%d_%H%M%S")
mkdir -p "$BACKUP_DIR"
pg_dump -U postgres -F c production_db > "$BACKUP_DIR/db_$DATE.dump"
# Retain last 7 days of backups
find "$BACKUP_DIR" -type f -name "*.dump" -mtime +7 -delete

sudo chmod +x /usr/local/bin/backup-postgres.sh
(crontab -l 2>/dev/null; echo "0 3 * * * /usr/local/bin/backup-postgres.sh") | crontab -

Frequently Asked Questions (FAQ)

How do I connect to PostgreSQL securely from DBeaver on my local laptop?

Never expose port 5432 publicly to the Internet. Instead, configure an SSH Tunnel in DBeaver using your remote server IP and SSH key; DBeaver forwards traffic securely through SSH to localhost:5432.