Connect to cloudSQL db using service account with pymysql or mysql.connector

Question

I have a running CloudSQL instance running in another VPC and a nginx proxy to allow cross-vpc access. I can access the db using a built-in user. But how can I access the DB using a Google Service Account?

import google.auth
import google.auth.transport.requests
import mysql.connector
from mysql.connector import Error
import os

creds, project = google.auth.default()
auth_req = google.auth.transport.requests.Request()
creds.refresh(auth_req)


connection = mysql.connector.connect(host=HOST,
                                     database=DB,
                                     user=SA_USER,
                                     password=creds.token)
if connection.is_connected():
    db_Info = connection.get_server_info()
    print("Connected to MySQL Server version ", db_Info)
    cur = connection.cursor()
    cur.execute("""SELECT now()""")
    query_results = cur.fetchall()
    print(query_results)

When using mysql connnector, I get this error:

DatabaseError: 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

Then I tried using pymysql

import pymysql
import google.auth
import google.auth.transport.requests
import os

creds, project = google.auth.default()
auth_req = google.auth.transport.requests.Request()
creds.refresh(auth_req)


try:
    conn =  pymysql.connect(host=ENDPOINT, user=SA_USER, passwd=creds.token, port=PORT, database=DBNAME)
    cur = conn.cursor()
    cur.execute("""SELECT now()""")
    query_results = cur.fetchall()
    print(query_results)
except Exception as e:
    print("Database connection failed due to {}".format(e))    

Database connection failed due to (1045, "Access denied for user 'xx'@'xxx.xxx.xx.xx' (using password: YES)"

I guess these errors are all related to the token. Anyone to suggest a proper way to get SA token to access CloudSQL DB?

PS: Using cloudsql auth proxy is not a good option for our architecture.

Answer 1

It finally works. I had to enforce SSL connection.

import pymysql
from google.oauth2 import service_account
import google.auth.transport.requests

scopes = ["https://www.googleapis.com/auth/cloud-platform", "https://www.googleapis.com/auth/sqlservice.admin"] 
credentials = service_account.Credentials.from_service_account_file('key.json', scopes=scopes)
auth_req = google.auth.transport.requests.Request()
credentials.refresh(auth_req)
config = {'user': SA_USER,
            'host': ENDPOINT,
            'database': DBNAME,
            'password': credentials.token,
            'ssl_ca': './server-ca.pem',
            'ssl_cert': './client-cert.pem',
            'ssl_key': './client-key.pem'}
try:
    conn =  pymysql.connect(**config)
    with conn:
        print("Connected")
        cur = conn.cursor()
        cur.execute("""SELECT now()""")
        query_results = cur.fetchall()
        print(query_results)
except Exception as e:
    print("Database connection failed due to {}".format(e))