--- title: Gate the league_data_view_reader GRANT list against data-view table references type: task status: completed description: >- Nothing reconciles the tables league_data_view_reader can SELECT against the tables data-view column definitions reference, so a new data-view-readable table is caught only by hand or by a runtime permission error. Named the highest-value gate-shaped gap in the area; it has already cost one corrective adhoc. base_uri: user:task/league/gate-data-view-reader-grants.md created_at: '2026-08-29T21:07:14.461Z' entity_id: 09e2bf11-0a30-49c7-b08b-c8e79a6b4624 finished_at: '2026-10-09T21:13:52.995Z' observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: Medium public_read: true relations: - relates [[███████████████████████-████-████-██████-████-████-██████████████]] - relates [[user:task/league/pin-season-projected-period-to-season-key.md]] started_at: '2026-09-07T00:38:18.649Z' tags: - user:tag/league-xo-football.md updated_at: '2026-10-09T21:14:07.183Z' visibility_analyzed_at: '2026-11-05T23:49:31.735Z' --- # Gate the `league_data_view_reader` GRANT list against data-view table references ## Problem Nothing reconciles the tables `league_data_view_reader` can SELECT against the tables data-view column definitions actually reference. A new data-view-readable table is caught only by hand, or at runtime by a permission error in front of a user. This has already cost a corrective adhoc: Phase A of `█████████████████████████████████████████████` added a table the definitions referenced and the role could not read, and the miss surfaced only after the fact. The chain that closed the week-zero sentinel named this **the highest-value gate-shaped gap in the area** and deliberately did not adopt it, because it is a different lane from the sentinel work. ## Current state, measured 2026-08-29 - The role exists in `league_production`. - 274 objects in `public` carry an explicit `league_data_view_reader=` entry in `relacl` (tables, partitioned tables, views and matviews). - `check-league-fixture-reset-coverage`'s schema parse reads 316 tables in total, so roughly forty carry no grant — some of them legitimately, which is exactly why a hand comparison is not the answer. - `information_schema.role_table_grants` reports 0 for this grantee and is a trap: it only returns rows where the querying role is grantor, grantee, or a member. Read `pg_class.relacl`. ## The gate Two sets, compared: - **Referenced** — every table a data-view column definition can reach. `db/gates/check-data-view-sql-validity.mjs` already resolves every column to real SQL and is the natural place to harvest table names from, rather than re-parsing the definitions. - **Granted** — `pg_class.relacl` filtered to the role. A referenced table with no grant is a finding. A granted table nothing references is NOT automatically a finding — the role may hold grants for reasons outside the data views — so the reverse direction wants an adjudications file in the shape the repo already uses (`call-site-param-contract-adjudications.json`, `data-view-description-coverage-adjudications.json`), not a hard failure. ## Constraint **The gate needs a negative control before it is trusted.** The chain that surfaced this gap also recorded that five existing cluster gates run with no negative control, so their green is unproven — and this session separately found that a scoped eslint rule reporting zero proved nothing until a planted in-scope case made it fire. A gate whose reach is underived from its own subject is the recurring defect in this area. Plant a table that is referenced and ungranted, confirm the gate fails on it, then remove it. Related trap from the same chain, worth reading before writing the parse: `check-league-fixture-reset-coverage` terminated a `CREATE TABLE` on `\n);` and so swallowed the successor of any table with a trailing `WITH (...)` or `PARTITION BY` clause, losing six tables silently for as long as they had been partitioned. Anchor any count on something derived independently of the parser. ## Plan - [x] **Harvest the referenced-table set** - Description: take table names from the resolved SQL `check-data-view-sql-validity.mjs` already produces, rather than re-parsing the column definitions. - Purpose: a second parser of the same definitions is a second thing to keep in sync, and the resolved SQL is the ground truth of what a request actually touches. - Verify: the harvested set contains every table named in a hand-picked sample of five column definitions spanning three sources. - Done: `harvest()` in `db/gates/check-data-view-reader-grants.mjs` sweeps all 597 columns x grains x axes x shapes (2269 statements), mines FROM/JOIN identifiers from the resolved SQL, and filters against `pg_class` so CTE/alias names drop out. 75 tables reachable. - [x] **Read the granted set from `pg_class.relacl`** - Description: filter `public` relations of kind `r`, `p`, `v`, `m` on an explicit `league_data_view_reader=` entry. - Purpose: `information_schema.role_table_grants` returns 0 here for a visibility reason and would make the gate pass vacuously. - Verify: the count matches the 274 measured 2026-08-29, or the difference is explained by an intervening DDL. - Done: `aclexplode(c.relacl)` in the throwaway DB the gate provisions; 281 objects, which matches live production relacl exactly (verified via `base db query league`; 274 + intervening DDL). - [x] **Fail on referenced-and-ungranted; adjudicate the reverse direction** - Description: a referenced table with no grant fails. A granted table nothing references goes to an adjudications JSON in the existing repo shape. - Purpose: the reverse direction has legitimate members, so failing on it would make the gate noise and it would get muted. - Verify: the gate exits 0 against current `master`, and every entry it adjudicates carries a stated reason. - Done: gate exits 0; 3 referenced-but-ungranted relations are held as `denied_referenced` (all three are already deliberately denied in `EXCLUDED_RELATIONS`), 209 surplus grants adjudicated in `db/gates/data-view-reader-grants-adjudications.json`, every entry with a stated reason. See the `[decision]` observation for the one plan amendment. - [x] **Plant a negative control and confirm the gate fails on it** - Description: add a referenced, ungranted table; run the gate; remove it. - Purpose: an unproven green is the exact defect this area keeps producing. - Verify: the gate exits non-zero naming the planted table, and exits 0 again once removed. - Done: five synthetic controls (four prove RED, one proves SILENT) plus a real end-to-end plant — the `player_gamelogs` grant line was removed from the throwaway schema, the gate exited 1 naming it, and restored to green. - [x] **Wire it into the cluster run** - Description: register alongside the other `db/gates/` checks so `yarn check:cluster` picks it up. - Purpose: a gate nothing runs is documentation. - Verify: `yarn check:cluster` lists it as run rather than skipped or blind. - Done: manifest entry `data-view-reader-grants` (requires test-container, negative_control true); `node scripts/check-cluster-gates.mjs --list` shows it. A full `yarn check:cluster` run on 2026-10-09 listed it as RUN with its control declared. - [x] **Triage the 18 findings so the gate exits 0 on master** - Description: adjudicate the 12 unreferenced grants (e.g. `nfl_game_weather_forecasts`, the `view_nfl_depth_chart_*` family, `player_season_alignment`, `athletic_testing_score_calibration`) or revoke them, and remove the 6 stale entries (`adp_draft_board`, `auction_blocks`, `prop_market_selection_observations`, `prop_market_payout_terms`, `restricted_free_agency_releases`, `scoring_format_player_gamelogs`). - Verify: `node db/gates/check-data-view-reader-grants.mjs` exits 0 with its negative controls still RED. - Done: league 0127b09d2 adjudicates all 12 grants with a stated reason and removes the 6 stale entries; the gate exits 0 with all five controls holding. - [x] **Make the gate run where it can stop drift** - Purpose: the CI runner has no test container on :5433, so the gate is SKIPPED there and has never gated master; adjudications rot between hand runs, which is how 18 findings accumulated. - Verify: a full `yarn check:cluster` (step 1 of `████████████████████████████████████████████████████████████████████████████`) lists it as RUN and green, and the skip-on-CI state is either fixed or recorded as accepted. - Done: league 8f44cd456 runs the gate as its own step in test.yml's `test` job, against that job's Postgres 16 on :5432. The cluster job still skips it, since it has no Postgres. The gate passes in a worktree without the private submodule. ## Key locations - `db/gates/check-data-view-sql-validity.mjs` — resolves every column to SQL; the harvest source - `db/gates/check-league-fixture-reset-coverage.mjs` — the schema-parse trap described above - `db/gates/call-site-param-contract-adjudications.json` — the adjudications-file shape to follow - `docs/guides/gates.md` — how gates are registered and what the cluster runner expects - Production: `base db query league`, or `ssh league` plus psql against localhost. `league_production` takes no direct TCP connection.