#126 - Fix pgvector extension ownership — remove CREATE EXTENSION from migration - ldraney/pal-e-api

forgejo_admin commented

2026-03-09 03:40:48 +00:00

Contributor

Lineage

plan-2026-02-26-tf-modularize-postgres → Phase 6 → Phase 6b-1 (extension ownership fix)

Repo

forgejo_admin/pal-e-docs (migration fix)
forgejo_admin/deployments (CNPG CRD — extension provisioning)

User Story

As a platform operator
I want pgvector extension creation to follow the platform-provides/app-consumes pattern
So that Alembic migrations don't need superuser privileges and fresh deployments work without manual intervention

Context

PR #122 added a pgvector schema migration that includes CREATE EXTENSION IF NOT EXISTS vector. This fails at deploy time because the Alembic migration runs as the paledocs app user, which is NOT a superuser. CREATE EXTENSION requires superuser privileges in PostgreSQL.

We fixed this manually by running CREATE EXTENSION as the postgres superuser via kubectl exec. But this is a band-aid — the migration will fail again on any fresh cluster bootstrap.

The enterprise fix follows the same pattern as CNPG itself: platform provides the extension, app assumes it exists.

File Targets

pal-e-docs (migration fix):

alembic/versions/l2g3h4i5j6k7_add_vector_embeddings.py — remove CREATE EXTENSION IF NOT EXISTS vector from upgrade(). Replace with a check that the extension exists (raise informative error if not). Remove DROP EXTENSION from downgrade().

deployments (CNPG CRD — extension provisioning):

Find the CNPG Cluster CRD YAML and add pgvector to bootstrap.initdb.postInitApplicationSQL or equivalent, so the extension is created automatically on cluster bootstrap as superuser. Example:
```
bootstrap:
  initdb:
    postInitSQL:
      - CREATE EXTENSION IF NOT EXISTS vector
```

Acceptance Criteria

Migration no longer runs CREATE EXTENSION — it checks and fails with a clear error if the extension is missing
Migration downgrade no longer runs DROP EXTENSION
CNPG Cluster CRD provisions the pgvector extension on bootstrap
Existing tests still pass
App deploys cleanly (current cluster already has extension installed)

Test Expectations

pytest tests/ -v passes
Migration upgrade is idempotent (safe to re-run)

Constraints

Do NOT change the migration revision ID or chain — only modify the upgrade/downgrade functions
The extension is already installed on the running cluster — this is a future-proofing fix
Follow the pattern: platform provides capability (extension), app consumes it (columns, indexes, triggers)

Checklist

PR opened (pal-e-docs)
PR opened (deployments) — if CRD change needed
Tests pass
No unrelated changes

phase-postgres-6-vector-search — parent phase
Issue #121 / PR #122 — original migration that introduced the problem
plan-2026-02-26-tf-modularize-postgres Architecture Revision — "platform provides capability, apps consume it"

### Lineage `plan-2026-02-26-tf-modularize-postgres` → Phase 6 → Phase 6b-1 (extension ownership fix) ### Repo `forgejo_admin/pal-e-docs` (migration fix) `forgejo_admin/deployments` (CNPG CRD — extension provisioning) ### User Story As a platform operator I want pgvector extension creation to follow the platform-provides/app-consumes pattern So that Alembic migrations don't need superuser privileges and fresh deployments work without manual intervention ### Context PR #122 added a pgvector schema migration that includes `CREATE EXTENSION IF NOT EXISTS vector`. This fails at deploy time because the Alembic migration runs as the `paledocs` app user, which is NOT a superuser. `CREATE EXTENSION` requires superuser privileges in PostgreSQL. We fixed this manually by running `CREATE EXTENSION` as the `postgres` superuser via `kubectl exec`. But this is a band-aid — the migration will fail again on any fresh cluster bootstrap. The enterprise fix follows the same pattern as CNPG itself: **platform provides the extension, app assumes it exists.** ### File Targets **pal-e-docs (migration fix):** - `alembic/versions/l2g3h4i5j6k7_add_vector_embeddings.py` — remove `CREATE EXTENSION IF NOT EXISTS vector` from `upgrade()`. Replace with a check that the extension exists (raise informative error if not). Remove `DROP EXTENSION` from `downgrade()`. **deployments (CNPG CRD — extension provisioning):** - Find the CNPG Cluster CRD YAML and add pgvector to `bootstrap.initdb.postInitApplicationSQL` or equivalent, so the extension is created automatically on cluster bootstrap as superuser. Example: ```yaml bootstrap: initdb: postInitSQL: - CREATE EXTENSION IF NOT EXISTS vector ``` ### Acceptance Criteria - [ ] Migration no longer runs `CREATE EXTENSION` — it checks and fails with a clear error if the extension is missing - [ ] Migration downgrade no longer runs `DROP EXTENSION` - [ ] CNPG Cluster CRD provisions the pgvector extension on bootstrap - [ ] Existing tests still pass - [ ] App deploys cleanly (current cluster already has extension installed) ### Test Expectations - [ ] `pytest tests/ -v` passes - [ ] Migration upgrade is idempotent (safe to re-run) ### Constraints - Do NOT change the migration revision ID or chain — only modify the upgrade/downgrade functions - The extension is already installed on the running cluster — this is a future-proofing fix - Follow the pattern: platform provides capability (extension), app consumes it (columns, indexes, triggers) ### Checklist - [ ] PR opened (pal-e-docs) - [ ] PR opened (deployments) — if CRD change needed - [ ] Tests pass - [ ] No unrelated changes ### Related - `phase-postgres-6-vector-search` — parent phase - Issue #121 / PR #122 — original migration that introduced the problem - `plan-2026-02-26-tf-modularize-postgres` Architecture Revision — "platform provides capability, apps consume it"