Add Kamatera pg_hba.conf access runbook for the sync tool

51ed27a9ac4192c15623952619b704518ffbc471 · 2026-06-30 12:56:11 -0700 · Steve Abrams

Files touched

A KAMATERA-DB-ACCESS.md

Diff

commit 51ed27a9ac4192c15623952619b704518ffbc471
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jun 30 12:56:11 2026 -0700

    Add Kamatera pg_hba.conf access runbook for the sync tool
---
 KAMATERA-DB-ACCESS.md | 37 +++++++++++++++++++++++++++++++++++++
 1 file changed, 37 insertions(+)

diff --git a/KAMATERA-DB-ACCESS.md b/KAMATERA-DB-ACCESS.md
new file mode 100644
index 0000000..dfcbc1f
--- /dev/null
+++ b/KAMATERA-DB-ACCESS.md
@@ -0,0 +1,37 @@
+# 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).

← 9dd39cf Option C: parameterized vendor_registry write-back to canoni · back to Dw Vendor Registry Sync · (newest)