ldraney/pal-e-platform

Fork 0

k8s Job: provision admin_app Postgres user + grants on basketball db #302

New issue

Closed

opened 2026-04-25 22:03:15 +00:00 by forgejo_admin · 2 comments

forgejo_admin commented

2026-04-25 22:03:15 +00:00

Contributor

Type

Task

Lineage

Companion to westside-admin deploy. Independent — can start before app code exists. Blocks forgejo_admin/westside-admin#1 (Drizzle integration test needs to connect as admin_app) and forgejo_admin/pal-e-deployments#133 (overlay's DATABASE_URL secret depends on this user existing).

Scope-revision lineage: Original ticket offered three options (terraform via cyrilgdn provider, k8s Job, manual SOP) and asked dev agent to pick. Scope review (review-1097-2026-04-25) discovered:

cyrilgdn/postgresql provider is NOT registered in pal-e-platform (would require provider expansion)
basketball-api Postgres is a plain postgres:16-alpine Deployment in pal-e-deployments/overlays/basketball-api/prod/postgres.yaml, NOT CNPG
The only existing user-management precedent (paledocs_db_url in terraform/modules/database/main.tf) targets the CNPG pal-e-postgres-rw cluster — different infra, not applicable
No admin_app or sub-user pattern exists anywhere — this is genuinely new ground

Decision (path A — k8s Job in pal-e-deployments): provision via an idempotent k8s Job manifest that runs psql against the existing basketball-api Postgres pod. Lower scope expansion than adding a terraform provider. Job is idempotent (uses IF NOT EXISTS patterns) so it can be re-run safely.

Repo

forgejo_admin/pal-e-platform (this ticket's tracking repo). The actual k8s Job manifest lands in pal-e-deployments/overlays/basketball-api/prod/ (or a sibling overlay) — that PR is part of this ticket's deliverable.

Scope

Three deliverables, all must complete:

1. Generate admin_app password. A 32-character random hex string (e.g., openssl rand -hex 32). Store in pal-e-platform's secret pattern (or document where; existing pattern likely in terraform/modules/database/ for paledocs's CNPG cluster — replicate the shape).

2. Create idempotent k8s Job manifest in pal-e-deployments/overlays/basketball-api/prod/admin-app-user-job.yaml (or similar). The Job:

Runs postgres:16-alpine image
Mounts the basketball-api postgres superuser credentials (existing Secret) for connecting as superuser
Mounts the new admin_app password as a Secret env var

Runs SQL via psql:

DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'admin_app') THEN
    CREATE ROLE admin_app WITH LOGIN PASSWORD '<from env>';
  END IF;
END $$;

GRANT USAGE ON SCHEMA public TO admin_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO admin_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO admin_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO admin_app;

restartPolicy: OnFailure
backoffLimit: 4

Add the Job to kustomization.yaml resources so ArgoCD applies it. Job runs to completion on apply; safe to re-run on grant updates.

3. Mirror the password Secret to pal-e-deployments/overlays/westside-admin/prod/westside-admin-secrets.enc.yaml as DATABASE_URL=postgresql://admin_app:<password>@postgres.basketball-api.svc.cluster.local:5432/basketball. Coordinate with forgejo_admin/pal-e-deployments#133.

After apply: verify the three connection tests below all behave as expected. Document the password rotation procedure as a brief SOP note in pal-e-docs (slug sop-admin-app-password-rotation, tag sop,active).

Acceptance Criteria

