Closed RaulMaya closed 3 weeks ago
Hi @RaulMaya thanks for raising an issue on the Cloud SQL Python Connector! 😄
There are a couple quick questions I need to understand your use-case before I can assist properly.
connect my Django application to a PostgreSQL instance
Answers to these two questions will help me tailor the guidance on your best path forward.
However, I can give a bit of clarity/help as is now.
'HOST': '/cloudsql/company-XXXXXXXXXXXXXXXX:us-central1:pareto-instance-prod-XXXXXX'
Setting your HOST
to this path assumes you have the Cloud SQL Proxy running in your environment... which by the error you are seeing you either do not or maybe have it mis-configured?
If your Cloud SQL instance is configured with a Private IP (recommended) and your application is deployed to Google Cloud then the recommended path forward would be to just connect directly by setting your HOST
to the Private IP address of your Cloud SQL instance.
DATABASES = {
'default': {
'ENGINE': 'django_gcp_iam_auth.postgresql',
'NAME': 'my_db_name',
'USER': 'user@email',
'PASSWORD': '',
'HOST': 'X.X.X.X', # your Cloud SQL instance's private IP address
'PORT': '5432', # Postgres default port
'OPTIONS': {
'gcp_iam_auth': True,
'sslmode': 'require', # should always be set to 'require' for IAM AuthN
},
}
}
Hope this helps a bit, once I know more about your deployment/configuration I can help more 😄
Related to #437
Hello @jackwotherspoon thanks for addressing this issue, regarding the questions:
Where does your Django application live? Are you running it locally, deployed to Cloud Run, GKE etc? The App is running using Cloud Run. I'm trying to create this connection locally to emulate how it will work while deployed, in theory the connection should work.
Does your Cloud SQL instance have a Public or Private IP? It is a private IP.
DATABASES = {
'default': {
'ENGINE': 'django_gcp_iam_auth.postgresql', # Use the custom engine
'NAME': 'pareto_db_1',
'USER': 'rmaya@ford.com',
'PASSWORD': '', # Password is managed via IAM, so leave it empty
'HOST': '10.XX.XXX.141', # Use the correct Cloud SQL connection name
'PORT': '5432', # Typically leave as empty string for Unix socket connection
'OPTIONS': {
'gcp_iam_auth': True, # Enable IAM authentication
'sslmode': 'require', # Set SSL mode as needed (e.g., disable if using Unix sockets)
},
}
}
Additional Data:
gcloud auth application-default login
in the powershellI use the suggested snippet, but I'm encountering with an error: django.db.utils.OperationalError: connection to server at "10.XX.XXX.141", port 5432 failed: FATAL: Cloud SQL IAM user authentication failed for user "rmaya8@ford.com"
django.db.utils.OperationalError: connection to server at "10.XX.XXX.141", port 5432 failed: FATAL: Cloud SQL IAM user authentication failed for user "rmaya8@ford.com"
@RaulMaya This is a promising error, it means your connection is actually reaching the Cloud SQL server.
'USER': 'rmaya@ford.com',
Is this missing an "8" in the username? I am going to move forward assuming this is just a typo and was the proper username in your code.
Additional Data:
- I authenticate to google using the gcloud auth application-default login in the powershell
- Also I pass my GOOGLE_APPLICATION_CREDENTIALS in the terminal before I start my App
This is most likely the cause of your error. Both of these steps do the same thing, set your Application Default Credentials (ADC), if you do them both then the second one will override the first.
If you are setting GOOGLE_APPLICATION_CREDENTIALS
then it means your app will be running as an IAM service account user which does not match your database USER
. Cloud SQL IAM database authentication only works when the OAuth2 token passed as the database password matches and belongs to the database user. It seems you have a mismatch which is why your IAM authentication is failing...
Let me know if the above paragraph makes sense, I can try to clarify if need be.
Hello @jackwotherspoon
Thanks for the huge help, it finally worked, and it was exactly what you wrote, the GOOGLE_APPLICATION_CREDENTIALS
where overriding the credentials, it is working and now I can write and call into specific tables of the database, thanks again for the help.
I will close this issue, with this comment. Thank you :)
Question
How Do I Correctly Connect Django to Google Cloud SQL via Unix Socket with IAM Authentication?
Code
Additional Details
I'm trying to connect my Django application to a PostgreSQL instance hosted on Google Cloud SQL using a Unix socket and IAM authentication. Here’s my configuration in settings.py.
Actually I'm using the suggested repo for this authentication: https://github.com/rcleveng/django_gcp_iam_auth, that @rcleveng post
The error I'm getting is: django.db.utils.OperationalError: connection to server on socket "/cloudsql/company-XXXXXXXXXXXXXXXX:us-central1:pareto-instance-prod-XXXXXX/.s.PGSQL.5432" failed: Invalid argument (0x00002726/10022) Is the server running locally and accepting connections on that socket?