GCP CloudSQL: Proxy connections

[suskin]

What problem are you facing?

This is related to connecting to CloudSQL instances from an application managed by Crossplane.

@ichekrygin and I were talking about different connectivity models for accessing CloudSQL instances from managed applications, and I want to capture some of the notes from that discussion. Maybe it makes sense to turn this into a one-pager in the future. This is all early thinking, and is not necessarily well thought out yet : ).

The question is: how do we connect to a Crossplane-managed CloudSQL instance from a managed application? Because it's a managed application and a managed database, the following requirements apply:

• The specification should not be provider-specific; it should be portable
• The specification should be simple

There is also a consideration of supporting connections between an application in a non-GCP provider and a database in GCP. But I consider this more of a tradeoff between approaches than a requirement.

Proposal

The discussion here will explore the CloudSQL Proxy option. The reason for focusing on the proxy approach is the following positive tradeoffs:

• On the application side, the proxy is portable across all environments and providers, and it sidesteps any network connectivity questions
• The proxy model is similar to access models for other services (e.g. Redis), and could inform our thinking around other proxy types.

From the application configuration side, the naive approach is to configure the proxy next to the application, which is provider-specific and not portable. The portable way to do this would be for the application to receive a connection string which points it to the cloudsql proxy. This is also a better developer experience.

To determine the connection string, the most likely scenario is that the crossplane controller would need to set up a cloudsql proxy container for the application, and would need to return a connection string that could be injected into the application container's environment. In order for the controller to know how to configure the proxy container, it would need to know which database the application is trying to connect to.

To summarize, the proposed model for interacting with a CloudSQL instance using a proxy would be:

• The application configuration specifies which database it depends on (at the crossplane level, so for example, the name of a crossplane-managed database claim)
• The controller managing KubernetesApplications (or maybe KubernetesApplicationResources; I'm not sure at this time) spins up a CloudSQL proxy for an application which declares that it depends on a particular database (if that database is bound to a CloudSQL instance). This could also be a controller which is separate from those other controllers. Maybe it goes as far as being modeled as a claim on a database connection, for example. Or maybe the same controller which manages a database claim handles this.
• The aforementioned controller exposes the connection string for the application to consume
• The application is now able to consume the connection string in the regular way

Further reading and related issues

• About the CloudSQL Proxy
• Resource connectivity model: crossplaneio/crossplane#606

[negz]

I also have not thought this through particularly thoroughly, but I've previously imagined a supplement to Crossplane that was capable of automatically injecting proxies (CloudSQL, service mesh sidecars, etc) into workloads (perhaps using a mutating webhook) by intelligently analysing their connectivity needs as declared (somehow) by Crossplane. My suspicion is that this functionality would be best if it were not baked into Crossplane but was instead an optional addon.

[dsyer]

Until Crossplane has a fully baked solution for this can I suggest that we just add the connection string to the generated secret and document it? The sample here is broken until that gets fixed really: https://github.com/crossplaneio/crossplane/blob/master/cluster/examples/wordpress/gcp/wordpress.yaml (it uses the IP address "endpoint" not the full connection string). At least it's broken for an app running outside GCP (haven't tried it in GKE).

I already see the connection string in the status of the cloudsqlinstance:

$ kubectl get cloudsqlinstance -o yaml
apiVersion: v1
items:
- apiVersion: database.gcp.crossplane.io/v1beta1
  kind: CloudSQLInstance
  metadata:
    annotations:
      crossplane.io/external-name: default-mysql-claim-j2b85
...
  status:
    atProvider:
      backendType: SECOND_GEN
      connectionName: cf-sandbox-dsyer:us-central1:default-mysql-claim-j2b85
...

so I believe it is available to the controller that creates the secret. The command line for cloud_sql_proxy would look like this:

cloud_sql_proxy -dir/cloudsql -instances=cf-sandbox-dsyer:us-central1:default-mysql-claim-j2b85=tcp:3306

so that status.connectionName from the CloudSQLInstance is needed in the app deployment for the proxy sidecar.

[muvaf]

@dsyer Would exposing status.atProvider.connectionName alongside the existing ones in the connection secret be enough to satisfy your use case?

The sample here is broken until that gets fixed really: https://github.com/crossplaneio/crossplane/blob/master/cluster/examples/wordpress/gcp/wordpress.yaml (it uses the IP address "endpoint" not the full connection string). At least it's broken for an app running outside GCP (haven't tried it in GKE).

The Wordpress example is intended to be used in a private network. That's why it instructs you to create a network, subnetwork etc. If you'd like to use the CloudSQLInstance from an app that is outside GCP, another option besides proxy is to expose CloudSQLInstance IP address to the public. To achieve this, you can set spec.forProvider.settings.ipConfiguration.ipv4Enabled: true. An example YAML looks like the following:

apiVersion: database.gcp.crossplane.io/v1beta1
kind: CloudSQLInstance
metadata:
  labels:
  name: crossplane-wordpress-cloudsql
spec:
  providerRef:
    name: example
  forProvider:
    region: us-west2
    databaseVersion: MYSQL_5_7
    settings:
      ipConfiguration:
        ipv4Enabled: true
      tier: db-n1-standard-1
      dataDiskType: PD_SSD
      dataDiskSizeGb: 10
  writeConnectionSecretToRef:
    namespace: crossplane-system
    name: demo-database-connection

In fact, spec.forProvider struct is almost exactly same as GCP API object.

[dsyer]

Would exposing status.atProvider.connectionName alongside the existing ones in the connection secret be enough to satisfy your use case?

Yes.

I think ipConfiguration.ipv4Enabled=true is the default. At least it doesn't behave any differently for me. You get a public IP address, but you can only connect to it though the connectionName.

[muvaf]

You get a public IP address

I am not sure. As far as I remember, the default is false. The IP you're getting is probably the private in-VPC IP. You should be able to see the IP addresses and their properties under status or on GCP console to see which one is private/public.

[dsyer]

It's public by default - at least that's what I see. But you can't connect with a regular mysql client, only the proxy (unless you authorize your client's network explicitly).

[muvaf]

@dsyer https://github.com/crossplaneio/stack-gcp/pull/159 this should at least make it easier for you to consume the connectionName but I don't have much context around how CloudSQL proxies work.

Do you think @negz is exposing only connectionName enough to fix this issue for all CloudSQL proxy connection scenarios?