Homogenize Google Cloud SQL Instances

[tphummel]

Currently on Terraform 0.6.9, when you create a google_sql_database_instance resource, it is not usable downstream as a mysql generic outside of the google ecosystem.

The only attribute exposed by the resource is self_link which looks something like: https://www.googleapis.com/sql/v1beta4/projects/:my-project-id/instances/:my-instance-id. I'm guessing this is useful for accessing the instance from Google App Engine or other google things. But my thought is this resource should be broadly useful as a MySQL primitive the same as an AWS RDS instance or a MySQL server created with another provider like Digital Ocean or OpenStack. I think the litmus test could be whether the resource can be used with the emerging MySQL provider. Very roughly, something like this:

variable "my_db_admin_user" { default="root" }
variable "my_db_admin_pw" { default="123asd" }

resource "google_sql_database_instance" "my_db" {
  name = "my_db"
  region = "us-central"
  settings { 
    tier = "db-f1-micro"
    ip_configuration { ipv4_enabled = true }
}

provider "mysql" {
  alias = "${google_sql_database_instance.my_db.name}"
  endpoint = "${google_sql_database_instance.my_db.endpoint}"  # endpoint/host/public_ip attribute doesn't exist currently
  username = "${var.my_db_admin_user}"
  password = "${var.my_db_admin_pw}
}

resource "mysql_database" "users" {
  name = "users"
}

as I see it, the two pieces that currently lack from the google_sql_database_instance resource are:

• firewall rule setup
• root user setup

basically, I needed to set these things up manually via the GCE web console after I created the instance with TF. and before I could run the generic mysql provider targeting the instance.

re: firewall rules, new instances are rightfully locked down by default. perhaps there is another google_* resource that can control this? in order for the provider "mysql" { ... } block above to work, the instance needs to be reachable from the control machine running terraform.

re: user setup, i think this was brought up the other day in #4558. some backstory:

A new instance has four default user accounts: 'root'@'127.0.0.1', 'root'@'::1', ''@'localhost', and 'root'@'localhost'. The default user accounts are part of standard MySQL. In Google Cloud SQL, these accounts are not used except for connecting from Google App Engine, which connects using '@localhost' users. For non-App Engine connections, you need to create user accounts to connect.

again for the provider "mysql" { ... } block to work, the terraform control machine needs to be able to connect as the admin user (ex: 'root'%'my-control-machine.ip).

I'm curious to hear feedback on this idea. I ❤️ Terraform. Thanks for the great tool 😄

[lwander]

Hey @tphummel, thanks for the writeup!

as I see it, the two pieces that currently lack from the google_sql_database_instance resource are:

• firewall rule setup

Authorizing external networks with your SQL instance is already supported, see settings.ip_configuration.authorized_networks[].

• root user setup

This is in the works! I'll prioritize getting this done.

[tphummel]

@lwander awesome! thanks for clarifying and for your hard work! this was my first time digging into google cloud in general and via terraform.

[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 have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.