# Дыра #7: Аудит dev↔prod RLS-разрыва — Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use `superpowers:subagent-driven-development` or `superpowers:executing-plans` to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax.

**Goal:** найти все Console-команды и Job-классы Лидерры, которые читают/пишут таблицы с RLS-политиками на `app.current_tenant_id` (или другие session vars), но не используют BYPASSRLS-канал (`pgsql_supplier`) и не устанавливают session-переменную. Пофиксить каждое найденное место. Класс проблемы: «работает на dev (`postgres` superuser, implicit BYPASSRLS), падает на prod».

**Прецедент:** 22.05 `IncidentsWatchFailures` упал на проде с `unrecognized configuration parameter "app.current_tenant_id"` — хотфикс через `DB::connection('pgsql_supplier')`. Сегодня — поиск всех остальных мест с тем же риском.

**Architecture:**

- **Phase A (discovery, read-only):** статический анализ — grep по `app/app/Console/Commands/**/*.php` + `app/app/Jobs/**/*.php` + `app/routes/console.php` × список RLS-таблиц из `db/schema.sql`. Выход — отчёт `docs/audit/2026-05-23-rls-gap-audit.md` с матрицей.
- **Phase B (fix-as-found):** для каждого ❌-findings — отдельный коммит «switch to pgsql_supplier» с обновлением тестов через `SharesSupplierPdo` trait. Pattern взят из `IncidentsWatchFailures.php` (commit `5df34a61` после P2-hotfix).
- **Phase C (deploy):** scp-tar-pipe изменённых файлов на боевой liderra.ru + restart workers.

**Tech Stack:** Laravel 13, PostgreSQL 16, Pest 4, PowerShell/Bash через SSH.

---

## Phase A — Discovery (read-only)

### Task A1: Inventory RLS-protected tables

**Files:**

- Read: `db/schema.sql` (find all `CREATE POLICY` referencing `current_setting('app.current_tenant_id'`, `current_setting('app.current_admin_id'`, or similar)
- Create: `docs/audit/2026-05-23-rls-gap-audit.md`

- [ ] **Step A1.1: Grep schema for RLS policies on app.* session vars**

Command:

```
grep -n "current_setting('app\." db/schema.sql
```

Expected: ~30-50 hits across 15-25 distinct tables.

- [ ] **Step A1.2: Compile table list (with policy summary)**

Output a list like:

```
- auth_log               — tenant_isolation on app.current_tenant_id
- activity_log           — tenant_isolation on app.current_tenant_id
- pd_processing_log      — tenant_isolation on app.current_tenant_id
- webhook_log            — tenant_isolation on app.current_tenant_id
- incidents_log          — tenant_isolation on app.current_tenant_id
- tenant_operations_log  — tenant_isolation on app.current_tenant_id
- saas_admin_audit_log   — admin_isolation on app.current_admin_id
- ... (continue)
```

Write to section "## Inventory" of the report file.

### Task A2: Inventory cron/job classes

**Files:**

- Read: `app/app/Console/Commands/**/*.php`, `app/app/Jobs/**/*.php`, `app/routes/console.php`

- [ ] **Step A2.1: List Console commands**

Glob: `app/app/Console/Commands/**/*.php`

Expected ~20-40 files. Record class name, file path, and short purpose (from class docblock or `$description`).

- [ ] **Step A2.2: List Job classes**

Glob: `app/app/Jobs/**/*.php`

Expected ~15-30 files.

- [ ] **Step A2.3: Cross-check with scheduler**

Read `app/routes/console.php` — note which commands are scheduled (cron context, no tenant in session) vs. ad-hoc (artisan called inside HTTP request, may have tenant set).

Write to section "## Inventory: commands and jobs".

### Task A3: Static analysis — for each command/job, check RLS-table touches

For each file from A2:

- Grep for `DB::table('<rls_table>')`, `DB::connection(...)->table('<rls_table>')`, and any Model class whose table is RLS-protected (Models in `app/app/Models/` → match $table to A1 list).
- For each touch, classify:
  - ✅ **SAFE** — uses `DB::connection('pgsql_supplier')` OR explicitly calls `DB::statement('SET LOCAL app.current_tenant_id = ...')` first
  - ❌ **GAP** — touches RLS-table on default connection without setting session var
  - ⚠️ **AMBIGUOUS** — touch happens inside `tenants()->each(function ($t) { DB::statement('SET LOCAL ...'); ... })` loop — needs case-by-case review
  - ➖ **N/A** — file doesn't touch any A1 table

- [ ] **Step A3.1: Run static analysis**

For each command/job:

1. Read the file.
2. Grep for table names from A1 list.
3. Classify per above rules.

Output matrix in section "## Findings" of the report:

```
| File | Tables touched | Classification | Reason |
|------|---------------|----------------|--------|
| app/app/Console/Commands/Foo.php | activity_log | ❌ GAP | DB::table('activity_log') without SET LOCAL or pgsql_supplier |
| app/app/Jobs/BarJob.php | webhook_log, incidents_log | ✅ SAFE | uses DB::connection('pgsql_supplier') |
| ... |
```

- [ ] **Step A3.2: Tally and prioritize**

Summary table at top of "## Findings":

- Total commands+jobs analyzed: NN
- ✅ SAFE: NN
- ❌ GAP: NN (list each — these need fixing)
- ⚠️ AMBIGUOUS: NN (list each — needs human review)
- ➖ N/A: NN

### Task A4: Commit the audit report

- [ ] **Step A4.1: Commit**

