# PostgreSQL Security and Access Control

## Current state (post-install default)

After a fresh `initdb`, PostgreSQL ships with:

- One superuser: `postgres` (no password)
- Auth method: `trust` for localhost only
- `listen_addresses = 'localhost'` — only accepts connections from inside the jail
- No remote access — other jails (cms, controlplane) cannot connect

This is fine for initial setup but must be hardened before any remote jail connects.

## Roles and databases

### Role: postgres (superuser)

Keep as-is. Only used for admin from inside the db jail via `su - postgres`.
Never expose this role to remote connections.

Set a password anyway as defense in depth:

```sh
. /home/clawdie/clawdie-ai/.env
sudo jexec db su - postgres -c "psql -c \"ALTER USER postgres WITH PASSWORD '$POSTGRES_ADMIN_PASSWORD';\""
```

### Role: clawdie_app (Clawdie memory)

For the Clawdie agent memory database:

```sh
. /home/clawdie/clawdie-ai/.env
sudo jexec db su - postgres -c "createuser clawdie_app"
sudo jexec db su - postgres -c "psql -c \"ALTER USER clawdie_app WITH PASSWORD '$CLAWDIE_DB_PASSWORD';\""
sudo jexec db su - postgres -c "createdb -O clawdie_app clawdie_memory"
```

### Role: strapi_user (CMS)

For the Strapi content management database:

```sh
. /home/clawdie/clawdie-ai/.env
sudo jexec db su - postgres -c "createuser strapi_user"
sudo jexec db su - postgres -c "psql -c \"ALTER USER strapi_user WITH PASSWORD '$STRAPI_DB_PASSWORD';\""
sudo jexec db su - postgres -c "createdb -O strapi_user strapi_cms"
```

## Network access (pg_hba.conf)

Path inside db jail: `/var/db/postgres/data18/pg_hba.conf`

### Current (default)

```
local   all   all                  trust
host    all   all   127.0.0.1/32   trust
host    all   all   ::1/128        trust
```

### Target

Keep localhost trust for admin. Add password-authenticated remote access
for specific jails only:

```
# local admin — trust (postgres superuser only uses this)
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

# controlplane jail — Clawdie memory
host    clawdie_memory  clawdie_app     192.168.100.100/32      scram-sha-256

# cms jail — Strapi CMS
host    strapi_cms      strapi_user     192.168.100.3/32        scram-sha-256

# deny everything else from the subnet
host    all             all             192.168.100.0/24        reject
```

Rules are evaluated top-to-bottom. The explicit `reject` at the end ensures
no other jail on the subnet can connect.

### Apply changes

```sh
# edit pg_hba.conf inside db jail (use the commands above)
# then reload — no restart needed
sudo jexec db service postgresql reload
```

## Listen addresses (postgresql.conf)

Path inside db jail: `/var/db/postgres/data18/postgresql.conf`

Change from:

```
#listen_addresses = 'localhost'
```

To:

```
listen_addresses = 'localhost, 192.168.100.2'
```

This makes PostgreSQL listen on both localhost (for admin) and the VNET
interface (for remote jails). Do NOT use `'*'` — only bind to known IPs.

Reload after changing:

```sh
sudo jexec db service postgresql reload
```

## Verification

### From inside db jail (should work — trust)

```sh
sudo jexec db su - postgres -c "psql -c 'SELECT 1;'"
```

### From controlplane jail (should work — password)

```sh
sudo jexec controlplane psql -h 192.168.100.2 -U clawdie_app -d clawdie_memory -c "SELECT 1;"
```

Will prompt for password. Use the `clawdie_app` password.

### From cms jail (should work — password)

```sh
sudo jexec cms psql -h 192.168.100.2 -U strapi_user -d strapi_cms -c "SELECT 1;"
```

Will prompt for password. Use the `strapi_user` password.

### From a random jail or host (should be rejected)

```sh
psql -h 192.168.100.2 -U postgres -d postgres -c "SELECT 1;"
```

Should fail with: `FATAL: pg_hba.conf rejects connection`

## Password storage

All database passwords live in `/home/clawdie/clawdie-ai/.env`:

```
POSTGRES_ADMIN_PASSWORD=<generated>
CLAWDIE_DB_PASSWORD=<generated>
STRAPI_DB_PASSWORD=<generated>
```

Generate with: `python3 -c "import secrets; print(secrets.token_urlsafe(24))"`

Source before running setup commands: `. /home/clawdie/clawdie-ai/.env`

When jails need their own `.env`, copy only the relevant variable:
- controlplane: `CLAWDIE_DB_PASSWORD`
- cms: `STRAPI_DB_PASSWORD`

Never commit passwords to git. Never store them in skill files.

## Execution order

1. ZFS snapshot db jail first
2. Set postgres superuser password
3. Create clawdie_app role + clawdie_memory database
4. Create strapi_user role + strapi_cms database
5. Update pg_hba.conf with remote access rules
6. Update listen_addresses in postgresql.conf
7. Reload PostgreSQL
8. Test from inside db jail (trust)
9. Test from controlplane jail (password)
10. Test from cms jail (password — after cms jail exists)
11. ZFS snapshot db jail after successful setup

## ZFS snapshots

```sh
# before security changes (snapshots are non-destructive, no dry-run needed)
sudo zfs snapshot zroot/clawdie-runtime/jails/db@pre-security_YYYY-MM-DD_HH:MM:SS

# after successful setup
sudo zfs snapshot zroot/clawdie-runtime/jails/db@security-configured_YYYY-MM-DD_HH:MM:SS
```
