Access denied for user on Cloud SQL when connecting from Cloud Run service

Question

I have built a python flask app, which connects to cloud SQL instance and does some stuff. I made it work locally, so I dockerized it, and uploaded it to container registry on google cloud. Next, I made a cloud run service from that container, and the error that I get is that **"mydb" is not defined**. By inspecting the logs from SQL instance, I saw the following error: **[Server] Access denied for user 'root'@'ACTUAL_IP_ADDRESS' (using password: YES)"**

Here is only a snippet of code to show the actual problem:

from flask import Flask

app = Flask(__name__)

@app.route('/')
def index():
    result = mydb.execute(text("SELECT * from users")).fetchall() #error line
    msg = ""
    for row in result:
        msg += str(row) + "\n"
    return msg

def createSQLConnection():
    # create SQLAlchemy connection pool
    with Connector() as connector:
        conn = connector.connect(
            "actual_region", 
            "pymysql",
            user="root",
            password="actual_password",
            db="actual_db",
            ip_type=IPTypes.PUBLIC 
        )
    pool = sqlalchemy.create_engine(
        "mysql+pymysql://",
        creator=lambda: conn,
    )
    return pool

if __name__ == "__main__":
    pool = createSQLConnection()
    mydb = pool.connect()
    app.run(debug=True)`

I followed the docs for connection. Previously I used mysql connector for python, but I switched to google's library for sql because it has authorization automatically.

I configured my sql instance to have public ip adress and to accept anyone(I know it is bad practice to do that, but I'll change it later)SQL Config

I enabled IAM admin api, and I added sql cliend permission to both the default app engine account that should be used here, and to account that is listed as service account on sql instance Account permissions

And also while deploying, I added my sql instance. SQL Instance

As I sad, this code works locally, and I can connect to the db from like sql workbench without any issues, so the credential are good. I guess my issue is something with cloud run configuration. If possible I would like to keep it simple (no VPC, sockets)

Answer 1

This error is caused by Flask. Variables initialized in your if __name__ == "__main__": block aren't global variables and won't be accessible within the scope of your index function and route. You can look at using a Flask session to store the database connection pool.

For simplicity you can also try unblocking yourself with using a global variable.

from flask import Flask
from google.cloud.sql.connector import Connector, IPTypes
from sqlalchemy import text, create_engine

app = Flask(__name__)

def createSQLConnection():
    # create SQLAlchemy connection pool
    with Connector() as connector:
        conn = connector.connect(
            "project:region:instance", 
            "pymysql",
            user="root",
            password="actual_pass",
            db="actual_db",
            ip_type=IPTypes.PUBLIC 
        )
    pool = create_engine(
        "mysql+pymysql://",
        creator=lambda: conn,
    )
    return pool

# global connection pool
pool = createSQLConnection()
mydb = pool.connect()

@app.route('/')
def index():
    result = mydb.execute(text("SELECT NOW()")).fetchone() #error line
    time = str(result[0])
    return time

if __name__ == "__main__":
    app.run(debug=True)

I configured my sql instance to have public ip adress and to accept anyone(I know it is bad practice to do that, but I'll change it later)SQL Config

You do not need to add any authorized networks at all. The Cloud SQL Python Connector will authorize the IP for you with a secure connection.

And also while deploying, I added my sql instance. SQL Instance

Adding this connection is also unnecessary when using a Cloud SQL Language Connector such as the Python one you are using. Adding a Connection to your service is only necessary while using the Cloud SQL Auth Proxy.