steampipe plugin install awssteampipe plugin install aws

Table: aws_iam_policy

An IAM Policy is an AWS Identity and Access Management (IAM) Managed Policy

Note that 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

select
  name,
  arn
from
  aws_iam_policy
where
  not is_aws_managed;

List customer-defined policies with a path prefix

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

Find attached customer-managed policies

select
  name,
  arn,
  permissions_boundary_usage_count
from
  aws_iam_policy
where
  is_attached;

Find unused customer-managed policies

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

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

Find policy statements that grant service level full access

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 '%:*'
  );

Expand wildcards to list all actions granted by a policy

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;

Query examples

.inspect aws_iam_policy

AWS IAM Policy

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
account_id	text	The AWS Account ID in which the resource is located.
akas	jsonb	Array of globally unique identifier strings (also known as) for the resource.
arn	text	The Amazon Resource Name (ARN) specifying the iam policy.
attachment_count	bigint	The number of entities (users, groups, and roles) that the policy is attached to.
create_date	timestamp with time zone	The date and time, when the policy was created.
default_version_id	text	The identifier for the version of the policy that is set as the default version.
is_attachable	boolean	Specifies whether the policy can be attached to an IAM user, group, or role.
is_attached	boolean	Specifies whether the policy is attached to at least one IAM user, group, or role.
is_aws_managed	boolean	Specifies whether the policy is AWS Managed or Customer Managed. If true policy is aws managed otherwise customer managed.
name	text	The friendly name that identifies the iam policy.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
path	text	The path to the policy.
permissions_boundary_usage_count	bigint	The number of entities (users and roles) for which the policy is used to set the permissions boundary.
policy	jsonb	Contains the details about the policy.
policy_id	text	The stable and unique string identifying the policy.
policy_std	jsonb	Contains the policy in a canonical form for easier searching.
region	text	The AWS Region in which the resource is located.
tags	jsonb	A map of tags for the resource.
tags_src	jsonb	A list of tags attached with the IAM policy.
title	text	Title of the resource.
update_date	timestamp with time zone	The date and time, when the policy was last updated.

turbot/aws