Overcoming Cloud Logging’s 100 KB limit and GCP’s 90-day IAM recommender ceiling using serverless pipelines.
1. Introduction: The Two Silent Blind Spots in Enterprise BigQuery
Google BigQuery powers analytics across thousands of enterprises. But as cloud data platforms scale to hundreds of projects and petabytes of data, enterprise security architects, data engineers, and compliance teams routinely collide with two systemic platform limitations:
- The 100 KB Payload Truncation Trap: Google Cloud Logging caps audit log entries at ~100 KB. When a BigQuery query exceeds this threshold, Cloud Logging flags queryTruncated = true and slices the query text. In enterprise environments running complex ETL, deep analytical joins, CTEs, or BI dashboards, the most critical and computationally expensive queries are the exact ones that get truncated. This creates an immediate compliance gap for SOC2, HIPAA, and internal security auditing.
- The 90-Day IAM Recommender Ceiling: Dormant, over-privileged permissions represent one of the most common vectors for data exfiltration. While Google Cloud’s native IAM Recommender highlights inactive roles, it enforces a strict 90-day observation window. In enterprise business cycles (e.g., quarterly financial reconciliations, annual tax reporting, peak holiday seasons), 90 days is too aggressive. Security teams require flexible 6-month, 12-month, or 15-month lookback windows to prevent breaking vital business processes.
In this deep dive, we present an end-to-end, serverless blueprint implemented on Google Cloud that solves both challenges. The solution guarantees 100% audit log completeness with zero compute overhead for standard queries and introduces an automated, fail-safe dataset access revocation engine with an instant single-table rollback state machine.
2. High-Level Architectural Blueprint
The architecture is divided into two distinct, decoupled serverless pipelines:

