17 posts tagged with "gcp"

If you've audited buckets in more than one cloud, you already know what this is like. Three consoles. Three CLIs. Three auth patterns. And a bit of glue code to pull the outputs together every time someone asks a question that spans all three.

The question is usually simple. "Which buckets are public across all our clouds?" You end up running three separate scripts, then reconciling three different output shapes just to give one answer.

This tutorial doesn't make that faster. It replaces it. One query, one view, all three clouds.

What StackQL is, in three sentences​

StackQL is SQL for cloud APIs. You write a normal SQL query, and it makes the API calls to AWS, GCP, Azure, or dozens of other providers to get the answer back as rows and columns.

The important part for this tutorial: it queries the live provider APIs on every run. There's no local database, no cache, no sync job to schedule. What you see is the current state of your cloud, right now.

It exists because someone got tired of writing the same audit logic three times.

What you'll need​

You'll need Docker installed and read-only credentials for whichever clouds you want to audit. All three are optional. If you leave a cloud's credentials blank, it gets skipped.

For AWS, GCP, and Azure, the audit needs a service account or role that can list storage buckets and read their configuration. The audit-action repo has the exact permissions per provider here.

Run the audit​

The tutorial folder has three files: a docker-compose file, an .env.audit.example template, and a README. Grab the folder from docs/tutorials/preview-bucket-01/ in the stackql/stackql repo.

1. Copy the env template

cp ./audit/.env.audit.example ./audit/.env.audit

Then open .env.audit and fill in credentials for whichever clouds you want. The variables are named for what they are: AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION, AZURE_TENANT_ID, AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, GOOGLE_CREDENTIALS (the service account JSON, on one line), and GOOGLE_ORG_ID.

If you're only auditing one or two clouds, leave the others blank.

2. Pull the StackQL Docker image

docker compose -f docker-compose.bucket.audit.yaml pull

The tutorial pins to stackql/stackql:v0.10.601. The audit uses views under stackql_preview.* which are still evolving, so pinning matters for reproducibility.

3. Run the audit

docker compose -f docker-compose.bucket.audit.yaml run --rm stackql

On a modest account this takes about 30 seconds. Output goes straight to your terminal as a table.

What the output tells you​

When the audit finishes, you get a single table printed to your terminal. Something like this (bucket names are placeholders):

Example output from the audit. Bucket configuration across three cloud providers in one normalised table.

The point isn't the specific findings. It's the shape.

Three different resource types (S3 buckets, GCS buckets, and Azure Storage Accounts) normalised into one table with the same columns. Encryption class, public flag, HTTPS enforcement. Side by side, for every cloud you provided credentials for.

A few patterns to notice in the table:

• Public buckets across providers. Filter by public = true and you have the answer to your security team's question, in every cloud, in one place.
• Encryption side by side. The encryption_class column shows which buckets use provider-managed keys versus customer-managed. Different providers have different naming conventions internally; StackQL normalises them to the same two categories.
• HTTPS enforcement in one column. Whether it's S3 bucket policies, GCS uniform access, or Azure secure transfer, same column, same values, no translation needed.

This is what SQL for cloud APIs actually delivers. Not just SQL as a query language, but a uniform data model across providers, so you can ask one question and get one answer.

Performance and scope​

StackQL runs each provider's query in parallel using the credentials you provided. There's no local database being populated and no background sync; every run hits the live APIs and returns what's there right now.

On the test account this took about 30 seconds. The query is scoped to one AWS region and one GCP organization, which keeps the API footprint bounded and reflects the current preview default. Streaming output and broader audits (across regions, deeper across resource types) are in flight from the StackQL team.

What this covers today​

Today, this audit covers storage buckets across AWS, GCP, and Azure. One region at a time for AWS, one organization at a time for GCP. Entitlements, IAM, and other resource types are on the roadmap as separate audits under stackql_preview.* and will get their own tutorials as they land.

Where to go next​

github.com/stackql/stackql is the flagship repo. If this tutorial was worth reading, give it a star. It's genuinely the thing that tells the team to build more tutorials like this one, and it helps other engineers with the same three-console problem find the project.

A few natural next steps from here:

• Run it against your own accounts. The audit works exactly the same way against real credentials. You can start with one cloud and add the others by adding their env vars.
• Move it into CI. The stackql-audit-action repo has ready-to-use GitHub Actions workflows for all-clouds, single-cloud, deep-audit, and OIDC-authenticated variants. Drop one into your repo, add secrets, and you have a scheduled cross-cloud audit running on every push.
• Ask questions. The StackQL community Slack is the fastest way. GitHub issues on the flagship repo also work.

