Can't connect to Google Cloud Sql

[xolott]

Describe the bug
Can't connect to Cloud SQL (postgres 9.6) using istio in Google Container Engine (Kubernetes). The service uses the cloudsql proxy sidecar along with istio in the same pod. Tested with a docker container with psql installed to test the connection.

In vanilla kubernetes I can connect to the database:

root@auth-66c4bf88df-bs7q2:/app# psql -h 127.0.0.1 -U auth -d passport
Password for user auth: 
psql (9.6.7, server 9.6.6)
Type "help" for help.

passport=>

But, with istio I got:

root@auth-7f769bc877-fbzp4:/app# psql -h 127.0.0.1 -U auth -d passport
psql: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

In the early tries I got blocked by the container (Cloud Proxy), and trying to connect to googleapis.com and accounts.google.com

$ cat <<EOF | istioctl create -f -
apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: account-google-serviceentry-rule
spec:
  hosts:
  - accounts.google.com
  ports:
  - number: 443
    name: https
    protocol: HTTPS
EOF

$ cat <<EOF | istioctl create -f -
apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: googleapis-serviceentry-rule
spec:
  hosts:
  - www.googleapis.com
  ports:
  - number: 443
    name: https
    protocol: HTTPS
EOF

Expected behavior
Successful connection to the database

Steps to reproduce the bug

• Create a Cloud SQL database instance (postgres 9.6)
• Create a service account with Cloud Client role enabled and Cloud Sql account following the GCloud tutorial
• Install istio following the Quick Start page without auth enabled
• Deploy a service (based on debian stretch with psql installed)
• Try to connect to the Database using the credential provided in the previous steps. For example:

$ psql -h 127.0.0.1 -U auth -d passport

Version

$ istioctl version
Version: 0.8.0
GitRevision: 6f9f420f0c7119ff4fa6a1966a6f6d89b1b4db84
User: root@48d5ddfd72da
Hub: docker.io/istio
GolangVersion: go1.10.1
BuildStatus: Clean

$ kubectl version
Client Version: version.Info{Major:"1", Minor:"10", GitVersion:"v1.10.4", GitCommit:"5ca598b4ba5abb89bb773071ce452e33fb66339d", GitTreeState:"clean", BuildDate:"2018-06-06T08:13:03Z", GoVersion:"go1.9.3", Compiler:"gc", Platform:"linux/amd64"}
Server Version: version.Info{Major:"1", Minor:"10+", GitVersion:"v1.10.4-gke.2", GitCommit:"eb2e43842aaa21d6f0bb65d6adf5a84bbdc62eaf", GitTreeState:"clean", BuildDate:"2018-06-15T21:48:39Z", GoVersion:"go1.9.3b4", Compiler:"gc", Platform:"linux/amd64"}

Is Istio Auth enabled or not?
Installing following the Quick Start page without auth enabled

$ kubectl apply -f install/kubernetes/istio-demo.yaml

Environment
Kubernetes Engine in Google Cloud
Tested with version 1.9.7 and 1.10.4-gke.2

[ahmmadkour]

I got a similar issue where I wasn't able to connect from another container to the database using the cloudsql proxy (I was able to connect from inside cloudsql proxy using localhost, though) it is solved after I found the solution in https://groups.google.com/forum/#!topic/istio-users/-cNoLrE5904

The cloudsql proxy service port name should not be http, the problem solved after I changed it from http to db

[veryhumble]

Any update on this? I am experiencing the same problem using the cloudsql-proxy sidecar.

Edit:
I got the connection somehow working. I added a sidecar with the postgres client and tried connecting from there manually. In the logs of the cloudsql-proxy i saw that it's trying to connect to the postgresql db using port 3307. So I added the following ServiceEntry:

apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: cloudsql-external
spec:
  hosts:
  - IP_ADDRESS
  addresses:
  - IP_ADDRESS/32
  ports:
  - name: tcp
    number: 3307
    protocol: tcp
  location: MESH_EXTERNAL

It now prompts correctly the password after psql -h 127.0.0.0 -U proxyuser -d test . But its stuck on that password prompt. So not quite resolved yet.

[yskopets]

I confirm that solution suggested by @veryhumble works.

It's actually documented at https://istio.io/blog/2018/egress-tcp/

Here is the complete configuration that works for me:

# Cloud SQL Proxy makes requests to `www.googleapis.com`
apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: www.googleapis.com
  namespace: istio-example-cloud-sql
spec:
  hosts:
  - www.googleapis.com
  ports:
  - number: 443
    name: https
    protocol: HTTPS
  resolution: DNS
  location: MESH_EXTERNAL
---
# see https://istio.io/blog/2018/egress-tcp/
apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: cloud-sql-instance
  namespace: istio-example-cloud-sql
