add ssl certificate outputs resources for cloud sql postgres

[andrewchambers]

A feature request for:

1. Outputting the server root certificate as an output of google_sql_database_instance so ssl encryption can be automated.
2. A new set of resources for ssl client certificates so ssl authentication can be automated.

[johnsonj]

It'd be great start to export the serverCaCert which is exposed as part of the instance resource.

[Lawouach]

Hey folks,

Just wanted to confirm, we still can't automate client certs creation, right?

Thanks,

[paddycarver]

I believe we are still missing the necessary resource for that. I do not believe anyone is working on that at the moment. PRs would be accepted, or we'll get to it as soon as we can. Unfortunately, there's a lot going on in the provider right now, and we have only a handful of maintainers, so sometimes we can take longer to get to features than we'd like.

[silvpol]

I've been trying to automate handover from google_cloud_sql to postgres provider and came up with the solution below, I hope this workaround helps somebody until proper solution is in place.

1. Before running Teraform shell needs to have env vars set so that Postgres provider can find our certs/keys.

# Adjust file names to match Terraform files
PGSSLCERT=terraform-client-cert.pem
PGSSLKEY=terraform-client-key.pe
PGSSLROOTCERT=terraform-ca-cert.pem

Shell script is required due to hashicorp/terraform-provider-postgresql#49

2. Run terraform as usual

# Create Terraform user
resource "google_sql_user" "terraform" {
  name = "terraform"
  project = "${module.project.project_id}"
  instance = "${google_sql_database_instance.cluster_db.id}"
  password = "${random_string.tf_tmp.result}"
}

# Specify location of cert/key files
data "null_data_source" "tf_sql_ssl_files" {
  inputs {
    client_key = "${path.root}/${google_sql_user.terraform.name}-client-key.pem"
    client_key_sha = "${path.root}/${google_sql_user.terraform.name}-client-key.sha1"
    client_cert = "${path.root}/${google_sql_user.terraform.name}-client-cert.pem"
    ca_cert = "${path.root}/${google_sql_user.terraform.name}-ca-cert.pem"
  }
}

# Save cert/key files
resource "null_resource" "db_certs" {

  provisioner "local-exec" {
    command = "gcloud sql ssl client-certs create terraform ${data.null_data_source.tf_sql_ssl_files.outputs.client_key} --instance=${google_sql_database_instance.cluster_db.id} --project=${module.project.project_id} --format='value(sha1_fingerprint)' > ${data.null_data_source.tf_sql_ssl_files.outputs.client_key_sha}"
  }

  provisioner "local-exec" {
    command = "gcloud sql ssl client-certs describe terraform --instance=${google_sql_database_instance.cluster_db.id} --project=${module.project.project_id} --format='value(cert)' > ${data.null_data_source.tf_sql_ssl_files.outputs.client_cert}"
  }

  provisioner "local-exec" {
    command = "gcloud sql instances describe ${google_sql_database_instance.cluster_db.id} --project=${module.project.project_id} --format='value(serverCaCert.cert)' > ${data.null_data_source.tf_sql_ssl_files.outputs.ca_cert}"
  }

  triggers {
    user = "${google_sql_user.terraform.id}"
  }
}

provider "postgresql" {
  alias = "ssl"
  host = "${var.auth_host}"
  username = "${var.auth_user}"
  password = "${var.auth_pass}"
  sslmode = "verify-ca"
}

File .sha1 contains cert fingerprint and is not necessary for connection but useful for rotation.

[olib963]

@silvpol Have you managed to get this to work with google_sql_database_instance? How did you link auth_host to the google_sql_database_instance?

I have not been able to get the postgres provider to connect to the cloud sql instance without running the cloud sql proxy alongside terraform and have to remove the postgres provider if the google_sql_database_instance does not yet exist.

[silvpol]

@olib963 I have tried provisioning from scratch again on a separate environment and you're right, this needs to be done in two steps:

• first pass to create all dbs and terraform user to connect to instance
• second pass to complete provisioning

Our top level file is split into main env module and then series of namespaces, so our workflow is now like this:

• terraform apply --target module.environment (creates db instance, user for terraform and dbs)
• terraform apply (creates target users, schemas, extensions)
• terraform destroy --target module.namespace_abc (deprovision dbs, users, schemas, extensions)
• terraform destroy --target module.environment (destroy underlying resources)

We automatically enable current terraform IP for SSL connection with a snippet like this inside our environment module:

locals {
  terraform_local_cidr = "${chomp(data.http.local_ip.body)}/32"
}

data "http" "local_ip" {
  url = "https://ifconfig.co/ip"
}

resource "google_sql_database_instance" "cluster_db" {
//...

  settings {
    ip_configuration {
      authorized_networks {
        name = "Terraform local IP"
        value = "${locals.terraform_local_cidr}"
        expiration_time = "${replace(timeadd(timestamp(), "70m"),"/\\d+:\\d+Z/","00:00.000Z")}"
      }
    }
  }
}

It fetches current public IP of terraform machine, adds 1h 10m to current timestamp and rounds down to full hour (to avoid constant diffs). This means that authorized IP will be in the list for only between 10 mins and 1h. Caveat with this approach is that you need to run apply on env module before actual run.

[olib963]

@silvpol Awesome, thank you so much. I had been having trouble figuring out how to connect terraform without running the proxy. It is a bit frustrating that we need to run it in two separate steps, but it will do for now.

[danawillow]

This appears to be done in #1020 and #2290, so closing this out.

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!