Control: BigQuery tables should not have prohibited labels

Description

Check if BigQuery tables have any prohibited labels.

Usage

Run the control in your terminal:

powerpipe control run gcp_labels.control.bigquery_table_prohibited

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run gcp_labels.control.bigquery_table_prohibited --share

Steampipe Tables

gcp_bigquery_table

Params

Args	Name	Default	Description	Variable
$1	prohibited_labels	`["Password","Key"]`		prohibited_labels

SQL

with analysis as (
  select
    self_link,
    array_agg(k) as prohibited_labels
  from
    gcp_bigquery_table,
    jsonb_object_keys(labels) as k,
    unnest($1::text[]) as prohibited_key
  where
    k = prohibited_key
  group by
    self_link
)
select
  r.self_link as resource,
  case
    when a.prohibited_labels <> array[]::text[] then 'alarm'
    else 'ok'
  end as status,
  case
    when a.prohibited_labels <> array[]::text[] then r.title || ' has prohibited labels: ' || array_to_string(a.prohibited_labels, ', ') || '.'
    else r.title || ' has no prohibited labels.'
  end as reason,
  r.location, r.project
from
  gcp_bigquery_table as r
full outer join
  analysis as a on a.self_link = r.self_link