Issue connecting to Cloud SQL with psycopg2

[JoshTanke]

Question

I'm working on a temporary fix to allow us to connect a Django app to Cloud SQL while waiting for the Connector change mentioned in Issue#437.

I am trying to connect to Cloud SQL using IAM auth and psycopg2 with SSL settings set to "Require trusted client certificates". We'd like to avoid using the cloud sql proxy if possible.

So far I haven't had any luck connecting even with the SSL settings set to "Allow unencrypted network traffic" - the psycopg2.connect() function always times out with error:

Traceback (most recent call last):
File "/home/tanke/launchflow/launchflow-examples/gcp-examples/django_postgres/temp.py", line 27, in
with psycopg2.connect(
^^^^^^^^^^^^^^^^^
File "/home/tanke/.pyenv/versions/3.11.7/lib/python3.11/site-packages/psycopg2/init.py", line 132, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.OperationalError: connection to server at "[ PUBLIC IP ]", port 5432 failed: Connection timed out
Is the server running on that host and accepting TCP/IP connections?

Note: I replaced the actual ip with [ PUBLIC IP ] in that error message

Code

import google.auth
import psycopg2
from google.auth import impersonated_credentials
from google.auth.transport.requests import Request

# initialize ADC creds
source_credentials, _ = google.auth.default(
    scopes=["https://www.googleapis.com/auth/sqlservice.login"]
)


target_credentials = impersonated_credentials.Credentials(
    source_credentials=source_credentials,
    target_principal="**********@**********.iam.gserviceaccount.com",
    delegates=[],
    lifetime=3600,
    target_scopes=["https://www.googleapis.com/auth/sqlservice.login"],
)
refresh_request = Request()
target_credentials.refresh(refresh_request)


# Cloud SQL Public Instance IP address
instance_ip = "**.**.**.***"

# interact with Cloud SQL database using psycopg2 connection
with psycopg2.connect(
    dbname="postgres",
    user="**********@**********.iam",
    password=str(target_credentials.token),
    host=instance_ip,
    port="5432",
) as con:
    with con.cursor() as cur:
        cur.execute("SELECT * FROM test.table")
        rows = cur.fetchall()
        print(rows)

Additional Details

No response

[jackwotherspoon]

HI @JoshTanke! Thanks for raising the separate issue 😄

So far I haven't had any luck connecting even with the SSL settings set to "Allow unencrypted network traffic" - the psycopg2.connect() function always times out with error.

So there are two elements at play to enable successful connections to Cloud SQL. There is...

1. the data security
2. the networking

The SSL/TLS settings handle 1). should the data be encrypted, does it need to be verified by the client etc etc. You have this item figured out (although we definitely don't recommend the "Allow unencrypted network traffic" option).

The networking aspect 2) is what I believe is causing your issue. Public IP Cloud SQL instances won't just allow anyone to connect by default for security reasons. For Public IP connections that do not use the Cloud SQL Proxy or Connector you will need to configure Authorized Networks and tell the Cloud SQL instance what IP range to accept connections from. For quick development you can enable 0.0.0.0 to allow all traffic but in production you will want to lock this down to just the addresses of your trusted applications.

Private IP would solve a lot of these issues for you since its a Private Network and you do not need to worry about authorized networks.

Let me know if I can add any clarification.

[jackwotherspoon]

Going to close this as answered. Happy to provide more follow up if need be 😄

[JoshTanke]

Going to close this as answered. Happy to provide more follow up if need be 😄

Sorry I lost track of this! The solution you provided worked great, thank you for your help!