Table: gcp_pubsub_topic - Query GCP PubSub Topics using SQL

Google Cloud Pub/Sub is a scalable, durable event ingestion and delivery system that serves as a foundation for real-time analytics and event-driven computing systems. Pub/Sub offers at-least-once message delivery and real-time streaming through a simple and consistent API. It provides strong security and authentication, ensuring that your data is safe and only accessible to authorized services and users.

Table Usage Guide

The gcp_pubsub_topic table provides insights into PubSub Topics within Google Cloud Platform (GCP). As a DevOps engineer, explore topic-specific details through this table, including topic name, project ID, and subscription information. Utilize it to uncover information about topics, such as their configurations, the number of subscriptions, and other associated metadata.

Examples

List of pubsub topics which are not encrypted

Discover the segments that have unencrypted pubsub topics in your Google Cloud Platform. This is particularly useful for identifying potential security risks and ensuring all your data is adequately protected.

select
  name,
  kms_key_name
from
  gcp_pubsub_topic
where
  kms_key_name = '';

select
  name,
  kms_key_name
from
  gcp_pubsub_topic
where
  kms_key_name is null;

List of regions which are allowed in message storage policy for each topic

Determine the areas in which message storage policies are permitted for each topic to manage and streamline your data storage strategy effectively.

select
  name,
  jsonb_array_elements_text(
    message_storage_policy_allowed_persistence_regions
  )
from
  gcp_pubsub_topic;

select
  name,
  json_each.value
from
  gcp_pubsub_topic,
  json_each(
    message_storage_policy_allowed_persistence_regions
  );

Find topics with policies that grant public access

This query allows you to pinpoint specific topics that have policies granting public access. This can be useful for identifying potential security risks and ensuring that sensitive information is adequately protected.

select
  name,
  split_part(s ->> 'role', '/', 2) as role,
  entity
from
  gcp_pubsub_topic,
  jsonb_array_elements(iam_policy -> 'bindings') as s,
  jsonb_array_elements_text(s -> 'members') as entity
where
  entity = 'allUsers'
  or entity = 'allAuthenticatedUsers';

select
  g.name,
  substr(
    json_extract(s.value, '$.role'),
    instr(json_extract(s.value, '$.role'), '/') + 1
  ) as role,
  e.value as entity
from
  gcp_pubsub_topic g,
  json_each(json_extract(g.iam_policy, '$.bindings')) as s,
  json_each(json_extract(s.value, '$.members')) as e
where
  e.value = 'allUsers'
  or e.value = 'allAuthenticatedUsers';

Query examples

Schema for gcp_pubsub_topic

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
iam_policy	jsonb		An Identity and Access Management (IAM) policy, which specifies access controls for Google Cloud resources. A `Policy` is a collection of `bindings`. A `binding` binds one or more `members` to a single `role`. Members can be user accounts, service accounts, Google groups, and domains (such as G Suite). A `role` is a named list of permissions; each `role` can be an IAM predefined role or a user-created custom role. For some types of Google Cloud resources, a `binding` can also specify a `condition`, which is a logical expression that allows access to a resource only if the expression evaluates to `true`.
kms_key_name	text		The resource name of the Cloud KMS CryptoKey to be used to protect access to messages published on this topic.
labels	jsonb		A set of labels attached with the topic.
location	text		The GCP multi-region, region, or zone in which the resource is located.
message_storage_policy_allowed_persistence_regions	jsonb		Policy constraining the set of Google Cloud Platform regions where messages published to the topic may be stored. If not present, then no constraints are in effect.
name	text	=	The name of the topic.
project	text	=, !=, ~~, ~~, !~~, !~~	The GCP Project in which the resource is located.
self_link	text		Server-defined URL for the resource.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tags	jsonb		A map of tags for the resource.
title	text		Title of the resource.

Export

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_pubsub_topic