Google Kubernetes Engine to Cloud SQL

Question

I am currently trying to learn to use Google Kubernetes Engine and Google Cloud SQL. In doing so i have created a AspnetCore 3.1 Web Api project in my visual studio 2019, to use as a training project.

At the moments i am currently able to do the following.

• Deploy the Web Api to GKE, using Google Cloud Tools for Visual Studio
• Connect to Cloud SQL from own PC, using SSMS and Cloud SQL Proxy

What i want to be able to do is as follows.

• Deploy the Web Api to GKE, using Google Cloud Tools for Visual Studio, with a sidecar, containing Cloud SQL Proxy

From my understanding of Cloud SQL, it is best to always access it via the proxy, as it is more secure, which is why i want the sidecar. To get the proxy to work, i however need the credential file saved in my secrets in GKE. I also have some database related variables that need to be passed in as environment variables, again from secrets in GKE.

At the moment in my solution, beside my api project file, i have a Dockerfile, that looks as follows.

FROM gcr.io/google-appengine/aspnetcore:3.1
COPY . /app
WORKDIR /app
ENTRYPOINT ["dotnet", "HelloCloud.Api.dll"]

#FROM gcr.io/cloudsql-docker/gce-proxy
#COPY . /app
#WORKDIR /app
#CMD ["/cloud_sql_proxy -instances=noble-cubist-294511:europe-west2:helloclouddb=tcp:1433 -credential_file=/app/secrets/cloudsql/key.json"]

The second section of the Dockerfile is commented out, as you can see. This is done because it crashes the pods on GKE, as it is missing the credential file, that needs to be mounted from the secrets.

Beside the Dockerfile, also lies a file called deployment.yaml, which content is as follows.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: hellocloud
spec:
  selector:
    matchLabels:
      app: hellocloud
  template:
    metadata:
      labels:
        app: hellocloud
    spec:
      containers:
      - name: hellocloud
        image: gcr.io/noble-cubist-294511/hello-cloud-api
        env:
            - name: DB_USER
              valueFrom:
                secretKeyRef:
                  name: helloclouddb-db-credentials
                  key: username
            - name: DB_PASS
              valueFrom:
                secretKeyRef:
                  name: helloclouddb-db-credentials
                  key: password
            - name: DB_NAME
              valueFrom:
                secretKeyRef:
                  name: helloclouddb-db-credentials
                  key: database
        resources:
          limits:
            memory: "128Mi"
            cpu: "500m"
        ports:
        - containerPort: 80

      - name: cloudsql-proxy
        image: gcr.io/cloudsql-docker/gce-proxy
        command: ["/cloud_sql_proxy",
                    "-instances=noble-cubist-294511:europe-west2:helloclouddb=tcp:1433",
                    "-credential_file=/secrets/cloudsql/key.json"]
        resources:
            limits:
              memory: "128Mi"
              cpu: "500m"
        volumeMounts:
            - name: credentials-volumn
              mountPath: /secrets/cloudsql
              readOnly: true

    volumes:
        - name: credentials-volumn
          secret:
            secretName: helloclouddb-instance-credentials

I have created the above deployment.yaml, by following the guide on this site: Connecting Cloud SQL

From working on this, i have found out that Google Cloud Tools for Visual Studio reacts to the Dockerfile, which is also why i tried the section that is commented out. I have been trying to figure out if i can instruct GKE, via the Dockerfile, to use the deployment.yaml file, because from my understanding that should do the trick.

