steampipe plugin install awssteampipe plugin install aws

Table: aws_iam_role

An IAM role is an AWS Identity and Access Management (IAM) entity with permissions to make AWS service requests.

Examples

List of IAM roles with no inline policy

select
  name,
  create_date
from
  aws_iam_role
where
  inline_policies is null;

List the policies attached to the roles

select
  name,
  description,
  split_part(policy, '/', 3) as attached_policy
from
  aws_iam_role
  cross join jsonb_array_elements_text(attached_policy_arns) as policy;

Permission boundary information for each role

select
  name,
  description,
  permissions_boundary_arn,
  permissions_boundary_type
from
  aws_iam_role;

Find all roles that allow *

select
  r.name as role_name,
  p.name as policy_name
from
  aws_iam_role as r,
  jsonb_array_elements_text(r.attached_policy_arns) as policy_arn,
  aws_iam_policy as p,
  jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
  jsonb_array_elements_text(stmt -> 'Action') as action
where
  policy_arn = p.arn
  and stmt ->> 'Effect' = 'Allow'
  and action = '*'
order by
  r.name;

Find any roles that allow wildcard actions

select
  r.name as role_name,
  p.name as policy_name,
  stmt ->> 'Sid' as statement,
  action
from
  aws_iam_role as r,
  jsonb_array_elements_text(r.attached_policy_arns) as policy_arn,
  aws_iam_policy as p,
  jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
  jsonb_array_elements_text(stmt -> 'Action') as action
where
  r.name = 'owner'
  and policy_arn = p.arn
  and (
    action like '%*%'
    or action like '%?%'
  );

List higher-level permissions for any specific role

select
  r.name,
  a.action,
  a.access_level,
  a.description
from
  aws_iam_role as r,
  jsonb_array_elements_text(r.attached_policy_arns) as pol_arn,
  aws_iam_policy as 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 as a on a.action like action_regex
where
  pol_arn = p.arn
  and stmt ->> 'Effect' = 'Allow'
  and r.name = 'AWSServiceRoleForRDS'
  and access_level not in ('List', 'Read')
order by
  action;

List all actions (with level) in role2, not in role1

with roles as (
  select
    name,
    attached_policy_arns
  from
    aws_iam_role
  where
    name in ('AWSServiceRoleForSSO', 'AWSServiceRoleForRDS')
),
policies as (
  select
    name,
    arn,
    policy_std
  from
    aws_iam_policy
),
role1_permissions as (
  select
    r.name,
    a.action,
    a.access_level,
    a.description
  from
    roles as r,
    jsonb_array_elements_text(r.attached_policy_arns) as pol_arn,
    policies as 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
    pol_arn = p.arn
    and stmt ->> 'Effect' = 'Allow'
    and r.name = 'AWSServiceRoleForSSO'
),
role2_permissions as (
  select
    r.name,
    a.action,
    a.access_level,
    a.description
  from
    roles as r,
    jsonb_array_elements_text(r.attached_policy_arns) as pol_arn,
    policies as 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
    pol_arn = p.arn
    and stmt ->> 'Effect' = 'Allow'
    and r.name = 'AWSServiceRoleForRDS'
)
select
  *
from
  role2_permissions
where
  action not in (
    select
      action
    from
      role1_permissions
  )
order by
  action;

List role with wildcard principal in trust policy(maintenance-role) and role(admin-role) that have trust relationship with maintenance-role

Refer here

select
  maintenance.name,
  admin.name,
  jsonb_pretty(maintenance_stmt),
  jsonb_pretty(admin_stmt)
from
  -- use the account to get the organization_id
  aws_account as a,
  -- check any role as the "maintenance-role"
  aws_iam_role as maintenance,
  -- Combine via join with any role as the "admin-role"
  aws_iam_role as admin,
  jsonb_array_elements(maintenance.assume_role_policy_std -> 'Statement') as maintenance_stmt,
  jsonb_array_elements(admin.assume_role_policy_std -> 'Statement') as admin_stmt
