securityremediation-2026-08-12 · v11099:claude-cowork-soc22026-08-12served from databaseAll documents

SOC 2 remediation log (2026-08-12) - 10 production fixes applied and verified, 4 items need the CEO

09 — Remediation log

12 August 2026 · Actor 1099:claude-cowork-soc2 · Billing session 5d0e88ae

Every change made, why, how it was verified, and how to reverse it. This file is itself CC8.1 evidence: it is the authorisation-and-testing record for a set of production changes.

Standing instruction "do not modify any code" was explicitly lifted by the CEO for this pass ("update the project. modify the code. fix everything").

Summary

#	Change	System	Status	Verified
1	`ops_is_admin()` fail-open → fail-closed	FedM8 DB	Applied	✅ 3/3 probes
2	`ops_is_admin()` fail-open → fail-closed	CipherDeck Apps DB	Applied	✅ 3/3 probes
3	16 dead write grants revoked	FedM8 DB	Applied	✅ 0 remaining
4	2 trigger functions + `ops_is_admin` removed from RPC surface	FedM8 DB	Applied	✅ + trigger still fires
5	`ops_log_404` user-id spoofing closed; client IP derived from headers	FedM8 DB	Applied	✅ spoof rejected
6	`admin_*` RPCs removed from anonymous surface	CipherDeck Apps DB	Applied	✅
7	78 orphaned billing sessions backfilled	Billing DB	Applied	✅ 0 remaining
8	12 unassigned projects assigned to the customer	Billing DB	Applied	✅ 0 remaining
9	Trigger added so orphans cannot recur	Billing DB	Applied	✅ probe passed
10	Directive version marker corrected v35 → v36	Portal DB	Applied	✅ 4/4 endpoints agree
11	Directive version plumbing made version-agnostic	`projects-portal/server.js`	Written, not deployed	Parses; needs push
12	Secret scanning added to 15 repos	Estate	Written, not pushed	16/16 coverage locally
13	Dependency scanning added to 15 repos	Estate	Written, not pushed	16/16 coverage locally
14	Railway gate repoint	CipherDeck Apps	Staged — needs CEO	See §R

1–2. `ops_is_admin()` fail-open

Was: if v_claims is null or v_claims = '' then return true; — absent JWT claims returned true. On CipherDeck Apps this guarded admin_search_users(), which returns user ids, email addresses, display names, last_sign_in_at, plan and subscription status.

