# SEC-P1 — DB least privilege (app role vs. migration/owner role)

> Companion to `docs/project/ops/db-audit-2026-07-07.md` (finding **SEC-P1**).
> Status: tooling + code ready on `dev`; **role creation and env cutover in
> prod are manual, sudo/DB-owner-gated ops** — not done by this doc.

## Problem

The app connects to Postgres as the **superuser** (`humperdoo` in dev,
confirmed via `.env.local`; unverified but presumed similar in prod), with
`BYPASSRLS`, no TLS, no password (trust-auth over loopback). Runtime queries
and schema migrations share one all-powerful role. Any SQL-adjacent bug (or
the MCP surface, or a future dependency compromise) becomes full DB
compromise: cross-tenant reads bypassing any RLS ever added, `COPY TO
PROGRAM` (OS command execution), unrestricted DDL/TRUNCATE.

## Target shape

Two roles, two connection strings:

| Role | Used by | Flags | Env var |
|---|---|---|---|
| owner (existing, e.g. `humperdoo` in dev / whatever prod uses today) | migrations only | superuser or at least `CREATEDB`-class, unchanged | `MIGRATION_DATABASE_URL` |
| `seo_insight_app` (new) | Next.js runtime (web service) | `NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS LOGIN`, `SELECT/INSERT/UPDATE/DELETE` only, no `TRUNCATE`, no DDL | `DATABASE_URL` |

Dev/test are **not touched** — see "Dev/test stays as-is" below.

## What's shipped on `dev`

1. **`scripts/db/create-app-role.sql`** — idempotent role + grants. Creates
   `seo_insight_app`, grants `CONNECT` on the DB, `USAGE` on schema `public`,
   `SELECT/INSERT/UPDATE/DELETE` on all existing tables, `USAGE` on all
   sequences, and `ALTER DEFAULT PRIVILEGES` so tables created by *future*
   migrations are automatically visible too (no re-grant per migration).
   Must be run **connected as the owner/migration role** — see the script's
   header comment for why (`ALTER DEFAULT PRIVILEGES` with no `FOR ROLE`
   binds to `current_user`).
2. **`src/env.ts`** — new optional `MIGRATION_DATABASE_URL`. Unset → nothing
   changes.
3. **`src/lib/db/migrate.ts`** — now opens its **own** one-off connection
   (`max: 1`, not the shared pool from `client.ts`) using
   `MIGRATION_DATABASE_URL ?? DATABASE_URL`. Unset → resolves to
   `DATABASE_URL`, i.e. today's exact behavior.
4. **`src/lib/db/client.ts`** — no code change (see "TLS" below); added a
   comment documenting why.

### Grant matrix

| Object | seo_insight_app gets | Rationale |
|---|---|---|
| Database | `CONNECT` | Needed to open a session at all. |
| Schema `public` | `USAGE` | Needed to resolve any object name in it. |
| Existing tables | `SELECT, INSERT, UPDATE, DELETE` | Full CRUD, the app's actual need. |
| Existing tables | **no** `TRUNCATE` | Bulk wipe stays owner-only (seed/backfill scripts must run with `MIGRATION_DATABASE_URL` in prod). |
| Existing tables | **no** DDL (`CREATE/ALTER/DROP`) | Schema changes stay exclusively on migrations. |
| Sequences | `USAGE` | `nextval()`/`currval()` for serial/identity PKs via inserts — nothing else. |
| Future tables (via `ALTER DEFAULT PRIVILEGES`) | same as existing tables | So `bun run db:migrate` (owner role) never needs a manual re-grant afterwards. |
| Row-level security | N/A today (no RLS policies exist yet) | `NOBYPASSRLS` makes the role RLS-actually-binds-to it, so SEC-P2 (RLS defense-in-depth, noted in the audit as the next step) has a real backstop the moment policies are added. |

### TLS

No code change to `src/lib/db/client.ts`. `postgres.js` parses `?sslmode=`
off the connection string itself (`parseOptions`, before it applies the
`ssl: false` default) — confirmed by reading
`node_modules/postgres/cjs/src/index.js`. That means:

- **Prod**: put `?sslmode=verify-full` on `DATABASE_URL` (and
  `MIGRATION_DATABASE_URL`) → TLS with cert verification, zero code change.
- **Dev/local loopback**: no `sslmode` param in `.env.local`'s
  `DATABASE_URL` → plaintext, exactly as today. Nothing to break.

If `verify-full` turns out to need a CA bundle Postgres 18's default cert
doesn't satisfy, fall back to `sslmode=require` (encrypted, no chain
verification) rather than hand-rolling an `ssl: {...}` object in
`client.ts` — keep the "nothing hardcoded" property.

### Dev/test stays as-is

- `.env.local` `DATABASE_URL` is unchanged (`postgresql://humperdoo@localhost:5432/seo_insight_dev`,
  trust-auth, no password) — only comments were added explaining the two-URL
  split for prod.
- `MIGRATION_DATABASE_URL` is not set anywhere in dev/test → `migrate.ts`'s
  `??` fallback resolves to `DATABASE_URL`, i.e. the same single connection
  string dev has always used, for both migrations and runtime.
- `bun dev`, `bun run db:migrate`, `bun run db:migrate:test`, `bun run
  test:all` — none of these reference `MIGRATION_DATABASE_URL`; behavior is
  byte-for-byte unchanged. (Verified: `grep -rn MIGRATION_DATABASE_URL`
  outside the three files touched here returns nothing.)

## Prod runbook

### 0. Prerequisites

