Connecting Cloud Run Flask app to Cloud SQL for PostgreSQL via SQL Auth Proxy always throwing an error

Question

As the title says, I have a Flask app, running on cloud run that I am attempting to connect to Cloud SQL where I have a postgres instance.

After much debugging I stripped down the app to the bare minimum and I receive the following error in the Cloud Run logs: Cloud SQL instance "<instance-name>" is not reachable. Deploy a new revision adding the Cloud SQL connection. See documentation: https://cloud.google.com/sql/docs/mysql/connect-run

But I do have a cloud SQL connection configured.

This is then followed by a long python stacktrace as we'd expect if there is no db connection, which I don't think is useful so I haven't included it here.

TL;DR: I have a feeling the issue is that I'm using the incorrect DB_HOST IP address in order to make sure SQL Auth proxy is used, what should I be using in the DB_URI string?

I followed the steps from this post which is essentially the same as the official documentation.

A summary of what I did:

I have a PostgreSQL instance with a public IP
I made a new table
I deployed my code using gcloud run deploy
I added the Cloud SQL Client role to the service account used for the cloud run service.
I made a revision in which I added the Cloud SQL connection to the cloud run service as detailed in the docs above
I also added DB_USER, DB_HOST, DB_PASS etc as environment variables in a revision.

I tested this locally using the SQL Auth Proxy and it works as expected. In the terminal running the auth proxy I can see a line New connection for "<instance-connection-name>" so it works locally. I used 127.0.0.1 as the DB_HOST when running locally. For context my DB URI is different on my local machine because I'm running Windows and I can't use unix sockets.

In cloud run I have tried setting DB_HOST to both 127.0.0.1 and the DB public IP address, both produce the same error.

my ./run.py file that I use to run the app.

from sampleapp import app

if __name__ == "__main__":
    app.run(debug=True, host='127.0.0.1', port=int(os.environ.get("PORT", 8000)))

my ./sampleapp/__init__.py which contains app instantiation.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

LOCAL_TESTING = bool(os.environ.get("LOCAL_TESTING", False)) # Set to True if running locally

DB_USER = os.getenv('DB_USER')
DB_PASS = os.getenv('DB_PASS')
DB_NAME = os.getenv('DB_NAME')
DB_HOST = os.getenv('DB_HOST')
DB_PORT = os.getenv('DB_PORT')

app = Flask(__name__)
app.config['SECRET_KEY'] = os.getenv('SECRET_KEY')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['DEBUG'] = os.getenv('DEBUG', False)

if LOCAL_TESTING == True:
    # This is what runs on my local machine
    print("Using tcp connection to db")
    DB_URI = f'postgresql+pg8000://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}'
else:
    # This run on cloud run
    print("Using unix_sock connection to db")
    db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")
    DB_URI = f'postgresql+pg8000://{DB_USER}:{DB_PASS}@/{DB_NAME}?unix_sock={db_socket_dir}/{DB_INSTANCE_NAME}/.s.PGSQL.5432'
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
db = SQLAlchemy(app)

from sampleapp import routes

The DB URIs above are taken from the code snippets in the docs but instead of using sqlalchemy.create_engine() I'm using flask-sqlalchemy and instead of sqlalchemy.engine.url.URL.create() I'm just making my own URI string.

The excecution line from my ./Dockerfile:

CMD exec gunicorn --bind :$PORT --workers 1 --threads 8 --timeout 0 run:app

From my cloud run logs I can see that my routes work fine up until the point I try to access the DB.

From reading this answer and the docs on the Cloud SQL Python connector I should be using the connector code from google but I don't know how to use this in the context of flask_sqlalchemy because google always uses standard sqlalchemy. I couldn't work out how to assign the engine object in google's code snippets to the flask-sqlalchemy SQLAlchemy object that I am using for db.

Apologies for the long question I have been trying to debug this for a long time to no avail. Any suggestions are greatly appreciated.

