Using Cloud SQL (Postgres) in Cloud Function

Question

I'm trying to connect to Cloud SQL running PostgreSQL 11 using a Cloud Function Python script. Ideally, I want to connect to the DB using a simple pandas.DataFrame.to_sql() which requires a sqlalchemy.create_engine() to connect.

This is my current attempt (I've tried both with/without /.s.PGSQL.5432 suffix):

engine = sqlalchemy.create_engine('postgresql+psycopg2://USERNAME:PASSWORD@/DB_NAME?host=/cloudsql/INSTANCE_CONNECTION_NAME/.s.PGSQL.5432')
engine.connect()

I also try the code sample provided by Google which uses psycopg2 directly but also didn't work

Error: function crashed. Details:
could not connect to server: Connection refused
    Is the server running locally and accepting
    connections on Unix domain socket "/cloudsql/vida-production:us-central1:vida-ops/.s.PGSQL.5432"?

Edit:

Thanks @kurtisvg for pointing this out. Since the Cloud Function is trying to connect to Cloud SQL that is on a different project. I have to add the Cloud Function's service account to IAM of the Cloud SQL project.

After setting IAM, the Python code I use to connect sqlalchemy engine to Cloud SQL is this:

# Don't add the "/.s.PGSQL.5432 suffix" because it will already be added back automatically by the library...

engine = sqlalchemy.create_engine('postgresql+psycopg2://USERNAME:PASSWORD@/DB_NAME?host=/cloudsql/INSTANCE_CONNECTION_NAME')
engine.connect()

Answer 1

Make sure to check the following things:

• Have the Cloud SQL Admin API enabled
• Make sure the Functions default service account has the Cloud SQL Client IAM role

If you are connecting between two projects, make sure to enable the Admin API and grant permissions (for both projects) to the Service Account that is running the function you are trying to connect from.