So you think it would be easy to use Cloud SQL for your Grafana (3)

This post is continuing from "So you think it would be easy to use Cloud SQL for your Grafana (2)".

And here is the first one if you haven't read it yet.

Well now it seems ready

let's see if it is working. I can do that by looking at the logs in the Grafana Pod.

Error: pq: Private key file has group or world access. Permissions should be u=rw (0600) or less

a related issue github.com/lib/pq/issues/825

Uh oh, there is an error. 😨

Workaround with issues with the library (and the chart)

I changed the code like these:

resource "helm_release" "grafana" {
  # ...

  values = [<<-EOT
    # mount secret as file in the container
    extraSecretMounts:
    - name: sql-certs-r
      secretName: ${kubernetes_secret.grafana_sql_creds.metadata[0].name}
      mountPath: /etc/grafana-certs-r

    # you don't have to do this if you just want to slip in 
    # `mkdir -p /etc/grafana-certs` in `command:`
    extraEmptyDirMounts:
    - name: sql-certs
      mountPath: /etc/grafana-certs

    command:
    - sh
    - -c
    - "cp /etc/grafana-certs-r/* /etc/grafana-certs && chmod 600 /etc/grafana-certs/client.key && /run.sh"

    # ...

    EOT
  ]
}

Actually, you shouldn't have to do this hack which are (ugly) copying and chmod the file on a run time since k8s supports defaultMode. However, this changing defaultMode simply didn't work for me (permission did not change) - maybe it's a bug in k8s since you can find similar issues a lot - so I took this workaround approach for now.

Alright then..., Is it working now?

Another error pops up.

x509: cannot validate certificate for x.y.z.a because it doesn't contain any IP SANs

Why? because generated (by GCP) CA doesn't contain any IP SANs

So verify-full wouldn't work in this case.

giving up verify-full

if you change to verify-ca

resource "helm_release" "grafana" {
  # ...

  values = [<<-EOT
    # ...
    env:
      # ...
      GF_DATATBASE_SSL_MODE: verify-ca
      # ...
    # ...
    EOT
  ]
}

Finally working correctly! 🚀😃

You can find more on verify-full vs verify-ca in here which contains this:

The difference between verify-ca and verify-full depends on the policy of the root CA. If a public CA is used, verify-ca allows connections to a server that somebody else may have registered with the CA. In this case, verify-full should always be used. If a local CA is used, or even a self-signed certificate, using verify-ca often provides enough protection.

Takeaways

• Connecting to GCP managed services via Private IP requires some setup and an effort to understand what's really going on.
• Cloud computing may look easy and all magical to create/delete any resources on demand but (hidden) restrictions could exist.
• Checking with Web Console could give you a better understanding of what's going in your Cloud service. Even if you operate everything by code in the end.
• Badly assuming (YAML) values syntax in a Helm chart could lead you to waste your time (and a chance of learning experiences)
• really understanding what is required to do with SSL can be tricky even if you know the basics of SSL and PKI.
• Integrating multiple technologies might be just complex even though you are just trying to achieve "simple" things.

Well... it was fun(?) writing this series 😅. See you again.

Extra stuff, after posting

Few tips about Grafana, PostgreSQL, Cloud SQL might save your time.

1. Cloud SQL Proxy will timeout if you can't reach the instance via IP

You might want to access your Cloud SQL with psql or other tools via proxy to take advantage of gcloud auth.

Proxy binary or container image is a pretty convenient tool but it's not a magic that will work in every scenario. It simply is restricted from any IP network. if your SQL instance has only private IP, and wherever you are running the proxy can't access that IP, you will see that your proxy will give you time out.

2. select * from user; is not what you want.

Now you are successfully connected to DB with psql and might want to select the user table. But user is a reserved keyword so you might want to do select * from public.user; instead.

You may look at here and [here](stackoverflow.com/questions/50719263/psql-s.. for details

3. You might have no clue why Grafana's default admin password is not admin

The current version (5.0.10 at the time of writing) of Grafana chart has some conditions to generate a random password and letting the Pod using it.

Which you can see here and here

In my case, it wasn't respecting my custom k8s Secret that supposes to set the admin password in the Pod

4. You can reset admin's password with grafana-cli

Grafana CLI comes in the Pod. So you can kubectl exec the container and use that CLI to reset password if you are in trouble with having hard time to find the password and just want to reset.