Cloud Run and Cloud SQL Proxy Do Not Seem To Work Together

Question

The service account used by cloud run has more than enough permissions. Cloud SQL Admin, Cloud Run Admin among many others not relevant here and I can give it full on Project Owner permissions and it won't work.

We ALSO know that connecting outside of Cloud Run to the database using Cloud SQL Proxy works, 100%, such as using a sidecar in the deployment or using it locally while authenticated as this account.

There can't possibly be any firewall issues because this is localhost and connected on the GCP back end, internal to our VPC.

There's no logs anywhere that we can find that show Cloud SQL proxy trying to connect to the database nor any hint or clue of whether it succeeded and if not why. We need logs. There aren't enough logs. That's one question, where are some more logs besides container logs?

This is the error:

2023-09-28 20:30:41 DEBUG rasa.core.tracker_store - Attempting to connect to database via 'postgresql://user1:***@localhost/rasa-oss-001'.

The account above has owner and can build tables in the db but this shouldn't be relevant.

2023-09-28 20:30:41 ERROR rasa.core.tracker_store - Could not create tables: (psycopg2.OperationalError) connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused

Open in Logs Explorer

{
insertId: "6515e27100075a076642487a"
labels: {1}
logName: "projects/bdt-eng-play/logs/run.googleapis.com%2Fstderr"
receiveTimestamp: "2023-09-28T20:30:41.492307554Z"
resource: {2}
textPayload: "2023-09-28 20:30:41 ERROR    rasa.core.tracker_store  - Could not create tables: (psycopg2.OperationalError) connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused"
timestamp: "2023-09-28T20:30:41.481799Z"
}

The above error to me means that the Cloud SQL Proxy piece does not work, clearly. It either isn't redirecting that local port to the instance or it isn't succeeding in a connection to the instance. This has NEVER worked for any app so the above logs can apply to anything.

Answer 1

When you deploy a Cloud Run service with a "Cloud SQL connection" either through the command line via the --add-cloudsql-instances flag or through the UI as follows:

What this does is deploys the Cloud SQL Proxy behind the scenes for you via Unix socket, this Unix socket deployment of the Cloud SQL Proxy only works for Public IP Cloud SQL connections. There is a good blog post which outlines this limitation.

For Private IP Cloud SQL connections (such as yours) there are two options:

• deploy the Cloud SQL Proxy as a side car using Cloud Run multi-container deployments
• connect directly to your Cloud SQL instance using TCP (no Cloud SQL Proxy)

The first option has a sample found here that showcases deploying the Cloud SQL Proxy alongside your Cloud Run application using the new multi-container feature. (the sample is for Ruby but the YAML will be identical).

The second option is to remove the Cloud SQL Proxy and connect to your Cloud SQL instance directly using TCP. We recommend the Cloud SQL Proxy primarily for Public IP Cloud SQL connections because of the automated mTLS connection benefit. The Proxy generates a client certificate and uses it to establish a secure SSL connection. However, client certificates are not a necessity for secure connections. If you are using Private IP then your connection is via a private VPC network, the requirement for client certs is not as prominent in this case and setting ssl="require" will still establish a secure SSL connection (which may be more than enough for your use case)

import sqlalchemy
from sqlalchemy import event

engine = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgresql+psycopg2://<user>:<password>@<host>:5432/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="postgresql+psycopg2",
        username="my-user",
        password="my-password",
        host=ip_address,  # Cloud SQL instance IP address (or localhost for local SQL)
        port=5432,
        database=db_name,  # "my-database-name"
    ),
    connect_args={"sslmode": "require"},
)

# use connection from connection pool to query Cloud SQL database
with engine.connect() as conn:
    time = conn.execute(sqlalchemy.text("SELECT NOW()")).fetchone()
    conn.commit()
    print("Current time is ", time[0])

If you are using automatic IAM authentication you can even still do so while connecting without the Proxy:

import sqlalchemy
from sqlalchemy import event

import google.auth
from google.auth.credentials import Credentials
from google.auth.transport.requests import Request

# initialize Google Auth creds
creds, _ = google.auth.default(
    scopes=["https://www.googleapis.com/auth/sqlservice.admin"]
)

def get_authentication_token(credentials: Credentials) -> str:
    """Get OAuth2 access token to be used for IAM database authentication"""
    # refresh credentials if expired
    if not credentials.valid:
        request = Request()
        credentials.refresh(request)
    return credentials.token

engine = sqlalchemy.create_engine(
    # Equivalent URL:
    # postgresql+psycopg2://<user>:empty@<host>:5432/<db_name>
    sqlalchemy.engine.url.URL.create(
        drivername="postgresql+psycopg2",
        username=user,  # IAM db user, e.g. [email protected]
        password="",  # placeholder to be replaced with OAuth2 token
        host=ip_address,  # Cloud SQL instance IP address
        port=5432,
        database=db_name,  # "my-database-name"
    ),
    connect_args={"sslmode": "require"},
)

# set 'do_connect' event listener to replace password with OAuth2 token
@event.listens_for(engine, "do_connect")
def auto_iam_authentication(dialect, conn_rec, cargs, cparams) -> None:
    cparams["password"] = get_authentication_token(creds)

One thing to note is that is that zero trust environments don't like relying on clients to do the right thing. For instance, Cloud SQL has a configuration option to "Allow only SSL connections" which forces connections to use client certs for an mTLS connection in order to succeed. While ssl=require, forces an encrypted connection, if you are requiring an mTLS connection then downloading and managing client certs may also be required. In this case the first option of using the Proxy would probably be a better option.