You are setting DB_URI under local testing but DB_URL under the else statement. Is this intentional? I believe you can also set the sqlalchemy.engine.url.URL object directly for app.config['SQLALCHEMY_DATABASE_URI'] — kurtisvg
– kurtisvg, Commented Mar 24, 2022 at 2:42
@WietseVenema apologies that's an unused variable I thought I would need. Now edited out. — Space Otter
– Space Otter, Commented Mar 24, 2022 at 15:42
@kurtisvg In the code it is all URL I just changed it to URI to stick with convention for this post and I must have missed one. Isn't that what I am already doing? I had assumed the app.config['SQLALCHEMY_DATABASE_URI'] get's passed to the sqlalchemy.engine object. DO you know of any documentation I can read with instructions for that? — Space Otter
– Space Otter, Commented Mar 24, 2022 at 15:44
Here's the documentation for flask-sqlalchemy. It looks like the regular way to configure the engine directly is with the SQLALCHEMY_ENGINE_OPTIONS which would let you use the Cloud SQL python connector. Alternatively, I think you can set SQLALCHEMY_DATABASE_URI equal to a sqlalchemy.engine.url.URL object that's initialized like in the current documentation. — kurtisvg
– kurtisvg, Commented Mar 24, 2022 at 16:27

ErnestoC · Accepted Answer · 2022-03-25 19:24:03Z

I made a bare-bones app deployed on Cloud Run resembling your setup. For local testing, I also used the Unix Sockets connection:

./cloud_sql_proxy -dir=$DB_SOCKET_DIR --instances=$INSTANCE_CONNECTION_NAME --credential_file=$GOOGLE_APPLICATION_CREDENTIALS &

When your Cloud Run service connects to a public Cloud SQL instance, it's using the Unix Socket connection, and therefore the variables of DB_HOST and DB_PORT are not being utilized. Only during local testing which you say worked as expected.

Besides that, it's not clear in your code where DB_INSTANCE_NAME comes from (the instance connection name for your Cloud SQL instance). DB_NAME is the database name you are querying inside your Postgres instance (wasn't clear for me at first).

main.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import os

db_user = os.environ["DB_USER"]
db_pass = os.environ["DB_PASS"]
db_name = os.environ["DB_NAME"]
db_socket_dir = os.environ.get("DB_SOCKET_DIR", "/cloudsql")
instance_connection_name = os.environ["INSTANCE_CONNECTION_NAME"]
DB_URI = f"postgresql+pg8000://{db_user}:{db_pass}@/{db_name}?unix_sock={db_socket_dir}/{instance_connection_name}/.s.PGSQL.5432"

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI

db = SQLAlchemy(app)

# Model to map to user table in postgres
class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    fullname = db.Column(db.String(255), unique=False, nullable=True)

#Fetches and displays a value from CloudSQL for testing
@app.route("/")
def hello_query():
    message = User.query.filter_by(id=1).first()
    return "Hello world, message: {}!".format(message.fullname)

if __name__ == "__main__":
    app.run(debug=True, host="127.0.0.1", port=int(os.environ.get("PORT", 8080)))

Deployment command (for testing only)

gcloud run deploy cloudrunpostgres \
--add-cloudsql-instances=$INSTANCE_CONNECTION_NAME \
--update-secrets=INSTANCE_CONNECTION_NAME=INSTANCE_CONNECTION_NAME:latest \
--update-secrets=DB_USER=DB_USER:latest \
--update-secrets=DB_PASS=DB_PASS:latest \
--update-secrets=DB_NAME=DB_NAME:latest \
--source . \
--service-account=$SERVICE_ACCT \
--max-instances=3 \
--allow-unauthenticated \
--region=us-central1

This worked both on localhost and deployed as a service, which I hope is useful as a reference in both the command and the code.

Note: I did install the cloud-sql-python-connector, since it also installs the pg8000 driver as a dependency (in addition to installing Flask, SQLALchemy and Flask-SQLAlchemy)

Collectives™ on Stack Overflow

Connecting Cloud Run Flask app to Cloud SQL for PostgreSQL via SQL Auth Proxy always throwing an error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related