---
name: bnna-postgres-setup
version: 1.6.0
description: Set up PostgreSQL on an Alpine LXC via webi. Use when installing postgres on a bnna Alpine CT, configuring port 5432 with tcpfwd forwarding 15432 (TLS router), registering as boot services, creating remote_users group, or creating tenant databases. Covers webi install, serviceman/OpenRC, pg-essentials, connection strings.
---

# Setup Postgres (BNNA Alpine)

## Ports

- PostgreSQL listens on its default port **5432** — no config changes needed.
- **tcpfwd** forwards port **15432** (= 10000 + 5432) to `<service-ip>:5432` for the TLS router.

## Quick Start

Run the setup script as the `app` user (bnna template default):

```sh
sh scripts/pg-setup-alpine.sh
```

The script handles everything: system deps, webi install, config, serviceman
registration, tcpfwd setup, and remote_users group. See below for what each step does.

```sh
# VERIFY: postgres is listening on 5432
psql -p 5432 -U postgres -c 'SELECT 1 AS ok'

# VERIFY: tcpfwd forwarding 15432 → <service-ip>:5432
netstat -tlnp | grep 15432
```

## What the Script Does

1. `sudo apk add` — tzdata, icu-data-full, openssl, logrotate
2. `webi postgres pg-essentials serviceman` — installs postgres, helper scripts, and service manager
3. `psql-store-credential` — saves the default `postgres:postgres` credential to `~/.pgpass`
4. Appends to `postgresql.conf` — `listen_addresses = '*'` (port stays at default 5432)
5. `serviceman add postgres` — registers postgres as an OpenRC boot service (also configures logrotate)
6. Installs tcpfwd and registers `postgres-fwd` service to forward 15432 → <service-ip>:5432
7. `pg-addgroup host remote_users 5432` — creates the remote_users role and pg_hba entry for LAN access

## pg-essentials Commands

| Command | Purpose |
|---------|---------|
| `pg-register-service` | Register postgres as a boot service via serviceman |
| `pg-addgroup` | Create a group role (e.g. `remote_users`) for TLS/SNI remote access |
| `pg-adduser` | Create a database + role with generated password, member of remote_users |
| `pg-passwd` | Rotate a user's password to a new random value |
| `psql-store-credential` | Parse a Postgres URL and store it in `~/.pgpass` |
| `psql-example-connect` | Print a connection alias template using credentials from `~/.pgpass` |
| `psql-backup` | 3-way SQL dump: schema+drops, schema only, data only |

## Creating Databases

Use `pg-adduser` to create a database and role with the same name:

```sh
pg-adduser bnna 5432
pg-adduser webi 5432
```

This creates:
- Role `<name>` with a generated password, member of `remote_users`
- Database `<name>` owned by `<name>`

Uses underscores, not hyphens (e.g. `foo_app` not `foo-app`). `pg-adduser`
appends a random suffix to prevent collisions between tenants. Save the
password — it's shown once.

## Debian LXC + systemd: mmap IPC

Debian LXC containers running systemd cannot use the default POSIX shared
memory (`shm_open` / `/dev/shm`). PostgreSQL must be configured to use `mmap`
instead:

```ini
dynamic_shared_memory_type = mmap
```

`pg-setup-debian.sh` adds this automatically. Without it, PostgreSQL will fail
to start in a Debian LXC.

## Connection String Format

Direct connection (within the same VLAN):
```
postgres://<dbname>:<password>@<container-ip>:5432/<dbname>?sslmode=disable
```

Via tcpfwd (TLS router port):
```
postgres://<dbname>:<password>@<container-ip>:15432/<dbname>?sslmode=disable
```

External via TLS router (ALPN `postgresql` on port 443 → container port 15432):
```
postgres://<dbname>:<password>@tls-<ip-dashes>.a.bnna.net:443/<dbname>?sslmode=require&sslnegotiation=direct
```
Port 443 is required — psql defaults to 5432, but the TLS router listens on 443.
`sslnegotiation=direct` is required: the TLS router expects TLS immediately
(like HTTPS/ALPN), not PostgreSQL's traditional SSL upgrade handshake.

For cross-VLAN connection strings (infra/vsvc174), see `bnna-infra-postgres-setup`.