spec:
  hosts:
  # although blog article says that `hosts` field is ignored for TCP service entries,
  # clients fail to establish connection when `hosts` is omitted, 
  # namely "curl: (56) Recv failure: Connection reset by peer"
  #
  # use `gcloud sql instances list` to find out the IP address of your Google Cloud Instance
  - X.X.X.X
  addresses:
  # use `gcloud sql instances list` to find out the IP address of your Google Cloud Instance
  - X.X.X.X/32 # a block of IPs in CIDR notation
  ports:
  - name: tcp
    number: 3307 # at the moment, Google Cloud SQL always available on port 3307
    protocol: tcp # enable TCP traffic
  location: MESH_EXTERNAL

[danielfoehrKn]

Hi,
I am also currently having an issue with the connection to CloudSQL from my istio cluster.

I am using https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory (NOT the CloudSQL Proxy).

The way it worked for me was to configure the sidecas to only intercept mesh-internal traffic as described here: https://istio.io/docs/tasks/traffic-management/egress/#calling-external-services-directly

However I was not able to make a successful connection to the MSQL Instance using ServiceEntries.
I could see that the authentication to the google servers using the ServiceAccount worked.
But when the java application tried to connect to the mysql, the SSL handshake always failed with

"SSL peer shut down incorrectly"

The same behaviour could be observed using a mysql client deployed in another container.

I think that my ServiceEntry configuration is still wrong, but I could not figure out why.
I am not sure about the CIDR Block tbh.
Definitely the sidecar is interfering in the wrong way in my case, because when I disable it, the connection works.

Maybe also worth mentioning: I am using the option "useSSL=false" in the jdbc connection string in the cloud-sql-jdbc library.

Does anybody have an idea where the problem might be? thanks!

This is the ServiceEntry configuration:

apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: www.googleapis.com
#  namespace: istio-example-cloud-sql
spec:
  hosts:
  - "*.googleapis.com"
  - 169.254.169.254 # metadata.google.internal   -> IP was being logged in istio-proxy with 404
  ports:
  - number: 443
    name: https
    protocol: HTTPS
#  - number: 80
#    name: http
#    protocol: HTTP
  resolution: DNS
  location: MESH_EXTERNAL

apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: cloud-sql-instance
#  namespace: istio-example-cloud-sql
spec:
  hosts:
  # although blog article says that `hosts` field is ignored for TCP service entries,
  # clients fail to establish connection when `hosts` is omitted,
  # namely "curl: (56) Recv failure: Connection reset by peer"
  #
  # use `gcloud sql instances list` to find out the IP address of your Google Cloud Instance
  - XXX.XXX.22.151
  addresses:
  # use `gcloud sql instances list` to find out the IP address of your Google Cloud Instance
  - XXX.XXX.22.0/32 # a block of IPs in CIDR notation
  ports:
  - name: tcp
    number: 3307 # at the moment, Google Cloud SQL always available on port 3307
    protocol: tcp # enable TCP traffic
  location: MESH_EXTERNAL

I also tried unsuccessfully to add a VirtualService (because of the SSL /TLS handshake error) as described here https://istio.io/docs/tasks/traffic-management/egress/#configuring-istio-external-services and here #5288

apiVersion: networking.istio.io/v1alpha3
kind: VirtualService
metadata:
  name: cloudsql-virtualservice-2
spec:
  hosts:
  - XXX.XXX.22.115
  tls:
  - match:
    - port: 443
      sni_hosts:
      - XXX.XXX.22.115
    route:
    - destination:
        host: XXX.XXX.22.115
        port:
          number: 443
      weight: 100

[danielfoehrKn]

UPDATE: I got it working with a slightly different configuration using jdbc Socket Factory connecting to CloudSQL Mysql 2.Gen.

Difference to @yskopets @veryhumble configuration:

• No "Adresses field" /CIDR block in cloud-sql-instance service entry
• Resolution: DNS for the service entry that allows the connection to the CloudSQL Instance

# Pod makes requests to `www.googleapis.com` for Authentication
apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: www.googleapis.com
spec:
  hosts:
  - "*.googleapis.com"
  - 169.254.169.254 # metadata.google.internal   -> IP was being logged in istio-proxy with 404
  ports:
  - number: 443
    name: https
    protocol: HTTPS
  - number: 80
    name: http
    protocol: HTTP
  resolution: DNS
  location: MESH_EXTERNAL

---
# jdbc connection to CloudSQL instance
apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: cloud-sql-instance
#  namespace: istio-example-cloud-sql
spec:
  hosts:
  # use `gcloud sql instances list` to find out the IP address of your Google Cloud Instance
  - 104.155.22.115
  ports:
  - name: tcp
    number: 3307 # at the moment, Google Cloud SQL always available on port 3307
    protocol: tcp # enable TCP traffic
  location: MESH_EXTERNAL
  resolution: DNS