- SSH access + the DB owner role's credentials (or run as the `postgres`
  superuser locally on the box — `psql` over the unix socket needs no
  password there today per `serveur-prod.md`).
- Confirm current prod `DATABASE_URL` in `/data/seo-insight/.env.production`
  (600, `seoinsight`-owned — not readable by the deploying admin user; ask
  whoever has it, or run the verification query from the DB host as
  superuser).

### 1. Create the role

```bash
# From the DB host (or anywhere with network access to :5432 — currently
# loopback-only, so this means the VPS itself).
PW="$(openssl rand -base64 24)"
psql "$MIGRATION_DATABASE_URL" -v pw="$PW" \
  -f /data/seo-insight/scripts/db/create-app-role.sql

# Save $PW to a password manager NOW — it is not stored anywhere, and the
# script above prints nothing back. Losing it just means re-running the
# script with a new password (idempotent — see script header).
```

`$MIGRATION_DATABASE_URL` here = the **current** prod `DATABASE_URL` (the
existing owner role), since the app role doesn't exist yet.

### 2. Build the two prod connection strings

```
# Owner role — unchanged from today's DATABASE_URL, renamed:
MIGRATION_DATABASE_URL=postgresql://<owner_user>:<owner_pw>@127.0.0.1:5432/seo_insight_prod?sslmode=verify-full

# New app role — DML only:
DATABASE_URL=postgresql://seo_insight_app:<PW from step 1>@127.0.0.1:5432/seo_insight_prod?sslmode=verify-full
```

Both point at the same DB over the same loopback socket Postgres already
listens on (`serveur-prod.md`: `postgresql@18-main.service` on
`127.0.0.1:5432`). `sslmode=verify-full` over loopback needs Postgres
configured with a server cert `libpq` can validate (typically `hostssl` +
`ssl_cert_file`/`ssl_key_file` in `postgresql.conf`, currently unconfirmed
for this box) — **check `ssl = on` and the cert chain are set up in
`postgresql.conf` before flipping `sslmode`, or connections will simply
fail to establish.** If TLS isn't provisioned yet, ship the role separation
first with `sslmode=require` (or no sslmode) and land TLS as a fast-follow —
don't block least-privilege on it.

### 3. Edit `.env.production`

Add `MIGRATION_DATABASE_URL` alongside the existing `DATABASE_URL`, then
flip `DATABASE_URL` to the app-role string from step 2. Do this **in the
same edit**, not as two separate deploys — see ordering below for why.

### 4. Deploy — ordering matters

`deploy/deploy.sh` already does the right thing **unmodified**: it sources
`.env.production` with `set -a` (line 22-27) before running `bun run
db:migrate` (line 56), so once `MIGRATION_DATABASE_URL` is in that file, the
migration step picks it up automatically via `src/env.ts` →
`src/lib/db/migrate.ts`'s `MIGRATION_DATABASE_URL ?? DATABASE_URL`. No
`deploy.sh` code change needed.

The step order inside `deploy.sh` (migrate → build → restart, lines 55-66)
already gives the safe sequence **as long as both env vars are in
`.env.production` before you run it**:

1. `bun run db:migrate` runs with `MIGRATION_DATABASE_URL` (owner role) —
   schema changes land under the privileged role, exactly as before.
2. `bun run build` + `systemctl restart seo-insight-web` — the freshly
   restarted process reads `DATABASE_URL` (now the app role) for all runtime
   queries.

So: **land both env vars in `.env.production` first, then run
`./deploy/deploy.sh`** (or just restart the service if no code changes are
pending — the role cutover alone only needs a service restart, no rebuild).
Do **not** flip `DATABASE_URL` to the app role while the currently-running
process is still using the old superuser session for anything — connections
are per-process-restart, so a live-reload/HMR-free `systemctl restart
seo-insight-web` is sufficient after the env edit.

### 5. Verify

```bash
# App role can read/write, cannot DDL/TRUNCATE:
journalctl -u seo-insight-web -n 50   # boots clean, no permission-denied on startup queries
psql "$DATABASE_URL" -c "select current_user, current_setting('is_superuser');"  # → seo_insight_app, off
psql "$DATABASE_URL" -c "truncate sites;"    # expect: ERROR permission denied
psql "$DATABASE_URL" -c "create table t(x int);"  # expect: ERROR permission denied
```

Also smoke-test one real login/dashboard load in prod to confirm no query
path silently depended on superuser (e.g. `pg_stat_*` introspection,
`COPY`, or an extension-owner-only function) — none identified in the
current audit, but this is unverified for prod specifically.

### 6. Rollback

Instant and low-risk: put the old `DATABASE_URL` (owner role) back in
`.env.production`, `systemctl restart seo-insight-web`. The `seo_insight_app`
role and its grants are harmless to leave in place — nothing depends on
deleting it. No migration/data changes are involved in this whole change, so
there is no forward-only state to unwind.

### 7. Follow-ups noted, not done here

- **SEC-P2** (same audit): once this role split is live, add RLS policies
  (`USING (org_id = current_setting('app.current_org_id'))`) as
  defense-in-depth — `NOBYPASSRLS` on `seo_insight_app` is what makes that
  actually bind.
- Confirm `postgresql.conf` TLS provisioning on the prod box before
  requiring `sslmode=verify-full` (step 2 caveat above).
- `seed.ts` / backfill scripts (`scripts/backfill-*.ts`) must be invoked with
  `MIGRATION_DATABASE_URL` in prod if ever run there — they call
  `db.delete(...)` (see `SEC-P4` in the audit), which the app role can do
  today (DELETE is granted) but should arguably run under stricter review
  regardless of role.