Cloud SQL connection for Kubernetes using proxy

2/15/2019

I'm currently running a Spring Boot Pod in Kubernetes. There's a side car in the pod for the cloud SQL proxy.

Below is my spring Boot application.properties configuration:

server.port=8081
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.continue-on-error=true
spring.datasource.url=jdbc:mysql://localhost:3306/<database_name>
spring.datasource.username=<user_name>
spring.datasource.password=<password>

Below is my pom.xml extract with plugins and dependencies:

<properties>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.jayway.jsonpath</groupId>
        <artifactId>json-path</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-hateoas</artifactId>
    </dependency>

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>ca.performance.common</groupId>
        <artifactId>common-http</artifactId>
        <version>1.1.1</version>
    </dependency>
    <dependency>
        <groupId>com.google.cloud.sql</groupId>
        <artifactId>mysql-socket-factory</artifactId>
        <version>1.0.10</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
        <version>1.1.0.RELEASE</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

And this is my deployment.yaml file:


apiVersion: v1
kind: Service
metadata:
  name: app-dummy-name
spec:
  selector:
    app: app-dummy-name
  ports:
  - port: 81
    name: http-app-dummy-name
    targetPort: http-api
  type: LoadBalancer
---
apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: app-dummy-name
spec:
  replicas: 1
  selector:
    matchLabels:
      app: app-dummy-name
  template:
    metadata:
      labels:
        app: app-dummy-name
    spec:
      containers:
      - name: app-dummy-name
        image: <image url>
        ports:
        - containerPort: 8081
          name: http-api
        env:
        - name: DB_HOST
          value: 127.0.0.1:3306
        - name: DB_USER
          valueFrom:
            secretKeyRef:
              name: cloudsql-db-credentials
              key: username
        - name: DB_PASSWORD
          valueFrom:
            secretKeyRef:
              name: cloudsql-db-credentials
              key: password
      - name: cloudsql-proxy
        image: gcr.io/cloudsql-docker/gce-proxy:1.11
        command: ["/cloud_sql_proxy",
                    "-instances=<INSTANCE_CONNECTION_NAME>=:3306",
                    "-credential_file=/secrets/cloudsql/credentials.json"]
        securityContext:
          runAsUser: 2  # non-root user
          allowPrivilegeEscalation: false
        volumeMounts:
          - name: cloudsql-instance-credentials
            mountPath: /secrets/cloudsql
            readOnly: true
      volumes:
        - name: cloudsql-instance-credentials
          secret:
            secretName: cloudsql-instance-credentials

I followed the instructions from this link, so I created the secrets and the service account. However, I'm constantly getting connection refusal errors when I deploy the previous yaml file in Kubernetes after creating the secrets:

org.springframework.jdbc.support.MetaDataAccessException: Could not get Connection for extracting meta-data; 
nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; 
nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure. 
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

I even tested the Spring boot application locally using the proxy and the same application.properties configuration and it was working fine.

-- Carlos Gomez
cloud-sql-proxy
kubernetes
mysql
spring
spring-boot

1 Answer

2/16/2019

Im adding my deployment yaml which worked for me, check if adding the following will help:

under volumes:

  volumes:
  - name: cloudsql
    emptyDir:

in the connection: --dir=/cloudsql

  - name: cloudsql-proxy
    image: gcr.io/cloudsql-docker/gce-proxy:1.11
    command: ["/cloud_sql_proxy", "--dir=/cloudsql",
        "-instances=<INSTANCE_CONNECTION_NAME=tcp:5432>",
        "-credential_file=/secrets/cloudsql/credentials.json"]

also make sure you enabled the Cloud SQL Administration API

here is my full deployment yaml

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: app-dummy-name
spec:
  replicas: 1
  revisionHistoryLimit: 1
  strategy:
      type: RollingUpdate
  template:
    metadata:
      labels:
        app: app-dummy-name
        tier: backend
    spec:
      securityContext:
        runAsUser: 0
        runAsNonRoot: false
      containers:
      - name: app-dummy-name
        image: <image url>
        ports:
        - containerPort: 80
        env:
        - name: DB_HOST
          value: localhost
        - name: DB_USER
          valueFrom:
            secretKeyRef:
              name: cloudsql-db-credentials
              key: username
        - name: DB_PASSWORD
          valueFrom:
            secretKeyRef:
              name: cloudsql-db-credentials
              key: password
      # proxy_container
      - name: cloudsql-proxy
        image: gcr.io/cloudsql-docker/gce-proxy:1.11
        command: ["/cloud_sql_proxy", "--dir=/cloudsql",
          "-instances=my-project-id:us-central1:postgres-instance-name=tcp:5432",
          "-credential_file=/secrets/cloudsql/credentials.json"]
        volumeMounts:
          - name: cloudsql-instance-credentials
            mountPath: /secrets/cloudsql
            readOnly: true
          - name: cloudsql
            mountPath: /cloudsql
      # volumes
      volumes:
      - name: cloudsql-instance-credentials
        secret:
          secretName: cloudsql-instance-credentials
      - name: cloudsql
        emptyDir:

here are my pre-delpoy script:

#!/bin/bash
# https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine
# 1. Go to the Cloud SQL Service accounts page of the Google Cloud Platform Console.
# GO TO THE SERVICE ACCOUNTS PAGE
# 2.  If needed, select the project that contains your Cloud SQL instance.
# 3. Click Create service account.
# 4. In the Create service account dialog, provide a descriptive name for the service account.
# 5. For Role, select Cloud SQL > Cloud SQL Client.
# Alternatively, you can use the primitive Editor role by selecting Project > Editor, but the Editor role includes permissions across Google Cloud Platform.
#
# 6. If you do not see these roles, your Google Cloud Platform user might not have the resourcemanager.projects.setIamPolicy permission. You can check your permissions by going to the IAM page in the Google Cloud Platform Console and searching for your user id.
# Change the Service account ID to a unique value that you will recognize so you can easily find this service account later if needed.
# 7. Click Furnish a new private key.
# 8. The default key type is JSON, which is the correct value to use.
# 9. Click Create.
# 10. enable Cloud SQL Administration API [here](https://console.developers.google.com/apis/api/sqladmin.googleapis.com/overview)
# make sure to choose your project


echo "create cloudsql secret"
kubectl create secret generic cloudsql-instance-credentials \
   --from-file=credentials.json=postgres-sql-credential.json

echo "create cloudsql user and password"
kubectl create secret generic cloudsql-db-credentials \
   --from-literal=username=postgres --from-literal=password=123456789

postgres-sql-credential.json file:

{
  "type": "service_account",
  "project_id": "my-project",
  "private_key_id": "1234567890",
  "private_key": "-----BEGIN PRIVATE KEY-----\n123445556\n123445\n-----END PRIVATE KEY-----\n",
  "client_email": "postgres-sql@my-project.iam.gserviceaccount.com",
  "client_id": "1234567890",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/postgres-sq%my-project.iam.gserviceaccount.com"
}
-- dina
Source: StackOverflow