Hello,
I'm relatively new to Google Cloud software, and despite my efforts to thoroughly read through the documentation, I find myself unable to access my MySQL database on Google Cloud. To provide some context, I hope this information will help clarify my issue.
I'm tasked with creating access to a MySQL database on Google Cloud for the employees of a company. I've created an instance with a private IP and configured a service account with all the necessary roles and permissions, which I intend to use to grant access to the database to the workers. Additionally, I've set up a VPC network and connected the Cloud SQL Instance to this VPC, as well as authorizing my local IP to connect. As of now, I haven't configured a Google Cloud VPN or a tunnel with my local network. My initial question is: Do I need to have a VPN IPsec for my local network in order to allow communication between my Cloud SQL instance and my local network?
At present, I can't configured a VPN IPsec in my local network, and assuming it was necessary, I temporarily switched my Cloud SQL instance to public, just long enough to establish a successful connection. My hope was that this would simplify the process for connecting to my database. I've set up some firewall rules to only allow communication from my local IP to my instance. I attempted to connect to my database using the following Python script (adapted from documentation):
'''
import os
import sqlalchemy
import pymysql
from google.auth import load_credentials_from_file
from google.cloud.sql.connector import Connector, IPTypes
from dotenv import load_dotenv
def connect_with_connector() -> sqlalchemy.engine.base.Engine:
"""
Initializes a connection pool for a Cloud SQL instance of MySQL.
Uses the Cloud SQL Python Connector package.
"""
# Note: Saving credentials in environment variables is convenient, but not
# secure - consider a more secure solution such as
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
# keep secrets safe.
load_dotenv()
instance_connection_name = os.getenv("INSTANCE_CONNECTION_NAME")
db_user = os.getenv("DB_USER")
db_pass = os.getenv("DB_PASS")
db_name = os.getenv("DB_NAME")
credential_path = os.getenv("CREDENTIAL_PATH")
credential, _ = load_credentials_from_file(credential_path)
# Chemins des fichiers SSL
ssl_ca = os.getenv("SSL_CA") # Chemin vers le fichier server-ca.pem
ssl_cert = os.getenv("SSL_CERT") # Chemin vers le fichier client-cert.pem
ssl_key = os.getenv("SSL_KEY") # Chemin vers le fichier client-key.pem
ssl_args = {
'ssl': {
'ca': ssl_ca,
'cert': ssl_cert,
'key': ssl_key
}
}
# ip_type = IPTypes.PRIVATE if private_ip else IPTypes.PUBLIC
ip_type = IPTypes.PUBLIC
connector = Connector(ip_type)
def getconn() -> pymysql.connections.Connection:
conn: pymysql.connections.Connection = connector.connect(
instance_connection_name,
"pymysql",
user=db_user,
db=db_name,
**ssl_args
)
return conn
pool = sqlalchemy.create_engine(
"mysql+pymysql://",
creator=getconn,
pool_size=5, # Taille de la pool de connexions
max_overflow=2, # Nombre maximal de connexions supplémentaires autorisées
pool_timeout=30, # Délai d'attente maximum pour obtenir une connexion
pool_recycle=1800, # Durée de vie maximale d'une connexion en secondes
)
return pool
As I intend to connect with a service account (as it aligns with the final use case), I executed the following command to set up Application Default Credentials (ADC):
gcloud auth application-default login --impersonate-service-account SERVICE_ACCT_EMAIL
gcloud config set auth/impersonate_service_account gcloud auth application-default login --impersonate-service-account SERVICE_ACCT_EMAIL
This provided me with a credential file, which I then set the path to in the environment variable GOOGLE_APPLICATION_CREDENTIALS. I've managed to grant all the necessary permissions to the service account. Below is the list of permissions:
- cloudaicompanion.entitlements.get
- cloudsql.backupRuns.create
- cloudsql.backupRuns.get
- cloudsql.backupRuns.list
- cloudsql.databases.create
- cloudsql.databases.get
- cloudsql.databases.list
- cloudsql.databases.update
- cloudsql.instances.addServerCa
- cloudsql.instances.connect
- cloudsql.instances.export
- cloudsql.instances.failover
- cloudsql.instances.get
- cloudsql.instances.list
- compute.networks.get
- iam.serviceAccounts.actAs
- iam.serviceAccounts.get
- iam.serviceAccounts.getAccessToken
- iam.serviceAccounts.getOpenIdToken
- iam.serviceAccounts.implicitDelegation
- iam.serviceAccounts.list
- iam.serviceAccounts.signBlob
- iam.serviceAccounts.signJwt
- resourcemanager.projects.get
I acknowledge that these permissions may be excessive for my needs, but I'll address that once I'm able to connect to my database. Additionally, as mentioned earlier, I've added firewall rules to:
- Allow incoming traffic from my local IP to my Cloud SQL instance and VPC IP on protocol and port 3306.
- Allow outgoing traffic from my VPC and Cloud SQL instance IP to my local IP, explicitly mentioning that IP in the 'Destination' field.
The fact is, as I'm learning these concepts while implementing them, and feeling a bit desperate, I also attempted to connect using Google Cloud Proxy and other methods, which may have affected my configuration.
To conclude, I'm encountering the following error message:
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1045, "Access denied for user 'serviceaccountusername’ @'cloudsqlproxy~’localIP' (using password: NO)")
(Background on this error at: URL Removed by Staff)
I'm not using a password because I'm attempting to connect with a service account, and I have the credential file. I've triple-checked information such as db_user, db_name, instance_connection_name, as well as ssl_certificate (I read after attempting that it may be unnecessary when connecting using the Python Connector).
I'm struggling to identify the origin of the error and, consequently, the solution. Is there something essential that I might have overlooked? Or perhaps something I'm doing incorrectly?
Thank you for your assistance.
LinkedIn
Twitter
