Error Connecting to CloudSQL from GKE cluster
SouvikBagchi opened this issue · 8 comments
Question
Unable to connect due to error -
Cluster info -
Name - second-cluster
IP Type - public
Service account - testserviceaccount
Service account has the following enabled -
Artifact Registry Service Agent
Cloud SQL Admin
Cloud SQL Client
Compute Engine Service Agent
Editor
Kubernetes Engine Service Agent
CloudSQL -
Public IP - enabled
I have set the DB_IAM_USER to be testserviceaccount.
Error -
resp = await self._client.get(url, headers=headers, raise_for_status=True)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/aiohttp/client.py", line 696, in _request
resp.raise_for_status()
File "/usr/local/lib/python3.12/site-packages/aiohttp/client_reqrep.py", line 1070, in raise_for_status
raise ClientResponseError(
aiohttp.client_exceptions.ClientResponseError: 403, message="Forbidden: Authenticated IAM principal does not seeem authorized to make API request. Verify 'Cloud SQL Admin API' is enabled within your GCP project and 'Cloud SQL Client' role has been granted to IAM principal.", url=URL('https://sqladmin.googleapis.com/sql/v1beta4/projects/testingdev-420720/instances/modam/connectSettings')
ERROR: Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/starlette/routing.py", line 732, in lifespan
async with self.lifespan_context(app) as maybe_state:
File "/usr/local/lib/python3.12/contextlib.py", line 210, in __aenter__
return await anext(self.gen)
^^^^^^^^^^^^^^^^^^^^^
File "/app/assistantservice/main.py", line 12, in lifespan
init_db()
File "/app/assistantservice/config/databaseconfig.py", line 70, in init_db
SQLModel.metadata.create_all(engine)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 5825, in create_all
bind._run_ddl_visitor(
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3254, in _run_ddl_visitor
with self.begin() as conn:
File "/usr/local/lib/python3.12/contextlib.py", line 137, in __enter__
return next(self.gen)
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3244, in begin
with self.connect() as conn:
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3280, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3304, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
with util.safe_reraise():
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
self.__connect()
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
with util.safe_reraise():
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 362, in <lambda>
return lambda rec: creator_fn()
^^^^^^^^^^^^
File "/app/assistantservice/config/databaseconfig.py", line 45, in getconn
conn: pg8000.dbapi.Connection = connector.connect(
^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/google/cloud/sql/connector/connector.py", line 212, in connect
return connect_task.result()
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 456, in result
return self.__get_result()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 401, in __get_result
raise self._exception
File "/usr/local/lib/python3.12/site-packages/google/cloud/sql/connector/refresh_utils.py", line 66, in _is_valid
metadata = await task
^^^^^^^^^^
aiohttp.client_exceptions.ClientResponseError: 403, message="Forbidden: Authenticated IAM principal does not seeem authorized to make API request. Verify 'Cloud SQL Admin API' is enabled within your GCP project and 'Cloud SQL Client' role has been granted to IAM principal.", url=URL('https://sqladmin.googleapis.com/sql/v1beta4/projects/testingdev-420720/instances/modam/connectSettings')
Code
import pg8000
from os import getenv, environ
from sqlmodel import SQLModel, create_engine, Session
from google.cloud.sql.connector import IPTypes, Connector
DATABASE_URL = None
engine = None
instance_connection_name = environ[
"INSTANCE_CONNECTION_NAME"
]
db_name = environ["DB_NAME"] # e.g. 'my-database'
print("instance connection name: ",instance_connection_name)
ip_type = IPTypes.PRIVATE if environ.get("PRIVATE_IP") else IPTypes.PUBLIC
print("IP Type: ", ip_type)
db_user, db_pass, enable_iam_auth = (
(getenv("DB_IAM_USER"), "", True)
if getenv("DB_IAM_USER")
else (getenv("DB_USER"),getenv("DB_PASS"), False)
)
# initialize Cloud SQL Python Connector object
connector = Connector(enable_iam_auth=enable_iam_auth)
def getconn() -> pg8000.dbapi.Connection:
conn: pg8000.dbapi.Connection = connector.connect(
instance_connection_name,
"pg8000",
user=db_user,
password=db_pass,
db=db_name,
ip_type=ip_type,
)
return conn
Additional Details
What I have tried with the same result -
- Deleting the deployment and setting environment variable to the email address of the principal
- Using user, pass, dn_name instead of iam.
When I Try to launch the app from local it doesn't give any error with db_user, pass and name.
Hi @SouvikBagchi! Thanks for raising an issue on the Cloud SQL Python Connector 😄
The most likely reason for the error you are seeing is to do with workload identity? Do you mind giving us some more details into how you are configuring your GKE credentials? Are you using workload identity?
I have not used workload identity. I will try and get back to you to see if it works.
This is a helpful link if you want to verify your setup: https://cloud.google.com/kubernetes-engine/docs/how-to/workload-identity#verify
I am confused.
Do I add the following information to the yaml? -
apiVersion: v1
kind: Pod
metadata:
name: test-pod
namespace: NAMESPACE
spec:
serviceAccountName: KSA_NAME
containers:
- name: test-pod
image: google/cloud-sdk:slim
command: ["sleep","infinity"]
resources:
requests:
cpu: 500m
memory: 512Mi
ephemeral-storage: 10Mi
Is there a simpler way to do this when creating a cluster ?
This page talks about Workloads enabling where it mentions go to security and click Enable Workload Identity but I don't see that option.
I do seem to have workload identity enabled (see attached picture)
Does it have anything to do with workload identity namespace?
Bear in mind I don't do any additional changes to the yaml when deploying.
Can you help? Screen shots would be great
I am still getting the error -
aiohttp.client_exceptions.ClientResponseError: 403, message="Forbidden: Authenticated IAM principal does not seeem authorized to make API request. Verify 'Cloud SQL Admin API' is enabled within your GCP project and 'Cloud SQL Client' role has been granted to IAM principal.", url=URL('https://sqladmin.googleapis.com/sql/v1beta4/projects/testingdev-420720/instances/modam/connectSettings')
I can also confirm when I try to app from local it works fine as the tables are being created in the db but doesn't allow connection in GKE.
I am used to AWS having worked with them extensively but this is my first time using GCP.
Perhaps I am missing something very obvious. Maybe it might be some granular IAM issue?
@SouvikBagchi The link provided, is a minimal sample to test if workload identity is working properly. https://cloud.google.com/kubernetes-engine/docs/how-to/workload-identity#verify
If you have tested the example and it works, then you have workload identity properly configured. You would then update your existing YAML with the namespace as that is the key part.
If you share your final product YAML here, I can take a look and see if anything stands out as potentially the cause.
Note: Another potential reason for the error you are seeing is if you are using multiple Google Cloud Projects and have a cross-project setup? If you are, you will want to make sure the Cloud SQL Admin API is enabled in both projects. If not, you can ignore this piece.
Where do we create the namespace ?
When creating the cluster from the GKE console there is no mention of namespace. Is the name the namespace?
I have checked the namespace which is default.
This is the following YAML for the service -
apiVersion: v1
kind: Service
metadata:
annotations:
cloud.google.com/neg: '{"ingress":true}'
creationTimestamp: "2024-04-29T21:10:06Z"
finalizers:
- service.kubernetes.io/load-balancer-cleanup
labels:
app: first-app
managedFields:
- apiVersion: v1
fieldsType: FieldsV1
fieldsV1:
f:metadata:
f:labels:
.: {}
f:app: {}
f:spec:
f:allocateLoadBalancerNodePorts: {}
f:externalTrafficPolicy: {}
f:internalTrafficPolicy: {}
f:ports:
.: {}
k:{"port":3000,"protocol":"TCP"}:
.: {}
f:port: {}
f:protocol: {}
f:targetPort: {}
f:selector: {}
f:sessionAffinity: {}
f:type: {}
manager: GoogleCloudConsole
operation: Update
time: "2024-04-29T21:10:06Z"
- apiVersion: v1
fieldsType: FieldsV1
fieldsV1:
f:metadata:
f:finalizers:
.: {}
v:"service.kubernetes.io/load-balancer-cleanup": {}
manager: cloud-controller-manager
operation: Update
subresource: status
time: "2024-04-29T21:10:06Z"
name: first-app-service
namespace: default
resourceVersion: "11672"
uid: ebf496da-9c67-4423-9984-d8d756523f1d
spec:
allocateLoadBalancerNodePorts: true
clusterIP: 34.118.225.18
clusterIPs:
- 34.118.225.18
externalTrafficPolicy: Cluster
internalTrafficPolicy: Cluster
ipFamilies:
- IPv4
ipFamilyPolicy: SingleStack
ports:
- nodePort: 31629
port: 3000
protocol: TCP
targetPort: 8000
selector:
app: first-app
sessionAffinity: None
type: LoadBalancer
status:
loadBalancer: {}
with the same result -
403, message="Forbidden: Authenticated IAM principal does not seeem authorized to make API request. Verify 'Cloud SQL Admin API' is enabled within your GCP project and 'Cloud SQL Client' role has been granted to IAM principal.", url=URL('https://sqladmin.googleapis.com/sql/v1beta4/projects/testingdev-420720/instances/modam/connectSettings')
@SouvikBagchi Just wanted to bring attention to part of my last comment in case you missed it.
Note: Another potential reason for the error you are seeing is if you are using multiple Google Cloud Projects and have a cross-project setup? If you are, you will want to make sure the Cloud SQL Admin API is enabled in both projects.
Are you using a single project or do you have cross-project dependencies?
Hey Thanks for your comment and I appreciate your help. I am closing this ticket at this time.