psql :var substitution fails inside DO dollar-quoted blocks (Job still failing after #318) #319

New issue

Closed

opened 2026-04-30 11:39:07 +00:00 by forgejo_admin · 0 comments

forgejo_admin commented

2026-04-30 11:39:07 +00:00

Contributor

Type

Bug

Lineage

Discovered scope post-#318 merge (2026-04-30). PR #318 fixed the DO $$ → DO $ HCL escape issue. Apply retry surfaced the next bug: :'admin_pw' (psql variable substitution) does NOT work inside DO $body$ ... $body$; blocks — psql treats the dollar-quoted body as a single literal token and skips substitution. Postgres receives literal :'admin_pw' and errors with syntax error at or near ":" at character 164.

Repo

forgejo_admin/pal-e-platform

What Broke

After #318 merged, the rendered SQL became valid HCL (body preserved). Apply retried, Job created fresh. Pod ran and hit:

ERROR:  syntax error at or near ":" at character 164
STATEMENT:  ...
   IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'admin_app') THEN
     EXECUTE format('CREATE ROLE admin_app WITH LOGIN PASSWORD %L', :'admin_pw');

The :'admin_pw' is psql client-side variable interpolation. psql substitutes :variable references in regular SQL but NOT inside dollar-quoted strings (treats them as literals). So the SQL sent to postgres still has the literal :'admin_pw' token, which is invalid SQL syntax.

This is a fundamental design conflict: cannot use psql variables inside DO $$...$$ blocks. The dev's original design (PR #304) tried to combine both. Either approach works alone; together they fail.

Repro Steps

Apply state with the post-#318 SQL: cd terraform && tofu apply -target=module.database
Job runs psql with the DO $body$ block referencing :'admin_pw'
Postgres logs show syntax error at or near ":" at character 164
Job hits BackoffLimitExceeded

Expected Behavior

Conditional CREATE/ALTER ROLE works idempotently with the password from the admin_pw psql variable.

Environment

pal-e-platform terraform/modules/database/main.tf lines 192-202
psql 16-alpine (Job container)
psql variable substitution rules: :var and :'var' work in regular SQL, NOT inside dollar-quoted strings

User Story

story:admin-row-crud — As the operator running the apply, I need the conditional CREATE/ALTER ROLE logic to actually use the password from the psql variable so the role gets provisioned.

Architecture

arch:postgres

Context

Three viable fixes:

psql \if/\gset (chosen) — drop the DO block, use psql client-side conditional outside any dollar-quoting. Cleanest; psql variable substitution works in regular SQL.
current_setting() server-side — SET app.admin_pw = '<literal>'; DO $body$ ... current_setting('app.admin_pw') ... $body$;. Requires literal password in SET (still needs psql substitution outside DO).
Always CREATE then always ALTER — let CREATE fail on duplicate, suppress error, then ALTER. Requires fiddling with ON_ERROR_STOP.

Going with #1. Replaces the procedural DO BEGIN IF NOT EXISTS ... ELSE ... END with:

SELECT EXISTS(...) AS role_exists \gset
\if :role_exists
  ALTER ROLE admin_app WITH LOGIN PASSWORD :'admin_pw';
\else
  CREATE ROLE admin_app WITH LOGIN PASSWORD :'admin_pw';
\endif

Same idempotent semantics, no dollar-quote, psql variables work natively.

This is the fourth post-merge runtime gap during the westside-admin bootstrap apply (after salt master crash, label /, and DO $$ HCL escape). All four would have been caught by a pre-merge tofu apply to a real cluster slice. Reinforces the lesson captured in feedback_tofu_validate_not_k8s_api. Worth a dedicated convention/process improvement after this loop closes.

File Targets

terraform/modules/database/main.tf lines 192-202 — replace the DO block with psql \if/\gset conditional

Acceptance Criteria

DO block replaced with psql \if + \gset pattern
tofu apply -target=module.database Job pod completes Succeeded
Pod logs show ==> admin_app role provisioned successfully
\du admin_app from postgres pod shows the role
Role login works with the rendered password

Test Expectations

After fix + apply: Job runs to Succeeded
Subsequent re-apply (rotation simulation) also succeeds (idempotent path verified)

Constraints

Don't change the surrounding GRANT statements, ALTER DEFAULT PRIVILEGES, or password env var handling
Don't introduce a new pillar var or terraform variable
Don't restructure the Job spec

Checklist

Reviewed via /review-ticket
Conditional rewritten with psql client-side primitives
Local apply re-attempted
PR opened with Closes #THIS
Merged via standard hook

Caused by: PR #304 (Postgres provisioning Job, design conflict between psql vars and DO blocks)
Sibling fixes: pal-e-platform#315 (label /), pal-e-platform#317 (DO HCL escape)
Memory: feedback_tofu_validate_not_k8s_api, feedback_verification_before_completion

