Table: gcp_kms_key - Query Google Cloud KMS Keys using SQL

Google Cloud Key Management Service (KMS) is a cloud service for managing encryption keys on Google Cloud. This service allows you to generate, use, rotate, and destroy AES256, RSA 2048, RSA 3072, RSA 4096, EC P256, and EC P384 cryptographic keys. KMS is integrated with Cloud IAM and Cloud Audit Logging so that you can manage permissions on individual keys and monitor how these are used.

Table Usage Guide

The gcp_kms_key table provides insights into the cryptographic keys managed by Google Cloud KMS. As a security engineer, you can explore key-specific details through this table, including key versions, key state, and associated metadata. Utilize it to uncover information about key usage, rotation schedule, and the verification of key permissions.

Examples

Basic info

Explore which cryptographic keys in the Google Cloud Platform have been created and their rotation periods. This can be useful in managing the lifecycle of your keys and ensuring regular key rotation for enhanced security.

select
  name,
  create_time,
  rotation_period
from
  gcp_kms_key;

select
  name,
  create_time,
  rotation_period
from
  gcp_kms_key;

List keys older than 30 days

Explore which security keys have been in use for more than a month. This can help in maintaining security standards by regularly updating the keys.

select
  name,
  create_time,
  rotation_period
from
  gcp_kms_key
where
  create_time <= (current_date - interval '30' day)
order by
  create_time;

select
  name,
  create_time,
  rotation_period
from
  gcp_kms_key
where
  date(create_time) <= date('now', '-30 day')
order by
  create_time;

List keys with rotation period greater than 90 days (7776000 seconds)

Determine the areas in which encryption keys have a rotation period exceeding 90 days, a parameter that may be relevant for assessing the security measures in place within your GCP environment.

select
  name,
  create_time,
  rotation_period
from
  gcp_kms_key
where
  split_part(rotation_period, 's', 1) :: int > 7776000;

select
  name,
  create_time,
  rotation_period
from
  gcp_kms_key
where
  cast(
    substr(rotation_period, 1, instr(rotation_period, 's') - 1) as integer
  ) > 7776000;

List publicly accessible keys

The query helps identify any security risks by pinpointing instances where encryption keys are publicly accessible in your Google Cloud Platform. This can assist in maintaining data confidentiality and preventing unauthorized access.

select
  distinct name,
  key_ring_name,
  location
from
  gcp_kms_key,
  jsonb_array_elements(iam_policy -> 'bindings') as b
where
  b -> 'members' ?| array [ 'allAuthenticatedUsers',
  'allUsers' ];

select
  distinct k.name,
  k.key_ring_name,
  k.location
from
  gcp_kms_key k,
  json_each(k.iam_policy, '$.bindings') as b
where
  json_extract(b.value, '$.members') like '%allAuthenticatedUsers%'
  OR json_extract(b.value, '$.members') like '%allUsers%';

Query examples

Control examples

Schema for gcp_kms_key

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.
create_time	timestamp with time zone		The time at which this CryptoKey was created.
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`.
key_ring_name	text	=	The resource name for the KeyRing.
labels	jsonb		Labels with user-defined metadata.
location	text	=	The GCP multi-region, region, or zone in which the resource is located.
name	text	=	The resource name for the CryptoKey.
next_rotation_time	timestamp with time zone		At next rotation time, the Key Management Service will automatically: 1. Create a new version of this CryptoKey. 2.Mark the new version as primary.
primary	jsonb		A copy of the primary CryptoKeyVersion that will be used by Encrypt when this CryptoKey is given in EncryptRequest.name.
project	text	=, !=, ~~, ~~, !~~, !~~	The GCP Project in which the resource is located.
purpose	text	!=, =	The immutable purpose of this CryptoKey.
rotation_period	text	!=, =	Next rotation time will be advanced by this period when the service automatically rotates a key.
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.
version_template	jsonb		A template describing settings for new CryptoKeyVersion instances.

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_kms_key