fix(server): apply tuist_processor DB grants at migrate time and add webhook_endpoints read

What

One logical fix, two parts:

1. Grant the least-privilege tuist_processor Postgres role SELECT on webhook_endpoints.
2. Apply the processor’s per-table grants from Tuist.Release.migrate/0 (as the schema owner, on every migrate), so the grant set no longer drifts behind schema changes. infra/cnpg/tuist-processor-grants.sql becomes a bootstrap/restore fallback.

Root cause

Remote xcresult/test ingestion was silently storing partial, non-deterministic results. A project with a large test plan (~24,883 tests / 224 targets) stored 371 test cases; two re-uploads of the same run stored 0 and 636.

The processor Oban job (ProcessXcresultWorker → Tuist.Tests.create_test/1) failed all 5 attempts with an identical error recorded in oban_jobs.errors:

** (Postgrex.Error) ERROR 42501 (insufficient_privilege) permission denied for table webhook_endpoints
    lib/tuist/webhooks/dispatcher.ex:68: Tuist.Webhooks.Dispatcher.dispatch_test_case_created/2
    lib/tuist/tests.ex:1897: Tuist.Tests.dispatch_test_case_created_webhooks/3
    lib/tuist/tests.ex:1736: Tuist.Tests.create_test_cases_for_module/9
    lib/tuist/tests.ex:1364: anonymous fn/7 in Tuist.Tests.create_test_modules/4

Since outbound webhooks (#10748) added webhook_endpoints and wired dispatch_test_case_created into the shared ingestion path, ingesting a module that contains a test case seen for the first time on the default branch reads webhook_endpoints. The tuist_processor grant set (hand-maintained in infra/cnpg/tuist-processor-grants.sql, which REVOKE ALLs and re-grants a fixed allowlist) was never updated for the new table, so the read raised 42501. The exception aborts the Enum.flat_map_reduce over modules, so only modules processed before the first “new test case” module land. Module order is non-deterministic (Dictionary(grouping:) in the parser), so the stored count varies between uploads. The job retries to max_attempts and is discarded, leaving a run with partial data (and a failure/skipped status). Each retry also appends a duplicate test_run_destinations row, which is the visible “same device listed 5 times” symptom.

Confirmed live: has_table_privilege('tuist_processor','webhook_endpoints','SELECT') = false in production (true for accounts/projects/oban_jobs).

Why this approach

The web runtime role already gets its grants applied at migrate time (do_grant_runtime_role, gated on TUIST_DATABASE_RUNTIME_ROLE), which is why the web tier could read the new table immediately. The processor role was deliberately excluded and kept on a manual least-privilege allowlist — and that manual step is what drifted.

This gives the processor role the same migrate-time treatment while keeping least-privilege: tables are enumerated explicitly (no ON ALL TABLES / ALTER DEFAULT PRIVILEGES), with a leading REVOKE ALL for deny-by-default. Adding a PG read to the ingest path is now one line in do_grant_processor_role, in the same PR, covered by the deploy, and it survives restores.

Alternatives considered:

• Keep it in the SQL runbook only — leaves the manual step and the drift that caused this.
• Give the processor ON ALL TABLES + default privileges like the web role — simplest, but discards the least-privilege posture.
• CNPG declarative — CNPG manages role lifecycle/membership and databases/schemas/extensions, not per-table object privileges, so it cannot express this.

Safety notes

• Grants run as the migration/owner role (which owns the app tables), so no superuser is required. Corrected a stale chart comment that claimed the processor grants need superuser.
• REVOKE ALL ON ALL TABLES only warns (never errors) on tables the owner can’t revoke, and every GRANT targets an owned table, so it can’t hit the permission denied abort a blanket GRANT ... ON ALL would.
• Mirrors the web grant’s SQL.query! (no rescue): a processor-grant failure would fail the migration Job, the same risk posture as the existing web grant. The role and tables are guaranteed present in managed CNPG (role created before the Job, tables after migrations). Flagged in case reviewers prefer the processor grant isolated so it can never wedge a deploy.

Impact

• Fixes silent partial ingestion for any remote-processed run that introduces a new test case on the default branch. Projects with stable test suites were unaffected (all cases already known → dispatch skipped), which is why this stayed quiet.
• Self-hosted / non-CNPG deployments are untouched (TUIST_DATABASE_PROCESSOR_ROLE unset).

Immediate hotfix (out of band, not gated on this PR)

This only takes effect on the next deploy. To unblock the live clusters now, apply the updated runbook (idempotent):

for ENV in staging canary production; do
  kubectl cnpg psql -n "tuist-$ENV" tuist-tuist-pg -- \
    -d tuist -v tuist_schema=public -f - < infra/cnpg/tuist-processor-grants.sql
done

Existing partial runs won’t self-heal; re-run / re-upload for a clean ingest.

Validation

• mix format --check-formatted clean; both Elixir files parse via Code.string_to_quoted!.
• No unit test added — the grant path has none today (the web grant is exercised only by the migration Job). After merge, staging’s migration Job should flip has_table_privilege('tuist_processor','webhook_endpoints','SELECT') to true with no manual runbook step; verify there, then canary/prod via the cascade.

🤖 Generated with Claude Code

No GitHub comments yet.