--- name: bnna-infra-mariadb-setup version: 1.0.0 description: Set up a shared infrastructure MariaDB CT on the global services network (vsvc174 / 172.24.0.0/24). Use when creating a MariaDB 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 — external clients use TLS via the TLS Router. depends: [bnna-mariadb-setup] --- # Infra MariaDB Setup (BNNA) Sets up a shared MariaDB CT on the global services network so all tenant VLANs can reach it via a private address. Requires `bnna-mariadb-setup` for the base MariaDB install. ## Overview Shared infrastructure (MariaDB, 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 MariaDB) 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 MariaDB setup Follow the `bnna-mariadb-setup` skill — run `scripts/maria-setup-alpine.sh` as the `app` user. The script uses `sudo` for privileged operations. This installs MariaDB on port 3306 with tcpfwd forwarding 13306 → :3306. ```sh # VERIFY: mariadb is listening on 3306 sudo mariadb -S /run/mysqld/mysqld.sock -e 'SELECT 1 AS ok' # VERIFY: tcpfwd forwarding 13306 → :3306 netstat -tlnp | grep 13306 ``` ## 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.{host} 172.24.0.0/24 dev eth1 scope link src 172.24.0.{host} ``` ### IP assignment | CT | VMID | vsvc174 IP | |----|------|------------| | pg-svc1 | 1110021 | 172.24.0.21 | | mariadb-svc1 | 1110022 | 172.24.0.22 | ### Static route for TLS router access After moving the default gateway to net1, the TLS router can't reach the CT through the 10.x address without a static route. Add to `/etc/network/interfaces` under the eth0 stanza: ``` up ip route add 10.11.10.0/24 via 10.11.10.1 dev eth0 ``` MUST: Set up cross-CT SSH (pg-svc1 → mariadb-svc1 via 10.11.10.x LAN) BEFORE the cold reboot, since TLS router access may be broken until routes are configured. 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. TLS and access control Encryption in transit is handled by the TLS Router, not at the MariaDB level. See `docs/2026-04-08_mariadb-security-model.md` for the full security model. NEVER: Set `require_secure_transport = ON`. The TLS Router terminates TLS and forwards plain TCP — MariaDB-level TLS enforcement breaks this path. Access control uses user@host grants: - Root: `unix_socket` only (no password, no TCP access) - Admin: `@'172.24.0.%'` and `@'10.11.10.%'` only - Tenant users: `@'{subnet}.%'` derived from instance host IP ### Optional: Self-signed cert for direct admin connections If you want TLS for direct connections (not via TLS Router), add a self-signed cert. This is optional — the vsvc174 network is controlled. ```sh sudo openssl req -new -x509 -days 3650 -nodes -text \ -out /etc/my.cnf.d/server.crt \ -keyout /etc/my.cnf.d/server.key \ -subj "/CN=mariadb-svc1" sudo chown mysql:mysql /etc/my.cnf.d/server.crt /etc/my.cnf.d/server.key sudo chmod 600 /etc/my.cnf.d/server.key ``` Add to `/etc/my.cnf.d/mariadb-server.cnf` under `[mysqld]`: ```ini ssl-cert = /etc/my.cnf.d/server.crt ssl-key = /etc/my.cnf.d/server.key # Do NOT set require_secure_transport — TLS Router needs plain TCP ``` ## 7. Create admin user Root is unix_socket only — no password, no TCP access. Create a separate admin user for remote management: ```sh sudo mariadb -S /run/mysqld/mysqld.sock << SQL CREATE USER IF NOT EXISTS 'bnna_admin'@'172.24.0.%' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON *.* TO 'bnna_admin'@'172.24.0.%' WITH GRANT OPTION; CREATE USER IF NOT EXISTS 'bnna_admin'@'10.11.10.%' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON *.* TO 'bnna_admin'@'10.11.10.%' WITH GRANT OPTION; FLUSH PRIVILEGES; SQL ``` - `172.24.0.%` — shared network access (TLS Router + direct) - `10.11.10.%` — v1110 subnet access (private SDN) NEVER: Create admin users for `@'localhost'` or `@'127.0.0.1'`. This prevents exploitation via SSH port-forwarding tunnels by restricted users. The credentials become the `admin_dsn` for this mariadb instance in bnna: ``` bnna_admin:@tcp(172.24.0.{host}:13306)/ ``` ## 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.{host} # Test MariaDB connection (port 13306 forwarded to :3306) mariadb -h 172.24.0.{host} -P 13306 -u -p'' -e "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: ``` mysql://:@172.24.0.{host}:13306/ ``` External access (dev/admin) via TLS router: ``` mysql://:@tls-.a.bnna.net:443/ ``` ## Related Skills - `bnna-mariadb-setup` — base MariaDB 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)