[ThusharaAma]

I'm also trying to connecting from GKE to the Cloud SQL using Cloud SQL Proxy as described here.
https://cloud.google.com/sql/docs/mysql/connect-kubernetes-engine

I have disabled istio mtls and configured Service Entries But I get the following message

couldn't connect to ":": Post https://www.googleapis.com/sql/v1beta4/projects/my-project/instances/my-db/createEphemeral?alt=json: oauth2: cannot fetch token: Post https://accounts.google.com/o/oauth2/token: http: server gave HTTP response to HTTPS client

But the same setup works without enabling istio.

My istio configuration

apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: account-google-serviceentry-rule
spec:
  hosts:
  - accounts.google.com
  ports:
  - number: 443
    name: https
    protocol: HTTPS

---
    
apiVersion: networking.istio.io/v1alpha3
kind: VirtualService
metadata:
  name: account-google-serviceentry-rule
spec:
  hosts:
  - accounts.google.com
  tls:
  - match:
    - port: 443
      sni_hosts:
      - accounts.google.com
    route:
    - destination:
        host: accounts.google.com
        port:
          number: 443
      weight: 100

---
    
apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: googleapis-serviceentry-rule
spec:
  hosts:
  - www.googleapis.com
  - 169.254.169.254
  ports:
  - number: 443
    name: https
    protocol: HTTPS
  - number: 80
    name: http
    protocol: HTTP
  resolution: DNS 
  
---
      
apiVersion: networking.istio.io/v1alpha3
kind: VirtualService
metadata:
  name: googleapis-serviceentry-rule
spec:
  hosts:
  - www.googleapis.com
  tls:
  - match:
    - port: 443
      sniHosts:
      - www.googleapis.com
    route:
    - destination:
        host: www.googleapis.com 
  - match:
    - port: 80
      sniHosts:
      - www.googleapis.com
    route:
    - destination:
        host: www.googleapis.com 
        
---

apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: mysql-serviceentry-rule
spec:
  hosts:
  - X.X.X.X
  ports:
  - number: 3306
    name: tcp
    protocol: TCP

  

[vadimeisenbergibm]

@ThusharaAma your configuration for www.googleapis.com and accounts.google.com looks correct, except for the IP 169.254.169.254 - can you check it works when you remove it? Just use the host www.googleapis.com.

The TCP entry for mysql is incorrect. You should either specify a CIDR block for TCP, see https://preliminary.istio.io/blog/2018/egress-tcp/#mesh-external-service-entry-for-an-external-mysql-instance or if your mysql supports TLS, configure egress traffic for it in the same way as for www.googleapis.com.

You can check if your mysql server supports TLS by running openssl s_client -connect X.X.X.X:3306 -servername X.X.X.X. If a certificate of the server is returned, it supports TLS.

[mr-smithers-excellent]

@danielfoehrKn I’m curious if you’re using the JDBC Socket Factory natively or as a part of the Spring Boot GCP project, which uses it under the hood. I’m having similar issues connecting a Java app to Cloud SQL in a GKE cluster with Istio 1.0.3.

[ermik]

I'm working with this issue now, and it's still unclear what exactly needs to be in place for egress to work on v1alpha3 — so far I have a Gateway with a wildcard tls server *.googleapis.com; then I use that gateway in my VirtualService that handles *.googleapis.com host entry sending it to a ServiceEntry via tls route (sniHosts: "*.googleapis.com") and that ServiceEntry has "*.googleapis.com" in hosts and is MESH_EXTERNAL. Doesn't work.

[vadimeisenbergibm]

@ermik The first question is if you need to direct the egress traffic thru a gateway, or you can just direct the traffic directly from the sidecar.

If you need the gateway, and you want to use wildcard hosts like *.googleapis.com, see this example how to do it:
https://preliminary.istio.io/docs/examples/advanced-gateways/wildcard-egress-hosts/

[Kampe]

I'm having this same issue, can confirm am able to hit www.googleapis.com from the running container, but while on the sidecar, I'm unable to get a response and get this error:
/ $ wget accounts.google.com Connecting to accounts.google.com (74.125.142.84:80) Connecting to accounts.google.com (74.125.142.84:443) wget: can't execute 'ssl_helper': No such file or directory wget: error getting response: Connection reset by peer

[vadimeisenbergibm]

@Kampe Did you define the ServiceEntry for www.googleapis.com or for accounts.google.com? Can you share your ServiceEntry?

[Kampe]

Yup here that is

apiVersion: networking.istio.io/v1alpha3
kind: ServiceEntry
metadata:
  name: external-svc-https
spec:
  hosts:
  - www.googleapis.com
  location: MESH_EXTERNAL
  ports:
  - number: 443
    name: https
    protocol: TLS
  - number: 80
    name: http
    protocol: HTTP
  resolution: DNS