More audits are on the way, including entitlements, deeper checks, and larger estates. If there's a specific one you'd find useful, opening an issue is the fastest way to get it on the roadmap.

We've released an update to the StackQL Google provider, regenerated from the latest Google API discovery documents. The google provider now covers 187 services, 2,183 resources and over 9,100 operations - up from 179 services, 1,966 resources and 8,423 operations in the previous release. The companion providers in the google family (googleworkspace, googleadmin and firebase) were regenerated in the same pass.

New Services​

Eleven services are new in this release:

Expanded Coverage in Existing Services​

• compute - the largest expansion in this release, 73 new resources: capacity planning (future_reservations, reservation_slots, reservation_blocks and reservation_sub_blocks versions), instant_snapshot_groups and regional snapshot resources, cross_site_networks and wire_groups, organization_security_policies, network_firewall_policies, rollouts and rollout_plans, plus per-resource IAM policy resources across addresses, firewalls, health checks, routes, target proxies and more
• aiplatform (Vertex AI) - agent engine build-out: agents, memory_banks, sandbox_environments (with templates and snapshots), online_evaluators, evaluation_metrics, responses and semantic governance policy resources
• oracledatabase - GoldenGate integration: deployments, deployment environments/types/versions, connections and connection assignments, plus autonomous database refreshable clones
• dataplex - universal catalog governance: data_domains, data_products, data_assets, metadata_feeds and change_requests
• redis - token-based auth: token_auth_users, auth_tokens and acl_policies
• networksecurity - dns_threat_detectors and Secure Access Connect realms and attachments
• cloudkms - single_tenant_hsm_instances, key deletion proposals and retired_resources
• observability - log analytics buckets, datasets, links, views and scope settings

Another 50+ services picked up incremental resources and operations, including discoveryengine, contactcenterinsights, netapp, artifactregistry, assuredworkloads and dataproc.

Removed Services​

Google has retired several APIs since the last release, and they are removed from the provider accordingly:

• datalabeling - AI Platform Data Labeling was shut down
• integrations - Application Integration no longer publishes a discovery document
• lifesciences - Cloud Life Sciences was shut down
• dataplex Explore resources (content, environments, sessions) were removed upstream

The duplicate *_aggregated helper resources in compute were also consolidated - cross-zone queries are served by the primary resources, so a zone-less SELECT fans out across all zones:

SELECT name, status, machineType, zone
FROM google.compute.instances
WHERE project = 'my-project';

Example: Open Firewall Audit​

Ingress rules open to the entire internet, and what they allow:

SELECT
  name,
  direction,
  sourceRanges,
  allowed
FROM google.compute.firewalls
WHERE project = 'my-project'
AND sourceRanges LIKE '%0.0.0.0/0%';

Get Started​

Pull the latest provider from the public registry:

stackql registry pull google;

Authenticate with a service account key in the GOOGLE_CREDENTIALS environment variable (or interactively via gcloud auth login), then explore:

SELECT
  json_extract(config, '$.name') AS api,
  state
FROM google.serviceusage.services
WHERE parent = 'my-project'
AND parentType = 'projects'
AND filter = 'state:ENABLED';

Provider docs, including getting-started queries for each provider in the family, are at google-provider.stackql.io, googleworkspace-provider.stackql.io, googleadmin-provider.stackql.io and firebase-provider.stackql.io. Visit us on GitHub and let us know how you're using it.

We've released a major update to the StackQL Google provider with a new service, enhanced AI/ML capabilities, and improvements across 177 service files.

New Service: Speech-to-Text v2​

The speechv2 service brings Cloud Speech-to-Text API v2 to StackQL with 6 resources:

Key features include support for multiple audio encodings (WAV, FLAC, MP3, OGG, WebM, MP4/AAC), translation capabilities, denoiser config, and KMS encryption support.

Vertex AI / AI Platform​

The largest update in this release with 87,000+ line changes introduces powerful new RAG and evaluation capabilities:

• RAG Resources: rag_corpora, rag_files, rag_engine_config for Retrieval-Augmented Generation
• Conversational AI: New chat resource
• Model Evaluation: evaluation_sets and evaluation_items for systematic model assessment
• New Resources: science, invoke, and openapi resources
• Performance: Enhanced cache_config for caching configurations

