Table: gcp_service_account - Query Google Cloud Platform Service Accounts using SQL

A Service Account in Google Cloud Platform is a special type of account used by an application or a virtual machine (VM) instance, not a person. Applications use service accounts to make authorized API calls, authorized as either the service account itself, or as G Suite or Cloud Identity users through domain-wide delegation. These accounts can be created and managed by users, and they are tied to the lifecycle of the project in which they are created.

Table Usage Guide

The gcp_service_account table provides insights into Service Accounts within Google Cloud Platform. As a security engineer, explore service account-specific details through this table, including permissions, roles, and associated metadata. Utilize it to uncover information about service accounts, such as those with excessive permissions, the roles assigned to each service account, and the verification of security configurations.

Examples

List of email ids associated with the service account

Explore which email IDs are linked to your service account to maintain a clear record of associated users. This can be particularly useful for managing access permissions and auditing user activities.

select
  display_name,
  name as service_account,
  email
from
  gcp_service_account;

select
  display_name,
  name as service_account,
  email
from
  gcp_service_account;

Find service accounts with policies that grant public access

Determine the areas in which service accounts have policies allowing public access. This is crucial for analyzing potential security risks and ensuring that sensitive data is not exposed to unauthorized users.

select
  name,
  split_part(s ->> 'role', '/', 2) as role,
  entity
from
  gcp_service_account,
  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_service_account 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

Control examples

Schema for gcp_service_account

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
description	text		A user-specified, human-readable description of the service account. The maximum length is 256 UTF-8 bytes.
disabled	boolean		Specifies whether the service is account is disabled, or not.
display_name	text		A user-specified, human-readable name for the service account. The maximum length is 100 UTF-8 bytes. Optional
email	text		The email address of the service account.
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`.
location	text		The GCP multi-region, region, or zone in which the resource is located.
name	text	=	The resource name of the service account
oauth2_client_id	text		The OAuth 2.0 client ID for the service account.
project	text		The GCP Project in which the resource is located.
title	text		Title of the resource.
unique_id	text		The unique, stable numeric ID for the service account. Each service account retains its unique ID even if you delete the service account.

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_service_account