main.tf:
provider "google" {
project = "PROJECT_ID"
}
resource "google_sql_database_instance" "test-sql" {
# deletion_protection = false
name = "test-sql"
region = "europe-central2"
database_version = "POSTGRES_15"
settings {
tier = "db-f1-micro"
ip_configuration {
# require_ssl = true
authorized_networks {
name = "me"
value = "SOURCE_IP"
}
}
}
}
resource "google_sql_user" "test-sql" {
name = "postgres"
instance = google_sql_database_instance.test-sql.name
password = 1
}
# resource "google_sql_ssl_cert" "test-sql" {
# common_name = "whatever"
# instance = google_sql_database_instance.test-sql.name
# }a.rb:
require 'pg'
PG.connect(host: 'HOST', password: '1', user: 'postgres')// replace PROJECT_ID, SOURCE_IP
$ docker run --rm -itv "$PWD:/app" -w /app google/cloud-sdk:457.0.0-alpine
/app # gcloud auth login --update-adc
/app # apk add terraform
/app # terraform init
/app # terraform apply; echo -e '\a'
/app # terraform show -json \
| jq -r '.values.root_module.resources[]
| select(.address == "google_sql_database_instance.test-sql")
.values.first_ip_address'
xx.xxx.xx.xxx
/app # psql -h xx.xxx.xx.xxx -U postgres
Password for user postgres:
psql (16.2, server 15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=>
/app # psql -h xx.xxx.xx.xxx -U postgres sslmode=disable
Password for user postgres:
psql (16.2, server 15.5)
Type "help" for help.
postgres=>
/app # psql -h xx.xxx.xx.xxx -U postgres sslmode=verify-ca
psql: error: connection to server at "xx.xxx.xx.xxx", port 5432 failed: root certificate file "/root/.postgresql/root.crt" does not exist
Either provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification.
/app # mkdir ~/.postgresql
/app # terraform show -json \
| jq -r '.values.root_module.resources[]
| select(.address == "google_sql_database_instance.test-sql")
.values.server_ca_cert[0].cert' \
> ~/.postgresql/root.crt
/app # psql -h xx.xxx.xx.xxx -U postgres sslmode=verify-ca
Password for user postgres:
psql (16.2, server 15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=>
/app # psql -h xx.xxx.xx.xxx -U postgres sslmode=verify-full
psql: error: connection to server at "xx.xxx.xx.xxx", port 5432 failed: server certificate for "31-45c75b34-8ff0-4158-a6ca-2b6506d29fff.europe-central2.sql.goog" (and 1 other name) does not match host name "xx.xxx.xx.xxx"
/app # psql -U postgres 'sslmode=verify-full hostaddr=xx.xxx.xx.xxx host=test-sql'
psql: error: connection to server at "xx.xxx.xx.xxx", port 5432 failed: server certificate for "31-45c75b34-8ff0-4158-a6ca-2b6506d29fff.europe-central2.sql.goog" does not match host name "test-sql"
/app # psql -U postgres 'sslmode=verify-full hostaddr=xx.xxx.xx.xxx host=ometv-stage:europe-central2:test-sql'
psql: error: connection to server at "xx.xxx.xx.xxx", port 5432 failed: server certificate for "31-45c75b34-8ff0-4158-a6ca-2b6506d29fff.europe-central2.sql.goog" does not match host name "ometv-stage:europe-central2:test-sql"
// uncomment the lines in main.tf
/app # terraform apply; echo -e '\a'
/app # psql -h xx.xxx.xx.xxx -U postgres sslmode=verify-ca
psql: error: connection to server at "xx.xxx.xx.xxx", port 5432 failed: FATAL: connection requires a valid client certificate
/app # terraform show -json \
| jq -r '.values.root_module.resources[]
| select(.address == "google_sql_ssl_cert.test-sql")
.values.cert' \
> ~/.postgresql/postgresql.crt
/app # terraform show -json \
| jq -r '.values.root_module.resources[]
| select(.address == "google_sql_ssl_cert.test-sql")
.values.private_key' \
> ~/.postgresql/postgresql.key
/app # chmod 600 ~/.postgresql/postgresql.key
/app # psql -h xx.xxx.xx.xxx -U postgres sslmode=verify-ca
Password for user postgres:
psql (16.2, server 15.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=>
// replace HOST in a.rb
/app # apk add ruby-dev postgresql-dev build-base
/app # gem install pg -v 1.5.6
/app # ruby a.rb
#<PG::Connection:0x00007e2d64574a08 host=xx.xxx.xx.xxx port=5432 user=postgres>
SSL is used by default, but the server certificate is not verified. To verify the server certificate it needs root.crt. To use certificate authentication it needs postgresql.crt, postgresql.key, and require_ssl = true.