turbot/guardrails
steampipe plugin install guardrails

Table: guardrails_resource - Query Guardrails Resources using SQL

Guardrails is a service that provides a unified view of the security, compliance, and operational health of your cloud infrastructure. It enables you to continuously monitor and manage your cloud resources across multiple cloud platforms, including AWS, Azure, and Google Cloud. Guardrails helps you identify potential risks and vulnerabilities, and take appropriate actions to mitigate them.

Table Usage Guide

The guardrails_resource table provides insights into the resources monitored by Guardrails. As a cloud security engineer, you can use this table to explore resource-specific details, including their current compliance status, potential risks, and associated metadata. This table is beneficial for identifying non-compliant resources, understanding the security posture of your cloud infrastructure, and taking necessary actions to ensure compliance and mitigate risks.

Important Notes

  • When querying this table, we recommend using at least one of these columns (usually in the where clause):
    • id
    • resource_type_id
    • resource_type_uri
    • filter

Examples

List all AWS IAM Roles

Explore the different roles within your AWS IAM setup to understand their configurations and creation times. This can aid in managing access controls and ensuring optimal security practices.

select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role';
select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role';

List all S3 buckets with a given Owner tag

Explore which S3 buckets are associated with a specific owner. This is useful to manage and track resources based on ownership within an AWS environment.

select
id,
title,
tags
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-s3#/resource/types/bucket'
and tags ->> 'Owner' = 'Jane';
select
id,
title,
tags
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-s3#/resource/types/bucket'
and json_extract(tags, '$.Owner') = 'Jane';

Get a specific resource by ID

Determine the details of a specific resource using its unique identifier. This can be particularly useful when you need to quickly access and review the specifics of a resource in your system.

select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
id = 216005088871602;
select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
id = 216005088871602;

Filter for resources using Turbot filter syntax

Analyze the distribution of resources based on their type to understand the prevalence of specific resource types in your AWS IAM configuration. This query is particularly useful in identifying and managing resource types that are heavily utilized.

select
resource_type_uri,
count(*)
from
guardrails_resource
where
filter = 'resourceTypeId:"tmod:@turbot/aws-iam#/resource/types/iam"'
group by
resource_type_uri
order by
count desc;
select
resource_type_uri,
count(*)
from
guardrails_resource
where
filter = 'resourceTypeId:"tmod:@turbot/aws-iam#/resource/types/iam"'
group by
resource_type_uri
order by
count(*) desc;

Search for AWS IAM Roles by name (Turbot side)

Explore which AWS IAM roles have 'admin' access to better understand and manage permissions within your AWS environment. This can help in enhancing security by identifying potential areas of risk. This query will ask Turbot to filter the resources down to the given filter, limiting the results by name.

select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role'
and filter = 'admin';
select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role'
and filter = 'admin';

Search for AWS IAM Roles by name (Steampipe side)

Discover the segments that include AWS IAM roles with a specific name. This is valuable for identifying roles that may have overly broad or administrative permissions, helping to enhance security and compliance management. This query gathers all the AWS IAM roles from Turbot and then uses Postgres level filters to limit the results.

select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role'
and title ilike '%admin%';
select
id,
title,
create_timestamp,
metadata,
data
from
guardrails_resource
where
resource_type_uri = 'tmod:@turbot/aws-iam#/resource/types/role'
and title like '%admin%';

Search for console logins within 7 days

Determine the areas in which console logins have occurred within the past week. This can help in monitoring user activity and identifying any unusual login patterns for enhanced security.

select
id,
title,
data ->> 'email' as email,
array_to_string(
regexp_matches(trunk_title, '^Turbot > (.*) >'),
' '
) as "directory",
trunk_title,
to_char(
(data ->> 'lastLoginTimestamp') :: timestamp,
'YYYY-MM-DD HH24:MI'
) as "last_login"
from
guardrails_resource
where
filter = 'resourceTypeId:"tmod:@turbot/turbot-iam#/resource/types/profile" $.lastLoginTimestamp:>=T-7d';
Error: SQLite does not support array_to_string
and regexp_matches functions.

Search for resources created within 7 days, join with count of controls in alarm state

Explore resources created within the past week and assess the number of controls in an alarm state. This is useful for monitoring new resources and their potential risks.

select
r.id,
r.title,
r.trunk_title,
r.resource_type_uri,
to_char(r.create_timestamp, 'YYYY-MM-DD HH24:MI') as create_timestamp,
count(c.*) as alarm_count
from
guardrails_resource as r
left join guardrails_control as c on r.id = c.resource_id
and c.state = 'alarm'
where
r.filter = 'notificationType:resource timestamp:>=T-7d'
group by
r.id,
r.title,
r.trunk_title,
r.resource_type_uri,
r.create_timestamp
order by
r.create_timestamp desc;
Error: The corresponding SQLite query is unavailable.

Extract all resources from Turbot Guardrails

Explore the full range of resources available in Turbot Guardrails to gain a comprehensive understanding of the scope of your resources and their management. This is beneficial for assessing overall resource utilization and identifying areas for improvement. WARNING - This is a large query and may take minutes to run. It is not recommended and may timeout. It's included here as a reference for those who need to extract all data.

select
*
from
guardrails_resource;
select
*
from
guardrails_resource;

Schema for guardrails_resource

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
akasjsonbAKA (also known as) identifiers for the resource.
create_timestamptimestamp with time zoneWhen the resource was first discovered by Turbot. (It may have been created earlier.)
datajsonbResource data.
filtertext=Filter used for this resource list.
idbigint=Unique identifier of the resource.
metadatajsonbResource custom metadata.
objectjsonbExtended Resource data.
parent_idbigintID for the parent of this resource. For the Turbot root resource this is null.
pathjsonbHierarchy path with all identifiers of ancestors of the resource.
resource_type_idbigint=ID of the resource type for this resource.
resource_type_uritext=URI of the resource type for this resource.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbTags for the resource.
timestamptimestamp with time zoneTimestamp when the resource was last modified (created, updated or deleted).
titletextTitle of the resource.
trunk_titletextTitle with full path of the resource.
update_timestamptimestamp with time zoneWhen the resource was last updated in Turbot.
version_idbigintUnique identifier for this version of the resource.
workspacetextSpecifies the workspace URL.

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)" -- guardrails

You can pass the configuration to the command with the --config argument:

steampipe_export_guardrails --config '<your_config>' guardrails_resource