p24-infra Docs

mongodb-user-isolation

7 min read

MongoDB User Isolation — Playbook

Created: 2026-07-05 (feat/#2728) Scope: MongoDB rs0 (bms-2 PRIMARY / bms-3 SECONDARY / bms-4 arbiter)

Current user map

UserRolesAuth DBCredential DBSOPS fileConsumer
adminrootadminadminbms-servers.env.sops + mongodb-bms.env.sopsmaintenance, backups, p24-status.py
et_operreadWriteadminw4_dbet-operational-platform.env.sopset-operational-platform Vercel
w3_appreadWritew3_dbw3_dbpinbox24-w3.env.sopsv32-prod (bms-1)
w4_appreadWritew4_dbw4_dbpinbox24-w4.env.sopsv42-prod (bms-1)
pinbox_productionreadWritew3_dbw3_db (legacy)n/a — not referenced by any tracked SOPS key (verified 2026-07-11, #3754)unknown / dormant — do not reuse without confirming live usage first
w3_read_v42readw3_dbw3_dbpinbox24-w4.env.sops (V42_v3MongoUrl)v42-prod legacy mongojs compat path (reads W3 data only — .aggregate(), never a write; see #3754)
prometheusclusterMonitoradminadminmongodb-bms.env.sops + n8n-bms4.env.sops (dup)mongodb-exporter on bms-4
sys-admin-aireadWriteAnyDatabase + dbAdminAnyDatabase + clusterMonitor (NOT userAdmin/root)adminadminmongodb-bms.env.sops (MONGODB_SYS_ADMIN_AI_USER/PASSWORD)PLANNED — not yet created (#6857) — AI-worker maintenance path + p24-status.py, replacing shared root admin

Note: prometheus credentials are temporarily in both mongodb-bms.env.sops and n8n-bms4.env.sops. Cleanup tracked in issue #2732.

Scope note (#6857, 2026-09-04): the per-service application users this repo’s isolation plan called for (per-service-credential-isolation-plan.md §3.2 — svc-pinbox24-w3/w4, svc-etop-*) already exist as w3_app / w4_app / w3_read_v42 / et_oper above (delivered by #3754 / #2728 / #2834). The Pinbox24 v32-prod / v42-prod containers on bms-1 and et-op Vercel no longer authenticate as root admin. The only remaining gap from the plan’s Part A is the AI-worker role sys-admin-ai (below) and moving p24-status.py off root admin. The plan’s §3.4 step 5 (“rotate + demote root once no app depends on it”) is therefore already de-risked — the #1716 outage vector (root baked into app containers) is closed; root remains only in maintenance/rotation scripts, which genuinely need userAdmin/root and must keep using it.

Adding a new MongoDB user

Step 1 — Identify PRIMARY

ssh -i ~/.ssh/id_ed25519 root@145.239.133.104 "mongosh admin -u admin -p '\$ADMIN_PW' \
  --authenticationDatabase admin --quiet --eval \"rs.isMaster().primary\""

Step 2 — Create user on PRIMARY

Write a shell script (avoids SSH quoting issues):

#!/bin/bash
mongosh admin \
  -u admin -p "$MONGO_ADMIN_PW" \
  --authenticationDatabase admin \
  --quiet \
  --eval "db.createUser({user:'NEW_USER',pwd:'NEW_PW',roles:[{role:'readWrite',db:'TARGET_DB'}]});"

SCP the script to PRIMARY, execute, delete:

scp -i ~/.ssh/id_ed25519 script.sh root@145.239.133.104:/tmp/create_user.sh
ssh -i ~/.ssh/id_ed25519 root@145.239.133.104 "bash /tmp/create_user.sh && rm /tmp/create_user.sh"

Step 3 — Test connection

ssh -i ~/.ssh/id_ed25519 root@145.239.133.104 \
  "mongosh 'mongodb://NEW_USER:NEW_PW@145.239.133.104:27017/TARGET_DB?authSource=admin&replicaSet=rs0' \
  --eval 'db.collectionName.countDocuments({})'"

Step 4 — Add to SOPS

Add NEW_USER_MONGODB_URI=mongodb://NEW_USER:PW@... to the correct SOPS file. Use explicit dotenv flags:

$env:SOPS_AGE_KEY_FILE = "C:\Users\konar\.age\p24-infra-keys.txt"
# 1. Decrypt to variable (never print)
$plain = sops --decrypt --input-type dotenv --output-type dotenv secrets\<file>.env.sops
# 2. Append new key (value from $env:NEW_VALUE — never hardcoded)
$content = ($plain -join "`n") + "`nNEW_KEY=$env:NEW_VALUE`n"
# 3. WriteAllText (no BOM, LF)
[System.IO.File]::WriteAllText("$PWD\secrets\<file>.env.sops", $content, [System.Text.UTF8Encoding]::new($false))
# 4. Encrypt in-place
sops --encrypt --in-place --input-type dotenv --output-type dotenv secrets\<file>.env.sops
# 5. Canary
sops --decrypt --input-type dotenv --output-type dotenv secrets\<file>.env.sops | Out-Null
$env:NEW_VALUE = ""

Step 5 — Update infrastructure-overview.md

Add row to §MongoDB user map with: user, roles, db, SOPS file, consumer.

Rollback procedure

If a new user causes authentication failures:

  1. Identify affected service in Vercel/PM2 logs
  2. Revert SOPS: restore previous value of the connection URI
  3. Update Vercel env var via API (see Vercel API pattern in secrets-sync.yml)
  4. Trigger service redeploy
  5. The MongoDB user can remain (harmless) while root cause is investigated

Notes on authSource

  • Users created in admin db: authSource=admin in connection string
  • Current convention: create all users in admin db for consistency

Connection string format

mongodb://<user>:<password>@145.239.133.104:27017,51.68.155.224:27017/<database>?authSource=admin&replicaSet=rs0

Generate URL-safe passwords (no special chars that need encoding):

$pw = -join ((65..90)+(97..122)+(48..57) | Get-Random -Count 32 | ForEach-Object { [char]$_ })

Creating the sys-admin-ai worker role + retiring root admin from AI paths (#6857)

This is the execution runbook for the only remaining piece of the per-service isolation plan’s Part A (see the Scope note at the top of this file — the W3/W4/et-op app users already exist). It is a cross-role, human-supervised procedure: a secret-manager step (SOPS write), a sys-admin step (live mongosh on the rs0 PRIMARY), and a live-verify gate. A dev-issue/infra-task worker on its own cannot complete it — it must not write SOPS and must not run an unsupervised credential mutation on production rs0 (the #1716 caution). Run the steps in order; do not skip the verify gate.

Why sys-admin-ai needs clusterMonitor — correction to the plan (#6857)

per-service-credential-isolation-plan.md §3.2 gives sys-admin-ai only readWriteAnyDatabase + dbAdminAnyDatabase and lists p24-status.py as a consumer. That combination is insufficient for the one thing p24-status.py actually does: gather_mongodb() runs rs.status() (scripts/p24-status.py), which requires the clusterMonitor action replSetGetStatus — not granted by readWriteAny/dbAdminAny. Repointing p24-status.py at a sys-admin-ai that lacks clusterMonitor would make it fail exactly the way it fails today without admin. Two valid fixes:

  • Preferred: grant sys-admin-ai clusterMonitor in addition to the two *AnyDatabase roles (still far below root/userAdmin), so it is a genuine one-stop worker role. This is what the user map row above records.
  • Alternative: leave sys-admin-ai as the plan wrote it and point p24-status.py at the existing least-privilege prometheus (clusterMonitor) user instead — since its only query is a read-only rs status. Then sys-admin-ai is reserved for actual read/write maintenance.

Confirm which model the executor wants before creating the role; the commands below assume the preferred model (with clusterMonitor).

Step 1 — secret-manager: generate + store the credential (SOPS write)

A secret-manager role/session (never inline in sys-admin/dev-coder) generates a URL-safe password and stores it in secrets/mongodb-bms.env.sops under keys MONGODB_SYS_ADMIN_AI_USER (value sys-admin-ai) and MONGODB_SYS_ADMIN_AI_PASSWORD. Follow the SOPS write path in “Adding a new MongoDB user → Step 4” above (LF-only WriteAllText, canary-decrypt, commit, PR to main). Do not distribute yet — secrets-sync.yml wiring is Step 4.

Generate the value with a URL-safe alphabet, e.g. openssl rand -base64 24 | tr -d '@#/?+='.

Step 2 — sys-admin: create the user on the live PRIMARY (additive, reversible)

Read the values from SOPS (a sys-admin session may read SOPS for verification) into shell variables — never echo them. Find the PRIMARY first (it moves): rs.isMaster().primary.

# PLAYBOOK: mongodb-user-isolation.md
# ADMIN_PW and NEW_PW are read silently from SOPS into vars — never printed.
mongosh admin -u admin -p "$ADMIN_PW" --authenticationDatabase admin --quiet --eval "
  db.createUser({
    user: 'sys-admin-ai',
    pwd:  '$NEW_PW',
    roles: [
      { role: 'readWriteAnyDatabase', db: 'admin' },
      { role: 'dbAdminAnyDatabase',   db: 'admin' },
      { role: 'clusterMonitor',       db: 'admin' }
    ]
  });"

This is additive — it touches no existing consumer and replicates to bms-3 automatically (the bms-4 arbiter holds no users). Rollback at this stage is a single db.dropUser('sys-admin-ai').

Step 3 — Verify the new user BEFORE any cutover (the gate)

# rs.status() must succeed as sys-admin-ai (proves clusterMonitor is effective):
mongosh "mongodb://sys-admin-ai:$NEW_PW@145.239.133.104:27017,51.68.155.224:27017/admin?authSource=admin&replicaSet=rs0" \
  --quiet --eval "rs.status().members.map(m=>m.stateStr)"

If this does not return the member states, the role grant is wrong — stop, fix the role (db.updateUser/db.grantRolesToUser), and re-verify. Do not proceed to Step 4 until it passes.

Step 4 — Distribute + cut over consumers one at a time

  1. Add a sync-mongodb-bms distribution path (or extend the existing one) in secrets-sync.yml so MONGODB_SYS_ADMIN_AI_USER/PASSWORD reach the hosts that run the AI consumers (vps-i1 for p24-status.py; bms-4 for worker maintenance paths). Keep the change additive — do not remove MONGODB_RS0_ADMIN_* yet.
  2. Repoint p24-status.py to prefer the new credential with a fallback, so the cutover is a no-op until the key is present and reversible by unsetting it: 
    mongo_user = os.environ.get("MONGODB_SYS_ADMIN_AI_USER") or os.environ.get("MONGODB_RS0_ADMIN_USER", "")
mongo_pass = os.environ.get("MONGODB_SYS_ADMIN_AI_PASSWORD") or os.environ.get("MONGODB_RS0_ADMIN_PASSWORD", "")
    (Apply the same to scripts/tests/test-mongodb-connectivity.py.) Verify p24-status.py MongoDB section still renders after the key lands.
  3. Any AI maintenance path that today reuses root admin for read/write (not user administration) → switch to sys-admin-ai. Do not switch the rotation scripts (scripts/rotate/w{3,4}_app_mongodb_password.sh, mongodb-admin-password-recovery.md) — those need userAdmin/root and correctly keep the break-glass admin.

Step 5 — No root demotion required for apps; register + log

The app-container demotion in plan §3.4 step 5 is already done (W3/W4/et-op are on their own users). After Steps 1–4:

  • Register sys-admin-ai in Supabase dev_r_services (element_type='credential', criticality, auto_rotate=true once a rotate entry exists) and reflect it in dev_r_ai_systems if it is consumed by an Annex-III AI system.
  • Log the new user in secrets-rotation-log.md.
  • Add a sys-admin-ai auto-rotate entry to scripts/rotate-credentials.py (Tier 1 — its own SOPS file, its own consumer restart; no cross-consumer fan-out). Tracked as plan §3.5 / P2 follow-up.

Rollback (any step)

The new user is additive, so rollback never touches a live app: unset the new env keys (consumers fall back to MONGODB_RS0_ADMIN_* via the Step 4 pattern) and db.dropUser('sys-admin-ai') on the PRIMARY. Because no app container was ever repointed, there is no 1716-class outage vector in this migration.

Graph View

Backlinks