### Type Bug ### Lineage Discovered scope post-#318 merge (2026-04-30). PR #318 fixed the `DO $$` → `DO $` HCL escape issue. Apply retry surfaced the next bug: `:'admin_pw'` (psql variable substitution) does NOT work inside `DO $body$ ... $body$;` blocks — psql treats the dollar-quoted body as a single literal token and skips substitution. Postgres receives literal `:'admin_pw'` and errors with `syntax error at or near ":" at character 164`. ### Repo forgejo_admin/pal-e-platform ### What Broke After #318 merged, the rendered SQL became valid HCL ($body$ preserved). Apply retried, Job created fresh. Pod ran and hit: ``` ERROR: syntax error at or near ":" at character 164 STATEMENT: ... IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'admin_app') THEN EXECUTE format('CREATE ROLE admin_app WITH LOGIN PASSWORD %L', :'admin_pw'); ``` The `:'admin_pw'` is psql client-side variable interpolation. psql substitutes `:variable` references in regular SQL but NOT inside dollar-quoted strings (treats them as literals). So the SQL sent to postgres still has the literal `:'admin_pw'` token, which is invalid SQL syntax. This is a fundamental design conflict: cannot use psql variables inside `DO $$...$$` blocks. The dev's original design (PR #304) tried to combine both. Either approach works alone; together they fail. ### Repro Steps 1. Apply state with the post-#318 SQL: `cd terraform && tofu apply -target=module.database` 2. Job runs psql with the `DO $body$` block referencing `:'admin_pw'` 3. Postgres logs show `syntax error at or near ":" at character 164` 4. Job hits BackoffLimitExceeded ### Expected Behavior Conditional CREATE/ALTER ROLE works idempotently with the password from the `admin_pw` psql variable. ### Environment - pal-e-platform `terraform/modules/database/main.tf` lines 192-202 - psql 16-alpine (Job container) - psql variable substitution rules: `:var` and `:'var'` work in regular SQL, NOT inside dollar-quoted strings ### User Story story:admin-row-crud — As the operator running the apply, I need the conditional CREATE/ALTER ROLE logic to actually use the password from the psql variable so the role gets provisioned. ### Architecture arch:postgres ### Context Three viable fixes: 1. **psql `\if`/`\gset` (chosen)** — drop the DO block, use psql client-side conditional outside any dollar-quoting. Cleanest; psql variable substitution works in regular SQL. 2. **`current_setting()` server-side** — `SET app.admin_pw = '<literal>'; DO $body$ ... current_setting('app.admin_pw') ... $body$;`. Requires literal password in SET (still needs psql substitution outside DO). 3. **Always CREATE then always ALTER** — let CREATE fail on duplicate, suppress error, then ALTER. Requires fiddling with `ON_ERROR_STOP`. Going with #1. Replaces the procedural `DO BEGIN IF NOT EXISTS ... ELSE ... END` with: ```sql SELECT EXISTS(...) AS role_exists \gset \if :role_exists ALTER ROLE admin_app WITH LOGIN PASSWORD :'admin_pw'; \else CREATE ROLE admin_app WITH LOGIN PASSWORD :'admin_pw'; \endif ``` Same idempotent semantics, no dollar-quote, psql variables work natively. This is the **fourth** post-merge runtime gap during the westside-admin bootstrap apply (after salt master crash, label `/`, and `DO $$` HCL escape). All four would have been caught by a pre-merge `tofu apply` to a real cluster slice. Reinforces the lesson captured in `feedback_tofu_validate_not_k8s_api`. Worth a dedicated convention/process improvement after this loop closes. ### File Targets - `terraform/modules/database/main.tf` lines 192-202 — replace the DO block with psql `\if`/`\gset` conditional ### Acceptance Criteria - [ ] DO block replaced with psql `\if` + `\gset` pattern - [ ] `tofu apply -target=module.database` Job pod completes Succeeded - [ ] Pod logs show `==> admin_app role provisioned successfully` - [ ] `\du admin_app` from postgres pod shows the role - [ ] Role login works with the rendered password ### Test Expectations - After fix + apply: Job runs to Succeeded - Subsequent re-apply (rotation simulation) also succeeds (idempotent path verified) ### Constraints - Don't change the surrounding GRANT statements, ALTER DEFAULT PRIVILEGES, or password env var handling - Don't introduce a new pillar var or terraform variable - Don't restructure the Job spec ### Checklist - [ ] Reviewed via /review-ticket - [ ] Conditional rewritten with psql client-side primitives - [ ] Local apply re-attempted - [ ] PR opened with `Closes #THIS` - [ ] Merged via standard hook ### Related - Caused by: PR #304 (Postgres provisioning Job, design conflict between psql vars and DO blocks) - Sibling fixes: pal-e-platform#315 (label `/`), pal-e-platform#317 (DO $$ HCL escape) - Memory: `feedback_tofu_validate_not_k8s_api`, `feedback_verification_before_completion`