steampipe plugin install aws

Table: aws_iam_policy - Query AWS IAM Policy using SQL

The AWS Identity and Access Management (IAM) Policy is a resource that allows you to manage permissions and control access to AWS services and resources. With IAM policies, you can specify who is allowed and denied access, and what actions they can or cannot perform. These policies help you secure your AWS resources, ensure compliance with your security policies, and manage access across your entire AWS environment.

Table Usage Guide

The aws_iam_policy table in Steampipe provides you with information about IAM policies within AWS Identity and Access Management (IAM). This table allows you, as a DevOps engineer, to query policy-specific details, including permissions, attachments, and associated metadata. You can utilize this table to gather insights on policies, such as policies with wildcard permissions, verification of policy documents, and more. The schema outlines the various attributes of the IAM policy for you, including the policy ARN, creation date, update date, attached entities, and policy default version ID.

Important Notes

  • The policy and policy_std columns require additional calls - You can greatly decrease your query time by NOT selecting those columns when you don't need them.

Examples

List customer-defined policies

Determine the areas in which custom policies, as defined by the user, are implemented within the AWS IAM service. This query is useful for auditing security measures and ensuring that AWS resources are governed by appropriate, user-defined policies.

select
name,
arn
from
aws_iam_policy
where
not is_aws_managed;
select
name,
arn
from
aws_iam_policy
where
is_aws_managed = 0;

List customer-defined policies with a path prefix

Explore the custom policies within a specific path prefix to understand their names and resources, which is particularly beneficial for managing and organizing security controls in a streamlined manner. This allows for efficient monitoring and modification of policies that are not managed by AWS, hence offering enhanced control over your security infrastructure.

select
name,
arn
from
aws_iam_policy
where
not is_aws_managed
and path = '/turbot/';
select
name,
arn
from
aws_iam_policy
where
not is_aws_managed
and path = '/turbot/';

Find attached customer-managed policies

Discover the segments that are utilizing customer-managed policies within your AWS environment. This allows you to better manage your resources and understand which policies are attached, enhancing overall security and governance.

select
name,
arn,
permissions_boundary_usage_count
from
aws_iam_policy
where
is_attached;
select
name,
arn,
permissions_boundary_usage_count
from
aws_iam_policy
where
is_attached = 1;

Find unused customer-managed policies

Determine the areas in which customer-managed policies are not being utilized. This is beneficial in identifying potential areas of cost reduction and improving security by eliminating unnecessary permissions.

select
name,
attachment_count,
permissions_boundary_usage_count
from
aws_iam_policy
where
not is_aws_managed
and not is_attached
and permissions_boundary_usage_count = 0;
select
name,
attachment_count,
permissions_boundary_usage_count
from
aws_iam_policy
where
not is_aws_managed
and not is_attached
and permissions_boundary_usage_count = 0;

Find policy statements that grant Full Control (:) access

This example helps identify policies that potentially grant unrestricted access, allowing for a comprehensive review of security settings. It aids in enhancing security by pinpointing areas where permissions may be overly broad.

select
name,
arn,
action,
s ->> 'Effect' as effect
from
aws_iam_policy,
jsonb_array_elements(policy_std -> 'Statement') as s,
jsonb_array_elements_text(s -> 'Action') as action
where
action in ('*', '*:*')
and s ->> 'Effect' = 'Allow';
Error: The corresponding SQLite query is unavailable.

Find policy statements that grant service level full access

Explore which policy statements allow full service level access. This can be useful for maintaining security standards by identifying policies that may potentially grant excessive permissions.

select
name,
arn,
action,
s ->> 'Effect' as effect
from
aws_iam_policy,
jsonb_array_elements(policy_std -> 'Statement') as s,
jsonb_array_elements_text(s -> 'Action') as action
where
s ->> 'Effect' = 'Allow'
and (
action = '*'
or action like '%:*'
);
select
name,
arn,
json_extract(s.value, '$.Action') as action,
json_extract(s.value, '$.Effect') as effect
from
aws_iam_policy,
json_each(policy_std, 'Statement') as s
where
json_extract(s.value, '$.Effect') = 'Allow'
and (
json_extract(s.value, '$.Action') = '*'
or json_extract(s.value, '$.Action') like '%:*'
);

Expand wildcards to list all actions granted by a policy

Identify all actions permitted by a specific policy. This is particularly useful for understanding the scope of permissions given to a particular policy, thereby aiding in effective access management.

select
a.action,
a.access_level,
a.description
from
aws_iam_policy p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action_glob,
glob(action_glob) as action_regex
join aws_iam_action a ON a.action LIKE action_regex
where
p.name = 'AmazonEC2ReadOnlyAccess'
and stmt ->> 'Effect' = 'Allow'
order by
a.action;
select
a.action,
a.access_level,
a.description
from
aws_iam_policy p,
json_each(p.policy_std, '$.Statement') as stmt,
json_each(stmt.value, '$.Action') as action_glob,
glob(action_glob.value) as action_regex
join aws_iam_action a ON a.action LIKE action_regex
where
p.name = 'AmazonEC2ReadOnlyAccess'
and json_extract(stmt.value, '$.Effect') = 'Allow'
order by
a.action;

Control examples

Schema for aws_iam_policy

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntext=The Amazon Resource Name (ARN) specifying the iam policy.
attachment_countbigintThe number of entities (users, groups, and roles) that the policy is attached to.
create_datetimestamp with time zoneThe date and time, when the policy was created.
default_version_idtextThe identifier for the version of the policy that is set as the default version.
descriptiontextA friendly description of the policy.
is_attachablebooleanSpecifies whether the policy can be attached to an IAM user, group, or role.
is_attachedboolean!=, =Specifies whether the policy is attached to at least one IAM user, group, or role.
is_aws_managedboolean!=, =Specifies whether the policy is AWS Managed or Customer Managed. If true policy is aws managed otherwise customer managed.
nametextThe friendly name that identifies the iam policy.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
pathtext=The path to the policy.
permissions_boundary_usage_countbigintThe number of entities (users and roles) for which the policy is used to set the permissions boundary.
policyjsonbContains the details about the policy.
policy_idtextThe stable and unique string identifying the policy.
policy_stdjsonbContains the policy in a canonical form for easier searching.
regiontextThe AWS Region in which the resource is located.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags attached with the IAM policy.
titletextTitle of the resource.
update_datetimestamp with time zoneThe date and time, when the policy was last updated.

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

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

steampipe_export_aws --config '<your_config>' aws_iam_policy