fix(cli): create/alter role password must be literal

Nik Samokhvalov · Nik Samokhvalov · commit 4f6806348088 · 2025-12-13T08:06:51.000-08:00
- Inline safely quoted password for CREATE/ALTER USER (bind params not allowed)

- Add regression test
diff --git a/cli/lib/init.ts b/cli/lib/init.ts
@@ -36,6 +36,12 @@ function quoteIdent(ident: string): string {
   return `"${ident.replace(/"/g, "\"\"")}"`;
 }
 
+function quoteLiteral(value: string): string {
+  // Single-quote and escape embedded quotes by doubling.
+  // This is used where Postgres grammar requires a literal (e.g., CREATE/ALTER ROLE PASSWORD).
+  return `'${value.replace(/'/g, "''")}'`;
+}
+
 export function maskConnectionString(dbUrl: string): string {
   // Hide password if present (postgresql://user:pass@host/db).
   try {
@@ -308,21 +314,20 @@ export async function buildInitPlan(params: {
 
   const qRole = quoteIdent(monitoringUser);
   const qDb = quoteIdent(database);
+  const qPw = quoteLiteral(params.monitoringPassword);
 
   const steps: InitStep[] = [];
 
   // Role creation/update is done in two alternative steps. Caller decides by checking role existence.
   if (params.roleExists === false) {
     steps.push({
       name: "create monitoring user",
-      sql: `create user ${qRole} with password $1;`,
-      params: [params.monitoringPassword],
+      sql: `create user ${qRole} with password ${qPw};`,
     });
   } else if (params.roleExists === true) {
     steps.push({
       name: "update monitoring user password",
-      sql: `alter user ${qRole} with password $1;`,
-      params: [params.monitoringPassword],
+      sql: `alter user ${qRole} with password ${qPw};`,
     });
   } else {
     // Unknown: caller will rebuild after probing role existence.
diff --git a/cli/test/init.test.cjs b/cli/test/init.test.cjs
@@ -40,6 +40,20 @@ test("buildInitPlan includes create user when role does not exist", async () =>
   assert.ok(!plan.steps.some((s) => s.optional));
 });
 
+test("buildInitPlan inlines password safely for CREATE/ALTER ROLE grammar", async () => {
+  const plan = await init.buildInitPlan({
+    database: "mydb",
+    monitoringUser: "postgres_ai_mon",
+    monitoringPassword: "pa'ss",
+    includeOptionalPermissions: false,
+    roleExists: false,
+  });
+  const step = plan.steps.find((s) => s.name === "create monitoring user");
+  assert.ok(step);
+  assert.match(step.sql, /password 'pa''ss'/);
+  assert.equal(step.params, undefined);
+});
+
 test("buildInitPlan includes alter user when role exists", async () => {
   const plan = await init.buildInitPlan({
     database: "mydb",