I like the development concept DRY (Don't Repeat Yourself) which is another reason it want to do be able to do it via Google Cloud Tools for Visual Studio. I have tried to create a deployment directly on GKE, which took me around 10 minutes to do, and ended up not even working. Ofcourse if i get more used to creating deployment on GKE, it is gonna reduce the time, and eventually also work, but it will be a WET (Write Every Time) way of doing it.

After two days of banging my head against the table i have gotten no closer, which is why i am writing this Stackoverflow questing, in the hopes that someone more experienced with Docker, GKE and Cloud SQL, can give me some pointers.

Fell free to ask for more detail, if i maybe have missed something crucial.

[Edit 1]

As a workaround, i am trying to have the file(s) on my drive, where the copy in the Dockerfile is gonna take it from, atleast from my understanding. Below an image of my project in Visual Studio can be seen, followed my by my updated Dockerfile.

FROM gcr.io/google-appengine/aspnetcore:3.1
COPY . /app
WORKDIR /app
ENTRYPOINT ["dotnet", "HelloCloud.Api.dll"]

FROM gcr.io/cloudsql-docker/gce-proxy
COPY . /app
WORKDIR /app/Secrets/CloudSQL
CMD ["/cloud_sql_proxy -instances=noble-cubist-294511:europe-west2:helloclouddb=tcp:1433 -credential_file=key.json"]

Building the Dockerfile on my computer and looking at the content of the image using Dive command, it contains the "key.json" at the specified location. Even then, when deploying to GKE, Cloud Build, builds it just fine, but when starting a pod, it throws a RunContainerError, complaining about "no such file or directory". Image of the full error can be seen below.

Answer 1

I managed to get it to work by creating my own class in my project, that given the path to the proxy file would run it with the required arguments, which i also feed to it via my class. To get the proxy file together with my own code i used the following Dockerfile to build my images.

FROM gcr.io/cloudsql-docker/gce-proxy as proxy
COPY . /app

FROM gcr.io/google-appengine/aspnetcore:3.1
Copy --from=proxy . /app
WORKDIR /app/app
ENTRYPOINT ["dotnet", "HelloCloud.Api.dll"]

Given the path to the proxy file, along with Credentials and Database file, my 'CloudSQLInitializer' class, starts the proxy.

public Startup(IConfiguration configuration)
        {
            ...
            try
            {
                CloudSQLInitializer cloud = null;

                // Currently running the proxy from either a bat or windows service.
                // As such will not have it start the proxy
                if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
                    cloud = new CloudSQLInitializer($"Secrets/CloudSQL/database.json");
                if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
                    cloud = new CloudSQLInitializer($"../cloud_sql_proxy", $"Secrets/CloudSQL/database.json", $"Secrets/CloudSQL/key.json");

                ...
            }
            catch (Exception e)
            {
                ...
            }
        }

public class CloudSQLInitializer
    {
        public string ConnectionString { get; private set; }

        ...
        public CloudSQLInitializer(string databaseInformationFilePath)
        {
            ...

            var information =
                JsonConvert.DeserializeObject<CloudDatabase>(File.ReadAllText(databaseInformationFilePath));

            ConstructConnectionString(information);
        }

        ...
        public CloudSQLInitializer(string cloudSQLProxyFilePath, string databaseInformationFilePath, string cloudCredentialsFilePath)
        {
            ...

            var information =
                JsonConvert.DeserializeObject<CloudDatabase>(File.ReadAllText(databaseInformationFilePath));

            ...

            ConstructConnectionString(information);
            RunCloudSQLProxy(information, cloudSQLProxyFilePath, cloudCredentialsFilePath);
        }

        private void ConstructConnectionString(CloudDatabase information)
        {
            var cs = new StringBuilder();

            cs.Append($"Data Source=127.0.0.1;");
            cs.Append($"Initial Catalog={information.Database};");
            cs.Append($"Persist Security Info=True;");
            cs.Append($"User ID={information.Username};");
            cs.Append($"Password={information.Password}");

            ConnectionString = cs.ToString();
        }

        private void RunCloudSQLProxy(CloudDatabase information, string cloudSQLProxyFilePath, string cloudCredentialsFilePath)
        {
            var cmd = new StringBuilder();

            cmd.Append($" -instances={information.InstancesToOneString()}");
            cmd.Append($" -credential_file={cloudCredentialsFilePath}");

            var proxy = new ProcessStartInfo {FileName = cloudSQLProxyFilePath, Arguments = cmd.ToString()};
            Process.Start(proxy);
        }
    }

public class CloudDatabase
    {
        [JsonProperty("database")]
        public string Database { get; set; }

        [JsonProperty("username")]
        public string Username { get; set; }

        [JsonProperty("password")]
        public string Password { get; set; }

        [JsonProperty("instances")]
        public List<string> Instances { get; set; }

        ...
    }

In the above code snippets i have cut away summeries for the methods, along with code that check to ensure the files exists and likewise checks. Also cut away much of the code that was otherwise irrlevant to this case.

Hope this is of use to somebody at some point, in the future.