Massed Compute

Provision Your VM

Launch a new VM in the Massed Compute dashboard or via API:

• Image: Ubuntu Server 24.04 LTS
• Product: cpu_mini_amd_epyc or larger CPU instance
• Region: Any region with available capacity
• SSH key: Attach a key you can access from your workstation

Wait until the VM status shows Running and SSH is reachable on port 22.

Set Database Credentials

Define your database configuration. Replace these placeholders with your actual values:

• YOUR_SSH_USER: SSH username (usually ubuntu)
• YOUR_VM_IP: Public IP of your VM
• YOUR_SSH_KEY_PATH: Path to your SSH private key
• your_app_db: Database name for your application
• app_user: Non-superuser database role
• secure_password_16_chars: Strong password (16+ characters)

Install and Configure PostgreSQL

Run this command from your workstation to install PostgreSQL with security hardening:

ssh -i YOUR_SSH_KEY_PATH YOUR_SSH_USER@YOUR_VM_IP 'bash -s' <<'REMOTE'
set -euo pipefail

DB_NAME='your_app_db'
DB_USER='app_user'
DB_PASSWORD='secure_password_16_chars'

export DEBIAN_FRONTEND=noninteractive
sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib ufw

# Configure UFW firewall
sudo ufw allow OpenSSH
sudo ufw --force enable

# Configure PostgreSQL for localhost-only access
PG_CONF=$(sudo -u postgres psql -Atc "SHOW config_file;")
sudo sed -i "s/^#\?listen_addresses.*/listen_addresses = 'localhost'/" "$PG_CONF"
sudo systemctl enable --now postgresql
sudo systemctl restart postgresql

echo POSTGRESQL_INSTALL_OK
REMOTE

Create Database and User

Set up your application database with a least-privilege user role:

ssh -i YOUR_SSH_KEY_PATH YOUR_SSH_USER@YOUR_VM_IP 'bash -s' <<'REMOTE'
set -euo pipefail

DB_NAME='your_app_db'
DB_USER='app_user'
DB_PASSWORD='secure_password_16_chars'

sudo -u postgres psql <-- Create application role
DO \$\$ BEGIN
  CREATE ROLE $DB_USER LOGIN PASSWORD '$DB_PASSWORD';
EXCEPTION WHEN duplicate_object THEN
  ALTER ROLE $DB_USER WITH LOGIN PASSWORD '$DB_PASSWORD';
END \$\$;

-- Create application database
SELECT 'CREATE DATABASE $DB_NAME OWNER $DB_USER'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DB_NAME')\gexec

-- Revoke public access and grant to app user
REVOKE ALL ON DATABASE $DB_NAME FROM PUBLIC;
\c $DB_NAME
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO $DB_USER;
SQL

echo DATABASE_SETUP_OK
REMOTE

Verify Installation

Test the database connection and security configuration:

ssh -i YOUR_SSH_KEY_PATH YOUR_SSH_USER@YOUR_VM_IP 'bash -s' <<'REMOTE'
set -euo pipefail

DB_NAME='your_app_db'
DB_USER='app_user'
DB_PASSWORD='secure_password_16_chars'

# Test application database connection
PGPASSWORD="$DB_PASSWORD" psql -h 127.0.0.1 -U "$DB_USER" -d "$DB_NAME" -c \
  "CREATE TABLE IF NOT EXISTS recipe_smoke(id int primary key);
   INSERT INTO recipe_smoke VALUES (1) ON CONFLICT DO NOTHING;
   SELECT count(*) FROM recipe_smoke;"

# Verify non-superuser role
if sudo -u postgres psql -Atc "SELECT rolsuper FROM pg_roles WHERE rolname='$DB_USER';" | grep -q '^t$'; then
  echo "PostgreSQL app role must not be a superuser" >&2
  exit 1
fi

# Check service and firewall status
sudo systemctl is-active --quiet postgresql
sudo ufw status | grep -q '^Status: active'
sudo ss -ltnp | grep -E '127\.0\.0\.1:5432|\[::1\]:5432'

echo POSTGRESQL_VERIFICATION_OK
REMOTE