Another funny issue: while parsing GCP audit logs I find multiple “cloudsql.instances.get” methodeName calls per query (all of them granted), but no “cloudsql.instances.connect” methodName call.
Is it possible to scope down BQ access to cloudSQL? Am I missing something?
When you see the error "MysqlErrorCode(2013): Lost connection to MySQL server during query," it’s likely due to IAM permissions. You tried to restrict access with a condition like resource.name == 'projects/zzzz-stg/instances/zzzz-staging'. However, IAM conditions can be complex:
IAM Conditions: They apply to the cloudsql.instances.connect permission. If not set up correctly, they can cause connection problems.
Audit Logs: You might see "cloudsql.instances.get" calls, which check permissions and retrieve instance details. If "cloudsql.instances.connect" isn’t showing up, the condition may not be evaluated correctly.
To limit BigQuery’s access to a specific Cloud SQL instance, follow these steps:
Go to IAM & Admin in the Google Cloud Console.
Select the BigQuery Service Account (typically named something like bq-agent-XXXXXX@gcp-sa-bigquery.iam.gserviceaccount.com).
Click “Add Another Role” and choose Cloud SQL Client.
Add a Condition:
Title: Give it a clear name.
Condition Expression: Use this to specify the instance:
Or for an exact match: resource.name == 'projects/zzzz-stg/instances/zzzz-staging'
Save and Apply the condition.
After setting up the IAM condition, test the connection from BigQuery to the Cloud SQL instance using the external query function. Ensure that the connection string and credentials are correct.
Alternative Approach: Use a Service Perimeter
For more robust access control, consider using a Service Perimeter:
Define the Perimeter: Include both BigQuery and your Cloud SQL instance.
Configure Access: Allow access only from BigQuery to the specific Cloud SQL instance.
Troubleshooting Tips
Instance Network Configuration: Ensure the Cloud SQL instance allows connections from BigQuery IP ranges or is correctly set up with VPC.
IAM Policy Propagation: Changes in IAM may take some time to take effect. Verify that the condition is active and correctly applied.
Audit Logs: Check for any "cloudsql.instances.connect" attempts in the Cloud SQL audit logs. Ensure logging is enabled.
Network and Firewall Rules: Make sure there are no network or firewall rules blocking the connection.
Additional Considerations
Custom Roles: For even finer control, create custom IAM roles with permissions tailored to your needs.
Debugging: If the issue continues, temporarily remove the IAM condition to test the basic connection setup. Review the IAM policy for any conflicts, and contact Google Cloud support if needed.
Example IAM Policy JSON
Here’s a sample IAM policy that restricts access to a specific Cloud SQL instance:
{
"bindings": [
{
"role": "roles/cloudsql.client",
"members": [
"serviceAccount:bq-agent-XXXXXX@gcp-sa-bigquery.iam.gserviceaccount.com"
],
"condition": {
"title": "Restrict to Specific Instance",
"description": "Allow access only to zzzz-staging instance.",
"expression": "resource.name == 'projects/zzzz-stg/instances/zzzz-staging'"
}
}
]
}