Discovery Engine​

Major enhancements (50,000+ line changes) for search and conversational AI:

• New assistants resource
• New sitemaps resource for site search
• New custom_models resource
• Enhanced sessions and answers for conversational search
• New authorized_views and authorized_view_sets for access control

Contact Center AI Insights​

Quality assurance and analytics improvements (20,000+ line changes):

• New qa_questions and qa_question_tags for quality assurance workflows
• New analysis_rules resource
• New segments resource
• New authorized_views with IAM policy support
• New datasets and views resources

BigQuery​

Enhanced governance and access control (18,000+ line changes):

• New routines_iam_policies for stored procedure/function IAM
• Enhanced row_access_policies

Healthcare API​

Expanded metrics and data mapping (15,000+ line changes):

• New data_mapper_workspaces_iam_policies
• Enhanced metrics: hl7_v2_store_metrics, dicom_store_metrics, series_metrics, study_metrics
• New instances_storage_info resource

Cloud Spanner​

Backup and security enhancements (14,000+ line changes):

• New backup_schedules with IAM support
• New databases_split_points resource
• New database_roles with IAM policies

Cloud SQL Admin​

New integration and management features (12,000+ line changes):

• New instances_entra_id_certificate for Microsoft Entra ID integration
• New instances_disk_shrink_config
• New instances_latest_recovery_time

GKE On-Prem​

Enhanced IAM across VMware and Bare Metal clusters (9,000+ line changes):

• Enhanced VMware cluster resources with IAM policies
• Enhanced Bare Metal cluster resources with IAM policies
• New vmware_node_pools and bare_metal_node_pools with IAM

Developer Connect​

Git integration improvements (3,500+ line changes):

• New git_repository_links_git_refs resource
• New users_self and users_access_token resources
• New token resources: read_token, read_write_token

Text-to-Speech​

Enhanced voices and text resources with new capabilities.

Get Started​

Update to the latest Google provider:

stackql registry pull google

Let us know your thoughts! Visit us and give us a star on GitHub.

The latest google provider for stackql is available now, and includes a new oracledatabase service, including resources for cloud_vm_clusters, db_nodes, db_servers, cloud_exadata_infrastructures, entitlements, and more.

Summary stats for the new google provider:

Let us know what you think! ⭐ us on GitHub.

Analyzing firewall rules is crucial for maintaining security in your cloud infrastructure. Using StackQL, you can efficiently query and analyze Google Cloud firewall configurations to ensure that your security policies are correctly implemented and that there are no unexpected open ports or protocols that might pose a security risk. Below is a simple query that retrieves important details about the ingress firewall rules for a specific network in a Google Cloud project.

SELECT 
	name, 
	source_range,
	ip_protocol,
	allowed_ports,
	direction
FROM (
	SELECT 
	name,
	source_ranges.value as source_range,  
	JSON_EXTRACT(allowed.value, '$.IPProtocol') as ip_protocol,
	JSON_EXTRACT(allowed.value, '$.ports') as allowed_ports,
	direction
	FROM google.compute.firewalls, json_each(sourceRanges) as source_ranges, json_each(allowed) as allowed
	WHERE project = 'stackql-k8s-the-hard-way-demo' 
	AND network = 'https://www.googleapis.com/compute/v1/projects/stackql-k8s-the-hard-way-demo/global/networks/kubernetes-the-hard-way-dev-vpc'
) t 
WHERE 
source_range = '0.0.0.0/0' 
and direction = 'INGRESS';

This query provides a comprehensive list of all ingress firewall rules that apply to any IP address (0.0.0.0/0) within the specified Google Cloud project and network. The results include the firewall rule name, the source IP range, the protocol, the allowed ports, and the direction of the traffic, an example is shown below:

|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
|                     name                      | source_range | ip_protocol | allowed_ports | direction |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-icmp                            | 0.0.0.0/0    | icmp        | null          | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-rdp                             | 0.0.0.0/0    | tcp         | ["3389"]      | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-ssh                             | 0.0.0.0/0    | tcp         | ["22"]        | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | tcp         | ["22"]        | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | tcp         | ["6443"]      | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | icmp        | null          | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------| 

You can use this query to help quickly identify potential security vulnerabilities. Regularly auditing these rules ensures that your cloud environment remains secure and that only the necessary ports and protocols are open to the internet.

Give us your feedback! ⭐ us here!