skip to Main Content

I’m trying to connect to a cloud sql postgreSQL instance using Python code from my local machine (locally) and using the Private IP of my cloud sql instance.

from google.cloud.sql.connector import Connector, IPTypes
import pg8000

import sqlalchemy
from sqlalchemy import text

def connect_with_connector_auto_iam_authn():

instance_connection_name = "connection name"
db_user = "SA name" # e.g. 'my-db-user'
db_name = "postgres"  # e.g. 'my-database'

ip_type = IPTypes.PRIVATE

# initialize Cloud SQL Python Connector object
connector = Connector()

def getconn() -> pg8000.dbapi.Connection:
    conn: pg8000.dbapi.Connection = connector.connect(
        instance_connection_name,
        "pg8000",
        user=db_user,
        password="",
        db=db_name,
        enable_iam_auth=True,
        ip_type=ip_type
    )
    return conn
# The Cloud SQL Python Connector can be used with SQLAlchemy
# using the 'creator' argument to 'create_engine'
pool = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn, pool_pre_ping=True
)

with pool.connect() as conn:
    results = conn.execute(text("SELECT current_user, current_database();"))
    for row in results:
        print(row)
print("connected")


return "connected"

connect_with_connector_auto_iam_authn()

I’m getting the following error message:

TimeoutError: [WinError 10060] A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
The above exception was the direct cause of the following exception:

sqlalchemy.exc.InterfaceError: (pg8000.exceptions.InterfaceError) Can’t create a connection to host 10.82.1.2 and port 3307 (timeout is None and source_address is None).

I’m thinking maybe I cannot use the private IP from my machine and the only way to use private ip is within the same VPC, this means using another GCP resource.

Thanks in advance!

2

Answers


  1. You can connect to Cloud SQL configured with a private IP address from outside Google Cloud using an SSH reverse tunnel. This does require a Compute Engine instance to provide the SSH tunnel.

    Login or Signup to reply.
  2. Maybe if you need to connect from your local machine without a VPN or Bastion host, you can configure your Cloud SQL instance to have a public IP address. However, for security reasons, is not a good Idea for production databases.

    https://cloud.google.com/sql/docs/postgres/configure-ssl-instance

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search