Checked first: no RLS policy on either database references ops_is_admin — only the admin_* SECURITY DEFINER functions do. So changing it could not break row security. (The runbook's rule: check what calls a function before touching it.)

Now: fails closed on absent claims. The trusted-server case is recognised by the database role (current_user = 'service_role') before the claims check, so it works under both legacy JWT keys and the new opaque sb_secret_ keys — CipherDeck Apps is already on the new format.

Verified (admin_search_users('a',5)):

Context	Before	After
Direct DB connection, no claims	5 user rows incl. emails	`forbidden` ✅
Anonymous PostgREST request	`forbidden`	`forbidden` ✅
`service_role`	5 rows	5 rows ✅ (no regression)

Reverse: re-apply the previous body from migration history.

3–4. Dead grants and RPC surface — FedM8

Revoked unbacked INSERT/UPDATE/DELETE from anon/authenticated on companies, company_ai_wallets, company_capabilities, company_invites, company_join_requests, company_members, crm_accounts, crm_activities, crm_contacts, notification_prefs. RLS was enabled on all of them, so the grants permitted nothing today — they are removed because they would have become live the instant anyone added a permissive policy.

Removed ops_touch_ticket(), vet_profiles_guard_stable_ids() and ops_is_admin() from the callable surface.

Trap worth recording. The first revoke was a silent no-op. revoke ... from anon, authenticated changed nothing because the privilege was held by PUBLIC (ACL =X/postgres), which those roles inherit. The ops runbook documents the opposite trap — that revoking from PUBLIC alone is insufficient because Supabase grants anon directly. Both are true. The complete rule is: revoke from PUBLIC and the roles, then re-assert with has_function_privilege(). Never trust a revoke to have worked.

Verified: dead-grant query returns 0 rows; has_function_privilege('anon', …) false for all three functions; the vet_profiles_guard_stable_ids trigger is still attached and firing (1 trigger); admin_404_recent still returns rows for service_role.

5. `ops_log_404` identity spoofing

Was: values (…, coalesce(p_user_id, auth.uid()), …) — a caller-supplied user id took precedence over the authenticated identity, so any anonymous caller could write security-relevant log rows attributed to an arbitrary user. Both ops_log_404 and ops_submit_ticket also trusted a caller-supplied p_ip, which is the key the rate limiting uses — so the limit could be evaded by varying it.

Now: identity is auth.uid() only. A new ops_client_ip() helper derives the address from request headers (cf-connecting-ip, then the first hop of x-forwarded-for, then x-real-ip), falling back to the parameter only when there is no request context. Parameters are retained so existing callers keep working.

Verified: a call passing p_user_id = 0000…deadbeef wrote 0 rows with that id; ops_submit_ticket still creates tickets normally (regression test passed, probe rows deleted).

7–9. Billing repair

76+ of 269 billing.work_sessions rows had project_id NULL and 12 of 17 projects had no customer assignment — the directive's "invisible hours" defect. Backfilled project_id from project_slug; assigned every project with recorded work to the single real customer account (joseph.b.nguyen@gmail.com — demo is a demonstration account, so there was no possibility of mis-assignment).

Root cause fixed, not just the data: a before insert or update trigger on billing.work_sessions now resolves project_id from the slug and creates the billing.projects row when the slug is new — which is what directive rule 7 requires.

Verified: 0 orphaned sessions; 0 projects-with-work unassigned; a probe insert on a brand-new slug auto-created the project row and linked it (probe rolled back).

10–11. Directive integrity

agent_ops.policy.project_llm_directive held version = 36 and a v36 body, but the body's own inline marker still read version 35. / and /json report the version field; /md and /txt serve the body — so agents reading Raw or Plain saw v35. (pf_llm_directive is a view over agent_ops.policy, so there is one source of truth, not two.)

Corrected the marker in the data. Separately, server.js hardcoded 35 in four places, and the bundled fallback file sql/directive-v35.md no longer exists — so if the database were unreachable, the master directive every agent boots from would have failed to serve at all rather than falling back. DIRECTIVE_VERSION, the filename, and the marker check are now derived from one constant.

Verified: /, /json, /md, /txt all report 36 and all carry the PROD-IT two-lane content. node --check passes.

12–13. Estate-wide scanning

Secret scanning went from 1 of 18 repositories to 16 of 16 git repositories; Dependabot the same. fetch-depth: 0 so history is scanned — a credential removed in a later commit is live until rotated. Action versions pinned to commit SHAs, matching the existing quality-gates.yml convention. 31 files written, all valid YAML.

R. What still needs you

1. Railway repoint — genuinely blocked, and now tested three ways.

update-service does not support source changes (documented limitation).
The Railway agent reported "applied / staged", but re-reading the config shows

branch: "main" unchanged. This is the runbook's 2FA trap, confirmed empirically.

There is now a staged change on project-cipherdeck → web. It is safe to apply:

production and main are the identical commit (5d021d5).

Do not repoint the other two yet — one would roll production backwards:

Project	`production` branch	Safe to repoint?
`cipherdeck-apps`	identical to `main` (`5d021d5`)	Yes — apply the staged change
`fedm8-scan`	20 commits behind `main`	No. Repointing then deploying would revert 20 commits, including the published refunds policy. Fast-forward `production` to `main` first
`nodedough`	not present in refs (last fetched 7 Aug)	No. Confirm it exists or create it first

2. Push the code changes. The sandbox has no GitHub credentials, so 32 files are written to D:\gh but not committed: projects-portal/server.js plus 31 scanning configs. Note that jbnx.io is on the PROD-IT lane — pushing main deploys the portal and the directive live.

3. Until server.js ships, the deployed build still looks for sql/directive-v35.md, which does not exist. It will log a warning on the directive path. Harmless — the error is caught and the database copy serves — but it disappears once the push lands.

4. Rotate or scope GITHUB_TOKEN on the fedm8 scan-worker production service. I could not inspect its scopes without GitHub access.