Check the Stage 12 database role's table privileges before each deploy #855

Problem

The simulation API and its Modal worker write the temporary comparison-run tables, stage12_evaluation_reports and stage12_evaluation_simulations, in the v2 Supabase database as policyengine_stage12_staging or policyengine_stage12_production, through libs/policyengine-stage12-persistence. Nothing in this repository checks, before a deploy, that the role can do what that code does.

The Public API had the same gap. In PolicyEngine/policyengine-api#3977, production's runtime role had no SELECT on a table one route read, and every reform save returned 503. CI ran as the database superuser, so no test could fail on it.

PolicyEngine/policyengine-api#3980 (PR PolicyEngine/policyengine-api#3985) now declares every managed role's table privileges in the Public API repository, in policyengine_api/data/v2/privileges.py. The Stage 12 role is declared to hold SELECT, INSERT, UPDATE and DELETE on the two comparison-run tables and nothing else. The Public API's deploy grants those privileges after each v2 schema upgrade and checks them. That deploy runs separately from this repository's stage12-deploy.yml, so a simulation API deploy can still start with a role that lacks a privilege its code needs: for example, when the persistence library starts a new kind of operation, or when grants are changed by hand.

Proposal

1. A rule in rules/registry.toml, for example sim.stage12.role-privileges-checked-before-deploy: a deploy of the Stage 12 path doesn't start until the Stage 12 role has passed the check below.

2. A check before the deploy steps in stage12-deploy.yml (or simulation-deploy.reusable.yml when deploy_stage12_v2 is true). It connects as the Stage 12 role and, in one transaction that is always rolled back, runs each operation policyengine-stage12-persistence performs on the two tables:

   • insert a report and a simulation with ON CONFLICT DO NOTHING ... RETURNING;
   • select them, including with FOR UPDATE;
   • update them.

   Any permission error fails the deploy and names the table and operation. No row survives the check.

3. Proof: a role without UPDATE on stage12_evaluation_simulations fails the check; the declared role passes.

4. Environment variables: name the secret that holds the Stage 12 role's connection URL for each environment, and confirm it reaches the new step (environment secrets:, reusable-workflow secrets: inherit, step env:).

Related

• Declare v2 database table privileges in the repository and check them in CI and deploys policyengine-api#3980 and Declare v2 table privileges and check them in CI and deploys policyengine-api#3985: declared privileges, the grant-and-verify deploy step, and integration tests that run the Public API's comparison-run services (policyengine_api/services/v2/comparison_runs) as policyengine_stage12_test.
• v2/user-policies returns 503 for logged-in users: reforms cannot be saved policyengine-api#3977: the production incident.