```
git add docs/audit/2026-05-23-rls-gap-audit.md
git commit -- docs/audit/2026-05-23-rls-gap-audit.md -m "$(cat <<'EOF'
docs(audit): RLS dev↔prod gap discovery — Phase A of hole #7

Inventory + static analysis of all Console commands and Job classes
against tables with RLS policies on app.current_tenant_id / app.current_admin_id.

Prompted by 22.05 IncidentsWatchFailures prod-only failure (RLS context
absent in cron). This audit finds all similar latent gaps.

Phase B (fixes) follows in separate commits per finding.
EOF
)"
```

**STOP after Phase A** — return the report path to the controller. Controller reviews ❌ findings and approves Phase B execution.

---

## Phase B — Fix-as-found (per ❌ finding)

For each ❌ GAP finding in the report, repeat:

### Task B-N: Fix [filename]

**Files:**

- Modify: `app/app/Console/Commands/<File>.php` OR `app/app/Jobs/<File>.php`
- Modify: `app/tests/Feature/Console/<File>Test.php` OR equivalent test file

- [ ] **Step B-N.1: Add SharesSupplierPdo to test file (if missing)**

Pattern from `IncidentsWatchFailuresTest.php`:

```php
use Tests\Concerns\SharesSupplierPdo;

uses(DatabaseTransactions::class);
uses(SharesSupplierPdo::class);
```

- [ ] **Step B-N.2: Switch RLS-table queries to pgsql_supplier**

Pattern from `IncidentsWatchFailures.php` (commit `5df34a61`):

Add:

```php
private const DB_CONNECTION = 'pgsql_supplier';
```

Replace each `DB::table('<rls_table>')` with `DB::connection(self::DB_CONNECTION)->table('<rls_table>')`.

Keep non-RLS table queries on default connection.

- [ ] **Step B-N.3: Run test for this file**

```
cd app && ./vendor/bin/pest tests/Feature/Console/<File>Test.php
```

Expected: PASS.

- [ ] **Step B-N.4: Run full suite to ensure no regression**

```
cd app && ./vendor/bin/pest --parallel
```

Expected: same green baseline as before (~742/739/3sk/0).

- [ ] **Step B-N.5: Commit**

```
git add app/app/Console/Commands/<File>.php app/tests/Feature/Console/<File>Test.php
git commit -- app/app/Console/Commands/<File>.php app/tests/Feature/Console/<File>Test.php -m "$(cat <<'EOF'
fix(rls): <File> — switch to pgsql_supplier (dev↔prod RLS gap)

Found by hole #7 audit (docs/audit/2026-05-23-rls-gap-audit.md).
<File> touched RLS-table <table> via default connection without
setting app.current_tenant_id — would fail on prod where the role
is not BYPASSRLS.

Mirror of P2 IncidentsWatchFailures hotfix pattern (commit 5df34a61).

Tests: <File>Test now uses SharesSupplierPdo trait for cross-connection
visibility in DatabaseTransactions wrapper.
EOF
)"
```

---

## Phase C — Push and Deploy

### Task C1: Push all fix commits

- [ ] **Step C1.1: Final pre-flight**

```
git fetch origin
git log HEAD..origin/main --oneline
```

If non-empty: rebase or stop and ask controller.

- [ ] **Step C1.2: Push**

```
git push origin main
```

### Task C2: Deploy to prod (per affected file)

For each fixed file, follow the established pattern from P2 deploy:

- [ ] **Step C2.1: Compute local MD5**

```powershell
Get-FileHash -Algorithm MD5 app/app/Console/Commands/<File>.php
```

- [ ] **Step C2.2: SCP to server**

```bash
scp -i ~/.ssh/liderra_deploy app/app/Console/Commands/<File>.php ubuntu@111.88.246.137:/tmp/<File>.php
```

- [ ] **Step C2.3: Install on server**

```bash
ssh -i ~/.ssh/liderra_deploy ubuntu@111.88.246.137 "sudo install -D -m 644 -o www-data -g www-data /tmp/<File>.php /var/www/liderra/app/app/Console/Commands/<File>.php && sed -i 's/\r$//' /var/www/liderra/app/app/Console/Commands/<File>.php"
```

- [ ] **Step C2.4: Verify MD5 match**

```bash
ssh ... "md5sum /var/www/liderra/app/app/Console/Commands/<File>.php"
```

- [ ] **Step C2.5: Clear config cache + restart workers (once after all files)**

```bash
ssh ... "cd /var/www/liderra/app && php artisan config:clear && sudo systemctl restart liderra-worker"
```

- [ ] **Step C2.6: Smoke test**

For each affected scheduled command, manually invoke once:

```bash
ssh ... "cd /var/www/liderra/app && php artisan <command:name>"
```

Expected: exit 0, no `unrecognized configuration parameter` errors.

### Task C3: Update tracker

- [ ] Mark hole #7 as done in `docs/superpowers/plans/2026-05-23-7-holes-overview.md` Tracker section.

---

## Self-review checklist (for controller after subagent returns)

1. **Phase A report quality:** does the matrix cover all commands and jobs? Any obvious omissions?
2. **Classification accuracy:** spot-check 3-5 ✅ SAFE and 3-5 ❌ GAP findings — does the reasoning hold?
3. **AMBIGUOUS handling:** review each ⚠️ case manually — decide GAP or SAFE.
4. **Phase B coverage:** every ❌ GAP from Phase A has a corresponding fix commit?
5. **No regressions:** Pest full suite still green?
6. **Deploy verification:** MD5 match on every deployed file, smoke tests pass.