3. Workstream 1: Real-Time Truncated Log Reassembly
Why Not Just Query INFORMATION_SCHEMA?
When logs are truncated, engineers often schedule batch queries against INFORMATION_SCHEMA.JOBS_BY_*. However, at enterprise scale, this approach breaks down:
- Latency: INFORMATION_SCHEMA metadata ingestion can lag by several minutes, making real-time security alerting impossible.
- Slot Contention: Repetitive metadata queries consume reserved analytical slots and cost money.
- Quota Exhaustion: Frequent polling across dozens of source projects rapidly exhausts BigQuery metadata concurrency quotas.
The Solution: The Bifurcated Dual-Sink Pattern
Instead of batch polling, we route logs through an event-driven dual sink:
- The Direct Path: Over 95% of queries are well under 100 KB. A Cloud Logging exclusion filter allows these normal logs to stream directly into the destination BigQuery audit table at zero compute cost.
- The Enrichment Path: Only entries flagged with queryTruncated = true are filtered into a dedicated Pub/Sub topic.
- Targeted REST API Reassembly: The Pub/Sub event triggers an ephemeral Cloud Function. The worker calls the lightweight Google Cloud BigQuery REST API bigquery.jobs().get(), extracts the full un-truncated SQL text directly from the API control plane, updates the payload, and streams it into the destination table.
Step 1: Configure Cloud Logging Sinks
A. Direct Sink (Excludes Truncated Queries)
- Destination: bigquery.googleapis.com/projects/YOUR_SECURITY_HUB_PROJECT/datasets/security_audit_hub
- Inclusion Filter:
resource.type="bigquery_project"
protoPayload.serviceName="bigquery.googleapis.com"
protoPayload.methodName="jobservice.jobcompleted"
- Exclusion Filter:
protoPayload.metadata.jobChange.after.jobConfig.queryConfig.queryTruncated = true
OR protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.queryTruncated = true
B. Dedicated Enrichment Sink (Captures Truncated Queries Only)
- Destination: pubsub.googleapis.com/projects/YOUR_SECURITY_HUB_PROJECT/topics/top-truncated-logs
- Inclusion Filter:
resource.type="bigquery_project"
protoPayload.serviceName="bigquery.googleapis.com"
protoPayload.methodName="jobservice.jobcompleted"
AND (
protoPayload.metadata.jobChange.after.jobConfig.queryConfig.queryTruncated = true
OR protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.queryTruncated = true
)
Step 2: The Log Untruncator Cloud Function
Step 2: The Log Untruncator Cloud Function
Deploy this event-driven Cloud Function (Python 3.11, Gen 2). It reads the Pub/Sub payload, makes a targeted API call to fetch the query text, and uses the native insertId as the deduplication key to guarantee idempotent delivery.
main.py
python
import base64
import json
import logging
import os
from google.cloud import bigquery
from google.api_core.exceptions import GoogleAPIError
# Initialize BigQuery client
bq_client = bigquery.Client()
DESTINATION_TABLE = os.getenv(
"DESTINATION_TABLE",
"YOUR_SECURITY_HUB_PROJECT.security_audit_hub.cloudaudit_googleapis_com_data_access"
)
def process_truncated_log(event, context):
"""Event-driven worker triggered by Pub/Sub to reassemble truncated BigQuery audit logs."""
try:
# 1. Parse Pub/Sub envelope
pubsub_message = base64.b64decode(event['data']).decode('utf-8')
log_payload = json.loads(pubsub_message)
# 2. Extract execution coordinates
proto = log_payload.get("protoPayload", {})
job_completed = (
proto.get("serviceData", {})
.get("jobCompletedEvent", {})
.get("job", {})
)
job_name = job_completed.get("jobName", {})
job_id = job_name.get("jobId")
project_id = (
job_name.get("projectId")
or log_payload.get("resource", {}).get("labels", {}).get("project_id")
)
location = job_name.get("location", "US")
insert_id = log_payload.get("insertId")
if not job_id or not project_id:
logging.warning("Skipping payload: Missing job coordinate metadata.")
return
# 3. Retrieve complete query string via API control plane
logging.info(f"Fetching un-truncated query text for Job: {job_id} in {project_id}")
job = bq_client.get_job(job_id=job_id, project=project_id, location=location)
full_sql_query = job.query
# 4. Enrich payload preserving standard Google Cloud Logging schema
# Enrich Audit Log v1 format (servicedata_v1_bigquery)
if "serviceData" in proto and "jobCompletedEvent" in proto["serviceData"]:
proto["serviceData"]["jobCompletedEvent"]["job"]["jobConfiguration"]["query"]["query"] = full_sql_query
# Enrich Audit Log v2 format (metadataJson)
if "metadataJson" in proto and proto["metadataJson"]:
try:
metadata = json.loads(proto["metadataJson"])
if "jobChange" in metadata and "after" in metadata["jobChange"]:
query_config = metadata["jobChange"]["after"].get("jobConfig", {}).get("queryConfig", {})
query_config["query"] = full_sql_query
query_config["queryTruncated"] = False
proto["metadataJson"] = json.dumps(metadata)
except Exception as parse_err:
logging.warning(f"Failed to parse metadataJson: {parse_err}")
# 5. Idempotent streaming insert using insertId as deduplication key
errors = bq_client.insert_rows_json(
DESTINATION_TABLE,
[log_payload],
row_ids=[insert_id]
)
if errors:
logging.error(f"BigQuery streaming insert failed: {errors}")
raise RuntimeError(f"Insert errors: {errors}")
logging.info(f"Successfully enriched and restored audit log for job: {job_id}")
except Exception as err:
logging.error(f"Error executing log reassembly: {err}")
raise err
requirements.txt
google-cloud-bigquery>=3.14.0
google-api-core>=2.15.0
With 100% of BigQuery audit records preserved in the centralized audit lake, we now have an authoritative foundation to enforce Least-Privilege Access Management.
Enterprise Guardrails & Scope Boundaries
To deploy automated IAM revocation safely in enterprise production environments, strict guardrails must be established:
- Scope Boundary (Dataset-Level ACLs Only): Remediation is strictly isolated to direct dataset-level permissions (userByEmail). Group memberships (groupByEmail) and project-level IAM roles are explicitly out of scope, avoiding unintended consequences for non-BigQuery workflows.
- Service Account Exemption: Identities matching *.gserviceaccount.com or tagged in enterprise CI/CD registries are automatically bypassed to protect automated batch workloads.
- Flexible Lookback Windows (30 Days to 15 Months): Instead of GCP’s rigid 90-day ceiling, we run custom SQL queries against the audit lake, mapping projects to custom inactivity thresholds.
- Single-Table Rollback Ledger: Prior to revoking any permission, a full JSON snapshot of the dataset ACL array and the cryptographic ETag is committed to an audit ledger (iam_governance_actions). Administrators can execute a 1-click rollback for up to 60 days.
Step 3: Inactivity Detection Query
This query joins current dataset permissions against historical query activity, isolating users who have direct access but zero queries over the configured window:
SQL
WITH current_dataset_acls AS (
-- Extract direct user access bindings from dataset metadata
SELECT
project_id,
dataset_id,
user_email,
role_name
FROM `YOUR_SECURITY_HUB_PROJECT.security_metadata.dataset_permissions_inventory`
WHERE
NOT ENDS_WITH(user_email, '.gserviceaccount.com') -- Bypass Service Accounts
AND user_type = "userByEmail" -- Direct User Bindings Only
),
historical_active_users AS (
-- Identify users who executed queries in the configurable lookback window
SELECT DISTINCT
protopayload_auditlog.authenticationInfo.principalEmail AS active_email,
resource.labels.project_id AS project_id
FROM `YOUR_SECURITY_HUB_PROJECT.security_audit_hub.cloudaudit_googleapis_com_data_access`
WHERE
timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 MONTH) -- Configurable Lookback
AND protopayload_auditlog.serviceName = "bigquery.googleapis.com"
AND protopayload_auditlog.methodName = "jobservice.jobcompleted"
)
SELECT
d.project_id,
d.dataset_id,
d.user_email,
d.role_name,
CURRENT_TIMESTAMP() AS evaluation_timestamp
FROM current_dataset_acls d
LEFT JOIN historical_active_users a
ON d.user_email = a.active_email
AND d.project_id = a.project_id
WHERE a.active_email IS NULL; -- Zero activity confirmed
Step 4: The Safe Revocation & Rollback Engine
The remediation logic is packaged into a container on Cloud Run, scheduled weekly by Cloud Scheduler.
import uuid
import logging
from google.cloud import bigquery
from google.cloud.exceptions import NotFound
bq_client = bigquery.Client()
STATE_LEDGER_TABLE = "YOUR_SECURITY_HUB_PROJECT.security_audit_hub.iam_governance_actions"
def remediate_inactive_binding(project_id, dataset_id, target_user, dry_run=False):
"""Snapshots dataset ACL, revokes inactive direct user entry, and logs event."""
action_id = str(uuid.uuid4())
dataset_ref = bigquery.DatasetReference(project_id, dataset_id)
try:
dataset = bq_client.get_dataset(dataset_ref)
original_entries = list(dataset.access_entries)
original_etag = dataset.etag
# 1. Capture snapshot of pre-action state
snapshot_json = [entry.to_api_repr() for entry in original_entries]
# 2. Filter out target inactive user binding
updated_entries = [
entry for entry in original_entries
if not (entry.entity_type == "userByEmail" and entry.entity_id == target_user)
]
if len(updated_entries) == len(original_entries):
logging.info(f"User {target_user} not found in direct ACL for {dataset_id}.")
return None
# 3. Record snapshot to rollback ledger
state_record = {
"action_id": action_id,
"principal_email": target_user,
"project_id": project_id,
"dataset_id": dataset_id,
"action_type": "REVOKE",
"status": "STAGED" if dry_run else "EXECUTED",
"policy_etag": original_etag,
"pre_action_acl_json": str(snapshot_json),
"execution_timestamp": "AUTO"
}
bq_client.insert_rows_json(STATE_LEDGER_TABLE, [state_record])
# 4. Apply atomic update via API if not in dry-run mode
if not dry_run:
dataset.access_entries = updated_entries
bq_client.update_dataset(dataset, ["access_entries"])
logging.info(f"Successfully revoked access for {target_user} on {dataset_id}. Action ID: {action_id}")
# 5. Trigger post-action notification (e.g., SendGrid / Pub/Sub)
dispatch_user_notification(target_user, dataset_id, action_id)
return action_id
except Exception as err:
logging.error(f"Remediation failed for {target_user} on {dataset_id}: {err}")
raise err
def rollback_revocation(action_id):
"""Restores pre-remediation dataset ACL snapshot using the recorded state ledger."""
# Queries STATE_LEDGER_TABLE for action_id, parses pre_action_acl_json,
# verifies ETag, and restores original dataset.access_entries via BQ API.
pass
5. Architectural Trade-offs & Production Metrics

6. Key Takeaways for Cloud Architects
- Decouple Ingestion Early: Never process all logs through expensive compute pipelines. Use Cloud Logging’s native exclusion filters to route standard traffic directly, reserving compute triggers strictly for logs that require enrichment.
- Prioritize Control Plane APIs over Metadata Scanning: When enriching data warehouse telemetry, querying control plane APIs (like jobs.get()) is dramatically faster, cheaper, and less disruptive than running analytics over INFORMATION_SCHEMA.
- Safety First in Zero-Trust Automation: Automated IAM governance cannot succeed without strict guardrails. By isolating remediation to direct dataset bindings, exempting service accounts, and persisting pre-action snapshots in an atomic state ledger, security teams can enforce least-privilege policies without risking production outages.
Solving BigQuery’s Silent Security Gaps: Real-Time Log Recovery & Fail-Safe Governance was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/solving-bigquerys-silent-security-gaps-real-time-log-recovery-fail-safe-governance-d837a9256eba?source=rss—-e52cf94d98af—4
