steampipe plugin install awssteampipe plugin install aws

Table: aws_iam_role - Query AWS Identity and Access Management (IAM) Roles using SQL

The AWS Identity and Access Management (IAM) Roles are a feature of your AWS account that you can use to delegate permissions to AWS services or users. They enable trusted entities to carry out operations on your behalf, without sharing your root user credentials. Using IAM Roles, one can define a set of permissions to access the resources that a user or service needs to perform tasks.

Table Usage Guide

The aws_iam_role table in Steampipe provides you with information about IAM roles within AWS Identity and Access Management (IAM). This table allows you, as a DevOps engineer, to query role-specific details, including permissions, trust policies, and associated metadata. You can utilize this table to gather insights on roles, such as roles with wildcard permissions, trust relationships between roles, verification of trust policies, and more. The schema outlines the various attributes of the IAM role for you, including the role ARN, creation date, attached policies, and associated tags.

Examples

List IAM roles that have an inline policy.

Determine the areas in which AWS IAM roles have an inline policy configured. This can be useful to assess potential security risks, as inline policies can grant additional permissions to roles.

select
  name,
  create_date
from
  aws_iam_role
where
  inline_policies is not null;

select
  name,
  create_date
from
  aws_iam_role
where
  inline_policies is not null;

List the attached policies for each IAM role.

Identify the policies associated with each Identity Access Management (IAM) role. This helps in understanding the permissions and access rights granted to each role, aiding in security and access management.

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;

Error: The corresponding SQLite query is unavailable.

List IAM roles with their associated permission boundaries.

Discover the segments that have IAM roles and the associated permission boundaries. This is useful for understanding the limitations and permissions associated with each role in your AWS environment.

select
  name,
  description,
  permissions_boundary_arn,
  permissions_boundary_type
from
  aws_iam_role;

select
  name,
  description,
  permissions_boundary_arn,
  permissions_boundary_type
from
  aws_iam_role;

List IAM roles that have policies allowing all (*) actions.

Identify instances where IAM roles have policies that permit all actions. This can be useful in auditing security settings to ensure that no roles have overly broad permissions, which could pose a security risk.Use this query to identify which AWS IAM roles and their respective policies allow all actions, in order to assess potential security concerns.

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;

select
  r.name as role_name,
  p.name as policy_name
from
  aws_iam_role as r,
  aws_iam_policy as p,
  json_each(r.attached_policy_arns) as policy_arn,
  json_each(p.policy_std) as stmt,
  json_each(stmt.value) as action
where
  policy_arn = p.arn
  and json_extract(stmt.value, '$.Effect') = 'Allow'
  and action.value = '*'
order by
  r.name;

Find all iam policy actions with wildcards for a given role.

Discover the segments within your IAM policy that contain wildcard actions for a specific role. This is useful in identifying potential security risks, as these wildcard actions can grant broader permissions than intended.

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

select
  r.name as role_name,
  p.name as policy_name,
  json_extract(stmt.value, '$.Sid') as statement,
  action.value as action
from
  aws_iam_role as r
  join json_each(r.attached_policy_arns) as policy_arn
  join aws_iam_policy as p
  join json_each(p.policy_std) as stmt
  join json_each(json_extract(stmt.value, '$.Action')) as action
where
  r.name = 'owner'
  and policy_arn.value = p.arn
  and (
    action.value like '%*%'
    or action.value like '%?%'
  );

Identify actions that grant elevated privileges to a specific IAM role.

Determine the areas in which specific IAM roles are granted elevated privileges. This is useful for auditing security measures and ensuring that roles do not have unnecessary access rights.

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;

select
  r.name,
  a.action,
  a.access_level,
  a.description
from
  aws_iam_role as r,
  json_each(r.attached_policy_arns) as pol_arn,
  aws_iam_policy as p,
  json_each(json_extract(p.policy_std, '$.Statement')) as stmt,
  json_each(json_extract(stmt.value, '$.Action')) as action_glob,
  aws_iam_action as a
where
  pol_arn.value = p.arn
  and json_extract(stmt.value, '$.Effect') = 'Allow'
  and r.name = 'AWSServiceRoleForRDS'
  and a.action glob action_glob.value
  and a.access_level not in ('List', 'Read')
order by
  a.action;

Compare permission actions between two roles.

Analyze the differences in permission actions between two specific roles in an AWS environment. This is beneficial for understanding discrepancies in access levels, which is essential for maintaining security and proper role allocation.

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;

Error: SQLite does not support JSON operations equivalent to jsonb_array_elements_text
and glob function in the given PostgreSQL query.

Identify roles using wildcard principals in their trust policy and those roles trusted by them.

Determine the areas in which roles are using wildcard principals in their trust policy and identify those roles that are trusted by them. This is particularly useful for assessing security vulnerabilities and understanding the levels of access within your AWS environment.

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;

select
  maintenance.name,
  admin.name,
  json_pretty(maintenance_stmt),
  json_pretty(admin_stmt)
from
  aws_account as a,
  aws_iam_role as maintenance,
  aws_iam_role as admin,
  json_each(maintenance.assume_role_policy_std, '$.Statement') as maintenance_stmt,
  json_each(admin.assume_role_policy_std, '$.Statement') as admin_stmt
where
  json_extract(maintenance_stmt.value, '$.Principal.AWS') = '*'
  and json_extract(
    maintenance_stmt.value,
    '$.Condition.StringEquals."aws:principalorgid"'
  ) = a.organization_id
  and json_extract(admin_stmt.value, '$.Principal.AWS') = maintenance.arn;

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

Determine the areas in which certain roles may potentially allow other users to circumvent their assigned IAM permissions. This query is useful for identifying potential security risks and ensuring that permissions are correctly assigned within your AWS environment.

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/%'

select
  r.name,
  json_extract(r.assume_role_policy_std, '$.Statement') as stmt
from
  aws_iam_role as r,
  json_each(r.assume_role_policy_std, '$.Statement'),
  json_each(json_extract(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

This query is designed to assess the trust policy of a role when interacting with GitHub Actions. It helps identify any roles that may be missing specific condition checks, providing a valuable tool for enhancing security and ensuring proper configuration.

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

Error: The corresponding SQLite query is unavailable.

Query examples

Control examples

Schema for aws_iam_role

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
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_document	text		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.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
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.

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_role