Problems connecting Docker container to PostgreSQL in App Engine using Flask

Question

I want to deploy a Flask application that uses Orator as the ORM and I'm having problems connecting to a SQL instance in Google Cloud Platform. I've already set up the IAM permissions needed as explained here but I'm still not being able to connect to the instance. If I manually set the firewall permission of the instance's IP the connection succeeds, but if the IP changes (it does several times) I cannot connect anymore.

This is my Dockerfile:

FROM gcr.io/google-appengine/python

RUN virtualenv /env

ENV VIRTUAL_ENV /env
ENV PATH /env/bin:$PATH

ADD requirements.txt /app/requirements.txt
RUN pip install -r /app/requirements.txt

ADD . /app

CMD gunicorn -b :$PORT main:app

This is my app.yaml:

runtime: custom
env: flex

env_variables:
  POSTGRES_HOST: <SQL-INSTANCE-IP>
  POSTGRES_DB: <MY-POSTGRES-DB>
  POSTGRES_USER: <MY-POSTGRES-USER>
  POSTGRES_PASSWORD: <MY-POSTGRES-PASSWORD>

automatic_scaling:
  min_num_instances: 1
  max_num_instances: 1

Answer 1

The problem was that the cloud_sql_proxy was not being executed in my docker image. For this I had to create a script like this:

run_app.sh

#!/bin/bash

/app/cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE-CONNECTION-NAME> -credential_file=<CREDENTIAL-FILE> &
gunicorn -b :$PORT main:app

Then give it execution permission:

chmod +x run_app.sh

Then changed my Dockerfile so it downloads the cloud_sql_proxy, creates the /cloudsql directory and executes the new_script:

FROM gcr.io/google-appengine/python

RUN virtualenv /env

ENV VIRTUAL_ENV /env
ENV PATH /env/bin:$PATH

ADD requirements.txt /app/requirements.txt
RUN pip install -r /app/requirements.txt

RUN wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /app/cloud_sql_proxy
RUN chmod +x /app/cloud_sql_proxy
RUN mkdir /cloudsql; chmod 777 /cloudsql

ADD . /app

CMD /app/run_app.sh

And finally changed the POSTGRES_HOST in my app.yaml:

runtime: custom
env: flex

env_variables:
  POSTGRES_HOST: "/cloudsql/<INSTANCE-CONNECTION-NAME>"
  POSTGRES_DB: <MY-POSTGRES-DB>
  POSTGRES_USER: <MY-POSTGRES-USER>
  POSTGRES_PASSWORD: <MY-POSTGRES-PASSWORD>

automatic_scaling:
  min_num_instances: 1
  max_num_instances: 1

Cheers