Postgres role admin_app exists on basketball-api Postgres after Job runs
Role has USAGE + SELECT/INSERT/UPDATE/DELETE on schema public, all current tables, all future tables (via ALTER DEFAULT PRIVILEGES), all sequences
Role does NOT have CREATE/ALTER/DROP/superuser
Password generated (random, ≥32 chars, hex) and stored in pal-e-platform secret pattern with documented reference path
DATABASE_URL written to westside-admin-secrets.enc.yaml in pal-e-deployments overlay (companion to #133)
Job manifest is IDEMPOTENT — re-running it produces no errors and no changes
Connection test (read): psql "$DATABASE_URL" -c "SELECT 1" succeeds (run from a debug pod)
Connection test (write): INSERT/UPDATE/DELETE on a test row succeeds
Connection test (blocked): psql "$DATABASE_URL" -c "CREATE TABLE foo (id int)" FAILS with permission denied
SOP note sop-admin-app-password-rotation created in pal-e-docs

Test Expectations

Manual (after Job runs): three psql connection tests above all behave as expected
Manual (idempotency): re-trigger ArgoCD sync; Job re-runs without errors
Manual (forward grant test): create a test table as the basketball superuser, verify admin_app immediately has SELECT/INSERT/UPDATE/DELETE on it (proves ALTER DEFAULT PRIVILEGES works)

Constraints

DO NOT use the cyrilgdn/postgresql terraform provider (out of scope; would require provider expansion)
DO NOT modify the basketball-api Deployment or its existing Postgres superuser credentials
DO NOT grant CREATE/ALTER/DROP/superuser to admin_app
Job must be idempotent — IF NOT EXISTS patterns in SQL, declarative resource definition
Password rotation procedure documented in companion SOP

Checklist

Password generated and stored in pal-e-platform secret pattern
k8s Job manifest committed to pal-e-deployments
DATABASE_URL written to westside-admin-secrets in pal-e-deployments overlay
All three connection tests verified
Idempotency verified by re-running
SOP note created
PR description (in pal-e-deployments) documents the rationale + links review-1097-2026-04-25

project-westside-admin
arch-deployment-westside-admin
review-1097-2026-04-25 (scope investigation that drove path A)
feedback_never_write_prod_db (this is the legitimized write user)
BLOCKS: forgejo_admin/westside-admin#1 (Drizzle), forgejo_admin/pal-e-deployments#133 (overlay secrets)

### Type Task ### Lineage Companion to westside-admin deploy. Independent — can start before app code exists. Blocks `forgejo_admin/westside-admin#1` (Drizzle integration test needs to connect as admin_app) and `forgejo_admin/pal-e-deployments#133` (overlay's DATABASE_URL secret depends on this user existing). **Scope-revision lineage:** Original ticket offered three options (terraform via cyrilgdn provider, k8s Job, manual SOP) and asked dev agent to pick. Scope review (`review-1097-2026-04-25`) discovered: - `cyrilgdn/postgresql` provider is NOT registered in pal-e-platform (would require provider expansion) - basketball-api Postgres is a plain `postgres:16-alpine` Deployment in `pal-e-deployments/overlays/basketball-api/prod/postgres.yaml`, NOT CNPG - The only existing user-management precedent (`paledocs_db_url` in `terraform/modules/database/main.tf`) targets the CNPG `pal-e-postgres-rw` cluster — different infra, not applicable - No `admin_app` or sub-user pattern exists anywhere — this is genuinely new ground Decision (path A — k8s Job in pal-e-deployments): provision via an idempotent k8s Job manifest that runs `psql` against the existing basketball-api Postgres pod. Lower scope expansion than adding a terraform provider. Job is idempotent (uses `IF NOT EXISTS` patterns) so it can be re-run safely. ### Repo `forgejo_admin/pal-e-platform` (this ticket's tracking repo). The actual k8s Job manifest lands in `pal-e-deployments/overlays/basketball-api/prod/` (or a sibling overlay) — that PR is part of this ticket's deliverable. ### Scope Three deliverables, all must complete: **1. Generate admin_app password.** A 32-character random hex string (e.g., `openssl rand -hex 32`). Store in pal-e-platform's secret pattern (or document where; existing pattern likely in `terraform/modules/database/` for paledocs's CNPG cluster — replicate the shape). **2. Create idempotent k8s Job manifest** in `pal-e-deployments/overlays/basketball-api/prod/admin-app-user-job.yaml` (or similar). The Job: - Runs `postgres:16-alpine` image - Mounts the basketball-api postgres superuser credentials (existing Secret) for connecting as superuser - Mounts the new admin_app password as a Secret env var - Runs SQL via `psql`: ```sql DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'admin_app') THEN CREATE ROLE admin_app WITH LOGIN PASSWORD '<from env>'; END IF; END $$; GRANT USAGE ON SCHEMA public TO admin_app; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO admin_app; GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO admin_app; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin_app; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO admin_app; ``` - `restartPolicy: OnFailure` - `backoffLimit: 4` Add the Job to `kustomization.yaml` resources so ArgoCD applies it. Job runs to completion on apply; safe to re-run on grant updates. **3. Mirror the password Secret** to `pal-e-deployments/overlays/westside-admin/prod/westside-admin-secrets.enc.yaml` as `DATABASE_URL=postgresql://admin_app:<password>@postgres.basketball-api.svc.cluster.local:5432/basketball`. Coordinate with `forgejo_admin/pal-e-deployments#133`. After apply: verify the three connection tests below all behave as expected. Document the password rotation procedure as a brief SOP note in pal-e-docs (slug `sop-admin-app-password-rotation`, tag `sop,active`). ### Acceptance Criteria - [ ] Postgres role `admin_app` exists on basketball-api Postgres after Job runs - [ ] Role has USAGE + SELECT/INSERT/UPDATE/DELETE on schema public, all current tables, all future tables (via ALTER DEFAULT PRIVILEGES), all sequences - [ ] Role does NOT have CREATE/ALTER/DROP/superuser - [ ] Password generated (random, ≥32 chars, hex) and stored in pal-e-platform secret pattern with documented reference path - [ ] DATABASE_URL written to `westside-admin-secrets.enc.yaml` in pal-e-deployments overlay (companion to #133) - [ ] Job manifest is IDEMPOTENT — re-running it produces no errors and no changes - [ ] Connection test (read): `psql "$DATABASE_URL" -c "SELECT 1"` succeeds (run from a debug pod) - [ ] Connection test (write): INSERT/UPDATE/DELETE on a test row succeeds - [ ] Connection test (blocked): `psql "$DATABASE_URL" -c "CREATE TABLE foo (id int)"` FAILS with permission denied - [ ] SOP note `sop-admin-app-password-rotation` created in pal-e-docs ### Test Expectations - Manual (after Job runs): three psql connection tests above all behave as expected - Manual (idempotency): re-trigger ArgoCD sync; Job re-runs without errors - Manual (forward grant test): create a test table as the basketball superuser, verify admin_app immediately has SELECT/INSERT/UPDATE/DELETE on it (proves ALTER DEFAULT PRIVILEGES works) ### Constraints - DO NOT use the cyrilgdn/postgresql terraform provider (out of scope; would require provider expansion) - DO NOT modify the basketball-api Deployment or its existing Postgres superuser credentials - DO NOT grant CREATE/ALTER/DROP/superuser to admin_app - Job must be idempotent — `IF NOT EXISTS` patterns in SQL, declarative resource definition - Password rotation procedure documented in companion SOP ### Checklist - [ ] Password generated and stored in pal-e-platform secret pattern - [ ] k8s Job manifest committed to pal-e-deployments - [ ] DATABASE_URL written to westside-admin-secrets in pal-e-deployments overlay - [ ] All three connection tests verified - [ ] Idempotency verified by re-running - [ ] SOP note created - [ ] PR description (in pal-e-deployments) documents the rationale + links `review-1097-2026-04-25` ### Related - `project-westside-admin` - `arch-deployment-westside-admin` - `review-1097-2026-04-25` (scope investigation that drove path A) - `feedback_never_write_prod_db` (this is the legitimized write user) - BLOCKS: `forgejo_admin/westside-admin#1` (Drizzle), `forgejo_admin/pal-e-deployments#133` (overlay secrets)

forgejo_admin commented

2026-04-25 22:08:59 +00:00

Author

Contributor

Scope Review: NEEDS_REFINEMENT

Review note: review-1097-2026-04-25

Pre-investigation done: cyrilgdn/postgresql provider is NOT registered in pal-e-platform, and basketball-api Postgres is a plain postgres:16-alpine Deployment (NOT CNPG). The 3-option fork in the body should be replaced with a concrete chosen approach.

Issues to refine:

[BODY] Pick one provisioning approach (recommend: one-shot Job/migration via kustomize, or terraform kubernetes_job_v1 running psql) — drop the cyrilgdn option since adding a provider is out of scope.
[BODY] Specify the secret-handoff contract: does this ticket create the consumer Secret in westside-admin ns directly (paledocs precedent), or does pal-e-deployments#133 own it via SealedSecret? The two tickets need a clear interface.
[SCOPE] Missing backing arch note arch-deployment-westside-admin (referenced by project-westside-admin but not yet created). Optionally arch-postgres-grants as sub-component.

Strengths: traceability story leg verified, 8 AC are sharp and testable, blast radius low, no decomposition needed.

Once body is tightened and arch stub exists, this is READY.

## Scope Review: NEEDS_REFINEMENT Review note: `review-1097-2026-04-25` Pre-investigation done: `cyrilgdn/postgresql` provider is NOT registered in pal-e-platform, and basketball-api Postgres is a plain `postgres:16-alpine` Deployment (NOT CNPG). The 3-option fork in the body should be replaced with a concrete chosen approach. Issues to refine: - `[BODY]` Pick one provisioning approach (recommend: one-shot Job/migration via kustomize, or terraform `kubernetes_job_v1` running psql) — drop the cyrilgdn option since adding a provider is out of scope. - `[BODY]` Specify the secret-handoff contract: does this ticket create the consumer Secret in `westside-admin` ns directly (paledocs precedent), or does pal-e-deployments#133 own it via SealedSecret? The two tickets need a clear interface. - `[SCOPE]` Missing backing arch note `arch-deployment-westside-admin` (referenced by `project-westside-admin` but not yet created). Optionally `arch-postgres-grants` as sub-component. Strengths: traceability story leg verified, 8 AC are sharp and testable, blast radius low, no decomposition needed. Once body is tightened and arch stub exists, this is READY.

forgejo_admin changed title from ~~Postgres admin_app user + grants on basketball db (for westside-admin)~~ to k8s Job: provision admin_app Postgres user + grants on basketball db

2026-04-25 22:35:14 +00:00

forgejo_admin commented

2026-04-25 23:43:25 +00:00

Author

Contributor

Scope Re-Review: READY

Review note: review-1097-2026-04-25-v2 (supersedes review-1097-2026-04-25)

Rewritten body addresses all v1 [BODY] recommendations: 3-option fork removed, Path A (idempotent k8s Job) explicitly chosen with rationale, SQL spelled out inline, secret handoff to #1095/#133 explicit, cyrilgdn provider explicitly out of scope.

Verified:

SQL idempotency holds: CREATE ROLE wrapped in DO $$ ... IF NOT EXISTS; GRANT and ALTER DEFAULT PRIVILEGES are idempotent by Postgres semantics.
Grants are minimal: USAGE on schema + SELECT/INSERT/UPDATE/DELETE on tables + USAGE on sequences. No CREATE/ALTER/DROP. Blocked-write AC is the correct minimality signal.
DATABASE_URL handoff well-defined: postgres.basketball-api.svc.cluster.local:5432/basketball matches pal-e-deployments/overlays/basketball-api/prod/postgres.yaml (Service postgres, namespace basketball-api, DB basketball). Companion #1095 has unambiguous consumer contract.

Non-blocking carryovers (optional polish, not blockers for READY):

[SCOPE] (carryover from v1): create arch-deployment-westside-admin and stub arch-postgres backing notes. Story:arch traceability is incomplete without them, but doesn't block ticket execution. Recommend Ava queue as a separate scoping ticket.
[BODY] Optional: replace "superuser credentials" with "owner credentials" — the role basketball is DB owner, not formally SUPERUSER. SQL semantics are correct as written.
[BODY] Optional: explicit note that admin_app's password Secret must be a NEW Secret (not basketball-api-secrets) for blast-radius isolation. Implied but worth stating.

Cleared to advance backlog → todo.

## Scope Re-Review: READY Review note: `review-1097-2026-04-25-v2` (supersedes `review-1097-2026-04-25`) Rewritten body addresses all v1 [BODY] recommendations: 3-option fork removed, Path A (idempotent k8s Job) explicitly chosen with rationale, SQL spelled out inline, secret handoff to #1095/#133 explicit, cyrilgdn provider explicitly out of scope. **Verified:** - SQL idempotency holds: `CREATE ROLE` wrapped in `DO $$ ... IF NOT EXISTS`; `GRANT` and `ALTER DEFAULT PRIVILEGES` are idempotent by Postgres semantics. - Grants are minimal: USAGE on schema + SELECT/INSERT/UPDATE/DELETE on tables + USAGE on sequences. No CREATE/ALTER/DROP. Blocked-write AC is the correct minimality signal. - DATABASE_URL handoff well-defined: `postgres.basketball-api.svc.cluster.local:5432/basketball` matches `pal-e-deployments/overlays/basketball-api/prod/postgres.yaml` (Service `postgres`, namespace `basketball-api`, DB `basketball`). Companion #1095 has unambiguous consumer contract. **Non-blocking carryovers (optional polish, not blockers for READY):** - `[SCOPE]` (carryover from v1): create `arch-deployment-westside-admin` and stub `arch-postgres` backing notes. Story:arch traceability is incomplete without them, but doesn't block ticket execution. Recommend Ava queue as a separate scoping ticket. - `[BODY]` Optional: replace "superuser credentials" with "owner credentials" — the role `basketball` is DB owner, not formally SUPERUSER. SQL semantics are correct as written. - `[BODY]` Optional: explicit note that admin_app's password Secret must be a NEW Secret (not `basketball-api-secrets`) for blast-radius isolation. Implied but worth stating. Cleared to advance backlog → todo.

forgejo_admin referenced this issue from a commit

2026-04-25 23:59:32 +00:00

feat(database): provision admin_app Postgres role on basketball-api (#302)

forgejo_admin referenced this issue from a pull request that will close it,

2026-04-26 00:00:35 +00:00

feat(database): provision admin_app Postgres role on basketball-api (#302) #304

forgejo_admin referenced this issue

2026-04-26 00:02:36 +00:00

Add admin_app_db_password to Salt pillar (gates pal-e-platform#302 apply) #306

forgejo_admin

2026-04-26 00:19:42 +00:00