Fine-grained IAM condition for connecting BigQuery agent SA to CloudSQL

[Bmtl]

Hi,

I'm trying to connect BQ to a specific CloudSQL instance.

It works when adding the "roles/cloudsql.client" role to the BQ agent SA.

But I want to scope the permission down to a specific CloudSQL instance, and forbid access to other instances.

I tried to add the IAM condition resource.name == 'projects/zzzz-stg/instances/zzzz-staging' but BQ is throwing a:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query For more information, see https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries#troubleshooting at [1:15]

I tried with the "startswith" function too.

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?

Thank you 🙂  

[ms4446]

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:

1. Go to IAM & Admin in the Google Cloud Console.

2. Select the BigQuery Service Account (typically named something like bq-agent-XXXXXX@gcp-sa-bigquery.iam.gserviceaccount.com).

3. Click “Add Another Role” and choose Cloud SQL Client.

4. Add a Condition:

   • Title: Give it a clear name.
   • Condition Expression: Use this to specify the instance:
     resource.name.startsWith('projects/zzzz-stg/instances/zzzz-staging')
     Or for an exact match:
     resource.name == 'projects/zzzz-stg/instances/zzzz-staging'

5. 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:

1. Define the Perimeter: Include both BigQuery and your Cloud SQL instance.
2. 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'"
      }
    }
  ]
}

[Bmtl]

Thanks for the quick reply.

I have the exact same IAM condition and it is not working, and not logging any "cloudsql.instances.connect" entries in audit logs.

The only thing different from your example is that I don't have a Google provided SA named 

serviceAccount:bq-agent-XXXXXX@gcp-sa-bigquery.iam.gserviceaccount.com

 Instead I'm using the SA:

service-XXXXXXXX@gcp-sa-bigqueryconnection.iam.gserviceaccount.com

[shrushti]

did you get solution for this issue ?
even I am facing the same issue.