Table: gcp_pubsub_subscription - Query Google Cloud Pub/Sub Subscriptions using SQL

Google Cloud Pub/Sub is a messaging service that allows you to send and receive messages between independent applications. Subscriptions in Pub/Sub represent a pipeline from a topic to a receiving entity. They allow the receiving entity to receive messages from a topic, ensuring reliable delivery of the messages.

Table Usage Guide

The gcp_pubsub_subscription table provides insights into Pub/Sub subscriptions within Google Cloud Platform. As a developer or system administrator, explore subscription-specific details through this table, including associated topics, acknowledgement deadlines, and message retention policies. Utilize it to monitor the configuration and status of your Pub/Sub subscriptions, ensuring reliable message delivery between your applications.

Examples

List of pubsub subscriptions which are not configured with dead letter topic

Determine the areas in which pubsub subscriptions are not configured with a dead letter topic, allowing you to pinpoint potential issues in the message delivery process.

select
  name,
  topic_name,
  dead_letter_policy_topic
from
  gcp_pubsub_subscription
where
  dead_letter_policy_topic is null;

select
  name,
  topic_name,
  dead_letter_policy_topic
from
  gcp_pubsub_subscription
where
  dead_letter_policy_topic is null;

Message configuration details for the subscriptions

Analyze the settings to understand the configuration of your message subscriptions, including message retention duration and delivery attempts. This can help you optimize your message delivery and retention processes for better resource management and efficiency.

select
  name,
  topic_name,
  ack_deadline_seconds,
  message_retention_duration,
  retain_acked_messages,
  dead_letter_policy_topic,
  dead_letter_policy_max_delivery_attempts,
  enable_message_ordering
from
  gcp_pubsub_subscription;

select
  name,
  topic_name,
  ack_deadline_seconds,
  message_retention_duration,
  retain_acked_messages,
  dead_letter_policy_topic,
  dead_letter_policy_max_delivery_attempts,
  enable_message_ordering
from
  gcp_pubsub_subscription;

Query examples

Schema for gcp_pubsub_subscription

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
ack_deadline_seconds	bigint		The approximate amount of time (on a best-effort basis) Pub/Sub waits for the subscriber to acknowledge receipt before resending the message.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
dead_letter_policy_max_delivery_attempts	bigint		The maximum number of delivery attempts for any message. The value must be between 5 and 100.
dead_letter_policy_topic	text		The name of the topic to which dead letter messages should be published.
enable_message_ordering	boolean		If true, messages published with the same `ordering_key` in `PubsubMessage` will be delivered to the subscribers in the order in which they are received by the Pub/Sub system. Otherwise, they may be delivered in any order.
expiration_policy_ttl	text		Specifies the "time-to-live" duration for an associated resource. The resource expires if it is not active for a period of `ttl`. The definition of "activity" depends on the type of the associated resource. The minimum and maximum allowed values for `ttl` depend on the type of the associated resource, as well. If `ttl` is not set, the associated resource never expires.
filter	text		An expression written in the Pub/Sub [filter language](https://cloud.google.com/pubsub/docs/filtering). If non-empty, then only `PubsubMessage`s whose `attributes` field matches the filter are delivered on this subscription. If empty, then no messages are filtered out.
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`.
labels	jsonb		A set of labels attached with the subscription.
location	text		The GCP multi-region, region, or zone in which the resource is located.
message_retention_duration	text		How long to retain unacknowledged messages in the subscription's backlog, from the moment a message is published. If `retain_acked_messages` is true, then this also configures the retention of acknowledged messages, and thus configures how far back in time a `Seek` can be done. Defaults to 7 days. Cannot be more than 7 days or less than 10 minutes.
name	text	=	The name of the subscription.
project	text		The GCP Project in which the resource is located.
push_config_attributes	jsonb		Endpoint configuration attributes that can be used to control different aspects of the message delivery. The only currently supported attribute is "x-goog-version". This attribute indicates the version of the data expected by the endpoint. This controls the shape of the pushed message (i.e., its fields and metadata).
push_config_endpoint	text		A URL locating the endpoint to which messages should be pushed. For example, a Webhook endpoint might use `https://example.com/push`
push_config_oidc_token_audience	text		Audience to be used when generating OIDC token. The audience claim identifies the recipients that the JWT is intended for. The audience value is a single case-sensitive string.
push_config_oidc_token_service_account_email	text		Service account email to be used for generating the OIDC token.
retain_acked_messages	boolean		Indicates whether to retain acknowledged messages. If true, then messages are not expunged from the subscription's backlog, even if they are acknowledged, until they fall out of the `message_retention_duration` window.
retry_policy_maximum_backoff	text		The maximum delay between consecutive deliveries of a given message. Value should be between 0 and 600 seconds. Defaults to 600 seconds.
retry_policy_minimum_backoff	text		The minimum delay between consecutive deliveries of a given message. Value should be between 0 and 600 seconds. Defaults to 10 seconds.
self_link	text		Server-defined URL for the resource.
tags	jsonb		A map of tags for the resource.
title	text		Title of the resource.
topic	text		The name of the topic from which this subscription is receiving messages.
topic_name	text		The name of the topic from which this subscription is receiving messages.

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_subscription