6

I've previously used Heroku and AWS and am now setting up a service on the Google Cloud platform using App Engine and Cloud SQL (Postgres).

We've tried to build the app using 12 factor principles.

The setup has been proving so tedious that I'm starting to question if I've just fundamentally missed something.

Here's the things that just have me confused:

  1. Being encouraged to write passwords into a file that ends up in source control (app.yaml).
  2. Needing to hack a workaround for loading environment variables (if I don't want them committed to source control).
  3. Finding that to connect to the cloud sql instance I need a line referring to the specific instance in app.yaml - so now I need app.staging.yaml and app.production.yaml ?
  4. Finding that line only seems to support 1 DB instance and unclear if there is support if we want the app to connect to 2 DBs.

Have I missed some major development in server admin where these have become best practices? Having just discovered #3 & #4, I'm really starting to think I must've done something fundamentally wrong in my set up. Have I?

ChrisJ
  • 285
  • 1
  • 9
  • The documentation here https://cloud.google.com/appengine/docs/standard/php/cloud-sql/using-cloud-sql-mysql does say "Add the Cloud SQL instance connection name, database, user, and password to the environment variables in app.yaml". If doing that, why have a password at all, just rely on IAM? – frabcus May 21 '18 at 10:30

2 Answers2

3

I haven't specifically attempted to configured a application the way you are describing but I know those are not the takeaways that Google intends. To address some specifics:

  1. Connecting to Cloud SQL specifically says the following to suggest storing passwords is not what they intend and provide a separate solution for secrets.

    # Remember - storing secrets in plaintext is potentially unsafe. Consider using
    # something like https://cloud.google.com/secret-manager/docs/overview to help keep
    # secrets secret.
    
  2. Based on this article, Google App engine does not support environment variables in app.yaml (Comments on similar issues with Google App Engine). My fix for a similar problem was the following code:

    #!/bin/bash
    # deploy-helper.sh
    
    prepare_yaml() {
        [ -z "$1" ] && echo "ERROR: template.yaml filename must be provided." && exit 1
    
        template="${1}"         # ARG #1 : filename of template yaml
        finalYAML=$(mktemp)     # make temporary file
    
        generated_stdin_cmds="$(echo "cat <<EOF >\"$finalYAML\""; cat $template; echo EOF;)"
        source /dev/stdin <<< "$generated_stdin_cmds"
        echo "$finalYAML";      # return filepath of filled-in file
        return 0
    }
    
    # Set environment variables 
    TYPE="prod"                     # in script file
    source env/production.vars      # read them in from a file
    
    DEPLOYMENT_FILE="app.flexible.yaml"
    tmpfile="$(prepare_yaml "app.tpl.yaml")"
    if [ $? -eq 0 ]; then
        mv "$tmpfile" "$PWD/$DEPLOYMENT_FILE"   # gcloud wants specific filename
        gcloud app deploy "$DEPLOYMENT_FILE"
        rm "$DEPLOYMENT_FILE"                   # Cleanup temporary file
    fi
    

    NOTE: This script can also be written in python by using the subprocess library. This is how I automated the build for my GKE project.

    Next, Take your application app.yaml file and add bash variable string replacements like this example app-tpl.flexible.yaml:

    # example app-tpl.flexible.yaml
    ---
    runtime: custom
    env: flex
    env_variables:
      MYSQL_SOCK: "/cloudsql/${project}:${region}:${instance}"
      MYSQL_DB: "${db_name}"
      MYSQL_USER: "${db_user}"
      MYSQL_PASSWORD: "${db_pw}"
    

    Make file containing of the necessary variables (ignored in source control)

    #!/bin/bash
    # FILE: env/production.vars
    
    project="project-name"
    region="europe-west1"
    instance="prod001"
    db_name="db001"
    db_user="root"
    db_pw="qwerty"
    
  3. This should be solved by answer #2. Add a flag/option on the script to execute for swapping the SQL instance or change the environment variable name.

  4. Looks like you will need to configure two CloudSQL connection instances and then the automatic CloudSQL Proxy will be able to connect to 2 databases. See cloud.google.com/sql/docs/mysql/connect-app-engine-flexible and this answer since they got it working with different port numbers.

Good Luck!

codejedi365
  • 156
  • 5
  • 1
    Thanks. At the time I wrote this question Google Secrets Manager would not be available for another 18 months, but that's what I'd do now. – ChrisJ Aug 17 '21 at 21:05
1

@codejedi's answer above (use Google Secrets Manager or inject them during deployment) is what I'd go with today.

At the time I posted this question (April 2018), Google Secrets Manager wouldn't be released for another 18 months.

I ended up rolling my own solution at the time using Datastore.

ChrisJ
  • 285
  • 1
  • 9