如何在Terraform中通过EOF传递多行PostgreSQL命令

你遇到的错误是因为CREATE和GRANT是PostgreSQL的SQL命令，并非Shell命令，但当前写法中这两行被Shell当作独立命令执行，因此会提示"not found"。必须将这些SQL命令传递给psql进程来执行，而非让Shell直接处理。

以下是几种可行的修正写法：

方案一：用psql的-c参数逐个传递SQL命令 #

通过-c参数可直接指定要执行的SQL语句，多个-c对应多条命令：

resource "kubernetes_job" "user" {
  metadata {
    name      = "create-user"
  }
  spec {
    template {
      metadata {}
      spec {
        container {
          name    = "test"
          command = ["/bin/sh", "-c"]
          args = [<<-EOF
            PGPASSWORD=<password> psql -h <host> -U <user> -d <db> -c "CREATE ROLE test WITH LOGIN PASSWORD 'test123';" -c "GRANT readonly_role TO test;"
          EOF
          ]
        }
        restart_policy = "Never"
      }
    }
    backoff_limit = 1
    completions   = 1
  }
}

方案二：用Heredoc传递多行SQL给psql #

这种方式适合编写多行SQL，可读性更强：

resource "kubernetes_job" "user" {
  metadata {
    name      = "create-user"
  }
  spec {
    template {
      metadata {}
      spec {
        container {
          name    = "test"
          command = ["/bin/sh", "-c"]
          args = [<<-EOF
            PGPASSWORD=<password> psql -h <host> -U <user> -d <db> <<SQL
              CREATE ROLE test WITH LOGIN PASSWORD 'test123';
              GRANT readonly_role TO test;
            SQL
          EOF
          ]
        }
        restart_policy = "Never"
      }
    }
    backoff_limit = 1
    completions   = 1
  }
}

方案三：将多条SQL合并为一个字符串传递 #

把所有SQL用分号分隔，通过单个-c参数传递：

resource "kubernetes_job" "user" {
  metadata {
    name      = "create-user"
  }
  spec {
    template {
      metadata {}
      spec {
        container {
          name    = "test"
          command = ["/bin/sh", "-c"]
          args = [<<-EOF
            PGPASSWORD=<password> psql -h <host> -U <user> -d <db> -c "CREATE ROLE test WITH LOGIN PASSWORD 'test123'; GRANT readonly_role TO test;"
          EOF
          ]
        }
        restart_policy = "Never"
      }
    }
    backoff_limit = 1
    completions   = 1
  }
}

内容的提问来源于stack exchange，提问作者jipot