DO block dollar-quote collides with HCL $$ escape (Job fails with syntax error) #317

New issue

Closed

opened 2026-04-30 11:15:18 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-04-30 11:15:18 +00:00

Contributor

Type

Bug

Lineage

Discovered scope from operator-side make tofu-apply (2026-04-30 by Ava). PR #304 added a SQL Job using DO $$ ... $$; for an anonymous block. HCL strings interpret $$ as an escape for $ (to avoid Terraform's ${...} interpolation), so the rendered SQL was DO $ ... $; (single dollar each). Postgres rejected with syntax error at or near "$" at character 4. Job hit BackoffLimitExceeded after 4 retries.

Repo

forgejo_admin/pal-e-platform

What Broke

module.database.kubernetes_job_v1.admin_app_user_provision runs psql against basketball-api postgres with this SQL:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'admin_app') THEN ...
END
$$;

But Postgres logs show:

ERROR:  syntax error at or near "$" at character 4
STATEMENT:  DO $
   BEGIN
     IF NOT EXISTS ...

The double-dollar dollar-quote $$ got collapsed to single $ by HCL string templating. HCL uses $$ as the literal-$ escape (to prevent Terraform's ${expression} interpolation from triggering). So DO $$ in the HCL source renders as DO $ in the final Job spec.

Job retried 4× (BackoffLimitExceeded), all failed with same syntax error. Apply errored.

Repro Steps

cd ~/pal-e-platform/terraform && tofu apply -lock=false -target=module.database
Apply creates the Job; Job runs psql with the rendered SQL
Pod fails: kubectl logs -n basketball-api postgres-9b5b87b5-* --tail=10 shows syntax error at or near "$" at character 4
Job hits BackoffLimitExceeded, apply errors out

Expected Behavior

Job runs the SQL successfully, creates admin_app role, grants permissions. Apply completes.

Environment

pal-e-platform terraform/modules/database/main.tf lines 193, 201
HCL string templating rules: $$ → $ (single dollar in output)
Postgres anonymous DO blocks require $tag$ ... $tag$ dollar-quoting (where tag can be empty $$ ... $$)

User Story

story:admin-row-crud — As the operator running make tofu-apply, I need the admin_app provisioning Job's SQL to actually parse so the role gets created and the deploy chain proceeds.

Architecture

arch:postgres

Context

Two valid fixes:

Quadruple dollars DO $$$$ ... $$$$; — HCL renders as DO $$ ... $$; correctly, but visually confusing
Named dollar-quote DO $body$ ... $body$; — postgres treats any matched $tag$ as dollar-quote delimiter; $body$ has no $$ so HCL doesn't escape; visually clearer

Going with #2 — self-documenting and avoids the HCL escape collision entirely.

This is the third post-merge runtime gap during this bootstrap (after salt master crash + #310 label /). Reinforces the lesson captured as feedback_tofu_validate_not_k8s_api: tofu validate doesn't catch HCL string-templating semantic errors that surface at runtime.

File Targets

terraform/modules/database/main.tf lines 193 + 201 — change $$ to $body$

Acceptance Criteria

DO $body$ and $body$; replace DO $$ and $$; in the SQL heredoc
tofu apply -target=module.database completes without Job failure
kubectl logs of the Job pod shows ==> admin_app role provisioned successfully
psql ... -c "\du admin_app" from the postgres pod shows the role exists

Test Expectations

After fix + apply: Job pod runs to completion (status Succeeded)
admin_app role exists in basketball-api Postgres with login + password
admin-app-db-url secret created in both basketball-api AND westside-admin namespaces

Constraints

Don't restructure the SQL — minimum diff
Don't change the password handling, role permissions, or default privileges
Don't introduce a new variable

Checklist

Reviewed via /review-ticket
HCL fix applied
Local apply re-attempted
PR opened with Closes #THIS
Merged via standard hook

Caused by: PR #304 (Postgres provisioning Job, merged unblocked)
Sibling lesson: pal-e-platform#315 (label value / rejected by k8s API)
Memory: feedback_tofu_validate_not_k8s_api, feedback_verification_before_completion

### Type Bug ### Lineage Discovered scope from operator-side `make tofu-apply` (2026-04-30 by Ava). PR #304 added a SQL Job using `DO $$ ... $$;` for an anonymous block. HCL strings interpret `$$` as an escape for `$` (to avoid Terraform's `${...}` interpolation), so the rendered SQL was `DO $ ... $;` (single dollar each). Postgres rejected with `syntax error at or near "$" at character 4`. Job hit BackoffLimitExceeded after 4 retries. ### Repo forgejo_admin/pal-e-platform ### What Broke `module.database.kubernetes_job_v1.admin_app_user_provision` runs psql against basketball-api postgres with this SQL: ```sql DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'admin_app') THEN ... END $$; ``` But Postgres logs show: ``` ERROR: syntax error at or near "$" at character 4 STATEMENT: DO $ BEGIN IF NOT EXISTS ... ``` The double-dollar dollar-quote `$$` got collapsed to single `$` by HCL string templating. HCL uses `$$` as the literal-`$` escape (to prevent Terraform's `${expression}` interpolation from triggering). So `DO $$` in the HCL source renders as `DO $` in the final Job spec. Job retried 4× (BackoffLimitExceeded), all failed with same syntax error. Apply errored. ### Repro Steps 1. `cd ~/pal-e-platform/terraform && tofu apply -lock=false -target=module.database` 2. Apply creates the Job; Job runs psql with the rendered SQL 3. Pod fails: `kubectl logs -n basketball-api postgres-9b5b87b5-* --tail=10` shows `syntax error at or near "$" at character 4` 4. Job hits BackoffLimitExceeded, apply errors out ### Expected Behavior Job runs the SQL successfully, creates `admin_app` role, grants permissions. Apply completes. ### Environment - pal-e-platform `terraform/modules/database/main.tf` lines 193, 201 - HCL string templating rules: `$$` → `$` (single dollar in output) - Postgres anonymous DO blocks require `$tag$ ... $tag$` dollar-quoting (where tag can be empty `$$ ... $$`) ### User Story story:admin-row-crud — As the operator running `make tofu-apply`, I need the admin_app provisioning Job's SQL to actually parse so the role gets created and the deploy chain proceeds. ### Architecture arch:postgres ### Context Two valid fixes: 1. **Quadruple dollars** `DO $$$$ ... $$$$;` — HCL renders as `DO $$ ... $$;` correctly, but visually confusing 2. **Named dollar-quote** `DO $body$ ... $body$;` — postgres treats any matched `$tag$` as dollar-quote delimiter; `$body$` has no `$$` so HCL doesn't escape; visually clearer Going with #2 — self-documenting and avoids the HCL escape collision entirely. This is the third post-merge runtime gap during this bootstrap (after salt master crash + #310 label `/`). Reinforces the lesson captured as `feedback_tofu_validate_not_k8s_api`: tofu validate doesn't catch HCL string-templating semantic errors that surface at runtime. ### File Targets - `terraform/modules/database/main.tf` lines 193 + 201 — change `$$` to `$body$` ### Acceptance Criteria - [ ] `DO $body$` and `$body$;` replace `DO $$` and `$$;` in the SQL heredoc - [ ] `tofu apply -target=module.database` completes without Job failure - [ ] `kubectl logs` of the Job pod shows `==> admin_app role provisioned successfully` - [ ] `psql ... -c "\du admin_app"` from the postgres pod shows the role exists ### Test Expectations - After fix + apply: Job pod runs to completion (status `Succeeded`) - `admin_app` role exists in basketball-api Postgres with login + password - `admin-app-db-url` secret created in both basketball-api AND westside-admin namespaces ### Constraints - Don't restructure the SQL — minimum diff - Don't change the password handling, role permissions, or default privileges - Don't introduce a new variable ### Checklist - [ ] Reviewed via /review-ticket - [ ] HCL fix applied - [ ] Local apply re-attempted - [ ] PR opened with `Closes #THIS` - [ ] Merged via standard hook ### Related - Caused by: PR #304 (Postgres provisioning Job, merged unblocked) - Sibling lesson: `pal-e-platform#315` (label value `/` rejected by k8s API) - Memory: `feedback_tofu_validate_not_k8s_api`, `feedback_verification_before_completion`