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()

Are you using second generation cloud sql instances? cloud.google.com/functions/docs/… — Doug Stevenson
– Doug Stevenson, Commented Sep 14, 2019 at 4:32
@DougStevenson Yes, I've verified that I'm using second gen Cloud SQL — sasawatc
– sasawatc, Commented Sep 15, 2019 at 7:49

kurtisvg · Accepted Answer · 2019-09-16 15:50:08Z

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.

answered Sep 16, 2019 at 15:50

kurtisvg

3,5851 gold badge10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sasawatc Over a year ago

Thanks @kurtisvg, the Cloud Function and the Cloud SQL are in a different project. So after adding the service account to Cloud SQL IAM role like you suggested, it works!

Collectives™ on Stack Overflow

Using Cloud SQL (Postgres) in Cloud Function

Edit:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related