pal-e-docs

Ticket: Create westside_ops_reader Postgres role + GRANT allowlist

ticket-westside-ops-postgres-role Doc

active backlog westside-ops ticket
Project

Ticket: westside_ops_reader Postgres role + GRANT allowlist

Story: story-westside-ops-spreadsheet-access
Architecture: arch-domain-westside-ops (Key Decisions: defense-in-depth at role level)
Labels: story:spreadsheet-access,arch:postgres-role,type:infra,track:backend,scope:planned
Blocks: k8s overlay ticket (needs DB URL + password to populate the SOPS secret)
Blocked by: Streamlit spike ticket

Purpose

Create a dedicated Postgres role inside basketball-api's existing Postgres pod that is the only credential westside-ops ever uses to talk to the database. The role has explicit GRANTs on 14 tables and cannot read oauth_tokens, password_reset_tokens, or outbox. This is the load-bearing security control: even if the Streamlit app had a bug that tried to execute arbitrary SQL, those three tables would return permission denied.
basketball-api is hands-off per convention — this is the only touch we make, and it's an additive SQL migration run once via kubectl exec. No code changes to basketball-api.

Scope

  • Generate a strong password for the new role (32+ random bytes). Store locally at ~/secrets/westside-ops/reader-db-password following the existing ~/secrets/{service}/ convention documented in ~/secrets/README.md
  • Write a one-shot SQL file containing the CREATE ROLE + GRANT statements (see exact SQL below)
  • Apply via kubectl exec -n basketball-api <postgres-pod> -- psql -U basketball -d basketball -f - (piping the SQL file in)
  • Verify the allowlist works: connect as the new role and run a SELECT on every allowed table; confirm it succeeds
  • Verify the blocklist works: connect as the new role and attempt SELECT * FROM oauth_tokens, SELECT * FROM password_reset_tokens, SELECT * FROM outbox; confirm all three return permission denied

Exact SQL

Verification

Acceptance Criteria

  • [ ] ~/secrets/westside-ops/reader-db-password exists with 32+ bytes of entropy, not committed anywhere
  • [ ] \du westside_ops_reader in the basketball-api postgres pod shows the role exists with LOGIN attribute
  • [ ] All 14 allowed tables return a row count when queried as westside_ops_reader
  • [ ] All 3 forbidden tables return permission denied when queried as westside_ops_reader
  • [ ] SQL file archived at ~/westside-ops/migrations/001-create-reader-role.sql (committed later with the repo bootstrap, for reference)
  • [ ] Migration is idempotent — running the SQL twice does not error

Files touched

  • ~/secrets/westside-ops/reader-db-password (new, local-only)
  • basketball-api Postgres state (CREATE ROLE + GRANTs)
  • Eventually: ~/westside-ops/migrations/001-create-reader-role.sql (committed with repo bootstrap ticket)

Rollback

Requires disconnecting any active connections first. No data is affected by dropping the role.

Out of scope

  • Row-Level Security (RLS) policies — deferred to a later story if Marcus's personal-data view ever needs "Marcus sees only his row" semantics. For v1 operator use, table-level GRANTs are sufficient.
  • Read-write (INSERT/UPDATE/DELETE) grants — v1 is read-only per the user story. Write access is a future expansion ticket.
  • Any changes to basketball-api's application code, alembic migrations, or Python files.

Dependencies

Streamlit spike ticket must pass veto gate first. Otherwise nothing — this ticket is independent of repo/CI/overlay work and can execute in parallel with them.