Provide samples for using Python Connector with Django

[jackwotherspoon]

Django is one of the most widely used Python web frameworks. We should look at providing samples on how to connect to a Cloud SQL database using the Python Connector and Django.

What makes this a more difficult feat is that the default Django backend for Postgres is psycopg2 and for MySQL is mysqlclient or mysql-connector-python, which are not currently supported with the Python Connector.

• Django Postgres
• Django MySQL

[hadim]

Any ETA on this?

[jackwotherspoon]

@hadim I can't really provide a precise ETA as of right now, it most likely won't be until early 2023. I have begun playing around with usage of the Python Connector and Django as is currently. However, if development work is needed to add support within Django for the Python Connector I won't be able to get around to it until the new year.

If anyone here has had experience in leveraging the Python Connector with Django I'm more than happy to hear about their feedback/experience to update the Python Connector documentation accordingly with a code sample.

Will ping this thread with any updates moving forward.

[stiangrim]

Any updates on this?

[jackwotherspoon]

Hi @stiangrim, I've begun trying to find elegant workarounds to using the Cloud SQL Python Connector with Django as I hope to avoid having to write Django backend(s) to support the Python Connector.

In the meantime if you or anyone else has had any luck feel free to post your finding here! I'm sure others would benefit greatly 😃

[danielroseman]

I don't know about doing it without writing a Django backend, but it turned out to be super easy to write one that supports the connector. In my case, since I was only targeting MySQL, I put this into a cloudsql/base.py file in my project:

from django.db.backends.mysql import base
from google.cloud.sql.connector import Connector


class DatabaseWrapper(base.DatabaseWrapper):
    def get_new_connection(self, conn_params):
        return Connector().connect(**conn_params)

and then with a DATABASES setting like this:

