Skip to main content

Reaching a VPS-hosted Postgres from developer machines

For teams where Postgres runs on a VPS (see Deploying to a VPS) but app services still run locally on developer machines — bun --filter '@campus/api' dev against a remote database instead of the full stack being deployed together.

Why WireGuard, not a public database port​

Exposing Postgres directly on the VPS's public IP — even behind a firewall allowlist and TLS — is still a database port facing the entire internet, one misconfiguration away from a real problem. WireGuard instead means the only thing ever exposed publicly is the WireGuard UDP port itself, which has no database attack surface at all — a much smaller, better-audited thing to leave open than Postgres. Once connected, the tunnel behaves like a private network between your machine and the VPS; Postgres is bound only to that tunnel's address and is never listening on the public interface, regardless of firewall state.

This is a split tunnel — connecting only routes traffic for the tunnel subnet (10.8.0.0/24) through the VPS, not all of your internet traffic. It's for reaching the database, not a general VPN.

1. Server setup (once, on the VPS)​

git clone <repo-url> && cd ConnectedCampus   # if not already checked out there
./infra/wireguard/setup-server.sh

Works as root or as a non-root user with sudo — every privileged command inside the script is already sudo-prefixed, which is a harmless no-op when you're already root. On a VPS where root is the only account (common for a single-tenant box), just run it as root directly.

This installs wireguard-tools, generates the server keypair, writes /etc/wireguard/wg0.conf (interface address 10.8.0.1/24, listening on UDP 51820), enables wg-quick@wg0 on boot, and opens UDP 51820 through ufw if present. It detects and saves this VPS's public IP for use in client configs — check /etc/wireguard/public_endpoint is correct if the VPS sits behind a load balancer or NAT.

Then bind Postgres to the tunnel interface only, so it's never on the public interface at all. On the production VPS deployment (docker-compose.prod.yml), the postgres service deliberately has no ports: entry at all — it's reachable only from other containers on the backend network, by design (see that file's own comment). To add tunnel access without weakening that default, use the dedicated override, docker-compose.prod.wireguard.yml, instead of editing the base file:

# in .env on the VPS
POSTGRES_BIND_ADDR=10.8.0.1
docker compose -f docker-compose.prod.yml -f docker-compose.prod.nginx.yml \
  -f docker-compose.prod.wireguard.yml up -d postgres

(Include whatever other override files the deployment already uses alongside the base file — docker-compose.prod.nginx.yml above matches the shared-VPS-with-existing-nginx setup documented in Deploying to a VPS; the important part is adding docker-compose.prod.wireguard.yml to the same invocation.) POSTGRES_BIND_ADDR defaults to 127.0.0.1 (safe — publishes nothing new) until set to the tunnel address.

If instead you're running the local dev docker-compose.yml (not the production one) as a shared database for a team, that file already has ${POSTGRES_BIND_ADDR:-0.0.0.0}:55432:5432 built directly into its postgres service — no override file needed there, just set the same .env variable and docker compose up -d postgres.

2. Add each developer machine as a peer​

Run this on the VPS once per device — it's the reusable step for onboarding new machines, not a one-off:

./infra/wireguard/add-peer.sh harsh-laptop

Prints a client .conf (also saved on the VPS at /etc/wireguard/clients/harsh-laptop.conf) — this contains the device's private key, so treat it like any other credential: don't paste it into a chat tool, ticket, or shared doc. Pull it directly to the device instead:

scp root@<vps-ip>:/etc/wireguard/clients/harsh-laptop.conf ~/.wireguard/harsh-laptop.conf
chmod 600 ~/.wireguard/harsh-laptop.conf

If a peer's config was ever pasted somewhere it shouldn't have been, treat that key as compromised and reissue: remove its [Peer] block from wg0.conf and its file under /etc/wireguard/clients/ on the VPS, reload with wg syncconf wg0 <(wg-quick strip wg0), then re-run add-peer.sh with the same name.

3. Client setup, per device​

Linux / WSL2:

sudo apt-get install -y wireguard-tools
sudo wg-quick up ~/.wireguard/harsh-laptop.conf   # wg-quick accepts a direct path; no need to
                                                    # copy into /etc/wireguard/wg0.conf first

Bring it down later with sudo wg-quick down ~/.wireguard/harsh-laptop.conf. Check it actually connected with sudo wg show — a latest handshake line a few seconds old confirms the tunnel is live, not just configured.

Windows / macOS: install the official WireGuard app, then "Import tunnel(s) from file" and select the .conf. Toggle the tunnel on.

Verify the tunnel is actually up (not just configured) — run this from the client:

pg_isready -h 10.8.0.1 -p 55432

This should succeed only while connected. From a machine that is not connected, or against the VPS's public IP directly, the same command should time out — confirming the port genuinely isn't reachable outside the tunnel.

4. Point the app at the remote database​

Once connected, update .env on that machine:

DATABASE_URL=postgres://campus:<password>@10.8.0.1:55432/campus_os
RUNTIME_DATABASE_URL=postgres://campus_app:<password>@10.8.0.1:55432/campus_os

Then the normal sequence: bun run db:migrate (idempotent — creates the schema, RLS policies, and campus_app role the same way it does locally), and bun --filter '@campus/api' dev + curl http://localhost:4000/health as the smoke test that the app actually works against the remote database, not just that Postgres accepts a raw connection.

5. Migrating existing local data to the VPS​

If a local Postgres already has real data (imported org/people/curriculum/attendance, etc.) and the goal is to move it — not start fresh — bring the data over after the schema exists on the VPS:

# 1. On the VPS, with the WireGuard tunnel connected from wherever you're running this:
#    the schema/RLS/roles already exist from `bun run db:migrate` in step 1 above.

# 2. Locally — dump data only (schema already matches from the migrate step, so a full dump
#    would just fight it):
pg_dump --data-only -Fc "$DATABASE_URL" -f local-data.dump

# 3. Restore into the VPS instance, over the tunnel:
pg_restore --data-only -d "postgres://campus:<password>@10.8.0.1:55432/campus_os" local-data.dump

campus is a superuser on both ends, so RLS doesn't block the restore. Spot-check a few row counts against the local database afterward (select count(*) from persons, attendance_records, legacy_records, ...) to confirm nothing was silently dropped.