通过Terraform管理私有AWS RDS PostgreSQL实例遇证书验证问题

环境与操作步骤 #

我在AWS VPC的私有子网中运行PostgreSQL RDS实例，通过同VPC公有子网的堡垒机建立SSH隧道连接数据库，操作命令如下：

$ ssh -Nnf -L 5432:my-db-host:5432 my-bastion-host -p 2222
$ psql -h localhost -U root dbname
Password for user root: 
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1), server 13.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

dbname=> 

Terraform配置 #

为用Terraform管理数据库的schema和用户，编写了如下terraform.tf文件：

terraform {
  required_providers {
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "~> 1.0"
    }
  }
}

provider "postgresql" {
  scheme          = "awspostgres"
  host            = "localhost"
  port            = 5432
  database        = "dbname"
  username        = "root"
  password        = "password"
  sslmode         = "disable"
  superuser       = false
  connect_timeout = 30
}

resource "postgresql_role" "admin" {
  name = "admin"
}

错误信息 #

执行terraform apply时触发以下错误：

│ Error: error detecting capabilities: error PostgreSQL version: x509: certificate is valid for my-db-host, not localhost
│ 
│   with postgresql_role.admin,
│   on _terraform.tf line 22, in resource "postgresql_role" "admin":
│   22: resource "postgresql_role" "admin" {
│ 

解决方案 #

• 问题根源：awspostgres协议（基于GoCloud）存在缺陷：会忽略配置的sslmode参数，且无法通过ALL_PROXY的socks代理工作。本次错误是因为该协议强制验证SSL证书，而SSH隧道映射的localhost不在RDS证书的有效域名列表中。
• 解决方法：将协议切换为postgres即可解决问题，修改后的PostgreSQL Provider配置如下：

provider "postgresql" {
  scheme          = "postgres" # 替换原awspostgres协议
  host            = "localhost"
  port            = 5432
  database        = "dbname"
  username        = "root"
  password        = "password"
  sslmode         = "disable"
  superuser       = false
  connect_timeout = 30
}

• 注意事项：如果需要配置sslmode = "verify-full"，或者必须使用socks代理，此方案不适用；若无需AWS特定认证方式，切换协议是完全可行的。

内容的提问来源于stack exchange，提问作者Kasper Middelboe Petersen