Add support for psycopg2 driver.

Support PostgreSQL psycopg2 driver with connector.

@jackwotherspoon

Hey @jackwotherspoon, I've hit this exact problem this week. Do you have an estimate? Any rough ideas would be greatly appreciated. Thanks in advance.

@gunar

@gunar This has been prioritized and will be worked on in early 2022.

In the meantime, you can connect to Cloud SQL using Python and psycopg2 with the Cloud SQL Auth Proxy through Unix sockets or TCP. For an explanation and example see here: https://cloud.google.com/sql/docs/postgres/connect-admin-proxy#expandable-2

For the Unix sockets sample just swap out part of the pg8000 code for this code snippet:

pool = sqlalchemy.create_engine(
    sqlalchemy.engine.url.URL.create(
        drivername="postgresql+psycopg2",
        username=db_user,  # e.g. "my-database-user"
        password=db_pass,  # e.g. "my-database-password"
        database=db_name,  # e.g. "my-database-name"
        query={
            "host": "{}/{}".format(
                db_socket_dir,  # e.g. "/cloudsql"
                instance_connection_name)  # i.e "<PROJECT-NAME>:<INSTANCE-REGION>:<INSTANCE-NAME>"
        }
    ),
    **db_config
)

Hope this helps for the time being! Thanks for the feedback!

@jackwotherspoon

@jackwotherspoon Thanks man. I'm not inclined to go with the Cloud SQL Auth Proxy as that's one more thing for the user to remember to execute.

I guess I could make the python script itself spawn the process for the proxy—has anyone attempted that?

Also, do I need sqlalchemy in order to connect to the proxy or can I connect directly with psycopg2?

@jackwotherspoon

Hi @jackwotherspoon , thank you for all your work on cloud sql connectors

Do you have any estimates for psycopg2 support? It would be really nice to have

By the way the issue has p2 priority but it has the biggest number of thumb-ups😉

@kemics

Hi @kemics, thanks for the kind words and showing your enthusiasm towards the Cloud SQL Python connector.

I currently can't provide a specific timeline for psycopg2 support because there are a lot of factors at play here but I can provide some details for you.

In order to support psycopg2 connections with the connector we require the ability to pass in a pre-existing SSL connection and skip a couple of postgres database level protocols (this is the hard part and is required because Cloud SQL connections connect to a proxy server and not directly to the database itself).

I have been in talks with the psycopg2 team about helping make some changes in psycopg2 to help support these type of connections, the work also requires additional upstream changes into libpq.

Overall, there are a lot of moving parts but I will continue to keep the ball rolling on this and make sure that I update progress here on the issue for more transparency.

Thanks again for the gentle nudge here and for showing interest in the Python connector, much appreciated.

Have a great day :)

@jackwotherspoon

Thanks @jackwotherspoon , great explanation 👍

I'm a fan of what you are doing here and have successfully tried this repo with sqlalchemy and also https://github.com/GoogleCloudPlatform/cloud-sql-go-connector . Works like a charm and a huge improvement in terms of security

@jackwotherspoon

@jackwotherspoon

With regards to:

For the Unix sockets sample just swap out part of the pg8000 code for this code snippet:

