--- name: bnna-infra-postgres-setup version: 1.4.0 description: Set up a shared infrastructure Postgres CT on the global services network (vsvc174 / 172.24.0.0/24). Use when creating a Postgres instance that must be reachable from all tenant VLANs. Covers second NIC on vsvc174, switch VLAN trunking, gateway/routing, TLS via ACME DNS-01, cross-vnet connectivity verification, and tenant connection strings. Network is shared between tenants — TLS required. depends: [bnna-postgres-setup] --- # Infra Postgres Setup (BNNA) Sets up a shared Postgres CT on the global services network so all tenant VLANs can reach it via a private address. Requires `bnna-postgres-setup` for the base Postgres install. ## Overview Shared infrastructure (Postgres, internal APIs) lives on the **global services network** (`vsvc174` / VLAN 174 / `172.24.0.0/24`). Tenant networks (`10.11.x.x`) route to `172.24.0.x` via OPNsense. There is no inter-tenant routing — only tenant → global services. MUST: The global services network is **shared between tenants** — it is NOT a private/trusted network. All connections (especially Postgres) MUST use TLS. Use ACME with DNS-01 challenge for certs (no public reachability needed). ## 1. Provision the CT Use `bn-create` with the bnna provisioning env (`~/.config/proxmox-sh/bnna-dev.env`). The CT gets its primary NIC on the bnna account network (`v1110` / `10.11.10.x`). The global services NIC is added as a second interface after creation. ## 2. Run base Postgres setup Follow the `bnna-postgres-setup` skill — run `scripts/pg-setup-alpine.sh` as the `app` user. This installs postgres on port 5432 with tcpfwd forwarding 15432 → :5432. ```sh # VERIFY: postgres is listening on 5432 psql -p 5432 -U postgres -c 'SELECT 1 AS ok' # VERIFY: tcpfwd forwarding 15432 → :5432 netstat -tlnp | grep 15432 ``` ## 3. Switch VLAN trunking + OPNsense firewall Follow `docs/new-vlan-checklist.md` steps 2-4. The key steps for vsvc174: 1. **MikroTik switches** — add VLAN 174 to trunk ports on both sw1 and sw2 (see `bnna-infra-mikrotik-vlan` skill) 2. **OPNsense firewall** — add pass rules on the VLAN 174 interface (floating rules alone are NOT sufficient) 3. **TLS router** — trunk VLAN 174 to the TLS router's switch port if `tls-172-24-0-x` direct IP domains are needed NEVER: Skip the switch step. Proxmox SDN will show the bridge as UP and the veth as attached, but L2 frames won't reach the router. Symptoms: ping from the router to the CT's vsvc174 IP shows 100% packet loss. ## 4. Add second NIC on vsvc174 Add `net1` on the global services bridge (`vsvc174`) via the Proxmox API. The vsvc174 gateway (`172.24.0.1`) should be the **default gateway** — it has broader network access (routes to all tenant VLANs). Remove the gateway from net0 (v1110) — it only needs local /24 reachability. ``` PUT /nodes/{node}/lxc/{vmid}/config net0=name=eth0,bridge=v1110,ip=10.11.10.{host}/24,type=veth net1=name=eth1,bridge=vsvc174,ip=172.24.0.{host}/24,gw=172.24.0.1,type=veth ``` - MUST: IP addresses start at `.21` (.1-.20 reserved for gateways/firewalls/proxies) - MUST: Default gateway on net1 (vsvc174), NOT net0 (v1110) - MUST: Cold reboot (`POST /api/instances/{vmid}/reboot?cold=true`) after config change ### Expected route table ``` default via 172.24.0.1 dev eth1 metric 1 onlink 10.11.10.0/24 dev eth0 scope link src 10.11.10.21 172.24.0.0/24 dev eth1 scope link src 172.24.0.21 ``` ### IP assignment | CT | VMID | vsvc174 IP | |----|------|------------| | pg-svc1 | 1110021 | 172.24.0.21 | | mariadb-svc1 | 1110022 | 172.24.0.22 | PREFER: Track assigned IPs in `LOCAL.md` under the CT's section. ## 5. OPNsense firewall rules OPNsense blocks all traffic on an interface by default. Add firewall rules on the VLAN 174 interface to allow inbound traffic from tenant VLANs. Floating rules alone are not sufficient — interface-level rules are required. ## 6. Set up TLS The global services network (172.24.0.0/24) is shared between tenants — Postgres MUST use TLS for connections on that network. ### Self-signed (initial setup) Generate a self-signed cert to get `hostssl` working immediately: ```sh openssl req -new -x509 -days 3650 -nodes -text \ -out ~/.local/share/postgres/var/server.crt \ -keyout ~/.local/share/postgres/var/server.key \ -subj "/CN=pg-svc1" chmod 600 ~/.local/share/postgres/var/server.key ``` Enable SSL in `postgresql.conf`: ``` ssl = on ``` The default `ssl_cert_file` and `ssl_key_file` paths (`server.crt` / `server.key` relative to the data dir) match where we placed the files. Restart Postgres. ### ACME (production — replace self-signed) Use ACME with DNS-01 validation for a proper cert (no public reachability needed). Use domainpanel or acme.sh with Cloudflare DNS-01: ```sh acme.sh --issue --dns dns_cf -d pg-svc1.m.bnna.net ``` Update `ssl_cert_file` / `ssl_key_file` in postgresql.conf to point at the ACME cert, then reload Postgres. acme.sh handles renewal via cron. MUST: Use `hostssl` in pg_hba for 172.24.0.0/24. Never use `host` — the network is shared between tenants. ## 7. Create admin role Use `pg-addadmin` (from pg-essentials) to create the remote admin role: ```sh pg-addadmin bnna_admin --port 5432 \ --host-networks 10.11.10.0/24 \ --hostssl-networks 172.24.0.0/24 ``` This creates: - An `admin_users` group (if missing) - pg_hba: `hostssl all +admin_users 172.24.0.0/24` — shared network, TLS at pg level - pg_hba: `host all +admin_users 10.11.10.0/24` — via TLS router (TLS terminated externally) - A named admin role with CREATEDB + CREATEROLE (with random suffix, e.g. `bnna_admin_84e31d`) Only the local v1110 subnet (10.11.10.0/24) gets a `host` entry — the TLS router lives on this subnet and terminates TLS before forwarding. Other tenant 10.x networks cannot reach the CT's eth0 directly (VLAN isolation). The credentials become the `admin_dsn` for this pg_instance in bnna. ## 8. Verify cross-vnet connectivity From a tenant CT on a different VLAN (e.g. `10.11.8.x` or `10.11.4.x`): ```sh # Ping the global services address ping -c 3 172.24.0.21 # Test Postgres connection (port 15432 forwarded to :5432) psql "postgres://:@172.24.0.21:15432/?sslmode=require" -c "SELECT 1 AS ok;" ``` If ping fails, check in order: 1. Switch VLAN trunking (step 3) 2. OPNsense firewall rules (step 5) 3. OPNsense routing — tenant VLANs must route to `172.24.0.0/24` via their gateway NEVER: Assume connectivity works without testing from an actual tenant CT. ## 9. Tenant connection strings Tenants connect via the global services address, not the primary NIC: ``` postgres://:@172.24.0.{host}:15432/?sslmode=require ``` External access (dev/admin) still uses the TLS router via the primary NIC: ``` postgres://:@tls-.a.bnna.net:443/?sslmode=require&sslnegotiation=direct ``` ## Related skills - `bnna-postgres-setup` — base Postgres install (prerequisite) - `bnna-infra-mikrotik-vlan` — MikroTik switch VLAN trunking - `bnna-tls-router` — TLS router port/ALPN reference - `use-bnna-api` — CT lifecycle via the bnna API - `docs/new-vlan-checklist.md` — full new-VLAN checklist (SDN, switch, OPNsense, TLS router)