Terraform - aws_rds_proxy 接 RDS PostgreSQL 的 init_query 指令

在 Terraform 的文件 db_proxy_default_target_group 只有提供 MySQL 的使用範例:

resource "aws_db_proxy" "example" {
  name                   = "example"
  debug_logging          = false
  engine_family          = "MYSQL"
  idle_client_timeout    = 1800
  require_tls            = true
  role_arn               = aws_iam_role.example.arn
  vpc_security_group_ids = [aws_security_group.example.id]
  vpc_subnet_ids         = [aws_subnet.example.id]

  auth {
    auth_scheme = "SECRETS"
    description = "example"
    iam_auth    = "DISABLED"
    secret_arn  = aws_secretsmanager_secret.example.arn
  }

  tags = {
    Name = "example"
    Key  = "value"
  }
}

resource "aws_db_proxy_default_target_group" "example" {
  db_proxy_name = aws_db_proxy.example.name

  connection_pool_config {
    connection_borrow_timeout    = 120
    init_query                   = "SET x=1, y=2"
    max_connections_percent      = 100
    max_idle_connections_percent = 50
    session_pinning_filters      = ["EXCLUDE_VARIABLE_SETS"]
  }
}

這邊 init_query 是用 “SET x=1, y=2” 當作 proxy 連上 db 的測通指令, 但是 MySQL 的 SET 指令跟 PostgreSQL 的 SET 指令用法不同, 所以直接把這個範例給 PostgreSQL 用的時候會噴錯誤:

proxy log:
- [INFO] [dbConnection=1140488035] The database connection closed. Reason: An internal error occurred.

db log:
- ERROR: syntax error at or near "=" at character 11
- STATEMENT: SET x=1, y=2

(以上來自 CloudWatch Logs, RDS 跟 RDS Proxy 都有設定送出 log 到這邊)

伸進去 PostgreSQL db 手動執行指令看看:

postgres=> SET x=1, y=2;
ERROR:  syntax error at or near "="
LINE 1: SET x=1, y=2;
                  ^
postgres=> SET x=1;
ERROR:  unrecognized configuration parameter "x"
postgres=>

確認是 SET 指令造成錯誤.

Google 查了幾下也是有一些回報同樣的問題, 解法幾乎都是把 init_query 拿掉, 但是這樣就少了一個 proxy 連入 db 測通 db 要有正常反應的機制.

所以找個指令替代 SET 就解決了:

init_query = "VALUES (1,2)"