Creating a Log Sink to Route Cloud SchedulerJob Logs to BigQuery

Google Cloud Data Analytics
1

Hello,

I am working on setting up a log sink using Terraform to route logs from cloud scheduler jobs to a BQ table. After applying my Terraform configuration, I successfully created the log sink in my project. However, I’m encountering an issue when trying to modify the sink details. The error message indicates that I lack the required permissions to make these changes.

Currently, my service account has the following permissions:

  • ‘roles/logging.configWriter’
  • ‘roles/bigquery.dataEditor’

Are there additional permissions or roles that my service account needs to manage and modify the log sink? Any guidance on this would be greatly appreciated

2

To manage and modify log sinks in Google Cloud BigQuery effectively, having the appropriate permissions is crucial. Currently, your service account is equipped with the roles logging.configWriter and bigquery.dataEditor. While these roles allow the creation of log sinks and the writing of data to BigQuery tables, they do not suffice for modifying log sinks.

The roles/logging.admin role is essential for full control over logging resources, including creating, modifying, and deleting log sinks. The logging.configWriter role, which your service account currently has, is sufficient for creating sinks but not for modifying them. Therefore, to manage log sinks properly, your service account must be granted the logging.admin role. Additionally, the roles/bigquery.dataEditor role you already possess is necessary for the log sink to write data to your BigQuery tables.

To ensure your Terraform configuration correctly reflects these requirements, it is essential to explicitly include the updated service account with the logging.admin role. Below is an example of how you should configure your Terraform setup:

provider "google" {
  project     = "your-project-id"
  region      = "your-region"
  credentials = file("path/to/your/service_account_key.json")
}

resource "google_logging_project_sink" "my_sink" {
  # ... rest of your sink configuration
}

While configuring your setup, several troubleshooting tips can help address common issues. First, double-check that you are using the correct service account key file in your Terraform configuration. This step ensures that the service account with the required permissions is being used. Additionally, if you are operating within an organization, it may be necessary to grant the logging.admin role at the organization level. This ensures the service account has the required permissions across all projects within the organization.

It is also important to note that IAM role changes can take a few minutes to propagate. Therefore, after making changes to the IAM roles, allow some time before attempting to modify the sink again. If issues persist despite following these steps, you might consider using the gcloud CLI as an alternative approach. The following command can help update the sink directly:

gcloud logging sinks update my_sink --add-iam-policy-binding \
  "serviceAccount:your-service-account@your-project-id.iam.gserviceaccount.com=roles/logging.admin"

While the logging.admin role is powerful and can address the immediate issue, it is worth considering whether more granular permissions could achieve your goals. Roles such as logging.privateLogViewer and logging.sinkWriter might offer sufficient capabilities without granting extensive control, thus adhering to the principle of least privilege.

3

Hello @ms4446 ,

Thank you for your response! Following your guidance, I have added the necessary roles to the service account used for the log sink configuration. However, I’m encountering an issue where I cannot see the logs in my BigQuery dataset, despite successful configuration and roles setup.

I followed the troubleshooting steps outlined here but haven’t resolved the issue. One observation is that the writer_identity service account shown in my Sink Details differs from the service account used for the log sink configuration. When I specified the correct service account using Terraform, I encountered an error: "Can’t configure a value for ‘writer_identity’: its value will be decided automatically based on the result of applying this configuration."I think this means that Google Cloud determines the writer_identity based on project permissions and configuration.

After removing that attribute, the error disappeared, but I still can’t see the logs in my BigQuery dataset, although they are visible in the log explorer for the scheduled jobs. Any guidance or advice on this issue would be much appreciated! :grinning_face_with_smiling_eyes:

4

To effectively manage and modify log sinks in Google Cloud BigQuery, having the appropriate permissions is crucial. Despite adding necessary roles to your service account, including roles/logging.admin and roles/bigquery.dataOwner, you encounter issues where logs do not appear in the BigQuery dataset. This issue persists even though the log sink configuration and roles setup are seemingly correct.

A common observation in such cases is the difference in the writer_identity service account shown in the sink details compared to the service account used in the log sink configuration. The writer_identity is automatically managed by Google Cloud and should not be explicitly specified in the Terraform configuration. Instead, the focus should be on ensuring this writer_identity has the correct permissions to write to the BigQuery dataset.

First, verify the sink details to ensure that the destination and filter are correctly configured. This can be done in the Google Cloud Console under Logging > Logs Router. Next, ensure that the writer_identity has the necessary roles/bigquery.dataEditor role on the dataset. This can be accomplished with the following gcloud command:

gcloud projects add-iam-policy-binding your-project-id \
    --member="serviceAccount:service-<sink-writer-identity>@logging-<project-id>.iam.gserviceaccount.com" \
    --role="roles/bigquery.dataEditor"

Double-check the BigQuery dataset permissions to confirm that the writer_identity has the correct access. You can use the bq show command to verify this:

bq show --format=prettyjson your-project-id:your-dataset-id

Ensure the Terraform configuration does not explicitly specify writer_identity:

resource "google_logging_project_sink" "my_sink" {
  name        = "my-sink"
  destination = "bigquery.googleapis.com/projects/your-project-id/datasets/your-dataset"
  filter      = "logName:\"projects/your-project-id/logs/cloudaudit.googleapis.com%2Factivity\""

  bigquery_options {
    use_partitioned_tables = true
  }
}

Test the logs filter to ensure it accurately captures logs from Cloud Scheduler jobs. Use the Logs Explorer to verify the filter matches the expected logs:

resource.type="cloud_scheduler_job" logName="projects/your-project-id/logs/cloudaudit.googleapis.com%2Factivity"

Generate a test log entry from a Cloud Scheduler job to ensure it appears in the Logs Explorer and check the status of your sink in Logging > Logs Router. Additionally, view metrics related to your log sink in Cloud Monitoring to identify any issues with log ingestion or routing.

If issues persist, consider recreating the sink by deleting the existing one and recreating it using Terraform:

terraform destroy -target=google_logging_project_sink.my_sink
terraform apply

To verify if logs are being ingested in BigQuery, run a simple SQL query:

SELECT *
FROM `your-project-id.your-dataset.your-table`
ORDER BY timestamp DESC
LIMIT 100;

This query should return recent log entries if the sink is functioning correctly.