Table: gcp_storage_bucket - Query Google Cloud Storage Buckets using SQL

Google Cloud Storage is a service within Google Cloud Platform that provides scalable, durable, and highly available object storage. It offers multiple storage classes, versioning, fine-grained access controls, and other features for managing data. Google Cloud Storage is designed to help organizations of all sizes securely store and retrieve any amount of data at any time.

Table Usage Guide

The gcp_storage_bucket table provides insights into Storage Buckets within Google Cloud Storage. As a Cloud Engineer, explore bucket-specific details through this table, including configurations, access controls, and associated metadata. Utilize it to uncover information about buckets, such as their storage class, location, versioning status, and access control policies.

Examples

List of buckets where versioning is not enabled

Discover the segments that have not enabled versioning within their storage buckets. This is particularly useful to identify potential risk areas where data loss could occur due to overwriting or deleting of files.

select
  name,
  location,
  versioning_enabled
from
  gcp_storage_bucket
where
  not versioning_enabled;

select
  name,
  location,
  versioning_enabled
from
  gcp_storage_bucket
where
  versioning_enabled is not 1;

List of members and their associated iam roles for the bucket

Discover the segments that illustrate the relationship between members and their respective roles for a specific storage bucket in GCP. This could be useful in assessing access permissions and managing security within your cloud storage environment.

select
  name,
  location,
  p -> 'members' as member,
  p ->> 'role' as role
from
  gcp_storage_bucket,
  jsonb_array_elements(iam_policy -> 'bindings') as p;

select
  name,
  location,
  json_extract(p.value, '$.members') as member,
  json_extract(p.value, '$.role') as role
from
  gcp_storage_bucket,
  json_each(iam_policy, '$.bindings') as p;

Lifecycle rule of each storage bucket

Explore the lifecycle rules of your storage buckets to understand how they're configured. This can help in managing resources more effectively by determining when certain actions, such as transitioning to a different storage class or deleting objects, are set to occur.

select
  name,
  p -> 'action' ->> 'storageClass' as storage_class,
  p -> 'action' ->> 'type' as action_type,
  p -> 'condition' ->> 'age' as age_in_days
from
  gcp_storage_bucket,
  jsonb_array_elements(lifecycle_rules) as p;

select
  name,
  json_extract(p.value, '$.action.storageClass') as storage_class,
  json_extract(p.value, '$.action.type') as action_type,
  json_extract(p.value, '$.condition.age') as age_in_days
from
  gcp_storage_bucket,
  json_each(lifecycle_rules) as p;

List of storage buckets whose retention period is less than 7 days

Explore which storage buckets have a retention period of less than a week. This can be useful in identifying potential data loss risks due to short retention periods.

select
  name,
  retention_policy ->> 'retentionPeriod' as retention_period
from
  gcp_storage_bucket
where
  retention_policy ->> 'retentionPeriod' < 604800 :: text;

select
  name,
  json_extract(retention_policy, '$.retentionPeriod') as retention_period
from
  gcp_storage_bucket
where
  cast(
    json_extract(retention_policy, '$.retentionPeriod') as integer
  ) < 604800;

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- gcp

You can pass the configuration to the command with the --config argument:

steampipe_export_gcp --config '<your_config>' gcp_storage_bucket