Visitar URL original
Scope schema and ingest OIDC trust to their owning repositories (#34) · vortex-data/benchmarks-website@737386b · GitHub
Skip to content

Commit 737386b

Browse files
connortsui20connortsui20
authored
Scope schema and ingest OIDC trust to their owning repositories (#34)
Signed-off-by: Connor Tsui <connor.tsui20@gmail.com>
1 parent 1e523e2 commit 737386b

4 files changed

Lines changed: 109 additions & 52 deletions

File tree

‎.github/workflows/web-ci.yml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,12 @@ jobs:
3838
- name: Checkout
3939
uses: actions/checkout@v4
4040

41+
- name: Check infrastructure trust policies offline
42+
working-directory: .
43+
run: |
44+
bash -n infra/provision.sh
45+
python3 -m unittest discover -s infra/tests -v
46+
4147
# pnpm must exist before setup-node so its `cache: pnpm` can resolve the store.
4248
- name: Install pnpm
4349
run: npm install -g "pnpm@${PNPM_VERSION}"

‎infra/README.md‎

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -18,8 +18,8 @@ Single one-shot script. Provisions, in order:
1818
3. An RDS Postgres instance `vortex-bench-prod` on `db.t4g.micro`, Postgres 16, 20 GiB GP3 storage, IAM auth enabled, RDS-managed master password (auto-rotated, stored in Secrets Manager), publicly accessible, single-AZ, 35-day backup window.
1919
4. An RDS Proxy `vortex-bench-proxy` in front of the instance, `IAMAuth=REQUIRED`, TLS required, pulling the master credential from the Secrets-Manager-managed secret via a service-linked IAM role.
2020
5. The GitHub OIDC provider `token.actions.githubusercontent.com` (account-scoped — created once if not present).
21-
6. An IAM role `GitHubBenchmarkSchemaRole` trusted to GitHub Actions OIDC for the `vortex-data/vortex` repo (branches `develop` + `ct/bench-v4`) with `sts:AssumeRoleWithWebIdentity`. Permission policy: `rds-db:connect` scoped to the `migrator` Postgres user on the **instance** resource only (CI schema deploys connect to the public instance endpoint). The dead proxy grant this role carried through PR-1.6 was dropped in PR-2.1's least-privilege cleanup; the VPC-internal proxy serves only the Vercel reader.
22-
7. An IAM role `GitHubBenchmarkIngestRole` (PR-2.1) trusted to the same OIDC provider, repo, and branches. Permission policy: `rds-db:connect` scoped to the `bench_ingest` Postgres user on the instance resource only. This is the dedicated least-privilege identity for the Phase-2 CI dual-write ingest path, deliberately separate from the schema-deploy `migrator` identity so the high-frequency ingest path can write data but never run DDL or migrations.
21+
6. An IAM role `GitHubBenchmarkSchemaRole` trusted to GitHub Actions OIDC for `vortex-data/benchmarks-website` on `develop` with `sts:AssumeRoleWithWebIdentity`. Permission policy: `rds-db:connect` scoped to the `migrator` Postgres user on the **instance** resource only (CI schema deploys connect to the public instance endpoint). The dead proxy grant this role carried through PR-1.6 was dropped in PR-2.1's least-privilege cleanup; the VPC-internal proxy serves only the Vercel reader.
22+
7. An IAM role `GitHubBenchmarkIngestRole` (PR-2.1) trusted to the same OIDC provider for `vortex-data/vortex` on `develop`. Permission policy: `rds-db:connect` scoped to the `bench_ingest` Postgres user on the instance resource only. This is the dedicated least-privilege identity for the Phase-2 CI dual-write ingest path, deliberately separate from the schema-deploy `migrator` identity so the high-frequency ingest path can write data but never run DDL or migrations.
2323

2424
The `migrator` Postgres user is created by `migrations/002_iam_db_user.sql` (PR-1.3) and the `bench_ingest` user by `migrations/004_ingest_role.sql` (PR-2.1); each OIDC role's permission ARN is pre-scoped to its user. `002` is applied as the RDS master because it creates a role (it needs `CREATEROLE` and grants the `rds_iam` / schema privileges the master holds), while `003` and `004` are applied as the master because they additionally grant on master-owned objects (the ledger and the six data tables, respectively). All three run during the one-time bootstrap; the migrator-run `schema-deploy` path then records them as already-applied.
2525

@@ -243,3 +243,23 @@ Steady-state monthly bill once provisioned (rough). NOTE: prod `vortex-bench-pro
243243
| Secrets Manager (1 secret) | ~$0.40 |
244244
| Data transfer (CI ingests + reader fetches) | <$1 |
245245
| **Total** | **~$30/month** |
246+
247+
## OIDC trust inputs
248+
249+
`SCHEMA_GITHUB_REPO` defaults to `vortex-data/benchmarks-website` and
250+
`INGEST_GITHUB_REPO` defaults to `vortex-data/vortex`. Each role trusts only its repository's
251+
`refs/heads/develop` subject with the `sts.amazonaws.com` audience. PRs, tags, environments,
252+
and the retired `ct/bench-v4` branch are excluded. Manual schema deploys must select `develop`.
253+
The old shared `GITHUB_REPO` input is rejected to prevent an ambiguous role assignment.
254+
255+
Reprovisioning replaces each role's trust policy with these inputs. It does not preserve manual
256+
subjects. Review both generated policies offline before applying infrastructure changes:
257+
258+
```bash
259+
bash infra/provision.sh --print-trust schema
260+
bash infra/provision.sh --print-trust ingest
261+
python3 -m unittest discover -s infra/tests -v
262+
```
263+
264+
The print commands require only `jq` and make no AWS calls. They do not apply the policy.
265+
The schema and ingest permission policies remain scoped to their respective database users.

‎infra/provision.sh‎

Lines changed: 35 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -31,8 +31,8 @@
3131
# 7. Creates the RDS Proxy `vortex-bench-proxy` in front of the RDS
3232
# instance, also with IAM auth.
3333
# 8. Creates two GitHub Actions OIDC IAM roles trusted to
34-
# `token.actions.githubusercontent.com` for the `vortex-data/vortex` repo
35-
# (branches `develop` + `ct/bench-v4`), each scoped to an instance dbuser:
34+
# `token.actions.githubusercontent.com`, each scoped to its repository
35+
# develop branch and one instance dbuser:
3636
# - `GitHubBenchmarkSchemaRole`: `rds-db:connect` on the `migrator`
3737
# Postgres user (schema deploys; migration 002 in PR-1.3).
3838
# - `GitHubBenchmarkIngestRole`: `rds-db:connect` on the `bench_ingest`
@@ -69,7 +69,8 @@ readonly DB_MASTER_USERNAME="${DB_MASTER_USERNAME:-postgres}"
6969
readonly SCHEMA_ROLE_NAME="${SCHEMA_ROLE_NAME:-GitHubBenchmarkSchemaRole}"
7070
readonly INGEST_ROLE_NAME="${INGEST_ROLE_NAME:-GitHubBenchmarkIngestRole}"
7171
readonly PROXY_ROLE_NAME="${PROXY_ROLE_NAME:-vortex-bench-proxy-role}"
72-
readonly GITHUB_REPO="${GITHUB_REPO:-vortex-data/vortex}"
72+
readonly SCHEMA_GITHUB_REPO="${SCHEMA_GITHUB_REPO:-vortex-data/benchmarks-website}"
73+
readonly INGEST_GITHUB_REPO="${INGEST_GITHUB_REPO:-vortex-data/vortex}"
7374
# Postgres role created by migrations/002 in PR-1.3; the OIDC role's
7475
# rds-db:connect permission is scoped to this user. NOT overridable (unlike the
7576
# other names above): the role name is hardcoded in migrations/002
@@ -409,36 +410,29 @@ ensure_oidc_provider() {
409410
OIDC_PROVIDER_ARN="$provider_arn"
410411
}
411412

413+
# Exact subjects deliberately exclude PRs, tags, and retired migration branches.
414+
# Reprovisioning replaces trust with this declared policy, including on existing roles.
415+
github_trust_policy() {
416+
jq -n --arg account "$TARGET_ACCOUNT" --arg repo "$1" '{
417+
Version: "2012-10-17",
418+
Statement: [{
419+
Effect: "Allow",
420+
Principal: {Federated: ("arn:aws:iam::" + $account + ":oidc-provider/token.actions.githubusercontent.com")},
421+
Action: "sts:AssumeRoleWithWebIdentity",
422+
Condition: {StringEquals: {
423+
"token.actions.githubusercontent.com:aud": "sts.amazonaws.com",
424+
"token.actions.githubusercontent.com:sub": ("repo:" + $repo + ":ref:refs/heads/develop")
425+
}}
426+
}]
427+
}'
428+
}
429+
412430
ensure_schema_role() {
413431
log "Step 6: GitHub Actions OIDC role ${SCHEMA_ROLE_NAME}."
414432
ensure_oidc_provider
415433

416-
# Trust-policy sub-claim is scoped to the specific branches the
417-
# schema-deploy.yml workflow runs on (`develop` + `ct/bench-v4`); this
418-
# restriction is the gate against unauthorized OIDC role assumption. A
419-
# `schema-deploy` GitHub Environment / manual-approval gate was deliberately
420-
# declined per the 2026-05-29 deploy-model decision (it only re-confirms the
421-
# authorization already given at PR merge; execution safety comes from the
422-
# per-PR testcontainer migration test), NOT deferred for lack of repo-admin.
423434
local trust_policy
424-
trust_policy=$(cat <<EOF
425-
{
426-
"Version": "2012-10-17",
427-
"Statement": [{
428-
"Effect": "Allow",
429-
"Principal": {"Federated": "${OIDC_PROVIDER_ARN}"},
430-
"Action": "sts:AssumeRoleWithWebIdentity",
431-
"Condition": {
432-
"StringEquals": {"token.actions.githubusercontent.com:aud": "sts.amazonaws.com"},
433-
"StringLike": {"token.actions.githubusercontent.com:sub": [
434-
"repo:${GITHUB_REPO}:ref:refs/heads/develop",
435-
"repo:${GITHUB_REPO}:ref:refs/heads/ct/bench-v4"
436-
]}
437-
}
438-
}]
439-
}
440-
EOF
441-
)
435+
trust_policy=$(github_trust_policy "$SCHEMA_GITHUB_REPO")
442436

443437
if SCHEMA_ROLE_ARN=$(aws iam get-role --role-name "$SCHEMA_ROLE_NAME" \
444438
--query 'Role.Arn' --output text 2>/dev/null); then
@@ -493,29 +487,8 @@ ensure_ingest_role() {
493487
log "Step 6b: GitHub Actions OIDC role ${INGEST_ROLE_NAME}."
494488
ensure_oidc_provider
495489

496-
# Same branch-scoped trust as the schema role: the Phase-2 dual-write CI
497-
# workflows (bench.yml / sql-benchmarks.yml / v3-commit-metadata.yml) ingest on
498-
# push to `develop`, plus `ct/bench-v4` during the migration's dual-write soak.
499-
# The sub-claim restriction is the gate against unauthorized role assumption.
500490
local trust_policy
501-
trust_policy=$(cat <<EOF
502-
{
503-
"Version": "2012-10-17",
504-
"Statement": [{
505-
"Effect": "Allow",
506-
"Principal": {"Federated": "${OIDC_PROVIDER_ARN}"},
507-
"Action": "sts:AssumeRoleWithWebIdentity",
508-
"Condition": {
509-
"StringEquals": {"token.actions.githubusercontent.com:aud": "sts.amazonaws.com"},
510-
"StringLike": {"token.actions.githubusercontent.com:sub": [
511-
"repo:${GITHUB_REPO}:ref:refs/heads/develop",
512-
"repo:${GITHUB_REPO}:ref:refs/heads/ct/bench-v4"
513-
]}
514-
}
515-
}]
516-
}
517-
EOF
518-
)
491+
trust_policy=$(github_trust_policy "$INGEST_GITHUB_REPO")
519492

520493
if INGEST_ROLE_ARN=$(aws iam get-role --role-name "$INGEST_ROLE_NAME" \
521494
--query 'Role.Arn' --output text 2>/dev/null); then
@@ -631,6 +604,18 @@ EOF
631604
# -----------------------------------------------------------------------------
632605

633606
main() {
607+
[ -z "${GITHUB_REPO+x}" ] || die "GITHUB_REPO was replaced by SCHEMA_GITHUB_REPO and INGEST_GITHUB_REPO."
608+
if [ "$#" -gt 0 ]; then
609+
if [ "$#" -eq 2 ] && [ "$1" = "--print-trust" ]; then
610+
case "$2" in
611+
schema) github_trust_policy "$SCHEMA_GITHUB_REPO" ;;
612+
ingest) github_trust_policy "$INGEST_GITHUB_REPO" ;;
613+
*) die "Expected schema or ingest." ;;
614+
esac
615+
return
616+
fi
617+
die "Usage: provision.sh [--print-trust schema|ingest]"
618+
fi
634619
verify_prereqs
635620
discover_default_vpc
636621
ensure_db_subnet_group

‎infra/tests/test_oidc_trust.py‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
# SPDX-License-Identifier: Apache-2.0
2+
# SPDX-FileCopyrightText: Copyright the Vortex contributors
3+
4+
import json
5+
import os
6+
from pathlib import Path
7+
import subprocess
8+
import unittest
9+
10+
SCRIPT = Path(__file__).resolve().parents[1] / "provision.sh"
11+
12+
13+
class TrustTests(unittest.TestCase):
14+
def render(self, role, **overrides):
15+
env = {k: v for k, v in os.environ.items()
16+
if k not in {"GITHUB_REPO", "SCHEMA_GITHUB_REPO", "INGEST_GITHUB_REPO", "TARGET_ACCOUNT"}}
17+
env.update(overrides)
18+
return subprocess.run(["bash", str(SCRIPT), "--print-trust", role],
19+
env=env, text=True, capture_output=True)
20+
21+
def test_exact_default_subjects(self):
22+
for role, repo in (("schema", "benchmarks-website"), ("ingest", "vortex")):
23+
result = self.render(role)
24+
self.assertEqual(result.returncode, 0, result.stderr)
25+
statement, = json.loads(result.stdout)["Statement"]
26+
self.assertEqual(statement["Action"], "sts:AssumeRoleWithWebIdentity")
27+
self.assertEqual(statement["Principal"], {"Federated":
28+
"arn:aws:iam::245040174862:oidc-provider/token.actions.githubusercontent.com"})
29+
self.assertEqual(statement["Condition"], {"StringEquals": {
30+
"token.actions.githubusercontent.com:aud": "sts.amazonaws.com",
31+
"token.actions.githubusercontent.com:sub": f"repo:vortex-data/{repo}:ref:refs/heads/develop",
32+
}})
33+
34+
def test_repository_inputs_are_independent(self):
35+
overrides = {"SCHEMA_GITHUB_REPO": "example/schema", "INGEST_GITHUB_REPO": "example/writer"}
36+
for role, repo in (("schema", "schema"), ("ingest", "writer")):
37+
result = self.render(role, **overrides)
38+
self.assertEqual(result.returncode, 0, result.stderr)
39+
condition = json.loads(result.stdout)["Statement"][0]["Condition"]
40+
self.assertEqual(condition["StringEquals"]["token.actions.githubusercontent.com:sub"],
41+
f"repo:example/{repo}:ref:refs/heads/develop")
42+
43+
def test_shared_legacy_input_is_rejected(self):
44+
result = self.render("schema", GITHUB_REPO="example/old")
45+
self.assertNotEqual(result.returncode, 0)
46+
self.assertIn("was replaced", result.stderr)

0 commit comments

Comments
 (0)