This guide covers provisioning and securing a DigitalOcean Managed PostgreSQL cluster for the RMM+PSA platform.

Why Managed PostgreSQL?

For a multi-tenant SaaS platform handling customer data:

✅ Automatic daily backups with point-in-time recovery
✅ Automated security patches and version upgrades
✅ Built-in connection pooling (PgBouncer)
✅ High availability and failover
✅ Easy vertical/horizontal scaling
✅ Monitoring dashboard and alerts
✅ No manual backup/restore procedures
✅ Better compliance and audit trail

Cost: Starts at ~$15/mo (1GB RAM, 10GB storage, 1 vCPU)

Provisioning Steps

1. Verify DigitalOcean API Token

Ensure your token has write permissions:

cd devops/digitalocean

bash check_do_token.sh --write-check

2. Provision the Cluster

Default: Sydney region (syd1), 1GB tier, PostgreSQL 16

bash provision_managed_postgres.sh

Custom options:

bash provision_managed_postgres.sh my-cluster-name lon1 db-s-1vcpu-2gb

Available regions: syd1 (Sydney), nyc3 (New York), lon1 (London), sgp1 (Singapore), fra1 (Frankfurt), etc.

Available sizes:

db-s-1vcpu-1gb — $15/mo
db-s-1vcpu-2gb — $30/mo
db-s-2vcpu-4gb — $60/mo

Output: Connection string and credentials saved to devops/digitalocean/db_credentials.txt (chmod 600).

3. Configure Firewall (Allow Your Droplet)

Get your droplet's public IP:

curl -s http://checkip.amazonaws.com

Or use droplet ID directly. Then configure:

# By IP
bash configure_db_firewall.sh <db_cluster_id> 203.0.113.45
 
# By droplet ID (recommended for dynamic IPs)
bash configure_db_firewall.sh <db_cluster_id> droplet:12345678

Replace <db_cluster_id> with the ID from step 2 output.

Security Note: Only the specified IP/droplet can connect. All connections require SSL.

4. Apply Schema and Migrations

From your local machine or the droplet (once firewall is configured):

# Using credentials from .env or db_credentials.txt
bash apply_schema_to_managed_db.sh
 
# Or provide connection string directly
bash apply_schema_to_managed_db.sh "postgresql://user:pass@host:port/db?sslmode=require"

This applies:

init.sql (base schema)
All migrations (contracts, purchase orders, RLS, SLA, tenant settings)

5. Initialize Root Tenant and Admin User

IMPORTANT: Never hardcode passwords. Use the secure initialization script:

bash devops/digitalocean/init_root_tenant.sh

This will prompt you for:

Root tenant name (default: "Root MSP")
Subdomain (default: "root")
Admin email
Admin full name
Admin password (or auto-generate a secure one)

The script will:

✅ Hash the password with bcrypt (10 rounds)
✅ Create or update the root tenant (is_msp=true)
✅ Create or update the admin user
✅ Save credentials to admin_credentials.txt (chmod 600)
✅ Display the login URL and credentials

Security note: Credentials are saved locally in a secure file. Delete after copying to your password manager.

Backend Configuration

Update .env in your backend deployment:

# Old Docker setup (remove/comment these)
# DB_HOST=localhost
# DB_PORT=5432
 
# New Managed DB
DB_HOST=<from-credentials>
DB_PORT=25060
DB_USER=doadmin
DB_PASSWORD=<from-credentials>
DB_NAME=defaultdb
DB_SSL=true  # or DB_SSL_MODE=require
 
# Or use connection string directly
# DATABASE_URL=postgresql://...?sslmode=require

Update backend/services/db.js if needed to enforce SSL:

const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT || 5432,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
});

Restart backend:

systemctl restart rmm-psa-backend # or pm2 restart

Test connection:

curl http://localhost:3000/health # if you have a health endpoint

# or check logs for "Connected to database"

Security Best Practices

Firewall: Only allow your droplet's IP or use droplet ID (updates automatically).
SSL: Always use sslmode=require in connection strings.
Credentials: Store in .env, never commit. Use environment variables in production.
Read-only users: Create separate users for reporting/analytics with restricted permissions.
Backups: Verify automatic backups are enabled in DO dashboard (default: daily).
Monitoring: Set up alerts in DO dashboard for CPU/memory/disk usage.
Connection pooling: Use PgBouncer (built-in) or configure pool size in backend.

User Management

Create Additional Tenants

bash devops/digitalocean/create_tenant.sh

Prompts for:

Tenant name and subdomain
MSP status (yes/no)
Admin email, name, and password

Creates both the tenant and an admin user in one step.

Reset User Password

If a user forgets their password or you need to reset it:

bash devops/digitalocean/reset_user_password.sh user@example.com

Prompts for new password or auto-generates a secure one.

Security best practices:

Always use strong passwords (16+ characters, mix of upper/lower/numbers/symbols)
Use a password manager to store credentials
Rotate admin passwords every 90 days
Enable MFA for all admin accounts (if implemented)
Delete credential files after copying to secure storage

Monitoring and Maintenance

DigitalOcean Dashboard

Metrics: CPU, memory, disk, connections
Query analytics: slow queries, most frequent
Backups: view/restore from point-in-time
Alerts: configure for high CPU, storage, etc.

Recommended Metrics to Monitor

Active connections (should be < pool size)
Query duration (p95, p99)
Disk usage (scale before 80%)
Replication lag (if using read replicas)

Scaling

Vertical (more RAM/CPU):

DO dashboard → Resize (minimal downtime, ~2 min)

Horizontal (read replicas):

DO dashboard → Add standby/read node
Configure backend to route SELECT queries to replicas

Troubleshooting

Cannot connect from droplet

Check firewall rules:
curl -H "Authorization: Bearer $DO_API_TOKEN" \

https://api.digitalocean.com/v2/databases/<cluster_id>/firewall
Verify SSL mode: connection string must include ?sslmode=require
Check droplet's public IP: curl http://checkip.amazonaws.com

Slow queries

Check DO dashboard → Query Analytics
Add indexes for frequently filtered columns
Enable query logging in backend (log slow queries > 200ms)

Out of connections

Check pool size in backend (max: 20 by default)
Ensure connections are released (use finally blocks)
Consider connection pooling (PgBouncer already included)

Cost Optimization

Start small: 1GB tier ($15/mo) handles ~50 concurrent users easily.

Monitor and scale: Upgrade when:

CPU consistently > 80%
Active connections near pool limit
Disk usage > 75%

Use read replicas only when needed: For reporting/analytics with heavy SELECT queries.

Backup and Recovery

Automatic backups: Daily, retained 7 days (configurable to 35 days).

Point-in-time recovery: Restore to any second within retention window.

Manual backup before major changes:

pg_dump "postgresql://..." > backup_$(date +%Y%m%d).sql

Restore:

psql "postgresql://..." < backup_20251104.sql

Migration from Docker Postgres

If you have existing data in Docker:

Dump from Docker:
docker exec rmm_postgres pg_dump -U rmm_user rmm_psa > local_backup.sql
Restore to managed DB:
psql "postgresql://..." < local_backup.sql
Verify data:
psql "postgresql://..." -c "SELECT COUNT(*) FROM tenants;"

psql "postgresql://..." -c "SELECT COUNT(*) FROM users;"
Update backend .env and restart.

Last Updated: 4 November 2025
Status: ✅ Production-ready