DATABASES = {
  "default": {
    "ENGINE": "cloudsql",
    "USER": "...",
    "PASSWORD": "...",
    "NAME": "...",
    "OPTIONS": {
      "driver": "pymysql",
      "instance_connection_string": "project:region:instance"
    }
}
import pymysql
pymysql.install_as_MySQLdb()

it Just Worked. (The install_as_MySQLdb is needed as Django doesn't support PyMySQL out of the box; if the connector starts supporting mysqlclient, as proposed elsewhere, we can switch to that and this can go away.)

Happy to contribute this as a PR to the docs.

[jackwotherspoon]

@danielroseman That is great news! 👏

Would be greatly appreciated if you could put up a PR adding this to the frameworks section of our README, we'd love to showcase this solution for others 😄

[enocom]

I don't know about doing it without writing a Django backend, but it turned out to be super easy to write one that supports the connector.

Writing three separate custom backends for each db engine is probably the way to go. Incidentally, this mirrors what we do in Go with three separate database/sql hooks. Similar effort and similar result.

[punjabdhaputar]

Is there any support for Postgres + Django using the cloud-sql-connector?

[enocom]

You'd have to write a custom backend using pg8000 (or asyncpg) like above. I haven't tried this myself, but in theory it's possible.

[rcleveng]

Here's one way to do it.

https://github.com/rcleveng/django_gcp_iam_auth

Instructions on how to install with pip on the README.md
Just update the DATABASES entry to replace the ENGINE for your connection.

DATABASES["default"]["ENGINE"]  = "django_gcp_iam_auth.postgresql"

[enocom]

Thanks, @rcleveng -- that's a nice approach.

In effect it's a variation of #214 (comment).

It gets you IAM authentication without the Connector. Meanwhile, we're working on improving Django support in the near term.

[enocom]

Adding the code sample from the link above for posterity:

import copy
from django.db.backends.postgresql import base

try:
    import google.auth
    import google.auth.exceptions
    from google.auth.transport import requests
    from google.auth.credentials import Credentials, Scoped, TokenState

except google.auth.exceptions.DefaultCredentialsError:
    pass

CLOUDSQL_IAM_LOGIN_SCOPE = ["https://www.googleapis.com/auth/sqlservice.login"]

class DatabaseWrapper(base.DatabaseWrapper):
    def get_connection_params(self):
        params = super().get_connection_params()
        # need to remove this otherwise we'll get errors like
        #   'invalid dsn: invalid connection option "gcp_iam_auth"'
        if params.pop("gcp_iam_auth", None):
            self._credentials, _ = google.auth.default(scopes=CLOUDSQL_IAM_LOGIN_SCOPE)
            if not self._credentials.token_state == TokenState.FRESH:
                self._credentials.refresh(requests.Request())
            params.setdefault("port", 5432)
            # TODO - should we add in a resource restriction for the DB instance?
            # https://cloud.google.com/iam/docs/downscoping-short-lived-credentials#auth_downscoping_token_broker-python
            # Set password to newly fetched access token
            params["password"] = self._credentials.token

        return params

[JoshTanke]

Adding the code sample from the link above for posterity:

import copy
from django.db.backends.postgresql import base

try:
    import google.auth
    import google.auth.exceptions
    from google.auth.transport import requests
    from google.auth.credentials import Credentials, Scoped, TokenState

except google.auth.exceptions.DefaultCredentialsError:
    pass

CLOUDSQL_IAM_LOGIN_SCOPE = ["https://www.googleapis.com/auth/sqlservice.login"]

class DatabaseWrapper(base.DatabaseWrapper):
    def get_connection_params(self):
        params = super().get_connection_params()
        # need to remove this otherwise we'll get errors like
        #   'invalid dsn: invalid connection option "gcp_iam_auth"'
        if params.pop("gcp_iam_auth", None):
            self._credentials, _ = google.auth.default(scopes=CLOUDSQL_IAM_LOGIN_SCOPE)
            if not self._credentials.token_state == TokenState.FRESH:
                self._credentials.refresh(requests.Request())
            params.setdefault("port", 5432)
            # TODO - should we add in a resource restriction for the DB instance?
            # https://cloud.google.com/iam/docs/downscoping-short-lived-credentials#auth_downscoping_token_broker-python
            # Set password to newly fetched access token
            params["password"] = self._credentials.token

        return params

@enocom Your comments on this thread and others has been really helpful! I'm wondering if you might have any ideas on this related issue I'm hitting. I've been trying to get the IAM auth approach to work with Django on my local machine but the connection keeps timing out. AFAICT my psycopg2.connect database url matches the format in this other thead: dbname={db_name} client_encoding=UTF8 user={sa}@{project}.iam host={public_ip} port=5432 password={token}. I have public + private IPs enabled and also have "Require trusted client certificates" turned on in the SSL settings. My best guess is the SSL setting might be the issue - do I need to create an SSL client certificate and include it in my DatabaseWrapper somehow, or does the IAM auth flow do that for me somehow?

[jackwotherspoon]

@JoshTanke Thanks for the comment on this issue. Do you mind creating a new issue on the repo with the error you are seeing? That way we can help debug your problem while keeping this thread clean 😄

I have public + private IPs enabled and also have "Require trusted client certificates" turned on in the SSL settings. My best guess is the SSL setting might be the issue - do I need to create an SSL client certificate and include it in my DatabaseWrapper somehow, or does the IAM auth flow do that for me somehow?

Once you open a new issue with your stacktrace I'll get a better grasp of what is going on. However, yes if you have "Require trusted client certificates" turned on and are not creating a database connection using SSL with client certs (mTLS). Which by the sample above and database URL you provided you are not then I would expect it to error. You would need to download your certificates and add their path to your psycopg2 database url (this SO post does a good job explaining the psycopg2 config)

Depending on your use-case and especially for Private IP connections you may be fine with turning the setting to "Allow only SSL connections". This would allow you to update your database url with sslmode=require to:

bname={db_name} client_encoding=UTF8 user={sa}@{project}.iam host={public_ip} port=5432 password={token} sslmode=require

However, this still isn't ideal for Public IP connections which we are working on by adding support for psycopg2 to the Cloud SQL Python Connector soon (will be worked on this quarter) which will then also unblock Django for postgres and give users a way of using Django + Cloud SQL for "Require trusted client certificates" without having to manage certificates yourself.