where
  -- maintenance role can be assumed by any AWS principal
  maintenance_stmt -> 'Principal' -> 'AWS' ? '*' -- maintenance role principal must be in same account
  and maintenance_stmt -> 'Condition' -> 'StringEquals' -> 'aws:principalorgid' ? a.organization_id -- admin role specifically allow maintenance role
  and admin_stmt -> 'Principal' -> 'AWS' ? maintenance.arn;

List the roles that might allow other roles/users to bypass their assigned IAM permissions.

select
  r.name,
  stmt
from
  aws_iam_role as r,
  jsonb_array_elements(r.assume_role_policy_std -> 'Statement') as stmt,
  jsonb_array_elements_text(stmt -> 'Principal' -> 'AWS') as trust
where
  trust = '*'
  or trust like 'arn:aws:iam::%:role/%'

Verify the Trust policy of Role has validation conditions when used with GitHub Actions

select
  iam.arn as resource,
  iam.description,
  iam.assume_role_policy_std,
  case
    when pstatement -> 'Condition' -> 'StringLike' -> 'token.actions.githubusercontent.com:sub' is not null
    or pstatement -> 'Condition' -> 'StringEquals' -> 'token.actions.githubusercontent.com:sub' is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when pstatement -> 'Condition' -> 'StringLike' -> 'token.actions.githubusercontent.com:sub' is not null
    or pstatement -> 'Condition' -> 'StringEquals' -> 'token.actions.githubusercontent.com:sub' is not null then iam.arn || ' Condition Check Exists'
    else iam.arn || ' Missing Condition Check'
  end as reason
from
  aws_iam_role as iam,
  jsonb_array_elements(iam.assume_role_policy_std -> 'Statement') as pstatement
where
  pstatement -> 'Action' ? & array [ 'sts:assumerolewithwebidentity' ]
  and (pstatement -> 'Principal' -> 'Federated') :: text like '%token.actions.githubusercontent.com%'
order by
  status asc

Query examples

Control examples

.inspect aws_iam_role

AWS IAM Role

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 role.
assume_role_policy	jsonb	The policy that grants an entity permission to assume the role.
assume_role_policy_std	jsonb	Contains the assume role policy in a canonical form for easier searching.
attached_policy_arns	jsonb	A list of managed policies attached to the role.
create_date	timestamp with time zone	The date and time when the role was created.
description	text	A user-provided description of the role.
inline_policies	jsonb	A list of policy documents that are embedded as inline policies for the role..
inline_policies_std	jsonb	Inline policies in canonical form for the role.
instance_profile_arns	jsonb	A list of instance profiles associated with the role.
max_session_duration	bigint	The maximum session duration (in seconds) for the specified role. Anyone who uses the AWS CLI, or API to assume the role can specify the duration using the optional DurationSeconds API parameter or duration-seconds CLI parameter.
name	text	The friendly name that identifies the role.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
path	text	The path to the role.
permissions_boundary_arn	text	The ARN of the policy used to set the permissions boundary for the role.
permissions_boundary_type	text	The permissions boundary usage type that indicates what type of IAM resource is used as the permissions boundary for an entity. This data type can only have a value of Policy.
region	text	The AWS Region in which the resource is located.
role_id	text	The stable and unique string identifying the role.
role_last_used_date	timestamp with time zone	Contains information about the last time that an IAM role was used. Activity is only reported for the trailing 400 days. This period can be shorter if your Region began supporting these features within the last year. The role might have been used more than 400 days ago.
role_last_used_region	text	Contains the region in which the IAM role was used.
tags	jsonb	A map of tags for the resource.
tags_src	jsonb	A list of tags that are attached to the role.
title	text	Title of the resource.

turbot/aws

Table: aws_iam_role

Examples

List of IAM roles with no inline policy

List the policies attached to the roles

Permission boundary information for each role

Find all roles that allow *

Find any roles that allow wildcard actions

List higher-level permissions for any specific role

List all actions (with level) in role2, not in role1

List role with wildcard principal in trust policy(maintenance-role) and role(admin-role) that have trust relationship with maintenance-role

List the roles that might allow other roles/users to bypass their assigned IAM permissions.

Verify the Trust policy of Role has validation conditions when used with GitHub Actions

Query examples

Control examples

.inspect aws_iam_role

Steampipe

Resources

Community