`KAMATERA-DB-ACCESS.md`

38 lines
# One-time Kamatera Postgres access for the sync tool

`node sync.js --apply` reached Kamatera but was rejected:
> FATAL: no pg_hba.conf entry for host "100.65.187.120", user "dw_admin", database "dw_unified", no encryption

Cause: Postgres is listening on the tailnet and the DSN is correct, but `pg_hba.conf`
has no rule permitting this Mac's tailnet IP for `dw_admin`→`dw_unified`. Tailscale
already encrypts the link (WireGuard), so a plain `host` line is sufficient + secure.

## Fix — run on Kamatera (root). Paste each `!` block in-session, or run on the box.

**1. Locate the hba file and see how existing remote entries authenticate (match their method):**
```
! ssh my-server 'F=$(sudo -u postgres psql -tAc "SHOW hba_file;"); echo "$F"; sudo grep -nE "^host(ssl)?\s+(all|dw_unified)\s+dw_admin" "$F"'
```

**2. Append the rule (least-privilege: this Mac''s tailnet /32). Use `scram-sha-256`,
   or `md5` if step 1 shows your other entries use md5:**
```
! ssh my-server 'F=$(sudo -u postgres psql -tAc "SHOW hba_file;"); echo "host    dw_unified    dw_admin    100.65.187.120/32    scram-sha-256    # Mac2 sync tool (Steve 2026-06-30)" | sudo tee -a "$F"'
```
   *(Broader option — allow any of your tailnet devices instead of just this Mac:
   replace `100.65.187.120/32` with the Tailscale CGNAT range `100.64.0.0/10`.)*

**3. Reload Postgres (config reload, NOT a restart — zero downtime):**
```
! ssh my-server 'sudo -u postgres psql -tAc "SELECT pg_reload_conf();"'
```

**4. Re-test the sync:**
```
! cd ~/Projects/dw-vendor-registry-sync && node sync.js --vendors DWCQ-,DWXL-,DWAL-,DWOI- --apply
```

If step 4 still says "no encryption", your server only has `hostssl` lines for remote —
either add the line as `hostssl` (same fields) and set `REMOTE_DATABASE_URL=…?sslmode=require`
in `.env`, or keep the plain `host` line above (fine over Tailscale).