steampipe plugin install gcp

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%';

Schema for gcp_kms_key

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
create_timetimestamp with time zoneThe time at which this CryptoKey was created.
iam_policyjsonbAn 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_nametext=The resource name for the KeyRing.
labelsjsonbLabels with user-defined metadata.
locationtext=The GCP multi-region, region, or zone in which the resource is located.
nametext=The resource name for the CryptoKey.
next_rotation_timetimestamp with time zoneAt next rotation time, the Key Management Service will automatically: 1. Create a new version of this CryptoKey. 2.Mark the new version as primary.
primaryjsonbA copy of the primary CryptoKeyVersion that will be used by Encrypt when this CryptoKey is given in EncryptRequest.name.
projecttext=, !=, ~~, ~~*, !~~, !~~*The GCP Project in which the resource is located.
purposetext!=, =The immutable purpose of this CryptoKey.
rotation_periodtext!=, =Next rotation time will be advanced by this period when the service automatically rotates a key.
self_linktextServer-defined URL for the resource.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
version_templatejsonbA 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