Is there any way to use my already logged in gcloud IAM authentication and not have to pass in username and password as part of the code? (I'm trying to get away from .env files).

@gunar

@gunar

Is there any way to use my already logged in gcloud IAM authentication and not have to pass in username and password as part of the code? (I'm trying to get away from .env files).

This can be done in a fairly straight-forward way for a service account authentication using Manual IAM Database Authentication. You just need to add your service account as an IAM database user to your Cloud SQL instance and grant it the Cloud SQL Instance User IAM role alongside the usual Cloud SQL Client role.

Once this is done, the below snippet should work in any environment that uses the service account (i.e. Cloud Run, Cloud Functions, App Engine etc.) to connect to Cloud SQL via Public IP (unix sockets is recommended for Public IP, see below comment for Private IP TCP connection snippet). See comments below for running locally.

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

# uncomment below two lines to set application credentials to service account locally
# import os
# os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/key.json"

# get application default credentials
creds, _ = google.auth.default()

# refresh credentials if not valid
if not creds.valid:
  request = Request()
  creds.refresh(request)

# IAM service account database user, postgres removes suffix
sa_iam_user = (creds.service_account_email).removesuffix(
  ".gserviceaccount.com"
)

# create SQLAlchemy connection pool
pool = sqlalchemy.create_engine(
  sqlalchemy.engine.url.URL.create(
    drivername="postgresql+psycopg2",
    username=sa_iam_user, # login to database as service account user
    password=str(creds.token), # manual IAM database authn
    database=db_name,  # e.g. "my-database-name"
    query={
      "host": "{}/{}".format(
          "/cloudsql",  # e.g. "/cloudsql"
          instance_connection_name)  # i.e "<PROJECT-NAME>:<INSTANCE-REGION>:<INSTANCE-NAME>"
    }
  ),
)

# ... use connection pool

Correct -- the Proxy will handle that for you. If you'd like to open an issue in the Proxy repo, I'd be happy to help you out.

The approach describe above assumes you bypass the Connector and Proxy entirely.

We are currently using the Auth Proxy binary in a separate sidecar container, but would love to only use this library for simplicity. Are there still plans to add support for the psycopg2 driver?

@nordewal

We are currently using the Auth Proxy binary in a separate sidecar container, but would love to only use this library for simplicity. Are there still plans to add support for the psycopg2 driver?

@nordewal Yes it is still in our backlog and on our radar! Unfortunately, I have just have not had the time to tackle it.

That being said, if any external contributors want to take a first attempt at it we would happily take any contributions 😄

Happy to assist on providing guidance on the approach I would take.

FYI, it seems that psycopg2 is no longer getting new features and was superseded by pyscopg version 3. psycopg/psycopg2@89005ac

Any update on this?

@jackwotherspoon

@jackwotherspoon I would like to contribute on working on this. What should I do to get some more context about?

This might not be a great first issue to work on, as psycopg has the same limitations as the underlying libpq library and so doesn't expose an API to create sockets for the connection to Cloud SQL.

High level, we've been thinking that to support psycopg we would create a unix socket within the Python connector, and configure psycopg with that socket. Then internally, we would proxy data to and from the unix socket to the TLS connection to the Cloud SQL instance. We've done this in the Node connector, but it's a non-trivial change.

I can give it a try, I have the files already created and yesterday was experimenting with psycopg constructors to see if they allow to pass a socket, the solutions that I have found so far are:

Write temporary files having the TLS files and passing them to the parameters
Open some sort of ssh tunneling, and make the connection happen through local host and ports
The solution you mentioned
Going to give a shot to the solution you mentioned and I hope to get you back with a PR or with more questions 😄

@UzielSilva

@UzielSilva are you planning to implement legacy psycopg2 or psycopg version 3?

I'm already working on psycopg 3

@enocom

@enocom I managed to create the unix proxy and the connector is now working with psycopg 3, the issue now is related to SQLAlchemy, it has some issues detecting the version and it also expects show transaction isolation level to return an array, which isn't. BUT, if I try to use the connection without SQLAlchemy, it works, actually it gives me back something like {'transaction_isolation': 'read committed'}.

I think this is huge progress, since psycopg 3 is already beaten, but I need some help making it to work with SQLAlchemist. I can setup a PR but of course tests are going to break. Do you want me to do a test that doesn't use SQLAlchemy?

Adding my PR here: #1311

Navigation Menu

Repository navigation

Add support for psycopg2 driver. #214

13 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Add support for psycopg2 driver. #214

Description

Activity

gunar commented on Dec 16, 2021

jackwotherspoon commented on Dec 16, 2021

gunar commented on Dec 16, 2021

kemics commented on Jun 22, 2022

jackwotherspoon commented on Jun 23, 2022

kemics commented on Jul 21, 2022

gunar commented on Aug 3, 2022

jackwotherspoon commented on Aug 3, 2022

13 remaining items

enocom commented on Oct 13, 2023

nordewal commented on Mar 18, 2025

jackwotherspoon commented on Mar 18, 2025

jeffclay commented on May 14, 2025

areiner222 commented on Jul 12, 2025

UzielSilva commented on Jul 14, 2025

enocom commented on Jul 15, 2025

UzielSilva commented on Jul 15, 2025

jeffclay commented on Jul 15, 2025

UzielSilva commented on Jul 15, 2025

UzielSilva commented on Jul 16, 2025

UzielSilva commented on Jul 16, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions